Management Reporting
Thirteen Ways to Improve Your Monthly Board Reports
with the Help of Excel
An experienced financial consultant and Board member describes the problems typical with many Board Reports. And
here's how Excel users can fix many of those problems.
by Charley Kyd, MBA Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
|
Mike Gonnerman has seen
his share of Board reports in his 40 years of business experience.
He's
been the CFO of two public and two private companies. He's served on the
Boards of Directors and Advisors of more than 20 companies, where he
typically chairs the Audit and Compensation Committees. And he's been a
financial consultant for more than 75 companies in the past 15 years.
Mike writes that monthly 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, Mike writes, reports for the Board of Directors typically
have at least fourteen common problems. In my experience, Excel can
contribute to the solution of most of these problems.
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.
In
Metric Madness? I present the Reporting
Pyramid, which suggests a way to correct this problem. Provide the Board
with relatively few pages of high-level information, the article
suggests, and give them more detailed information as requested.
 Each new plan promises near-term success, while actual performance lags. |
Problem 2.
Reports don't compare actual results to the annual operating plan.
Instead, the reports compare actual results to Plan2, Plan3 or Plan4.
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 orange line, remains flat.
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, 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.
Preparing a forecast like this for the Board never will be easy; but
an Excel-friendly OLAP database can make it much less time-consuming.
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 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
graphs.
The other extreme, which also is a problem, is that some Board
reports include many pages of graphs, 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 Business Week
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.
The page
Sample Excel Dashboards
illustrates examples of Excel reports that use a variety of small
charts.
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 visit 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.
These problems are merely another instance of a common problem.
Follow the link to learn
how to cure
Spreadsheet Hell.
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.
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 southern Arkansas bank.
This problem defines at least two analytical Excel reports. Also, the
risk associated with changing interest rates touches on the final
problem that Mike listed.
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.
|