This is the first instalment of a new regular column examining issues in financial modelling and spreadsheet construction. The column will be written by experts including PwC Deals Modelling team leader Ian Bennett.

By Ian Bennett.

I am pretty sure that spreadsheets are killing your business. The good news for Excel lovers everywhere is that spreadsheets will be part of our lives for many years to come.

The problem is that spreadsheets are risky. Really risky. Risk of formula error; risk of input error; risk of misinterpreting the outputs; version control risk; access control risk; data import risk; macro risk … that’s a lot of risk.

This risk is generally well understood, and yet we still entrust our most precious processes to spreadsheets. Now we’ve accepted that risky spreadsheets are part of our corporate lives, we all know exactly what we need. Yes, categorically and absolutely, we need a new risk policy. Nailed it. Right? No. At this point, I should say that I’m not a controls and process person and I’m not an internal or financial auditor. 

Traditional spreadsheet risk management

I’ve reviewed and authored a number of spreadsheet or modelling risk policies, and generally these do a good job of striking a balance between the demands of local regulations, policy compliance obligations and reducing spreadsheet risk. What’s certain is that they satisfy the financial auditors.

But in my view they all miss the easiest way to reduce spreadsheet risk – improving the quality of the spreadsheet itself. Perhaps it’s due to the way we come at the problem. Spreadsheets that are involved in generating the numbers in the financial statements attract the attention of my auditing colleagues. These colleagues get worried when they see a spreadsheet at a critical point in the information flow, so they call for spreadsheet risk controls and policies.

Then management produces an inventory of the “critical spreadsheets” plus a definition of critical spreadsheets/models that is unambiguous but returns a small and manageable inventory. Almost all of the effort associated with writing, implementing and monitoring the policy goes into writing the definition, and subsequently determining if the organisation’s spreadsheets or models meet that definition.

Attend the Excel workshops hosted by experts at the World Congress of Accountants (WCOA) 2018

It’s worth noting that at this stage, the following spreadsheets probably all fall outside of the inventory: M&A, investments, salaries, pricing, executive remuneration, management fees, covenant compliance, short-term cash flow forecasting, budgets, contract bids and risk logs. They may not feed directly into the financial statements, but they can have a big impact on value. Once we have the inventory, we apply a bunch of controls to that inventory that the auditor can test. And we’re done. This is a good approach for systems that have inbuilt controls and long lifecycles and that evolve slowly. But spreadsheets are flexible and dynamic, can vary wildly in complexity, are dependent on the skills and training of the user and have no inbuilt controls.

Putting spreadsheets at the heart of the solution

The missing piece is the quality of the spreadsheet itself. We need to get our hands dirty and actually open up Excel. The quality of the spreadsheet isn’t considered in spreadsheet risk policies. There are plenty of reasons for that: the policy needs to be technology-agnostic; it’s hard to find technical people to test technical requirements; each part of the organisation needs to be able to do things their own way; no-one’s really sure what best practice is.

My view is that better-quality spreadsheets are a critical part of the solution to reducing spreadsheet risk. In our work, building and reviewing hundreds of spreadsheets every year, we know that about 90% of all modelling errors occur in the final 10% of a project. This actually comes down to one simple thing: Making changes to spreadsheets is risky.

How to make building spreadsheets less risky

We must do two things:

1. We must aim to avoid making changes to our spreadsheet: 

• by building the spreadsheet right the first time
• by following a good quality model development methodology.

PwC’s model build methodology includes the guidance that you should spend 40% of the total project time scoping, specifying the model before you start building it.

2. We must implement and use spreadsheet/model design standards:

• so changes, when required, can be made with the lowest chance of error for whoever makes the change; and whenever they make the change.
• so consistency in our team/division/company is maintained
• so everyone can adhere to the standards.

This second suggestion is easy in the saying, but difficult in the doing. It evokes strong views and long debates which are valuable, but which I must leave for another column.

However, I will say two things. First, PwC Australia has had 15 model design best practices for many years, and this year we will launch our first global modelling standards. This will be a milestone in providing our clients with the ammunition they need to implement design standards into their teams or organisations.

Second, spreadsheet and model design best practices are like seat belts in cars. When we become racing car drivers, we do not dispense with our seat belts, but instead we get better, stronger and more restrictive ones. Advanced drivers might argue that they should be allowed to rely on their own skill. But that misses two facts. First, an error at high speed (e.g. an indirect blowing out on a bend) is catastrophic.

Second, our survival on this spreadsheet racetrack of life actually depends on the other drivers on the road. Spreadsheets have many users and many owners. Advanced users owe it to those who come after them to adopt a consistent set of best practices. If we get the amateur spreadsheet drivers up to a consistent set of design standards, then we all go home at the end of the day.   

So implement a consistent set of spreadsheet design standards in your team, bring your advanced users to that level (make them champions), train the rest of your team in how to comply with the standards, and slice your spreadsheet risk. 

New technology is challenging the pre-eminence of Excel in financial modelling and analytics

Ian Bennett leads PwC Australia’s Deals Modelling team and has more than 17 years’ experience as a professional financial modeller.