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

This is under construction.

Click this link for the course's start

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.

Transparency

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.

Context

Units of Measure (UOM)

A description explaining what a value represents.

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?

Literal

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.

I use BXL Helper Add-In to display formula references with context.

Replacing Literals

The inputs section contains values with dependents (formulas that reference them) but no precedents. A type of input is constants.

Constant

A value that cannot change. Constants are usually managed by the modeler

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

1. Add a Constants sub section to your Inputs section
2. Adjust your model to eliminate literals from formulas

Assignments

1. Read Measuring Model Transparency
2. Complete the Lab
3. Complete the Quiz

Quiz

1. What do we mean by model transparency?
  1. Everyone can understand everything about the model
  2. Nothing is hidden from the user
  3. Ability to access all information within the model
  4. None of the above
  5. All of the above
2. What is the difference between data and information?

- 1. There is none. They are the same
  2. Data is values. Information is values with context to provide meaning

1. Literals make formulas opaque.

- 1. True
  2. False
  3. 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.

Model Structure

Introduction

Data Synchronization

Page updated

Google Sites

Report abuse