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

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"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.