Building transparent formula

Introduction

Transparency is arguably the most important aspect of a financial model and combined with computational efficiency, intuitive architecture and clear presentation is a pillar, indeed a hallmark, of a professional financial model. This post introduces how to structure transparent formula in financial models with the use of simple syntax rules, akin to how developers work with programming languages. If you would like to learn more about building transparent formula you will enjoy either of our two day, 100% hands-on, case study based courses, Mastering Financial Modelling or Project Finance Analysis - delivered by our Principals.
 

Download Excel    Training Courses

Previously I developed a financial modelling methodology which prioritised transparency; in doing so I focussed on: 

• Restricting formula length so formula could easily read on a single, short, line in the formula bar,
• Embedding logic only allowed by rare exception; and
• Avoiding range names 

In the last few years I’ve tested and refined the next version which avoids ‘transparency bloat’ caused by too many and too simple formula. This is a pillar of Vector’s financial modelling methodology, Alpha Spreadsheet Engineering. The innovation came by creating a mechanism which allowed nested logic to be presented transparently, by enabling:

1. Multi-lined formula
2. Syntax (spacing, line indenting, vertical alignment of elements, minimisation of brackets
3. Systematic range naming
   • Family grouping
   • Hierarchical and meaningful names
   • CamelCase capitalisation

The problem

A typical formula, built on a single line, or spilling onto multiple lines without spaces or intentional line breaks is difficult to construct correctly interpret, edit and check. Imagine if everyday instructions where written this way.

Adultdosageis,1tabletwithmeals,mayaffectsleepifsymptomscontinueconsultadoctorchildrencantakehalfatabletonceadaywarningcontainsarsenicandgluten.

Rather than

Adult dosage
  - 1 Tablet with meals
  - May affect sleep, if symptoms continue consult a doctor.
Children may take half a tablet once a day.
Warning: Contains Arsenic and Gluten

Instructions are structured with punctuation and line breaks to lower the cognitive load, avoid mistakes both in content and interpretation, an intentional consequence is it makes it easier to edit and re-use. The same applies to an ideal formula in a financial model.

How to build transparent formula

Here are the key guidelines that Alpha Spreadsheet Engineering uses to construct transparent, capable, formula in professional financial models. You do not need to write short formula to keep a financial model transparent, in the accompanying downloadable spreadsheet, you can see how to build Excel formula like a software developer. The main differences are the systemised used of:
 

1. Range names,
2. Line breaks; and
3. Spacing / syntax

After twenty years of financial modelling I think the results speak for themselves.

Example 1

An otherwise unacceptable and confusing formula to determine what % of a period falls between two key dates. Check it out in the accompanying spreadsheet.

 Using Alpha Spreadsheet Engineering, we would instead state it as

 

Example 2

An otherwise hard to read formula, but typical formula, which in one step, conditionally sums a Contract values, excludes dates before the Acquisition date and applies a scalar to individual contracts. Check it out in the accompanying spreadsheet.

Using Alpha Spreadsheet Engineering we would write this as

How to do this – simplified

1. Expand the formula bar (down) and widen the Range Name box.
2. Use Alt + Enter to create a line break
3. Indent according to the hierarchy / level of each component
4. Use Range names
5. Line up brackets to reflect heirachy (you can scroll vertically as well as left-right in the formula bar)
6. Don’t use more brackets than you need
7. Space out elements, especially either side of mathematical operators. A1XB2 becomes A1 X B2
8. Highlight any element and F9 will give you the numerical evaluation

The benefits of this approach

Building transparent formula in this way results in formula which are:

• Primarily - much easier to work with for the developer and more importantly, the user,
• Efficiently understood which means they can be more readily checked and communicated,
• Tested and unpdated easier,
• Easier to pick back up at a later date,
• Easier to transfer to a client / another team member; and
• Overall contributing to a financial model with a lower "cognitive load".

Summary

Build transparent formula in your financial model and in doing so take one step closer to lower organisational model risk through making financial models easier to quickly check, explain, edit and audit.  This approach is covered extensively in Vector’s Financial Modelling courses. If you found this helpful and would like to learn about other aspects of Project Finance Modelling or Advanced Financial Modelling then you would love our training courses! Check them out here or just give us a call.