How to Build an Excel Model

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.

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