25 March 2020 | Written by Danielle Stein Fairhurst and Lance Rubin

Introduction to the co-author

Danielle Stein Fairhurst is a financial modelling specialist and runs a Sydney-based consultancy which helps her clients create meaningful financial models in the form of business cases, pricing models and management reports. Last year the third edition of her book “Using Excel for Business and Financial Modelling” was published by Wiley Finance and in 2017 she authored “Financial Modeling in Excel for Dummies”.

Pre COVID-19, Danielle had regular engagements around Australia and in far flung corners of the globe as a keynote speaker, workshop facilitator and financial modelling consultant, but like most consultants these days, services her clients via webinar link from her home office:) 

Danielle Stein Fairhurst is on the judging panel for the Financial Modelling Innovation Awards and is on the Diversity Council for the ModelOff Financial Modeling World Championships. In 2015 she founded the Financial Modellers’ Meetup groups which now have over 5,000 members in seven countries (most of whom meet virtually at the moment) as well as a LinkedIn group with over 45,000 members. She holds a Master of Business Administration (MBA) from Macquarie Graduate School of Management (MGSM) and has taught management accounting subjects at Sydney University.

Why did Danielle select the topic and why is she passionate about it?

It’s never been more important to have a well built, flexible and robust financial model to use to predict outcomes and it’s at times like these a financial model really proves its worth.

With a global pandemic unfolding and the situation changing daily, having a model that can you can quickly and easily update as the situation requires, is going to make it so much easier to make the quick decisions that are going to be necessary in the coming weeks or months.

Let’s take a look at some of the key elements of a financial model and how their mechanisms are likely to be impacted by current events, and what we need to consider in a COVID-19 environment:

Forecasts

Most financial models are forward-looking and being able to build robust and accurate forecasts are one of the key skills underpinning the outputs of many financial models.

Much of the time we build forecasts using one of two simple methods;

1. Historical data, either by running regressions or simply continuing historical growth rate.

2. Known drivers, for example if income were contract based, you’d just forecast the expected contracts and multiply it by expected pricing of those contracts

These numbers are then adjusted for seasonality and other known factors, and bingo, there’s your forecasting done. 

No more!

Using historical data simply doesn’t work in this environment.

In living memory we’ve never seen anything what’s happened over the past few weeks and aren’t likely to again, so it’s improbable that you’ll be able to use historical data for forecasts.

Instead, we need to drill past the financial numbers down to their lowest level and the underlying drivers that result in those numbers. We then must decide which drivers to use and forecast each one - at line item level if necessary.

However, just like the historical data is risky to use, a “fixed” assumption that forecasts one or even three outcomes is not very helpful either. We simply cannot predict the numerous outcomes that are likely to occur and old ways of doing forecasts need to be replaced with a modelling mindset where these can be changed quickly and easily multiple times over.

This is when hardcoding of formulas becomes a big issue as you have to change all those formulas that have been hardcoded.

Maybe this pandemic will eradicate the hardcoded formula! One can only hope.

Cash flow

Having cash on hand and into the future is going to be a critical factor of survival over the coming weeks and months.

With the impact of Government decisions, stimulus packages and directives changing each day, business need to be able to predict cash shortfalls so that planning for funding shortages can take place or more aggressive decisions like closing the business.

Ask your accountant or Finance team to help you better understand the impact on cash flow using a financial model.

Ideally the model should be a 3-way cashflow model to understand the full impact of cash flow. A balanced Balance Sheet is critical to ensure that the model has integrity and is accurate, without it there is more risk of inaccurate cash flow and income.

A 1-way model is better than no model at all, so at the very least you should be creating this as a first step perhaps.

If they are unsure how to do this, you can reach out to trainers and consultants like Danielle and myself to assist.

Revenue

The impact on revenue differs between industries and business models within the same industry.

For many “non-essential” businesses, revenue stopped overnight (like leisure travel), whereas for others the impact will be slower and, in some cases, unprecedented demand for their products or services.

We don’t know how long the shutdowns and disruption to normal business is going to last, so having a model in place so that we can see the impact of revenue fluctuations on the financial model going forward is going to help enormously in making decisions.

Expenses

Similarly, expenses vary greatly by industry and business models (online vs offline) unfortunately the largest expense for most businesses is staff costs.

Going back to Management Accounting basics, it’s never been more important to distinguish between fixed and variable costs.

And in some cases, costs which are generally considered to be fixed (such as rent, for example) might be unexpectedly reduced in the current environment as a result of remote working.

For many industries without revenue right now, most fixed costs, however, will continue regardless, whereas the variable costs will reduce and demand and output declines, thereby impacting the cash flow outputs in the model.

Financial Statements

If the model requires all three statements; a Profit & Loss statement, a Cash Flow statement and a Balance Sheet (and not all models do) then these financial statements are considered outputs of the model and may not require material changes, if built well, even where the numbers change dramatically.

If the model has been set up correctly as a self-balancing three-way model, then all links will automatically update each statement with minimal effort from the modeller.

Although the numbers themselves will change, sometimes dramatically, the structure of the financial statements should remain the same despite the drastic variations in the inputs, drivers and scenarios change, unless disclosures are required by the regulators or accounting standards regarding discontinued operations.

