How to Build an Excel Model

by Matthew Kuo on April 21, 2013

in Excel, Model Building

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

When it comes to building Excel models, there really are no widely accepted guidelines that Excel users follow.  Because of this, Excel model building is more of an art than a science.  Every single model you encounter tends to have its own nuances and quirks.  Even if they come from the same user, as no standard processes exist, Excel models often show a lack consistency.

The reason this problem exists is pretty apparent: most Excel users just don’t invest time in thinking about how they should structure their models.  When designing a new model, we get so focused on building calculations and writing formulas that the context and structure of the model rarely prioritized.  In other situations, we start building an Excel model and underestimate how often we’ll need use it.  Over time, as we add more and more components to the model without regard to structure, it soon becomes unwieldy.

During my tenure as a management consultant, I worked with many different clients on several quantitative projects.  Having built and worked on so many models before, I’ve seen this potential improvement area countless times.  The concept of model building is still a gray, ambiguous subject matter.  The purpose of this post is to document what I’ve learned in my experience and begin to establish a standard Excel model building framework.

To review the essential skills of Excel model building, I will review key principles, propose a basic tab structure, and walk through specific steps that should be taken.  I will use an attached dummy model to illustrate the principles I’ve recommended.  Obviously my opinion is just one of several Excel experts, so I expect that this post will evolve as I receive feedback.

Key Principles

The secret to building good Excel models is all about having a user focus.  For every model that you build:

Assume that someone else will need to use your model in the future.

People always underestimate how many different users an Excel model will pass through.  Having a user focus is important even if you know you’ll be the only one using it.  Take the example of a financial model used to prepare for monthly close.  Every single month, either you or whoever owns that role will have to repeat the process with new data.  There have been several times in my career where I’ve had to go back to a model that I hadn’t touched in several months and stressed out trying to figure out my original thinking.

Please keep in mind that there is a difference between a calculation and a model.  We’ve all opened up Excel before just to perform some quick math that would’ve been difficult without a calculator.  In these situations, the principles of model building principles are probably not a priority.  However, if you’re building something that’s complex enough to be considered a model, there’s a very high likelihood that the process will either need to be repeated or, at the very least, explained to someone else in the future.

Taking a user perspective prepares you for this inevitable transition.  It encourages you to provide documentation, remove extraneous elements, and not take shortcuts that might cause confusion.  Every time you build a new Excel model, there are three key principles you should focus on:

Logic

As you are finalizing the model you build, you should always try to take the perspective of someone who is completely new to the model you’re building.  This is where stepping away from the model for a few hours would be helpful, assuming you have the leeway to do so.  When building a model, it’s easy to focus on the specific outputs you need to produce and forget the big picture of what the model is supposed to do.  Key questions you should ask yourself include:

  • Would your model make sense to a new user?
  • Have the most obvious issues been either built into the model or addressed in documentation?
  • Have you made any non-intuitive assumptions?
  • Is the end purpose of your model apparent and understandable?

Efficiency

Over the course of building a model, new information and requirements will often change how the model is structured.  When this happens, we rarely go back and rebuild the entire model in a more efficient fashion.  Because of this, models can become fragmented and inefficient.  The key here is to think about your Excel model holistically: with everything you’ve added thus far, think about the most efficient way to get to the answer.  Based on the time you have left, you should then make adjustments to the model to reflect this ideal.  Key questions include:

  • Do you get to your final outputs in the most efficient manner?
  • Are there any tabs or data points you are no longer using?
  • Have you used your space efficiently?
  • Are your tabs formatted consistently in terms of structure and arrangement?

Transparency

Over the course of my career, I’ve rarely come across an Excel model with adequate documentation.  People are either too busy or too lazy to invest time in this important characteristic.  There are two key things you should realize about adding transparency to your Excel model.  First, documentation doesn’t have to be a 20 page document that explains your file; it can literally be comments on cells, additional tabs with notes, or even small descriptions within formulas.  Secondly, any bit of documentation you add is beneficial; even if you don’t have time to provide context for your entire file, add notes and comments where they would be most helpful to the user.  Key questions for transparency are:

  • Is there adequate documentation?
  • Are each of the tabs and their purpose in the model apparent?
  • Is it obvious where the inputs and outputs of the model are?
  • Did you hide any tabs or cells?

Click here for Part 2 of this post, How to Build an Excel Model: Tab Structure
Click here for Part 3 of this post, How to Build an Excel Model: Step by Step

{ 2 comments… read them below or add one }

Mridula November 18, 2014 at 11:05 pm

Hi Matthew,

Hope you are doing well! I happen to come across your page which is extremely insightful and helpful.
I just wanted to request if you can spare some time for a quick chat. I would like to have some guidance and advice on some topics and really appreciate if you can help.

Please let me know some time/day and I can call you or alternatively you may call me.

Reply

Patrick September 1, 2016 at 7:46 am

Hey Matt,

About to build my first excel model and was wondering the best specifications for what I need to get done. Let me know if have any time to talk via email.

Thanks,
Pat

Reply

Leave a Comment

Previous post:

Next post:


Related pages


excel countif syntaxvlookup in a tablehow to add up totals in excelselect distinct values in excelcreating a normal distribution curve in excelvlookup rangesexcel counifside by side stacked bar chart excel 2010wingdings check markcombine excel formulasvlookup & matchtrue or false formula in excelhow to use nested if statements in excelexcel lottery checkerexcel tutorial pivot tablesvlookup two tableshow to create a stacked bar graph in excel 2010tutorial on vlookupexcel 2010 shortcuts pdfexcel lotto number generatorexporting pdf to excelhow do i count cells in excelexcel delete duplicate rowadd leading zeros in excel 2010mba uclahow to set up a histogramexcel hide formulahow to create stacked column chartbin numbers lookupbuilding formulas in excelexcel stacked barrandom pick in excelvlookup return valueestimating spreadsheet templatelearn excel 2007 formulasexcel vlookup arraycheap ucla apparelwingdings 2 symbolsnumbers spreadsheet tutorialifs excelhow to vlookup in excelhow to write vlookup formulaexcel formulas tutorialmaking histogram in excel 2010comma format in excelexcel 2010 transpose functionexcel average multiple rangesexcel template for inventorydefine conditional formatting in excelexcel lookup function examplesformula for random numbers in excelexcel graph titlevlookup index matchrandom surnameshow to excel file convert to pdfexcel formatting codestax spreadsheet templateexcel macro databasenormdist excel exampleabout excel formulas with exampleexcel unique records onlyshortcut key for pivot table in excelvlookup based on multiple criteriaifna excel 2010excel look up tableshow to use hlookup formula in excelvba iferrorrandom value generatorduplicates in excelexcel yearly schedule templateinsert tick mark in excelnested if function excel 2010 examplepiratebay excelbin limits excelexcel formula writingcharting excelexcel iferror
\n