Business
Planning
How to Audit and Improve Excel Business Plans
Excel is a great tool for creating business plans. But you need
to make them auditable and as accurate as possible. One massive
Excel
business plan offers some great ideas about what NOT to do.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

A client, who's a businessfinance consultant, recently was
hired to improve the financial forecast for a major startup
company. He recently sent me the workbook because he needed help
with some of its Excel issues.
After I answered his questions, I looked at the plan in
detail. As I did so, I had a sense of deja vu, because the plan
makes many of the same mistakes I made 20 to 30 years ago with
business plans that I created when I was a CFO...before I knew better
and before I had better tools with modern Excel.
An Overview of the Business Plan
The plan is massive. It contains 15 worksheets, some of which
contain nearly a thousand rows and more than 60 columns of calculations.
Logically, the plan is built
like a pyramid.
The bottom of this logical pyramid contains a series of
assumptions about future products and prices, the monthly
quantities that will be sold in each channel of distribution,
the costs to develop and manufacture the products, the
facilities that will be needed, the types of managers that will
be hired, expected financial ratios, and so on.
Then, as you climb the pyramid, other sheets summarize the
lower levels, apply additional assumptions, and methodically build a
detailed picture of five years of operations. Finally, at the top of the
pyramid, we have a fiveyear financial statement, highlighted by
charts that show expected trends in key measures.
Three Useful Excel Auditing Techniques
Before I get into some ways that the model could have been
improved, let's start with three Excel techniques I used when I
explored the workbook.
First, most of the formulas used cell references rather than
range names. The only way to learn what a formula was trying to
do was to visit each cell that it referenced. One way to do that is to doubleclick on a
formula. When you do so, Excel will select the topleft cell in
every area that the formula references.
For that doubleclick method to work, Excel needs to be set
so that "Allow directly editing in cells" is turned
off. If you
aren't already using that setting, choose File, Options,
Advanced. And then, in the Editing
Options section, you'll see the checkbox, Allow
directly editing in cells.
When you doubleclick a formula, Excel selects every range that the formula
references. However, you easily can examine only those discontiguous areas
in the first worksheet that Excel activates. After you look at
the first area, press the Tab key, which moves the active cell
from cell to cell in the first selected area, and then to
each cell in the next selected area, and so on.
If a selected area is large, you can shortcut this process by
pressing Ctrl + . (period), which will jump
your active cell from corner to corner in the selected area. So you
can press Ctrl + . twice to activate the
bottomright cell, and then press Tab to jump
to the topleft cell in the next discontiguous area.
To return to the cell that you doubleclicked, just press
F5, Enter.
The second method I often use is somewhat similar to the
first. If you want to visit a specific range that the formula in
your active cell references...
1. In your formula bar, select the text of the range you want
to visit,
2. Press Ctrl + C to copy that text to your
clipboard,
3. Press Esc to return to the Ready mode,
4. Press F5 to launch the Go To dialog,
5. Press Ctrl + V to paste the text of the
reference you want to visit, and then,
6. Press Enter.
After you explore the area you visited, you easily can return to the cell with the original formula
by
pressing F5, Enter.
The third technique uses the formula bar in a different way.
Suppose you see a formula that you don't understand. Perhaps its
value is strange, or perhaps it's returning an error value.
Here's how to find the problem quickly: In your formula bar
select any piece of the formula that's possible for Excel to
calculate, and then press F9. When you do so,
Excel turns that piece of the formula into the value the piece
contributes to the formula's calculation.
You can select another piece and then another. Finally, press
Esc to return to the Ready mode and to restore
the original formula.
To illustrate, if this were the formula in your formula
bar...
=C398*INDEX(Input!$D14:$H14,C5)
You could select and calculate...
 C398
 Input!$14:$H14
 C5
 INDEX(Input!$D14:$H14,C5)
