How to Build an Excel Model: Tab Structure

One of the most important aspects of model building is the way you setup the tabs in your Excel workbook. Every tab you create should have a specific purpose within your model; otherwise, the tab should be deleted. By having a clear understanding of the different tabs and their respective functions, you’ll build an efficient model that’s easy for other users to work with.

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

Index Tab

For every complex model I build, I always try to include and an index which is a tab that describes all the other tabs and provides the user the full scope of functionality within the model. Once you’ve completed building the model, it’s useful to add links from the index tab to every tab you’ve described, to facilitate ease of navigation. Ideally, the Index tab will not have any complex model calculations; it should mainly provide documentation and increase the transparency of your model.

Input Tabs

It’s extremely important to keep all of your inputs in as few tabs and cells as possible. Working with Excel files where user inputs are expected in several different tabs is one of the most cumbersome issues we deal with. You should keep all of your input tabs in one section of the workbook and highlight them to signal inputs are required. Also keep in mind that you should never have a single input being entered in two different locations – if you need the variable in two different locations just link both locations to the same cell.

Drivers – Drivers are the input variables within your model that you expect to adjust. Having drivers is what allows you to perform sensitivity and scenario analysis. The way you setup your driver inputs will be key to how you perform scenario analysis.

Static Inputs – Your static assumptions are the variables that you don’t expect to change and don’t need to perform sensitivity or scenario analysis on. Having a separate tab just for static inputs isn’t always a necessity, as some people just incorporate them with other inputs. The main reason for doing so is just to keep them separate from the true drivers of your model.

Data Tables – Your data tables essentially represent the database of your model. Any form of data that’s complex enough to have rows and columns should be put in a table. When creating your tables, be sure to follow proper database theory. For large tables, it’s best to have only one table per tab.

Calculation Tabs

The calculation tabs are the engine of your model. They should take your drivers, static assumptions, and data tables and perform all the calculations necessary to turn them into outputs values. Traditionally, this is where the majority of the work in model building will occur, in terms of formula writing. Do your best not to mix calculation tabs with either inputs or outputs.

Output Tabs

The output tabs are what you plan to present externally to the end customer of the model. For example, if you’re building a financial model, you’ll likely have a balance sheet, income statement, and statement of cash flows as output tabs. Keep these tabs clean of any inputs or calculations and use the Page Setup menu in Excel to format these for printing.

Documentation Tabs

Assuming you’ve already created an Index tab, having additional documentation tabs may not be necessary. However, if this additional information does not fit will in any of the other categories above, it’s best to create these tabs to ensure transparency for the user.