Rants on Excel Models

I have just spent the last three days re-building a very detailed client model. (Yes-I know that’s what I get paid to do.) When I first started modeling in the 1980s, people were amazed just to receive a spreadsheet. In the 1990s you had to talk people through a spreadsheet for them to understand it. Today the common expectation is that a spreadsheet or model is self-explanatory. Today, if your model is not self-explanatory, it reflects badly on the company (and the preparer).

My client’s model was not self-explanatory for several reasons:

1. There was no logic to the organization of the detail. Revenue detail was not all grouped together. For example, VAT (foreign companies have “value added tax”) calculations were mixed in with revenue and cost of sales detail even though they only had a balance sheet effect.

2. There was no balance sheet-- only a P&L and cash flow statement. This omission of the balance sheet happens frequently with models I get that are prepared by Europeans. I must assume that debt leverage (by any measure) and working capital are not concepts used frequently in Europe. (It can not be the case but their models don't make it easy to calculate!!)

3. There were color coded cells to indicate a change in an assumption instead of using date-based or period-based “if” statements. Takes quite awhile to determine why changing the assumption has no effect on the model because the timing of the assumption is hard coded. (Hint: whenever you see colored cells used anywhere in a spreadsheet except for title rows, you can safely assume that the model is not self-explanatory.)

A few tips on building better spreadsheets and models:

1. Build the spreadsheet as a stand alone document that does not need explanatory comments (or other spreadsheets that were not attached)

2. Group detail logically together. Put all the revenue detail in one section or tab. Then do the same thing for cost of sales. To improve understanding, calculate gross margins by product line or line of business. In models with multiple products, provide grand totals for revenue, cost of sales and gross margin. Going to a revenue cell on the summary P&L and finding links to 10 cells does not aid comprehension. (Hint: on the summary P&L link back to a grand total.)

3. Learn to use VLOOKUP, HLOOKUP and “name” cells instead of using "links". It makes makes it much easier to understand the model (and provides a smaller model size which passes through spam filters more easily).

4. Delete cells with values that are never used in the model. Maybe you were going to use that assumption and then changed your mind. That’s OK, but delete those cells before you send out the model. If you re-use the model, put in an on-off switch (Hint: multiply by 0 or 1) for assumptions you use but not for all cases.

5. Do not hard code numbers unless they are assumptions. Every number should be a formula or linked to an assumption. Sensitivity analysis and "what if" analysis don't work well when there are hard coded numbers outside of the assumptions.

Enough ranting. A good site for improving Excel skills is here courtesy of the Fuqua School of Business at Duke University.