- 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 dinghy: simple, efficient, 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)
- 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 sure that the fonts match and the calculations are spot on.
- 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.
Source: Flickr commons: Raft (NathanGibbs), Dinghy (Singapore 2010 Youth Olympic Games), Pontoon Boat (Phil_g), Yacht (Alex Proimis)