The basic issue here is that when you make your calculation tabs and columns presentation ready as a report should be, they don't do a very good job of performing the calculations in a logical, auditable fashion. You end up having to hide rows, or make very complicated calculations to go from line to line to fit the big picture metrics in one table. Likewise when you do too many calculations in your reports, you'll have that secondary report that will need the same calculation done to it and before you know if you have slightly different numbers when they should be exactly the same number.
Following along the post below about keeping each tab pure, a common issue I see is people who use their calculations tabs for displaying and printing reports/outputs, or who try to do place calculations into a report to get the report they want.
I understand why this happens. The modeler will provide a nice chart or graph to the boss for a presentation, but it may show slightly different numbers than the boss has already represented to the client. There is no ethical issue here to adjust the numbers up or down, or maybe you're moving to even more conservative numbers. So the modeler goes into the report, makes a change directly on the table and voila, everyone is happy.
Unfortunately, the detail table that's on the back up slide doesn't get changed, or if you do remember, it doesn't get changed in the right direction because there is a lot of moving variables. So when the client wants to dive deeper, it's an embarrassing look. How to prevent this is to keep the tabs pure. If the boss wanted a more conservative number, go ahead and make a quick change to the underlying calculation tab and then it will populate through to all the tables, and all the downstream calculations that feed off it.
The other error that occurs from changing the report directly is that if you've done this "on the fly" then when you refresh the analysis 6 months later you forget that you've 'hardcoded' that one cell in the report and when you refresh with new data, that one cell doesn't change. No big deal if it's noticed, but if it's not then it's just as embarrassing.
Tuesday, September 10, 2013
Friday, May 24, 2013
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)
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)
Wednesday, May 1, 2013
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
four additional items are:
10. Actual to Forecast continuity is important as it allows you to check for forecast viability
Future posts will address each one individually.
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.
Future posts will address each one individually.
Subscribe to:
Comments (Atom)