Advanced Excel Modeling - Transparency
Post date: Jul 14, 2018 3:34:05 PM
For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.
In the previous section we discussed model structure which helps us know where to find things. This section discusses how to understand things.
Lesson Objectives
At the end of this lesson you will be able to:
Define model transparency and why it is important
Describe the difference between data and information
Describe what is necessary to make values transparent
Describe what a literal is and why we should not use them in formulas
What is Model Transparency
When modeling for others it is important to understand that the models we create are not ours. They belong to those who hired us. If the work is important, our customers will need auditors to validate our work and our customers may also want others to maintain our work. Thus we must create models that auditors and other qualified modelers can understand. Key to understanding any model is identifying all values and what they mean.
A model that allows us to access and understand the meaning of all of its values is transparent.
Measures the ease of access to each component or cell's information which includes the values we see and the references from which the value is derived. See Excel Model Transparency Definition and Measure.
Data vs. Information
There is a subtle difference between data and information. Data are the facts or details from which information is derived. Individual pieces of data are rarely useful alone. For data to become information, data needs to be put into context. https://www.diffen.com/difference/Data_vs_Information
In Payton's simple model we have six values in the Inputs section: 100,000, 0.00, 1,533.00, 360 and 4.250. Those values are 'Data'. By themselves they are meaningless. To understand those values we need two things: Context and Units of Measure.
Identifies a value's units. This can be a label, or a currency or percentage symbol in the number format.
In the figures I posted we can see each value's context and units of measure. For example: Money Needed is the context for 100,000.00 and USD (US Dollars) is the units of measure. I have done this for every value in the model. Without these two labels our values are opaque and if any value in our model is opaque the entire model is opaque.
Formula Transparency
Formulas must also be transparent. For a formula to be transparent we must be able to access each value in our formula. Let's look at a formula to see if it is transparent. This formula calculates a monthly Payment.
If we click the fx icon we can access a bit more information about these references
In this example there are three cell references B7, B8 and F7. If we look at those cell addresses we see B7 is the Interest Rate, B8 is the loan Term in Months and B6 is the Amount Borrowed in USD. These values are transparent. But this formula also has a literal reference: 12. What is that? Where is its context and UOM?
A value not derived from a formula. Literals are usually keyed by the developer. They may be part of a formula but cannot be the result of a formula
This formula is opaque because the literal has insufficient context.
Replacing Literals
The inputs section contains values with dependents (formulas that reference them) but no precedents. A type of input is constants.
Constants Sub-Section
We can create a section for constants where we can provide context and UOM so they remain transparent. We can then reference these constants' value cells in formulas so those formulas can be transparent.
With the Constants sub section in place we can replace the literal (12) in our formula to reference the cell containing 12 in this section which has proper context and UOM labels; thus making our monthly Payment formula transparent.
Lab
Add a Constants sub section to your Inputs section
Adjust your model to eliminate literals from formulas
Assignments
Complete the Lab
Complete the Quiz
Quiz
What do we mean by model transparency?
Everyone can understand everything about the model
Nothing is hidden from the user
Ability to access all information within the model
None of the above
All of the above
What is the difference between data and information?
There is none. They are the same
Data is values. Information is values with context to provide meaning
Literals make formulas opaque.
True
False
Only if the formula's definition doesn't provide context for the literal
Summary
To maintain transparency all values must be labeled.
Text values (NOT labels) require context labels.
Numeric values require context and UOM labels*.
Compound values, such as date or GPS coordinates require context and format string labels.
Example
Payton's project requirements call for dividing Annual Percentage Rate by the number of months per year to calculate interest per month.
*
Currency symbols in numeric formats count as a UOM labels though they tend to clutter displays and are not as easily changed for different currencies.