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


create bar chart in excel 2010ms excel formulas not workingvlookup returns naexcel vlookup counttax return excel templateexcel isna formulahow to set up a lookup table in exceldecision tree template excelmicrosoft excel learning guidevlookup in excel 2007 with examplea cell address as it applies to excel 2010excel indexinghlookup exampletick sign in excelhlook up excelnested if formula excelvlookup tutorialindexing in excelmultiple if function excel 2010excel useful shortcutsexcel 2007 vlookupexcel how to make a pivot tablevlookup function with exampleinsert tick in word documenthighlight column exceldifference between rows and columns in exceladvanced excel tricks pdfcompare formula excelucla average gmat scorev lookup on excelformula for excel spreadsheetshow to use the lookup function in excelexcel 2010 bar chartexcel 2010 if statementsvlookup in excel sheetexample of lookup function in excelexcel hotkeysindex match vs vlookuphow to combine data in two columns in excelexcel remove duplicate columnsexcel logic statementsexcel pivotinglookup function on excelexcell histogramvba excel macro examplesdownloading microsoft excelexcel vba paste specialbest book for learning excelexcel matching data in two columnsarray formula in excel 2013ms excel lookup function exampleexcel formula inputvlookup tutorial 2013main formulas in excelspreadsheet modelling examplesursa uclaexcel control chart templateformula of vlookup in excel with examplereference table in excelcheck symbol in ms wordadvanced excel formattingremove duplicate data excelmultiple ifs in exceltypes of bar charts in excelhow to find duplicate records in excel2 if statements in excelprobability distribution chart excelcount if function in excelvlookup two sheetsexcel 2010 histogram chartucla bruin storedividing numbers in excelcommon excel commands
\n