This week I coached a new consultant in creating an excel model. Here are some of the words of advice I gave him. I wish I knew these pointers 20 years ago.
Excel model structure = easy to understand
- Put all the assumptions, drivers at the top of the page. This allows the owner of the excel model to tweak the numbers in one place
- Highlight inputs in black, and calculations in blue color. This tells the owner to only alter the black cells. Don’t lock the blue cells; while that prevents the model from breaking, it is hard to work with, and potentially annoying
- Add in notes to explain the source of the data, or assumption. Don’t make the client guess where the data came from. Make it audit-friendly
- Add in an extra column explaining the calculation (e.g., row A + row C / Row D). Like instructions on the IRS 1040 form
- When you are near completion, add in an extra tab with instructions on the model. Alternatively, add in “comment boxes” which explain the top 4-5 model parts
- The user of the model should be able to alter the inputs, and see the results on the same page . . as figures or in graphs. Don’t make them scroll down
Triangulate towards an answer
- Write down the 3 things you are trying to do with the model. What are the hypotheses you are testing for? If you don’t know what answers you are seeking, you will waste your time just geeking out. . .for no reason
- Add in dummy data, until you get more reliable information. This will help you to get the model “up and running”, make sure the mechanics work
- Quickly determine what they key data points you need. Reach out to the client, subject matter experts, or google to get the data points. You don’t need all the data points, but you need something to put into the model
- Use your common sense. Can you walk someone who knows nothing about the situation, through the model, so they understand?
- Don’t keep the model a secret. Iterate with your manager and get feedback. The worst thing you can do is privately mess it up, without enough time to correct
Stay flexible, and continually refine
- The model will start out very basic, and rough. That is okay. Put in the basic information and
- Continually readjust for the appropriate level of model granularity (e.g., 1=basic, 10=refined), so that you are prioritizing on the right things
- When you think of “nice-to-have” or more nuanced factors, don’t add them until later. Just write them in, and add them in later when yo u are 80% complete
- If you are comparing 2 scenarios (e.g., refurbish vs. buy), work on 1 of the scenarios first, get it stable, then copy/paste those rows . . .so the structure is parallel. Make it easy to do an apples-to-apples comparison
Stress test the model
- Change a variable and see if the changes make sense. For example, if you raise the costs from 15% to 150%. . . you better show a loss, and not a profit, right?
- When comparing A and B scenarios, add in the variables that actually change the outcomes. If something applies to both A and B, don’t put it in the model
- Can you tell your partner which variables most significantly affect the outcome of the model? What variables have the greatest sensitivities?
What other excel model tips do you have? I will add them in.
- Set up the excel to print correctly with headers, margins, page # (thanks Yaj)
