For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #66

Good management reports should show whether managers have achieved their forecasts. This chart compares actual performance to multiple forecasts.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Good management reports should show whether managers have achieved their forecasts. This chart compares actual performance to multiple forecasts.Several years ago, strategy+business, a magazine published by the business consulting firm of Booz Allen Hamilton, surveyed their readers about which ideas are most likely to affect the way businesses are conducted in the long run. They replied that execution is the most important idea.

If execution is the most critical quality, then management reports should track how successfully managers have achieved their plans or forecasts.

Unfortunately, at many companies, yesterday's failed plans are discarded like yesterday's garbage. Instead, if reports include yesterday's plans, managers can get a more accurate picture of their ability to execute.

For example, each forecast in this Excel display is like a hockey stick. Each says, "We're going to succeed like crazy, Real Soon Now." But actual performance shows a different picture.

Taken together, these failed forecasts somewhat resemble the rib bones of a long-dead fish. They are a testament to failure. But the testament is invisible if the prior plans are removed from this display.

Excel Issues

Series 2 in this chart plots a one-cell value to-which the “Forecasts” label has been attached. This allows the actual forecasts to be added or deleted without removing that label.

Series 1 displays actual performance, and the remaining series display forecasted performance.

The data that supports the chart requires a slightly unusual Excel capability, dynamic range names. This is because each time you add another forecast, all series in the chart need to expand automatically.

Like most dynamic range names, the ones in this workbook rely on the OFFSET function. To see the range names, launch the Name Manager dialog. To do so, choose Formulas, Defined Names, Name Manager. Now select the gActual range name to see its dynamic definition...

(Continued in the documentation.)

Order Details

Excel Versions:

Excel 2007 and after, for either Windows or Mac

Availability:

Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.

Guarantee:

One full year, unconditional.

Currency:

All prices are in US Dollar currency.

Licensing:

Two copies: One for work plus one for home.

Credit Cards:

Credit Cards
 

Only $16.95

New Excel