Financial Modeling: How to Craft the Best Excel Spreadsheet Models

Spreadsheeting is often seen as a core skill for finance professionals, many of whom are reasonably conversant with Excel. However, those who build complicated spreadsheets frequently forget that the key end users of a spreadsheet model (i.e., the decision-makers) are not necessarily sophisticated Excel users and often see the final output only on a printed page, e.g., as an appendix to a Word document or as part of a set of PowerPoint slides.

If you have this in mind, it becomes easier to understand why there have been numerous high-profile examples of material spreadsheet errors. I am not saying that well-structured models will eliminate mistakes, but in theory they should reduce both the number and the magnitude of these errors.

The problem is that we are all crunched for time in today’s business environment. Consequently, we dust off old templates, fit square pegs in round holes, and produce mistake-laden spreadsheets

Best Practice Models

Modelers should strive to build “Best Practice” models. Here, I want to avoid the semantics of what constitutes “best” in “Best Practice.” “B” and “P” are in capitals deliberately, as I see this as a proper noun insofar as no method is truly “best” for all eventualities.

Plenty of texts include copious instructions on what thou shalt and shalt not do regarding building a spreadsheet. And lo, by the seventh day, the reader is truly bored and sleepeth. I must admit that I have actually contributed to the Odyssey of developing some of these epic tomes that Homer would have been truly proud of. But, hey, I am a big fan of The Simpsons.

I would rather we consider the term as a proper noun to reflect the idea that a good model has four key attributes:

  • Consistency
  • Robustness
  • Flexibility
  • Transparency

These four attributes in turn can help model developers decide how best to design financial models. It’s a system our company calls CRaFT.

Consistency

Models constructed consistently are easier to understand as users become familiar with both their purpose and content. This will in turn give users more comfort about model integrity and make it easier to add or remove business units, categories, numbers of periods, scenarios, etc.

Consistent formatting and use of styles cannot be over-emphasized. Humans take in much information on a nonverbal basis. Consider the following old Print dialog box from Excel 2003:

True, this interface has long since been replaced. But like Quasimodo, does it ring any bells? Feast your eyes on the above dialog box. It has a drop-down box, check boxes, option buttons, scroll bars — all manner of data validation. You may have never seen this dialog box before in your life, but you just know where you need to input data.

  • 1
  • 2
  • 3
  • 4
  • 5
  • Next page