19 excel modeling tips

By | December 11, 2014

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)

Excel model quality

Related posts:

4 thoughts on “19 excel modeling tips

  1. Faceman

    Some design techniques:
    – add a table of content page with links to other sheets within the model
    – on every page add button linking it back to the table of contents
    – you may also add two buttons, linking to the next and previous sheet
    – if you have big tables of data, freeze view on every page and add buttons within the frozen area linking to cell a1 in this sheet. This will make a massive difference if someone would scroll through 000 of rows
    – on the model design stage, work with couple A4 sheets of paper to name the output, calculations and raw data and links between them, using a wall or a board. This technique allows to visualise the dependencies quickly

    Reply
  2. Yaj

    -Page Set up for excel models to print in landscape with the appropriate page break view settings to. A lot of directors/partners like to review hard copies of the datapack.

    Reply
    1. consultantsmindadmin Post author

      Very good point. Same way. Hate to see an oddly printed format. Will add to the list #19.

      Reply

Leave a Reply