Keep the Purpose of Each Tab Pure

One of the key aspects that makes a model 'work' in the long run is to keep the purpose of each tab pure.  Earlier I identified four types of tabs.  They are:  Data tabs, Input tabs, Calculation tabs,  and Reports tabs.  I identify them as four types of tabs, however probably every simple model consisting of only 1 tab would have these four parts on that one tab.  Let me try to describe each one:

Data tab:  This is where you store the underlying data that feeds the model.  This shoule be structured in a way that allows the data to be passed to another modeler easily, as well as structured so you can populate with new data, or with additional data.  As your modeleling becomes more advanced, this data should be organized in a way that is in keeping with database rules of normalizing data.  Often I describe this as organizing data 'down' instead of 'across'.  We'll save that for another post.  There can be many data tabs as that is often determine by how many data sources feed the model.

Input tab:  This is different than data.  Inputs are what is used to differntiate between various scenarios.  Often the Input tab is also the tab where you would select which scenario to run.

Calculations: This is where you lay out the process by which the Data is manipulated by the Inputs to create your results.  When the Calculations are layed out efficiently one of the greatest benefits is that there is no longer that tedious discussion of how certain results were obtained.  Questions that can go on-and-on via email or in verbal discussions of what was multiplied by what and what was the basis for this or that disappear.  All that is required is for both parties to view the Calculation tab as formulas are clear, concise and don't leave any room for interpretation.  This is where I often am challenged when a client wants a documentation of the model, and my best reponse is that the model is it's own documentation.  How can words even attempt to provide the clarity and certainty of a formula.

Reports:  This is what drives the model.  Decision makers are the ones who need the model to make a decision, and so the the Data you use, the Inputs you devise and the Calculations you develop are all focused to create the Report that is needed.  The moset common error in simple models I see is that the Data tab is used as the rRport, or likewise the Report is used as the Data tab.  Doing this rarely satisfies the need for efficient data management or productive reports.  Reports should be fed from the Calculations.  In fact there should not be a single number on a Report that doesn't also exist in the Calculations (except for simple summations etc). 

Good luck and good modeling (is that too corny?  I need to think of something to sign off with)

Maiden Post

This blog is designed to help the Enterprise Modeler in Excel.  I'm going to focus it not on how to write certain functions, or what functions to use, though it will address that from time to time.  Instead the goal is to discuss how to build a model so that it is robust, efficient, intuitive and flexible.

To start with here are my current 6 items that are essential to makeing a good Excel model

1.       Model consists of four types of tabs:  Data, Input, Calculations, Reports.

2.       Calculation tabs are not for reporting and Report tabs are not for calculating.

3.       Formulas should not have any constants

4.       The ISERROR function should never be used.

5.       Data inputs are easily ‘created’ from existing sources. 

6.       Source of data inputs are themselves treated as data so the current version of actuals/projections is not lost in the immense amount of data and analysis that’s done.

four additional items are:

7.  .  Input Data – feed calculations from one tab, not imbedded in calculations, or spread throughout calculations.

8.  Reports all fed from same set of calculations, so that they are consistent with each other.

9.  Allow Multiple sets of data inputs to check for accuracy of each.

10. Actual to Forecast continuity is important as it allows you to check for forecast viability

Future posts will address each one individually.