Date posted: 23/10/2020 5 min read

7 steps to reduce spreadsheet risk and errors in UDAs

An effective approach to spreadsheet risk management has long been missing. That needs to change, quickly.

In Brief

  • In a COVID world, the importance of having reliable User Developed Applications (UDAs) to aid decision-making is heightened.
  • If you are building UDAs, no matter how good you are, you can guarantee you will make an error.
  • Having skilled people who can build quality UDAs is a valuable yet under-recognised asset.

By Ian Bennett FCA and Thuthuka Manasa

Spreadsheets underpin most decisions in larger organisations, yet an effective approach to spreadsheet risk management has been missing.

During COVID’s turbulent economic times, having trust in your decision-making tools is a hygiene factor. As we write this, spreadsheets are being used to determine the impact of JobKeeper in Australia, quantify cash runways, perform supply-and-demand planning in the face of wild volatility, support financing conversations with banks and recalculate wages when dealing with wage trust issues.

It’s obvious spreadsheet risk just went up, but is your organisation’s approach to managing that risk working?

In a COVID world, where decisions are the difference between businesses surviving or thriving, the importance of having reliable User Developed Applications (UDAs) that support rapid clarity of thought is heightened.

Add the exponential growth of potent tools such as Power BI, Tableau, and Alteryx, which empower users to develop more sophisticated and data-rich analysis, and the importance of these user-based decision analysis tools has never been greater.

UDAs, those spreadsheets and programs developed by people in the organisation that sit outside of IT, emphasise usability, flexibility and versatility. They are agile and can mirror the thinking of the decision-maker, tackle the specific questions they are facing, and be flexed as the questions change. However, this flexibility is also their weakness: controlling them is difficult.

“If you are building User Developed Applications, no matter how good you are, you can guarantee you will make an error”
PwC’s Ian Bennett FCA and Thuthuka Manasa

Are your decision tools riddled with errors?

Errors in UDAs can be painful, expensive and embarrassing. Search online for “spreadsheet blunders” and you’ll see many examples, but that’s only the tip of the iceberg. The team at PwC reviews more than 100 models and spreadsheets every year and we always find errors in them – many of which are material.

Empirical evidence indicates about 95% of unreviewed spreadsheets have errors, but anecdotal evidence suggests the incidence of error in broader UDAs is even higher. If you are building UDAs, no matter how good you are, you can guarantee you will make an error.

The approach to controlling spreadsheets has shifted between attempting to either eradicate them (one short-lived CFO made them a sackable offence!) or police them with draconian policies. Neither has worked.

These approaches have dealt with the symptom, not the cause. They have failed to recognise that agile models and spreadsheets ultimately meet an important need in organisations that must respond quickly to changes.

So rather than trying to address the symptoms, we need to get to the root of the problem – and that lies in people and culture.

Train your people (and culture) in good model design

Having skilled people who can build quality UDAs that deliver clear actionable insights while mitigating the risks is a valuable yet under-recognised asset.

Looking back at all the models and spreadsheets from finance functions our PwC team has reviewed over the years, there is a clear correlation between the number and materiality of errors and poor design, and organisational modelling skill.

Analysts are not educated on good model design at university and their professional development rarely prioritises formal modelling training. So analysts with the required modelling skills are few and far between, and even rarer are finance functions with a culture that supports good modelling.

In response, PwC has been running modelling training courses for clients that provide a firm foundation in good model design. We’ve had very positive feedback and enthusiastic referrals. But although those on the front line recognise the value of this skill, this is rarely the case at an enterprise level.

From tactical reviews to holistic risk management

Once you have (i) your people and culture sorted; (ii) a greater appreciation of the tools, techniques and technologies now available; and (iii) a clear understanding of the risk, then it’s time for a practical approach to managing risk.

Taking a tactical and targeted approach, we are using diagnostic tools to help our clients at PwC quickly interrogate a suite of spreadsheets and models. This is to identify:

(i) the design and risk themes;

(ii) the high-risk models. Next, we remediate risk through the review, amendment and tactical rebuild of high-risk models, with an emphasis on using the right tool for the job. But this is just the start of a more fulsome approach to model assurance, one that should be the cornerstone of an effective spreadsheet risk management culture.

Risk management to a better finance function

It feels like this is a risk management article, however it’s actually about better spreadsheets and UDAs and a more effective approach to information management.

Don’t let your analysts spend their lives deciphering convoluted formulae in badly designed models created by their predecessors. Likewise, don't let them manually map and manipulate data because your policies or skill levels prevent you embracing new tools.

Free them up to leverage your rich data sets and identify actionable insights.

Ultimately, it comes down to trust. Trust is gained by consistently delivering reliable UDAs that cut through uncertainty.

Leaders need the flexibility and agility of UDAs to deal with a turbulent world where the stakes have gone up. However, to use UDAs with confidence, trust is imperative.

7 steps to reduce UDA and spreadsheet risk

These seven steps build the path to reliable model assurance.

1. Raise organisation-wide awareness of User Developed Application (UDA) risk and opportunities.

2. Conduct a regular skills audit in every team that builds, uses or develops UDAs.

3. Perform a one-off review of UDA quality and risks with a corresponding action plan.

4. Run UDA development training emphasising best practices and UDA risk awareness sessions.

5. Permanently increase modelling skill by improving staff training; set up Centres of Excellence to remove “spreadsheet evil”; externally train UDA champions and implement regular training delivered by champions to drive consistency of design across the whole organisation.

6. Implement methodologies for UDA integrity review, recognising the criticality of independent review and not just focusing on model environment reviews.

7. Embrace new tools and empower your teams to pick the right UDA for the job.

Ian Bennett FCA leads PwC Australia’s deals modelling team. Thuthuka Manasa is a partner in PwC Australia’s Deals Modelling team and has more than 18 years’ experience as a professional financial modeller.

Read more:

Bad spreadsheets are killing your business

Many Australian businesses rely on spreadsheets as much as on electricity or the internet. Ian Bennett FCA offers expert tips on how to reduce the risk that they may fail.

Read more

What are PwC’s top 10 tips for financial modelling?

PwC’s new set of Global Financial Modelling Guidelines provides the very best advice in financial modelling.

Read more

An introduction to spreadsheet evil

Advanced Excel users need to use their superpowers for good. Some functions can bring disaster down upon them and all their colleagues.

Read more