What is a good excel model?

by

Recently, I was given an excel model that was the Titanic: large, slow, overly ornate, and structurally unsound. Not only was it frustrating to work with and laborious to fix, it was also a bit laughable. It did not answer even the most basic questions:

 

Who is the customer?

In my mind, this is the very first question you have to ask because frankly – not all of your excel work will be seen by other people. Often, it is just something basic that you put together. It may be just a glorified calculator. Perhaps it is just a pivot table with some VLOOKUP formulas. No need to format it and make fancy graphs because, honestly, you are the only one to use it.
The more complex the problem, numerous the potential solutions, or discerning the customer – the better your model needs to be. Using this graphic, you have to start at the bottom with the basics and build up to a real client-ready excel model.

Good Excel Model - Excel Levels - Graph#1. Does it work?

At the very least, the excel needs to make sense to you. Using the analogy of boat-building, think of this like a canoe. It needs to float and not leak water. Simple, functional, gets the job done. Primarily for one person, cheap, useful.
Translating that to excel, it means asking yourself basic questions like:
  • What am I trying to calculate?
  • What data am I using and is it clean?
  • Is my data sample size (n = ?) big enough to be meaningful?
  • How am I organizing my data so it does not look like a sock drawer?
  • How is the data going to be analyzed?  How do I stitch it together with math?

#2 Is it flexible?

Once the data is organized and the calculations are working, you can start the “thinking part” of the exercise – the actual modeling. Since the whole point of an excel model is to test the results, make sure it is convenient to change things around. The last thing you want to do is make the excel model hard to work with.

This is a tool to prototype solutions and run “what-if” analyses. The excel should be like the jet ski: effective, fast, nimble. A few pointers might be:

  • Don’t hard code (type in the number) for any calculations
  • Reference the same source cells (so that 1 change affects everything)
  • Use consistent formatting and structure when possible
  • Don’t merge cells because it makes it hard to sum, sort, or filter
  • Keep the data separate and clean (no subtotals, no blank columns between data)

#3 Is it easy to use?

The goal here is to make the model both easy-to-use and also easy-to-audit. After spending 40-50 hours putting this monster together, spend a little time cleaning it up. You don’t want half-baked, left-over calculations everywhere.
This is critical because you want your team’s help to stress-test the model. Make sure it is accurate and comprehensive. A good consultant knows how to use the team. Have other people to double-check your work. You need to have some thick skin and know how to take constructive criticism. Think of it like a pontoon boat that is easy-to-use and generally fun. Make it a smooth review process for everyone:
  • Document the assumptions (data sources, links etc) so it is easy to audit
  • Take out unnecessary data sheets or things unrelated to the model.  Just like an appendix of a book, put all the boring data tables and reference stuff in the back
  • If you have analyses (charts, tables), put them in the front of the workbook

#4 Is it visual?

If your excel made it this far, it is actually a client-facing model. You have to assume that everything is transparent, so nothing should be hidden. No hidden tabs, no hidden columns. Make it wonderful to use, luxurious:

  • Put an introduction or table of contents to explain the way the Excel is organized. If you want the reviewer to print out pages, set up the print margins
  • Save the file so it opens up at beginning (top left). It is disorienting to open the file up and it starts in the middle of a random worksheet, cell AL453
  • Use macros to automate messy calculations, but use this sparingly
  • Create a dashboard on the first page that shows all the major metrics and status. Ideally, this is the only thing they look at because it is so comprehensive

A client-facing excel model is like a yacht.  It has all the bells and whistles and is very refined. A lot of the models made by investment bankers or valuation specialists are like this. Each font color has a different meaning (e.g., black = input, blue = calculation), and the excels are formatted for easy navigation without a mouse. Note: investment banking analysts pride themselves on the fact that they don’t use a mouse. All keyboard.

Related Posts:

Share This