Board reports should answer four types of questions:
1. What were the significant operating events last month? Are you on target?
2. How has your view of the future changed?
3. How much additional cash do you need, and when, and why?
4. What is management doing to maximize our collective gains?
Instead, reports for the Board of Directors typically have at least fourteen common problems. In my experience, Excel can contribute to the solution of them all.
Problem 1. The reports are one inch thick. This is too detailed for most directors, who can digest only three or four numbers each month. Include numbers like sales, number of employees, burn rate, cash balance, and so on. If you don’t believe this, look at your own file drawers and cabinets filled with previous reports, and ask yourself how much was really significant.
Instead, provide the Board with relatively few pages of high-level information, and give them more detailed information as requested.
Often, each new plan promises a spurt of growth Real Soon Now. At the extreme, a chart of each new plan resembles a Feather Chart, like the Excel chart shown here. Meanwhile, actual performance, shown by the blue line, remains flat.
Problem 2. Reports don’t compare actual results to the annual operating plan. Instead, the reports compare actual results to Plan2, Plan3 or Plan4.
Problem 3. There’s no integrated financial model showing monthly actual and forecast (not plan) balance sheet, cash flow, and income statement.
In many companies, Excel users spend days each month to update and maintain a model like this. However, Power Query or an Excel-friendly OLAP database can simplify this task considerably.
OLAP cubes can provide historical data that serves as the basis for each new forecast. Other cubes can contain key assumptions based on spreadsheet analysis of recent history.
Problem 4. Financial reports don’t measure contribution by manager, division, subsidiary, product, geographic unit, and so on.
Reports like this often are difficult to design and maintain. Few directors and senior managers want to see reports with long lists of details. (See Problem 1.) Instead, they typically want summary tables and charts.
This can be a real problem for Excel users who rely on database queries, because users must sort and summarize the query results in a variety of ways. Worse, the structure of these reports can change frequently. This is because information that’s important today could become irrelevant tomorrow.
However, when reports get their data from Power Query or from formulas linked to OLAP cubes reports, these problems are much easier to manage.
Problem 5. Reports do measure contribution, but include significant “cross charges” or intra-company transfer fees.
Some large companies deal with this problem by presenting “above the line” numbers, which exclude such charges, and “below the line” numbers, which include them.
Problem 6. Reports include 6 pages of text, single spaced.
Mark Twain once wrote, “I’d have written you a shorter letter but I didn’t have the time.” Managers must take the time to be succinct.
One effective way to use less text and take even less time is to include short captions with your charts and tables. To do this easily, and to enforce brevity, use Text Boxes in Excel reports. But rather than entering text in each Text Box, it’s often useful to maintain all caption text on one sheet in the workbook, and then link to each caption by Text Box formula.
For example, enter a short explanation in a cell and then name the cell SalesText. Select a Text Box and enter =SalesText in Excel’s formula bar. This causes the Text Box to display the text. When you change the text in the cell, the Text Box updates automatically.
Problem 7. There are no bullet summaries of results and key issues.
At first glance, this problem seems to have nothing to do with Excel. However, think about how business magazines explain business and economic performance. They include small charts and tables on nearly every page. That is, small charts and tables with captions can make text more informative, with less reading.
Problem 8. Reports don’t use charts.
The other extreme, which also is a problem, is that some Board reports include many pages of charts, printed one or two per page. If those charts were printed on a football field they probably would be no more difficult to understand at a glance.
In contrast, look at the current issue of The Economist magazine. Near the front of the magazine you’ll often see charts that are smaller than many postage stamps. One glance at these small charts typically brings instant understanding.
Problem 9. Reports aren’t available until the 15th of the month, because revenues are “kept open”.
I’m continually amazed that so many companies I’ve visited take so long to close their books each month. When I worked as a cost accountant for a large high-tech manufacturer, and when I was a CFO, we provided preliminary reports within about two working days each month. And we closed the books within about five working days.
Going further, I once wrote in Inc Magazine about a company that produced weekly and month-to-date financial statements every Tuesday morning. Once the books were closed each month, actual profits typically were within 5% of the final week’s estimate.
Most companies should be able to do the same.
Problem 10. Reports are distributed at the Board meeting, or less than 24 hours before.
Excel users often work long into the night to prepare for the next day’s Board meeting. The reports often aren’t assembled until the last possible second. And the panic doesn’t end until the door to the Boardroom closes, and the meeting begins.
Problem 11. Reports include 12 pages of financial information, and only one page about sales and backlog, and one page about major projects.
I once attended a Board meeting for a company whose CFO had recently left public practice. His entire report was financial information from a bookkeeper’s perspective. He droned on for an hour and said nothing that the directors cared about. He didn’t last long as CFO.
The Board needs information about the direction the company is headed. Excel users, accustomed to backward-looking reports, need to prepare analytical reports from that perspective.
Problem 12. Cash flow statements are prepared using the funds flow format, and not the checkbook format (beginning cash plus collections less spending equals ending cash). When these statements are prepared, spending isn’t summarized by natural expense, such as payroll, rent, travel. Incidentally, this information is not available from most accounts payable systems.
That is, from an accounting perspective, booking invoices debits specific expenses and credits AP. Writing checks debits AP and credits cash. Directors want to see the credit to cash explained in terms of the debits to the original expenses.
Whenever I’ve had to prepare such reports, the quick solution was to use detailed spreadsheets to meet the Directors’ needs. However, it certainly would be feasible to set up another approach that takes less work.
In Power Query, for example, merge the table that pays the invoice with the table that books the invoice…giving you spending by expense.
Problem 13. Reports don’t identify the cash on deposit and investments by bank and maturity. There’s a difference between investing in U.S. Treasury bills and a certificate of deposit in a struggling bank.
This problem defines at least two analytical Excel reports. Also, the risk associated with changing interest rates touches it.
Problem 14. Reports don’t cover sensitivity; i.e., what’s the impact on future cash balances of changes in days sales outstanding, days payable, inventory turns, financing alternatives, and the like.
This final problem introduces the idea of analytical modeling with Excel. This is a challenging, interesting, and useful technique for estimating how changes in key variables can affect results.
The article, Should You Raise Prices or Lower Them? showed how you can use charts to evaluate profit sensitivity to changes in prices. See if you can build on that concept.
