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


my ucla webmailexcel symbol shortcutmatch function excel 2007microsoft publisher 2013 download free trialcheap ucla geartick on microsoft wordhow to compare two columns in excel to find differenceslookup table in excelexcel spreadsheet modelingexcel double vlookupsensitivity analysis spreadsheetmicrosoft office home and business 2010 free trial downloadselect distinct values in excelexcel compare 2 columns show differenceswhat is a formula in spreadsheetshow to check for duplicates in excelonline name randomizerrandom number generator excel 2007difference between vlookup and hlookupmatch vba excelestimate worksheet templatebell curve with standard deviationsexcel tax spreadsheetxcel vlookupif and vlookup togetherwhat is the vlookup functionexcel functions vlookupdeloitte consulting internship salarydraw bar graph in excelauto format excel 2010how to draw a normal curveeli goldratt the goalwhat is the formula of vlookup in excelexcel comparing two lists for matchesvlookup across worksheetshow to excel formulas and functionsgoal by eliyahu goldrattexcel function vlookupucla cto ticketsexcel delete rowunique records only excelpricing model template in excelstatistical averages of random variableshlookup explainedhow to avoid duplicates in excelvlookup with two criteriaexel lookupexample formulas in excelwhy do we use vlookupexcel duplicates removein excel vlookuphistogram generator excelremoving duplicates in excel 2007vlookup learningin excel how can i find duplicatesrevenue projection template excelucla gradeswhat is col index num in vlookupwingdings2 fontvlookup tutorial 2010vlookup excel column index numberexcel sheet formulas with exampleformulas not working in excelexcel stdev ifpayment template excelhorizontal filter exceldraw normal distribution exceldividing numbers in excelexcel graph trickstax calculation spreadsheettab symbalexcell remove duplicatescontextures excelalways running sparknotesexcel macro offsetprobability histogram generatorpseudonym generator freequestions to ask during mba interviewinsert check mark symbol in wordto learn excel formulasexcel vba assign range to arraytick in excelexcel formulas and functions
\n