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.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)


You may also like

April 9, 2011

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

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!