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.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
