Advanced Excel Modeling - Model Structure
Post date: Jul 14, 2018 1:26:01 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 too often overlooked critical phase of creating a basic project plan. In this section we explore model structure.
Lesson Objectives
At the end of this lesson you will be able to:
Explain what model structure is and why it is important
Describe a common model structure
Explain how model structure helps modelers
Explain how model structure helps model auditors
Explain how model structure helps users
Explain how structure helps protect model integrity
Identify Excel's structural components
What is Model Structure?
Structure provides model component organization which groups components into logical sections. Above is a typical financial model structure (click to expand). Below is Payton's simple project as I would create it. It's main structure is readily discernable with Inputs first, processes second and outputs last.
Why Structure is Important
Think of structure like a well organized grocery store. Many grocery stores all over the US share a common structure. They start with the first section on the right as we enter because we (in the US) are accustomed to driving and walking on the right. The first section contains fruits and vegetables because these are non-refrigerated perishable items. Customers make more frequent trips to grocery stores for perishable non-refrigerated items so it makes sense to put these first so we can get them quickly and leave. The stores' center aisles contain non-perishable, non-refrigerated items. The last aisles contain frozen foods. We want these last so they will not melt while shopping.
As you can see, the store's organization is designed to make things easier for customers and to prevent problems (frozen foods melting while shopping). It also makes things easier for stock keepers because they know where things should go and do not have to hunt all over to store for where to put things.
Model structure serves similar purposes. A good model structure makes it easy for model users to enter assumptions and see results but hard to corrupt the model. It also tells the modeler where to put things and the model reviewer where to find them.
Model Structure Considerations
When creating a model structure we want it to accommodate user desires and to simplify model creation, testing, and maintenance.
Model User Considerations:
For the most part, model users are most interested in results but to get results they need to enter their 'assumptions' and before they can do that they need to know a little bit about our model and how to move around. Thus, the elements of interest to users are: Documentation, Navigation, Inputs, and Outputs.
Model Developer Considerations
Of course modelers are concerned about the entire model. But while our primary objective is Outputs we focus on Processes that turn Inputs into Outputs.
Model Auditor Considerations
Important models must be audited. Auditors want to know where things are, what they mean and how they relate. A clear model structure shows auditors where things are. Documentation in all its forms will help them know what various model values and components mean. Keeping calculations ordered with precedents in reading order also helps where in the US reading order is front to back (worksheets), top to bottom, then Left to right (see MeasuringSpreadsheet Formula Understandability (Section 4.2) by Felienne Hermans, Martin Pinzger & Arie van Deursen).
A cell referenced by a formula in another cell. When looking at a formula in one cell, all references in that formula, whether cell addresses or named references, are precedents to that formula. See Display the relationships between formulas and cells
Model Integrity Considerations:
Models made by one person and used by someone else really need calculations protected so model users don't invalidate models accidently. This means our structure must group those things that can be changed and segregate them from those things that must not change.
Generic Model Structure
Just as a generic layout suits most US grocery stores, a generic structure suits most models: Documentation, Navigation, Inputs, Processes, and Outputs.
Inputs Section
The inputs section contains values with dependents but no precedents. They can be user entries or model constants.
A cell containing a formula that references another cell. That formula depends on the value in other cells. See Display the relationships between formulas and cells
A value that cannot change. Constants are usually managed by the modeler.
A minimal inputs section contains a property (also known as Assumptions) subsection. As we will see later, an inputs section can also contain additional subsections like Labels, Constants, and Items.
These are our model's base level parameters. Other modelers call them assumptions. In general all properties are referenced by one or more formulas or they are superfluous and should be eliminated.
Processes Section
This section contains formulas that read inputs to produce outputs which, in turn, can be used as inputs to other processes. Thus, all formulas in this section have both precedents and dependents though some precedents come from the inputs section and some dependents are in the Outputs section.
Within a process section formula cells should be structured in reading order. Thus, precedents on the same row of dependents should be to the left of their dependents. If not in the same row, then the precedent should be above its dependents. If not on the same worksheet, precedents should be on prior worksheets.
Models often have multiple process sections feeding each other and a final summary process sections.
Example
Payton's project requirements identified our base level parameters as Money Needed, Percentage Fees, Fixed Fees, Term, and Interest Rate.
Example
Payton's project requirements identified a data element called Amount Borrowed which must be calculated from inputs (precedents) and used by outputs (dependents).
Outputs Section
This section contains values derived from precedents but which have no dependents which can include graphs and PivotTables. They are the key results our model users are interested in.
Excel's Structural Elements
So far we have been discussing conceptual structural elements but Excel has 'physical' structural elements that we can leverage to help make our conceptual structure real. Excel's physical structural elements range from the Workbook at the largest level to a single cell at the smallest level.
Cells placed randomly on a worksheet lack structure. We can add physical structure to cells by grouping them. We can strengthen their structure by grouping them in Tables and Named Ranges which we will discuss later.
Lab
Adjust your model for structure
Assignments
Complete the Lab
Complete the Quiz
Quiz
A good model structure:
Helps model users know what they can change and what they cannot
Helps model auditors know where things are
Makes models easier to maintain for modelers
None of the above
All of the above
Which section is characterized by both precedents and dependents?
Inputs
Processes
Outputs
Which section contains user entries?
Inputs
Processes
Outputs
Summary
A good model structure makes our models easier to create, maintain and use. It often includes these sections:
Documentation
Navigation
Inputs
Properties/Assumptions (Collection of user changeable single values)
Labels (Collection of user changeable Units of Measure and Format String labels)
Items (User changeable multiple-instance-values)
Constants (Collection of modeler maintained values)
Processes
Outputs
Sections, and cells within sections should be placed in reading order with precedents:
On prior worksheets or...
If on the same worksheet: above the current formula or...
If on the same row: Left of the current formula
Example
Payton's requirements included total payments and interest.