Scenarios & simulations

It’s important to have the calculations linking back to the inputs (we might also refer to as drivers or assumptions) of the model and this makes changing the scenarios or performing sensitivity analysis a relatively straight forward process.

Being able to change the inputs quickly and easily is a critical function of a good financial model – in fact, being able to run sensitivities, scenarios and what-if analysis is often the whole reason the model was built in the first place. 

Not to give the answer, but a range of answers.

Whether you use drop-downs, switches, data tables, goal seek or another method, running sensitivities and scenarios together with predictive forecasts are going to give us a level of assurance in an uncertain time.

If you want to go that extra step then you can consider Monte Carlo Simulations, but scenarios is a very solid first step if you don’t have anything but a single forecast output.

Topic and context in a few sentences

The reality is that a mass global pandemic of uncertainty poses significant challenges in the way we make decisions. There are two approaches we can take.

1)     Do nothing about it and pretend it doesn’t exist and continue to live our lives as we did before and make decisions as we did before by using “gut feel” and nothing more.

2)     Make a deliberate effort to manage those things we can manage and make better decisions on the controllable things whilst recognising that no matter what we do we cannot control the unknowns so let’s focus on those things that we can.

By making a deliberate effort to manage the controllable things we can look to the skills of financial modelling to enable us to test and stress different decisions against one another.

For example, the impact on cashflow (and cash in the bank) if we made staff redundant. However, what stimulus packages we would then no longer qualify for as a result also needs to be considered.

So, at times these scenarios are interlinked, and we need to consider many different alternatives and correlations not just 3 types of forecasts but more multiple dimensions of answers.

If we want to get fancy, we can explore Monte Carlo simulation.

A Monte Carlo Simulation is a select number of scenarios running based on randomly generated set of key assumptions across a given range i.e. low and high limits (e.g interest rates are likely to be between 0% - 2%).

The simulation then runs (calculates) the model for as many simulations as the user requires (sometimes 500 or 1000’s depending) and updates all of those assumptions quickly (faster than any human ever can) and produces a distribution curve of say cash balance or cash flow or whatever specific output that is most useful based on those randomly selected assumptions within the range of possibilities.

Understanding the lowest possibly point of cash in your bank across 1000 simulations over the next 12 months is reasonably valuable as a decision maker.

If you had to teach this topic in a class to school kids what key tips would you give them to focus on

For those that still have school open or if they are doing e-learning from home this is a great opportunity to explain the benefits of using a skill like modelling to understand the impact on our lives of key decisions that we, the government and our fellow humans are making.

Simple - If we decide to shut down schools and everyone stays at home then we are likely to reduce the spread of the virus. Yes, it has other economic impacts, but from a health perspective this is obvious.

Complex – What will be the actual rate of reduction? How many people will actually die? What secondary impacts will the economic freefall have on lives vs the pandemic directly?

These are much more complex question to answer as it has multiple facets like other health conditions that exist or age of people currently infected or the robustness of the healthcare system.

Even the number of currently infected people is not known accurately given the limited number of testing and many carriers not showing signs of the virus.

So, at a basic level we have to make decisions that we can understand (and control) and simply relate to the outcomes as compared to others which are a lot more complex.

As a parent we can decide not to send our kids to school, even when schools themselves remain open.

The power of modelling means we can create some hypothesis around potential outcomes and make slightly more informed decisions than we would without it.

It’s like walking in the dark with a torch.

It helps you see where you are going only a few meters ahead of you.

You might not be able to see the entire room and furniture, but you can make sure you don’t smash your toe on a table when you get close to it.

What practical steps can people take now to learn more

The most important step to take is to understand that a financial model is merely a tool to make better decisions.

Nothing more and nothing less.

The model will NEVER give you the EXACT answer. However, it WILL give you the POSSIBLE outcomes based on the assumptions and logic used within it.

Once you change your mindset on what a model CAN actually do and NOT do then you are on the right path to realising the value that you can extract from it.

More data and more complex logic doesn’t necessarily translate to better answers and can sometimes cause more inaccuracies and issues with finding the data to feed into the model.

There are other articles in this series that go through approaches on how to build a great model that is best practice and complies to the FAST acronym.

Where are good places (links) to find out more on the topic

Refer to the following important articles in relation to this topic. One of those relates to scenario analysis and simulation which is due to come out at some time, so look out for it.

Recently there are a number of articles released on a range of topics relating to Financial Modelling and moving away from simple messy spreadsheeting. In order to get access to these easily you can simply use the Financial Modelling Knowledge Hub.

In the App you will find links to other useful sites like YouTube and websites that are relevant to learning this skill.

In addition to the articles Danielle and Lance refer to the upcoming webinar on COVID19 uncertainty and how financial modelling can be used.

How important is this skill in the context of learning FM?

The skills of managing uncertainty is a critical skill in financial modelling, particularly when there are so many variables it can almost feel overwhelming.

By breaking down the uncertainty to known knowns and even known unknowns is useful. As for the unknown unknowns don’t worry about those for now, we have plenty other things we can focus on.

In uncertainty financial modelling can provide some degree of comfort and understanding.

If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App

If you want to find more information on financial modelling and content visit the Model Citizn website.