How to Build an Excel Model: Tab Structure

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
 

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.

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

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

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post:


Related pages


setting formulas in excelexcel 2007 offsetms excel hlookup examplecount duplicates exceldownload free trial of microsoft office 2010quotation in excelindex formula in excel 2007vlookup explained simplyhow to create bins in excelhow to subtract rows in excelexample of countifexcel lookup valueif statements in excellsites with most trafficlearning formulas in excelprobability distribution chart excelsimbol checkwingding keyhow to do a tick on microsoft wordhow to choose random numbers in excelbeautiful excel graphsexcel look upgreater than formula excelhow to use the match function in exceldinot bold fonthow to compare excel columnsgaussian distribution tutorialexcel tutorial vlookupxl lookupexcel formulas referencelookup vlookuphow to use array formula in exceloffset excel exampleuniformly distributed random numbershow to dedup in exceladvanced vlookup excela blank cell has the numeric value ofexcel vlookup different sheetinventory excel sheetuw software discountozgridtick mark symbol in wordexcel vlookup problemsirs allowance calculatorcumulative chart in excelexcel vlookup offsethow to use iferror function in exceldifferent formulas in excelexcel max formulaexcel counting formulafinding duplicate cells in excelwhat are vlookupsgoogle spreadsheet hlookupvlookup columnexcel indirect commandexcel formula refexcel formula iferrorhow to check duplicate rows in excelvlookup horizontalexcel formulas random number generationhow to learn excel shortcutsv look in excelexcel formulas vlookup example pdfthe goal goldratt summarybest buy microsoft office student discounthow do i do a vlookupvba excel cell valuedivide by zero error excelexcel indexingucla anderson loginhow to set up a histogramnested function in excelscheduling template for excel
\n