The application of consistent spreadsheet and model design best practices is the single biggest impact you can have on reducing spreadsheet risk.

While a lot of risk can be mitigated if the developer and the user of the model have advanced skills, and the model is independently reviewed, it’s not something that should be relied on. Each model should be built so it can be used by multiple users with a range of experience.

It is encouraging to see the increasing popularity of the Financial Modeling Institute’s CFA-style qualifications. (Most of my PwC team have sat both Level 1 and Level 2 certifications.) With the numbers of candidates now in the thousands, it’s further evidence that the financial modelling profession – which struggled for so long with its warring factions – is finally coming of age.

A major advantage of more modelling professionals completing this qualification is that it builds a mindset where the consistent use of best practice is valued as much as individual skills.

PwC’s financial modelling bible

In the spirit of supporting consistent best practice, PwC recently finalised a new set of Global Financial Modelling Guidelines. In just 30 pages it provides the very best advice in financial modelling, with topics such as core principles, design best practices, axis of spreadsheet evil, modelling guidelines and modelling considerations.

Developed over many years, the PwC Global Financial Modelling Guidelines are practical tips to apply when developing any type of financial model. These guidelines are the pathway (in conjunction with a rock solid project approach) to long-term financial modelling happiness. Here is a snapshot of 10 of the most important messages from the guide.

1. Keep it simple and transparent

First impressions count. The perceived quality of your model is determined in the first 30 seconds of use. Regardless of the quality of the calculations, if it looks complex or the model flow isn’t intuitive then expect immediate negative feedback. An advanced modeller shows their quality by elegant and short formulas.

2. Identify and separate inputs, calculations and outputs

From the macro (different worksheets) to the micro (hardwired values in cells), it’s absolutely critical that inputs are separated.

3. Format in a clear and consistent manner

Clear and consistent formatting gives immediate comfort to the user and a surprisingly high level of comfort over the calculations. Equally, if the colour coding is all over the place, people will assume there are errors. Always use the same formatting on all your models, and convince your mates to use it, too.

4. Use structured and descriptive labelling and units

We frequently get asked not to raise design issues when reviewing models, but frequently these incorrect labels uncover errors in the formulas themselves. In a recent case, a row was labelled “Income (incl GST)” in one sheet and “Income” in another. A financial adviser assumed the latter excluded GST, so in the transaction documents the income was overstated by 10%.

5. Keep the flow natural: left to right, top to bottom

Regardless of your first language, it’s expected that the flow of a model and each worksheet is left to right and top to bottom. Key output worksheets, though, are often placed towards the left so they grab the attention before the user dives into the inputs and calculations.

6. Use consistent column headings throughout the model

If you follow the best practices then your good design means you need to use the ‘evil’ functions less frequently, and so your model is less risky. This best practice forces you to assign each column a clear purpose and each time series must use the same column in every worksheet.

7. Use one unique formula per row that is copied across

This is a non-negotiable. Don’t break this one. I know you’re tempted to. But don’t.

8. Make extensive use of error checks

Your models have errors. Lots of them. If the model hasn’t been reviewed then some of them will impact the outputs, probably materially. Refer to my previous article “How to embed the perfect error check into a financial model” (Acuity Dec 2018/Jan 2019 issue) and employ the best strategy for identifying your errors.

9. Include a table of contents, user instructions and explanations

No-one reads user manuals any more. We expect everything to be intuitive. Step into the shoes of your model’s users and add the orientation and guidance they need to ensure their experience of using your model is wonderful.

“Step into the shoes of your model’s users and add the orientation and guidance they need to ensure their experience of using your model is wonderful.”

10. Avoid high-risk functionality or outputs

At times I feel like universities and Wall Street trainers are recommending bloodletting as a cure for a headache. It seems they forgot to update their syllabuses when everyone else realised that teaching people the most advanced aspects of Excel, with little or no context, was a recipe for disaster.

Apparently it’s OK to graduate thinking that you should balance a balance sheet with a circular reference and a ‘plug’, and believing that being able to use all of the Volatile Functions is a badge of honour. It’s horrifying! (See my article “An introduction to spreadsheet evil” in Acuity Oct/Nov 2018 issue.)

How can we extinguish spreadsheet risk?

Although adhering to guidelines can seem unnecessary and restrictive, once you see and understand the implications of departing from consistent best practices you are convinced of their benefits.

But as our financial modelling profession matures and advances, and the problem-solving and technology-agnostic skills of the professional financial modeller take it to a new stage, we need to be serious about this concept of consistent best practices.

At the micro, it’s using consistent design on all your models, or your team’s models, or your company’s models. But actually, as a community of formal and informal financial modellers, that consistency should apply to every financial model developed in the world. Yes, really.

If that were the case, spreadsheet risk would plummet, model life would increase and model confidence would skyrocket.

The complete set of PwC’s Global Financial Modelling Guidelines is now available. If you would like a copy, email [email protected]

PwC Australia’s popular Professional Model Build course is delivered frequently in all major cities. Register your interest at https://www.pwc.com.au/tax/schools.html

PwC New Zealand also runs financial modelling training, see https://training.pwc.co.nz/course/info.php?id=386

Your questions answered:

Have a financial modelling or spreadsheet question you’d like answered in Acuity? Send it to [email protected] with ‘Ask Ian’ in the subject line.

Read more: