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


microsoft excel formulas cheat sheetucla winter breakexcel matching formularemove duplicates from excelmost useful formulas in excelaverage gpa to get into uclamatch command excelmacro code excelvlookup to find multiple matchesexcel formula cell containshow to write heading in excelgpa curvehow do i count cells in excelexcel short cutsinsert check mark excelexcel binsexcel nesting functionshow to create a countif formula in excelhow to make stacked bar graph in excelcheck symbol in excel 2010ucla average gpaiserror excel 2003tips for excel formulasvlook up in excelsymbol tick markvlookup examples 2010my ucla webmailhow to write if statements in excelvlookup tutorialshow to plot histogram in excelexcel spreadsheet tips and trickshighlight duplicate rows in excel 2010excel 2007 lookupvlookup and hlookup in excel 2007excel training vlookuphlook upbell curve in exceltask tracking spreadsheetexcel standard deviation chartremove duplicates from two columns in excelucla anderson tuitiondeloitte career ladderinventory formula excelestimating excel templatewhat is a pivot table in excel used forhow to duplicate a cell in excelexcel duplicates removesalary uclasymbol for greater than or equal to in excelwhat is countif in excelhow to create a bin in excelbin range histogrammicrosoft excel trial versionbest buy downsizingcumbersome tabsusing vlookup and if function togetherms excel lookup function exampleexcel stacked bar graphexcel formulas functionslottery numbers generator excelhow to make a column graph on excelremove duplicate from excelucla anderson fembalearn excel formulaslookup function in excel 2007excel exact lookupexcel templates with macrosexcel true or false formulamicrosoft excel formula guidelookup vs vlookupcreating a column chart in excelexcel vba cell offsetzs associates case interviewmaking bar charts in excelinsert a tickpdf file converter to excelhow to delete doubles in exceleasy vlookup formulawebmail.ucla.eduexcel multiple if statement
\n