Suppose you select cells B4:C5, and then enter =B$3+20 and press CTRL + Enter what will C5 contain

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 five-year 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 double-click on a formula. When you do so, Excel will select the top-left cell in every area that the formula references.

For that double-click 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 double-click 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 bottom-right cell, and then press Tab to jump to the top-left cell in the next discontiguous area.

To return to the cell that you double-clicked, 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 that 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 single-quotes 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 last-minute 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 multi-cell 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 higher-level 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 floating-point 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 1-2-3. 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…

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.

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 ugly-but-safe or pretty-but-risky.

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:   =M233-M548

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 last-minute change—which are the most dangerous changes of all—would make that formula completely wrong.

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

C7:   =Sales

…Excel would return 200 in cell B7 and 250 in cell C7. This is because with Excel’s implicit intersection feature, if a formula references a multi-cell 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 C7 would return the Sales value from cell C3.

Therefore, in the actual figure, we don’t have a formula like:

B7:   =B3-B5

Instead, we have the formula…

B7:   =Sales-COGS

…and we can have the same formula in cells C7, D7, and E7.

This is a much-clearer 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 “design-time” 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.

How to reference a variable cell in Excel?

How do you select a cell with the same value?

How do I reference a column in Excel?