How to Build an Excel Model: Step by Step

The following post is the third in a series of posts about Excel Model Building. To review the previous posts on this topic, please click through the links below:

Click here for Part 1 of this post, How to Build an Excel Model: Key Principles
Click here for Part 2 of this post, How to Build an Excel Model: Tab Structure

Now that we’ve learned the key principles of model building, as well as a general tab structure, this final part of the Excel model building tutorial will review a step by step example of building a model from the ground up. The direction is still somewhat high level, but I’ve included a sample model that follows the prescribed guidelines. The model has already been built to completion, but it can be used to help you follow along and provide context for the high level steps referenced.

Click below to download the sample model:

MBA Excel Sample Model v1.0

Click here to view the supporting documentation for MBA Excel Sample Model

Step 1: Build Output Tabs Shell – Understand Your Requirements

Most people who’ve implemented software will tell you that the majority of errors occur during the requirements gathering phase of the project. (For those of you who haven’t done an implementation, this is the phase of the project where you figure out what your software actually needs to do) Excel model building faces the exact same issue. Before you start building, you should figure out what outputs your model needs to generate. These requirements will greatly influence every subsequent step you take. For example, if you need to build multiple scenarios, you may need to leave space to input different scenario assumptions. If you need to conduct sensitivity analysis, then you may need to use Excel Data Table or build a sensitivity tornado chart.

Understanding your exact requirements first is not always possible, but you should always try to be as definitive as you can. The worst case scenario is investing several hours into building a model that can’t generate the type of outputs you actually need.

In this adjusted snapshot of the sample model, you can see here that the requirements are to build a five your P&L forecast. In the model download, you’ll note that I’ve also included a “Sales Revenue by City” report as an additional requirement.

Step 2: Build Calculations on Paper – Determine Inputs Required

It’s always tempting to jump right into Excel to start building your model immediately. But whenever you’re dealing with a really complex model, it’s always useful to step back with just a pen and paper to sketch out your intended plan. While not everyone uses this step, it can definitely help you gain perspective on your work and prevent you from missing critical ideas.

Step 3: Build Input Tabs and Gather the Required Values

Once you’ve sketched out your calculations, you should have a pretty good idea of what inputs you need to build your model. As mentioned in the tab structure post, you can have three kinds of inputs: drivers, static inputs, and data tables. As a best practice, you should try to ensure that all of your inputs are in as few tabs as possible, with the minor exception of breaking them up into different tabs between these three categories. This is critical because one of the biggest transparency complaints people have when dealing with second hand models is that they can’t find out where they’re supposed to input values.

On many occasions, you won’t have the actual input value you need when you begin building your model. In this situation, it’s best to input a dummy figure, or some rough historical average, so you can continue the model building process. Just make sure to add a notation so you remember to come back and update it.

The example below shows the Drivers tab within the sample model.

Step 4: Load Data Tables

Data tables represent a slightly more complex input than drivers or static inputs. As a general rule, any table that has more rows and / or columns that you can fit and view on your laptop screen deserves its own tab. Make sure that you use proper database formatting for these tables, as it is very possible that someone will take the table and load it into another file in the future.

In the sample model, we have a data table representing Total Product Sales by Territory in 2013.

Step 5: Build Calculations off of Inputs, Drivers, and Data Tables

At this point we’ve finished most of the setup tasks for model building and are ready to get into the heart of the exercise. Your knowledge of database theory and Excel formulas will definitely come in handy here. The calculation tabs are probably the least uniform aspect of Excel models. This is because, for any given goal, there are several ways to get to your solution. While it would be ideal to build your model in the most efficient way possible during your first try, this is rarely possible. The fact is good model building requires trial and error. Only by going through different iterations of your model can you create the best possible outcome.

One best practice to keep in mind is that you should try to do most of your calculations in your calculation tabs and not in your output tabs. While it can definitely be faster to do calculations in the output tabs, it may make it harder for someone to audit your file in the future. By keeping all calculations in a defined area, you’ll significantly increase both the consistency and the transparency of your file. The major exception to this rule is if you’re just doing a sum, count, or lookup. Since these formulas summarize your data, they are appropriate for the output section.

Step 6: Link Calculated values to Output Tabs and Finalize Formatting of Output Tabs

By now the data you’ve run your calculations and should be very close to the exact values you need in your output tabs. You may still need to use some summary formulas to arrange your data based on your initial requirements. Since the output tabs are primarily for display, take time to format these for printing and apply Visual Design techniques. Additionally, make them as transparent as possibly by including sources, unit values, and any other beneficial documentation.

Step 7: Build Your Index Tab

At this point, you should have the majority of your tabs in place for your model. Building your Index Tab will significantly help summarize the scope and functionality of your model. Make sure that every tab you’ve created is documented and described. If you realize that any tabs are unnecessary, remove them. You will probably need to update this tab periodically if any aspect of your model changes.

I always find it useful to hyperlink the tab descriptions to the actual tabs they are describing to enhance the navigation within the model. You can see an example below of the Index Tab for the sample model.

Step 8: Link Key Output Values to Drivers Tab to Perform Scenario and Sensitivity Analysis

As you get towards the end of the model building process, it’s very likely that you’ll want to conduct some form of scenario or sensitivity analysis. To prepare for this step, you should determine what output values are the most important to your analysis and link them back to your drivers tab. The process for doing so is relatively simple and shouldn’t take much more than using the equals sign to write a simple linking formula.

Additionally, you’ll obviously need to understand which of your drivers are most likely to change and have a reasonable range of possible values for these variables. These factors will be completely dependent on the specific type of model you are building.

Another potential option for sensitivity analysis is to use Excel’s Watch Window feature. While this is a great tool for sensitivity specifically, linking your output values to the drivers tab is the best way to create defined scenarios.

Step 9: Create Documentation and Finish Index Tab

As I’ve mentioned before, few Excel models ever have adequate documentation. This additional step adds to your workload and no one really enjoys doing it. However, having documentation can make a huge difference to whomever you might hand your off work to. I like to think of documentation as an insurance policy; put in a little work now, and you’ll significantly reduce the need to answer questions in the future. You’ll also be much less likely to forget your own methodology if you ever need to come back to your file.

Documentation doesn’t have to be a long winded user guide that takes several hours to write. It can take the form of comment boxes in cells, notes in formulas, or just a separate tab in the Excel document explaining your assumptions. All of these things further the transparency of your model and should be included if possible.

For the sample model, I’ve included the documentation as a linked web page, in case it needs to be updated in the future.

Step 10: Add Cell & Workbook Protection Where Appropriate

This last step is important if you plan to either widely distribute your file or solicit inputs from your colleagues. This is especially important in the latter scenario because before you save a new version of your file, you want cell protection in areas that should not be adjusted as well as a clear understanding of what sections your colleagues had the ability to change. The process of protecting your file is fairly simple: you just need to choose which cells will be editable by other users and then assign a password to your workbook.

You’ll notice in the sample model, I’ve protected the Index Tab where the link to the documentation exists. That way, it reduces the likelihood that this sample model will be sent out without the accompanying documentation.