Written by Danielle Stein Fairhurst and Lance Rubin
This article first appeared as part of the Financial Modelling Knowledge Hub
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;
- Historical data, either by running regressions or simply continuing historical growth rate
- 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 have to 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 change multiple times over but in a way that is quick.
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 help you understanding the impact on cash flow using a financial model.
Revenue
The impact on revenue differs between industries and business models within the same industry.
For many “non-essential” businesses, revenue stopped overnight, 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 (P&L) 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 do 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.
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.