But if you select and calculate only INDEX, Excel will return the #NAME? error.
Excel Problems and Possible Solutions
I started my exploration of the plan by starting with the first cell in the income statement, which
shows sales for one product for Year 1. Right away, I saw an
irritant and a concern in that cell. The formula was...
='Monthly Financials'!BK5
The irritant was those singlequotes around the name of the
worksheet. Excel adds them automatically whenever the name of a
worksheet contains a space. Personally, I find them to be very
inconvenient because they always get in the way when I want to
select a reference. And they also clutter up my formulas.
To eliminate those single quotes, I changed the name of the
sheet from "Monthly Financials" to "MonthlyFinancials". And that
changed the formula to:
=MonthlyFinancials!BK5
The concern was that the formula uses a relative
reference, not an absolute reference. The author did that so he
could copy the formula to the right to return the sales for
succeeding years, and so he also could copy the formula downward to
return the sales for other products.
This is a problem because if the author is working in the
MonthlyFinancials worksheet, there's no practical way to know
that formulas in other worksheets are relying on the precise
arrangement of products in that section of MonthlyFinancials.
Therefore, a lastminute change in the MonthlyFinancials
worksheet could generate problems in formulas that reference
that area.
If you face this problem, one solution would be to name the
range of products and their titles in your version of the
MonthlyFinancials worksheet, and then use a multicell array
formula to reference that entire name as a block in the
financial statement. Taking this approach, the financial
statement will find more likely display errors if that block of
results is changed.
Here's another approach:
The section of sales by product in the Monthly Financial
sheet includes a line for total sales by period. Sometimes,
throughout this plan, the author referenced that total higher up
in the pyramid. And sometimes, the author entered his own sum in
the higherlevel display.
Either approach is a bad thing.
Instead, he should have used a formula like this:
=IF(ABS([remote total][local total])>.01, NA(),
[local total])
Here, if the remote total doesn't equal the local total, it
means that the model has a problem, so return #N/A. Otherwise,
return the locally calculated total.
Notice, however, that the formula doesn't actually test whether the
two formulas are equal. That's because with all the
calculations in the model, and with
floatingpoint issues, one total might be
72.9370279 and the other might be 72.9370277. And that means
that Excel wouldn't find them to be equal.
Therefore, in cases like this, we subtract one number from
the other, and then return an error indicator if the absolute value of the
difference is greater than a penny...or even a dollar in models
like this.
Risky Totals
Decades ago, when I wrote the Finance column for LOTUS
Magazine, I heard about a contractor who had created a detailed
bid in Lotus 123. At the last moment, he remembered a major
cost that he had forgotten to include in his calculations. So he
inserted a row for that cost just above a Total row, and then submitted his bid.
You know what's coming. Because he'd set up his SUM formula
to extended from the bottom value to the top value in each
column, adding a new row between the SUM formula and the bottom
value didn't include that new cost in his total.
So after the contractor "won" a very costly contract, he sued
Lotus. But the judge quickly sent him on his way with the
comment that Lotus couldn't be responsible for the contractor's
poor spreadsheet design.
In the model I'm telling you about, I saw many
examples of that same poor design.
I know, several versions ago Microsoft added a feature that's
supposed to recognize when a user inserts a row like the
contractor did, and then Excel is supposed to correct the
formula automatically. But that feature doesn't always work. In fact, it
doesn't work in the model, for some reason.
So here's what you do instead...
In
your supporting worksheets—not the ones you deliver to your
readers—you never set up a total area like the one in this
figure.
That's because the formula in cell B5 uses the
formula...
B5: =SUM(B1:B4)
And that means that it's possible that when you insert
a row above the total row, and enter a value, the SUM formula
won't include that value.
Instead,
you set up your worksheet to use gray border rows like this. And
then you "anchor" your sum range in those gray borders. This
gives you a formula like this:
B7: =SUM(B1:B6)
When you have gray border rows like this, the rule is that
you insert new content only between the gray borders.
Of course, you only use those ugly borders in supporting
worksheets, never in a worksheet that you distribute to others.
But for those supporting worksheets your choice is between using
a method that's uglybutsafe or prettybutrisky.
And the choice is obvious.
Mysterious References
Another problem I found was that the model had formulas that
referenced values hundreds of rows apart. For example, a
calculation of Gross Profit might be something like:
M649: =M233M548
There's no way to learn whether those references are correct
without visiting them. And if the model still is under
development, there's always the risk that a lastminute
change—which are the most dangerous changes of all—would make
that formula completely wrong.
Instead,
this is a great time to use what Excel calls an "implicit
intersection," as shown in this figure.
Here, I defined these two ranges:
Sales =Sheet1!$B$3:$E$3
COGS =Sheet1!$B$5:$E$5
And then, if I were to enter...
B7: =Sales
B8: =Sales
...Excel would return 200 in cell B7 and 250 in cell B8. This is
because with Excel's implicit intersection feature, if a formula
references a multicell range in its column, Excel returns the
intersection of its column and that range...the implicit
intersection. That's why cell B7 would return the Sales value
from cell B3 and cell B8 would return the Sales value from cell
C3.
Therefore, in the actual figure, we don't have a formula like:
B7: =B3B5
Instead, we have the formula...
B7: =SalesCOGS
...and we can have the same formula in cells C7, D7, and E7.
This is a muchclearer method...particularly if the Sales and
COGS ranges are hundreds of rows apart, as they are in the
original model.
Dangerous Macros
The model
workbook is an xlsm file, not an xlsx file. That means, of
course, that it has macros. More precisely, it has one ugly macro.
Here are the first few lines of code for that macro:
Range("C617:BJ617").Select
Selection.Copy
Range("C618").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
You don't need to know anything about VBA to understand
how dangerous this macro is. If you were to launch this macro,
it would select the range C617:BJ617 on the active sheet, copy
it, select cell C618, and then use Paste Special to paste values
to the range beginning with cell C618.
Keep in mind that the model has 15 worksheets. All of them are
different. And since the macro was written, the worksheet for
which this macro actually was intended to be used probably has
changed.
Therefore, there's about a 99% chance that if you were to run
this macro today, it would do terrible things to the
model.
So if you write macros to save some keystrokes as you're
building a model, here's the bottom line...
First, never write a macro to reference specific cell addresses.
Second, remember to delete your "designtime" macros when you've
completed your model. If you leave them in your model, someone
is going to run one accidentally and create terrible problems
for you at the worst possible moment.
Third, before you write your macro, ask yourself whether it
would take you more time to write and test the macro than the
time it would save you once the macro is ready to use. In my
experience, most such macros use more time than the save.
I've only begun to audit this model. If I find more
issues that you should
avoid, I'll pass them along in future articles.
