Principles of a Good Excel Model

When we design something physical, we can pick it up, turn it over, or thump it when something is not working. Not so with spreadsheet modelling. Despite the fact that we can see a model, it is not actually "there," and when problems arise we have only our mental map of it to figure out what is wrong — and a problem in one area can be caused by something seemingly unrelated elsewhere.

So the design principles we apply as we build are critical. The more we do things correctly the first time, the less trouble results.

1. KISS — Keep it simple

Keep formulas simple, even if it means breaking calculations across several cells. If you re-read a formula 10 minutes later and struggle to understand it, break it up. Keep the structure simple with calculations flowing in one consistent direction, and keep formatting simple — bold sparingly, no psychedelic colours.

2. Have a clear idea of what the model needs to do

Without a clear goal, step away from the computer — sketch flows on paper or build a small pilot proof-of-concept. If the model must serve two functions (e.g. credit analysis and equity valuation), build one solid calculation engine whose output can be used in different ways.

3. Be clear about what users want and expect

Do not assume — users often only vaguely know what they want. If they like an existing model, mirror its layout and analytical steps. Gauge their skill level and check Excel version compatibility.

4. Maintain a logical arrangement

Follow the flow of calculations: what must be calculated first to get to the next round? Order calculation blocks logically so the workings are easy to follow and check. Present final output on a separate summary sheet.

5. Make all calculations visible

A "black box" model intimidates users; visible formulas — plus visible toggles/settings — reassure them and let them verify the logic.

6. Be consistent

Use the same label for the same item everywhere ("cash flow from operations" in one place and "operating cash flow" in another breeds confusion). Keep the same columns holding the same years across sheets; use consistent fonts, sizes, and colours for the same types of items.

7. Use one input for one data point

Enter each data point once and have the model always read that single input. Multiple inputs for the same item invite conflicting values.

8. Think modular

Build blocks of formulas that perform discrete operations and pass results to the next block — easier to build, audit, and change ("develop and forget").

9. Make full use of Excel's power

Excel has 250+ functions (financial, date/time, statistical, lookup & reference, database, text, logical, information) — for financial modelling you need about 35 to start. Combine functions for exponential gains, and use VBA macros to automate tasks and build user forms.

10. Provide ways to prevent or back out of errors

Guard formula errors (e.g. wrap divisions to avoid #DIV/0!) and design against user errors with data validation, clear on-screen instructions, and screens that guide users to do the right thing.

11. Save in-progress versions under different names, and save often

Save in-progress versions under different names, and save often — so a corrupted latest version only costs you the work since the last save.

12. Test, test, and test

A great model "disappears": users get the results they want without the model's functions or interface intruding into their consciousness.