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).

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 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.

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.