Entries from March 2008

Wharton article: Biased Expectations: Can Accounting Tools Lead to, Rather than Prevent, Executive Mistakes?

Date March 20, 2008

Here’s an interesting article (with links to underlying studies) from Knowledge@Wharton summarizing two studies by Gavin Cassar.  In one, he observes that (unsuprisingly)  ”internal accounting report preparation significantly improves forecast accuracy“.  The catch is: “the accuracy benefits from internal reports preparation are only observed for firms with high uncertainty” in forecasting.
In his second study, he shows how some of [...]

Posted in Articles, Business Comments No Comments »

Get data from a cell in a closed workbook

Date March 17, 2008

There are lots of ways to get data from other workbooks. This has helped when grabbing summary data for report summaries (see here for creating an email)
Example:
p = “\some\directory”
f = “Some fake file.xls”
s = “Sheet1″
a = “A1″
result = GetValue(p, f, s, a)
Private Function GetValue(path, file, sheet, ref)
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ [...]

Posted in Excel Comments No Comments »

Create an email and attach a spreadsheet

Date March 12, 2008

As long as you’re automating your reports, you might as well automate the email notification!
Example:
strHTMLBody = “<html>Test Message,<br/><br/>This is a test. If this were not a test:” & _
“<ul><li>There would be useful information here</li><li>There would perhaps be more information here</li></ul><br/>Thank you</html>”
Call CreateNewMail(”recipients@somedomain.com;morerecipients@anotherdomain.com”, “ccedfolks@adomain.com”, “Test Subject”, strHTMLBody,”Some fake file.xls”)
Make sure you enable Microsoft Outlook 11 [...]

Posted in Excel Comments No Comments »

Last day of month for a date

Date March 6, 2008

When you create a date with the day part of 0, excel uses the last day of the previous month.
=DATE(YEAR(A1),MONTH(A1)+1,0)
Number of days in the month for a cell:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Posted in Excel Comments No Comments »