Most Excel reports, forecasts, and analyses I’ve seen in my career could serve as excellent examples of what NOT to do in Excel.
These reports fail for at least one of three reasons: bad strategy, bad structure, or bad technique. In this article, I’ll cover best practices in the general design strategy that you should use to create your reports. In future articles, I’ll cover report structure and technique. And in all articles, for simplicity, I’ll use “report” as the general term for all reports, analyses, forecasts, models, and so on.
In my experience, few Excel users actually think about their basic Excel reporting strategies. This a shame, because bad strategy puts many users on the path to bad reports before they even launch Excel.
Here’s how to avoid the worst reporting strategies…
1. Provide Information, Not Documentation
Years ago, I was the CFO of a company that was purchased by one of the largest companies in the US. After the sale, I had the opportunity to visit with the CEO in his office.
During our conversation, the Chief Information Officer carried in a stack of reports that was at least one foot tall. Smiling proudly, he dropped them with a thud! on the CEO’s side table near me. He excused his interruption, and left.
I glanced at the top page and saw long columns of tiny numbers. “That’s quite a stack of reports,” I said. “Do you read them all?”
“Not a page,” he said.
As a general rule, people who receive your reports—particularly managers—don’t want to read documentation of your data. Instead, they want information. They want insight. They want new knowledge that’s useful to them, knowledge they can glean from the summaries you give them in your reports.
If they want more detail, your readers will ask for it…which they seldom do in my experience.
Your readers want answers to questions like…
- What are the trends?
- Where are the surprises?
- Where are new problems that I should worry about?
- Where have our recent activities worked, and where have they failed?
- What’s on the horizon?
- And so on.
Answers to questions like that seldom will be found in pages of detail.
In contrast, lower-level managers typically need to see more information about their own departments than their boss does, for at least two reasons. First, this gives them support for the numbers their boss sees. Second, they need to be closer to their own numbers than their boss does.
Unfortunately, lower-level managers often assume that the amount of detail that interests them also will interest their boss…and that’s one of the reasons that senior managers often are swamped with a flood of detail.
2. Plan for Report Updating
Early in my career with spreadsheets, I realized that there’s no practical difference between a “one-time analysis” and a “report.” This is because my boss often would say about a new analysis, “That’s great, Charley! Can you give me an update next month?”
Questions like that turned my “one-time” analyses into periodic reports.
Also, some of my “one-time” analyses took me longer to prepare than I had expected. Therefore, by the time an analysis finally was done, new data had become available, data that made my analysis obsolete.
Don’t make the mistakes I did. Plan your reports, analyses, forecasts, and other presentations so that you can update them easily.
3. Plan for Structural Changes
If you don’t plan for the structure of your company and its data to change in the future, you’ll create at least two problems for you and your company.
First, you’ll do a lot of work to update your current report to reflect those changes when they occur. And second, you’ll likely make at least one serious error when you make those unplanned changes.
On the other hand, if you try to design your report for all possible changes, you’ll never finish your report.
So you’ll walk a fine line here. However, I found that if I kept the possibility of structural changes in the back of my mind as I worked on new reports, I automatically laid out my reports in a way that made the most-likely changes easier to manage in the future.
4. Plan for Questions
When you give managers numbers that make sense to them—rather than giving them a flood of data with “Report” in the title—managers tend to understand and remember your numbers. And that often causes them to ask questions like…
- “Where did that number come from?”
- “That isn’t the same number that this other report shows. How come?”
- “How does this change compare to changes in previous periods?”
- “What key assumptions did you make to get to that result?”
- “That number doesn’t look right. Can you make sure it’s not an error?”
- “Why was there such a big change from last month?”
- And so on.
If you plan for questions like this, you can include tables with supporting calculations in your report workbook. You don’t usually distribute these supporting tables, but you can have them available when someone asks the right questions.
Creating these tables in advance, gives you several benefits…
- It saves you time, because the calculations are updated automatically in your report workbooks.
- It makes you look good, because you can answer many of your boss’s questions immediately.
- It trains you to think more like your boss…and like your boss’s boss.
- It can alert you to possible errors in your workbook and even in your data.
5. Plan for Errors
Your Excel reports will have errors at times. But you’ll find most of them by planning carefully.
One great way to find errors is to set up an Error Test section in your report workbook. This section has a series of tests that return TRUE if an error is found, and FALSE otherwise.
Your formulas could compare report totals with similar totals calculated directly from your source data. They could check your source data for accuracy, to the degree that’s possible. They could perform reasonability checks for your data and reports. They could check for error values returned by your formulas. And so on.
One way to implement this plan is to ask yourself where errors might be possible in your most important reports, and then set up an Error Test section that checks for those errors. And then you move on to other reports.
Unfortunately, you probably won’t think of all possible ways that errors could develop. So as you discover an error in a future report, try to add additional error-testing formulas that will find errors like it in the future. By doing so, you ratchet down the errors in your reports.
If you can think of other best practices in Excel reporting strategies, please let me know.
