How are accountants using strategic modelling?

aat comment

Strategic modelling is more important than ever before. What tools are accountants using and how are they using them?

During the Covid-19 lockdown and downturn, the focus in almost all organisations was on planning and strategic modelling. Financial models were a constant focus for both big and small businesses, particularly in these areas:

  • Cash flow modelling
  • Scenario planning
  • Profitability

Most of this work was done using the tools that accountants had to hand, particularly Excel, which remains a powerful modelling tool despite the more specialist, cloud-based platforms on the market.

Here accountants explain which tools and methods they used to tackle planning and modelling.

True scenario planning needs a solid, interconnected foundation

Mark Cracknell, head of research, GenerationCFO

The key thing is to be able to do a multiple-scenario model. I’ve spoken to accountants who were doing 10-15 versions of a budget, all with different parameters. But of course, you can’t really scenario model unless you’ve got a model that is built from the bottom up. It has to be driver-based. I think a lot of people have struggled with scenario modelling because they didn’t have the foundations in place to do it.

At the beginning of lockdown, many people would have been stuck with existing modelling tools, most likely Excel, which is very capable. [But]  there are other tools out there such as planning tools, such as Anaplan, Jedox and Planful.

To be able to get that foundation, you need to have what Anaplan calls ‘connected planning’. You need to have all of your key departments connected. For example, you need an HR bit which has all of your HR costs, driven by each individual. Then you have your sales forecasts, driven by customer and by product. All of those things need to be interconnected so that if you change one thing in one area, it feeds through to the others. That’s the only way to scenario plan with any confidence – otherwise you’re just playing around with figures.

Those companies with a basic financial planning regime would still struggle during the pandemic, because they had no building blocks from which to build a model. A lot of those people used to build on gut feel. Well, gut feel went out of the window with the pandemic. Finance functions had to quickly create driver-based planning models [instead].

Modelling advice: If you’re going to create driver-based planning models, you’ve got to have collaboration. You should have multiple users operating a tool at the same time, and everything changes in real time.

Verdict: If you want effective models, you need to get the foundation right.

Excel has been my saviour – I’ve had to learn a lot in a few months

Farha Jamadar FMAAT, finance manager, Todd Doors

While we use our own software, I’ve relied on Excel to really project and change the future projections and forecasts of the company. I took a Filtered Excel course through AAT in April and this really developed my skills. It allowed me to use pivot tables and macros to really be able to develop sophisticated models and analysis.

I found that death rates and infection rates were an important gauge as to when we could start reopening. There were other factors we had to consider, such as the lowered interest rates and the fluctuations of the exchange rates. All of these hugely affected my modelling; factors I would not normally consider became crucial. The biggest factor was the various government measures and understanding how to implement them to best benefit the company.

My environment has become fast-paced. We now have the news on constantly to ensure we didn’t miss a beat. I think you need to make yourself aware of your industry and how it is coping and really think out of the box to try and stay on top of everything.

Modelling advice: Keep developing your Excel skills and bring in more data sources than the typical financial model.

Verdict: Financial models should look at outside factors as well as core financial data.

Profitability and growth are critical factors to model

Sohaib Ahmed, financial planning and analysis manager, challenger bank

I do all the forward-looking metrics and financial strategy, looking at the capital, liquidity and profitability of the bank. During COVID, I mean, my role became very crucial in determining how we navigate the market and what actions that we need to take to ensure that we’re still profitable.

Profitability will always be a key thing to model and business growth. It’s also about looking at liquidity and that the capital metrics are in line with our expectations and above the regulatory limits that we have. The focus is on becoming more and more efficient year-on-year, which then influences the decisions that you’re taking. The quickest way to become more efficient is to kind of spend less and earn more, so that’s what we aim for.

We mainly focus on the retail market, but we do have some ultra-wealthy customers and some commercial customers, so we look at commercial financing as well. They all require their own modelling and specialist sets of reports to understand how the book moves, how the customers will react and how different decisions will impact different customers.

Excel is very good as a modelling tool – it only becomes challenging when you’re listing large models and you’re trying to run three or four different scenarios at the same time. There were times, especially early on, during Covid-19, where the IT systems were under a lot of strain, so it was taking five minutes every time you tried to save a model.

You can implement a platform such as Anaplan to tackle this issue, which is cloud-based. Some people describe it at Excel on steroids. But those platforms have their own positives and negatives.

Next step: When you are building a model, it’s always good to do sensitivity analysis, not in terms of output but how flexible your model is.

Verdict: Measure growth, profitability and liquidity above all else.

Mark Rowland is a journalist and former editor of Accounting Technician and 20 magazine.

Related articles