What Makes Excel Models Good

The Titanic Problem

Some inherited spreadsheets deserve their own disaster documentary. One recent example was the Titanic: large, slow, overly ornate and structurally unsound, frustrating to work with, laborious to fix and, in the end, a little laughable. It could not answer even the most basic questions about itself. The experience distills into a maturity ladder for models, and the ladder starts with a question most builders skip.1

Who Is the Customer?

This is the first question, because frankly not all spreadsheet work gets seen by other people. Much of it is a glorified calculator, perhaps a pivot table with some lookup formulas, built for an audience of one.2 Nothing wrong with that, and no reason to spend an evening formatting it. The rule scales upward: the more complex the problem, the more numerous the potential solutions and the more discerning the customer, the better the model needs to be. Build from the bottom of the ladder and climb only as far as the customer requires. The boat analogy carries the four levels.

Level One: Does It Work?

The canoe level. The model needs to float and not leak, simple, functional, cheap and useful, primarily for one person. Translating to spreadsheet terms, the builder asks basic questions. What am I trying to calculate? What data am I using, and is it clean? Is the sample size big enough to mean anything? How is the data organized so it does not resemble a sock drawer? And how will the pieces be stitched together with math? A canoe that answers these questions honestly beats a yacht that cannot.

Level Two: Is It Flexible?

Once data is organized and calculations work, the thinking part begins, which is the actual modeling. The whole point of a model is testing results, so changing things must be convenient, and the craft is the jet ski: effective, fast, nimble. The rules are absolute. Never hard-code a number inside a calculation. Reference the same source cells so one change ripples everywhere it should. Keep formatting and structure consistent. Never merge cells, because merging breaks summing, sorting and filtering. Keep the data itself separate and clean, with no embedded subtotals and no blank columns interrupting ranges. Every violation of these rules is a leak that appears exactly when the partner asks for one more scenario at 11 p.m.

Level Three: Is It Easy to Use?

The pontoon boat level: easy to use, generally fun and built for passengers. After 40 or 50 hours assembling the monster, spend a little time cleaning it up, because half-baked leftover calculations scattered through the tabs sabotage the next step. That step is the team stress-test. Good consultants use the team to double-check accuracy and comprehensiveness, which requires thick skin and a taste for constructive criticism. Smooth the review for everyone. Document the assumptions, data sources and links so auditing is easy. Remove data sheets unrelated to the model, and file the boring reference tables in the back like a book's appendix. Put the analyses, the charts and tables people actually want, at the front of the workbook. A model nobody can review is a model nobody can trust.

Level Four: Is It Visual?

A model that climbs this far is client-facing, and the standard changes: assume total transparency, with nothing hidden, no hidden tabs and no hidden columns. Make it wonderful, even luxurious, to use. Open with an introduction or table of contents explaining the organization, and set print margins for reviewers who want paper. Save the file positioned at the top-left of the first sheet, because opening into cell AL453 of a random worksheet disorients everyone. Use macros sparingly to automate messy calculations. Build a front-page dashboard showing the major metrics and status, ideally so comprehensive it is the only page the executive reads. This is the yacht, refined with every bell and whistle, the standard investment bankers and valuation specialists live by. Their conventions are worth copying: font colors with fixed meanings, black for inputs and blue for calculations, and files formatted for full keyboard navigation, since banking analysts pride themselves on never touching a mouse.3

Habits That Keep Models Honest

A few construction habits raise any model a full level. Separate inputs, calculations and outputs onto distinct sheets or clearly marked zones, and color the input cells so users know exactly where to type. Never bury a hard-coded number inside a formula, because six months later nobody remembers why the figure exists or whether it still holds. Build a small checks section that flags broken balances and impossible values automatically, since silent errors are the expensive kind. Document assumptions beside the inputs rather than in a separate file nobody opens. And test the model by handing it to someone uninvolved with one instruction: change a scenario and interpret the result. If they need you standing behind them, the model is not finished.

Match the Boat to the Voyage

The ladder's discipline runs both directions. Underbuilding embarrasses you in front of a discerning client, while overbuilding a private calculator wastes hours that analysis deserved. Ask who the customer is, build to that level and stop. Keep the canoe honest, the jet ski nimble, the pontoon welcoming and the yacht transparent. And when someone hands you a Titanic, recognize it quickly, salvage the data and rebuild from level one, because patching structural unsoundness costs more than starting clean ever does.

• 1Financial modeling
• 2Pivot table
• 3Microsoft Excel