It’s clear from the feedback I receive that spreadsheets are still of great interest to accountants. This will come as no surprise to anybody.

We have total Excel dependence in the finance sector, so whether it’s your captor, your addiction or the pure energy and joy that powers your day (like me!), I’m here to help all accountants understand and love their own spreadsheets, and even those built by others.

It’s time for an update on a number of matters, but also to ask you to be inspired to ask for more. Why do you read these articles? Do you need help? What are your fears? This is a safe place, so lie on the couch and let’s begin our session.

Will XLOOKUPs have their day?

Not that long ago I predicted XLOOKUPs in Excel would shortly take over the world and, in doing so, would replace VLOOKUP, HLOOKUP, LOOKUP and INDEX (MATCH). Accountants across Australia and New Zealand went racing for Excel help to learn and prepare for this once-in-a-generation transition.

Two things:

1. It hasn’t happened. I have barely seen a single financial model with an XLOOKUP in it and I am not using them when building for clients.

2. It hasn’t happened, yet! The issue is backward compatibility and there is a surprising number of large and small organisations that are yet to move to Office 365. The risk that someone important (a bank, a government, an investor, your boss) isn’t able to open a spreadsheet that you built is too high for now. But chatter on XLOOKUP on the forums is high, as is its use among advanced users of Excel, so it will have its day, I promise.

We are all a little intimidated by Dynamic Arrays

Introduced to Excel at the same time as XLOOKUP, this new feature aims to turn the humble spreadsheet into a fungible database. After 12 months of testing, I will be honest and say I may have underestimated just how crazy Dynamic Arrays are.

Microsoft threw out the rulebook with this one. Version compatibility is hard to predict and although backward compatibility is a big issue, uptake is slow because people are bamboozled by them. However, they’re extraordinarily powerful and soon we’ll all be using them for complex modelling and everyday spreadsheets. If you have a spare hour, I urge you to investigate them.

Consistent application of best practices

I have written in this column many times about financial modelling best practices. (As a reminder, you can access PwC’s Global Financial Modeling Guidelines at the end of this article.)

But no-one should take any comfort from a set of very advanced best practices if they’re the only one using them.

I need to be clear that the consistent application by all model developers and users in your team or organisation of a set of mediocre best practices (plus training to understand why best practices are important), lowers risk and reduces stress more than if each person is adhering to their own unique advanced best practices. It’s all about consistency within an ecosystem.

Model risk scores

Following a number of articles and webcasts on the topic, there is growing interest in being able to produce consistent and insightful risk scoring of any model or spreadsheet. There’s a need for a simple methodology to produce a risk score across a number of dimensions and summarised to a single score.

There are real-world benefits for CFOs to track spreadsheet quality both by comparison across their portfolio of critical spreadsheets looking for hotspots, and also over time as models evolve.

Business case models might start with a score of 3 (quite bad) and end up a 9 (very good). A CFO should know when a business case model falls to 5 and remediate immediately.

Let me extend the thought experiment to credit panels that will only lend off a model with a model risk score above (say) 7. Or 9 for project finance. This is possible.

The demand has seen a few technologies emerging to produce risk scores and PwC is working to create a fully automated solution, which hopefully will be digitised as a subscription-based self-service portal.

Small is not less complex nor more willing to compromise

As mentioned in Feb-Mar 2021 Acuity, my PwC team has been exploring new ways to make high-quality forecast models accessible to the SME sector. First steps have been encouraging. But one thing we’ve found – and it’s something that many Acuity readers will know – is small businesses are often more diverse and complex than the big end of town. They also, quite rightly, have higher expectations about solutions being tailored to their needs. A generic financial model simply doesn’t cut it.

It’s a beautiful challenge and we’re grateful to those SMEs that have volunteered to help us.

Data is quality

Models are getting bigger and more dependent on quality data. If you can’t obtain data without prohibitive cleaning and manual intervention, or on a frequent or timely enough basis – or you simply can’t obtain it at all – then even the world’s best financial model will be a costly ornament.

Five years ago, I predicted that data issues in our industry would be extinct, but in fact the opposite is true. Chief data officers are spending too much of their time fixing data governance and lineage issues that are preventing end users from producing insights as quickly or regularly as they need to.

“Five years ago, I predicted that data issues in our industry would be extinct, but in fact the opposite is true.”

For those considering new financial models (Excel or cloud-based platforms), please do your diligence on the data you need before you start and have a plan for how you’re going to connect to it.

At PwC, we now offer rapid deployment of a tactical data warehouse alongside every modelling job to act as a staging, review and management layer before the model. This is proving very effective and is helping turbocharge the shift to data-driven predictive planning.

Taking steps towards the future

Financial modelling is not going to change in major leaps and bounds. It has always grown with small improvements and it will continue to do so.

A rising tide lifts all boats so there’s a responsibility on all of us to have a go with Power Query, Power Pivot, Power BI, tactical data warehouses, Dynamic Arrays and XLOOKUPs and not see them as scary unknowns. We should be embracing them as valuable enhancements to our everyday spreadsheet lives.

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.

Resources