April 27

Making Better Spreadsheet Models


At the PASS Business Analytics Conferences, I spoke about building better Excel models. Some of this was technical, but a good portion of my advice had to do with thinking about problems differently. Indeed, I suggest we must think differently about development specifically.

There were three objectives I discussed:

  • Development Memory
  • Model Transparency
  • Model Efficacy

Let’s go through each.

Development Memory

Development memory refers to our ability to create models that naturally capture all that we’ve done in developing our models. We must think about this for when we hand off the model to someone else—or when we look at it again having not seen it for six months.

Development memory can be increased by making things less complicated. For instance, breaking up complicated nested IF’s can go a long way into making the underlying process more easily understood. Another concept I advocate is using proper names for named ranges. Following this trajectory, instead of giving a range the name, valDCF, we could simply call the range, DiscountedCashFlowsInput. The latter is much easier to understand, and the title is not obscured needlessly by abbreviation or Hungarian notation.

Model Transparency

Model transparency refers to traceability of the model. We know most spreadsheets have errors. While we often develop ways to handle and avoid errors, we must also develop in a way that exposes these errors. That is, a proper model setup most ramify errors throughout the entire model. If there is an error in the backend data, it must be carried through all the way to the front end. This will expose the user to the error and help the developer trace where it began. Model transparency is greatly helped by connecting the model by way of formulas (over VBA) to the extent possible. So if one formula develops an error in the formula chain, we can use formula auditing tools to find the problem.

Model Efficacy

Model efficacy refers to the confidence we have in the model to deliver on its promise. Good data visualization and design helps build confidence in the model by allowing results to be easily understood. More important, efficacy is also about trusting your own ability to show results with making things needlessly complex or too simple. You don’t need to explain everything to your audience like they are in fifth-grade but you shouldn’t also making things complex to fulfill a self-indulgent need. If you respect your audience, you should feel comfortable delivering model results to their level.

I firmly believe if we make these items objectives to our own models, we’ll be better off building work that does what it’s supposed to do.


You may also like

February 17, 2022

Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks

February 8, 2021

51: Oz du Soleil & the Global Excel Summit 2021

January 29, 2021

Global Excel Summit 2021

January 22, 2021

50: Randy Austin – Excel for Freelancers

January 8, 2021

49: Theresa Estrada – Microsoft Principal Program Manager Lead

May 26, 2020

All Excel LOOKUPs Explained
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.