The COVID-19 pandemic has created unprecedented levels of uncertainty for almost all businesses. Business leaders are being pressured to make challenging decisions and implement changes swiftly in order to mitigate near-term risk and drive long-term continuity. However, before they can plan and implement effectively, these leaders must consider a range of possible scenarios. In this post, we’ll examine scenario analysis and walk through a very specific scenario analysis example that can serve as a guide to optimizing the process.
Some Initial Scenario Analysis Considerations
Unlike financial planning and analysis, which is often a single-point projection and scenario, scenario analysis is much more dynamic and robust. It provides business leaders with insights on various outcomes and assists them in planning and dealing with uncertainty.
Today’s circumstances and changing variables create a unique set of challenges for business leaders undertaking scenario analysis. With so many elements beyond their control, many critical questions simply don’t have clear and ready answers:
- How long will this pandemic last?
- How fast will the virus spread?
- When will the economy fully open for business?
- What is the overall impact on our demand?
- What impact do the lockdown and work from home have on workforce productivity?
- How have our customers been affected? In particular, what is the impact on their disposable incomes?
- How do travel restrictions affect our suppliers and their ability to distribute goods and services?
What Scenarios Should Businesses Consider When Preparing a Forecast?
Business leaders should include at least three scenarios in their analysis, with different drivers and assumptions under each scenario that show the impact on revenue as well as on fixed and variable expenses. Based on the current business environment, our team recommends these scenarios:
- Pre-COVID-19 Management Plan
- Probable Case Based on Current Data
- Worst Case
Before we dig into a specific scenario analysis example, let’s take a look at each of these three cases.
Pre-COVID-19 Management Plan
This should be the starting point of a (base line) scenario analysis that shows management’s current strategic forecast for the year under “business as usual” drivers and assumptions. Since a 2020 plan would already have been forecasted and finalized in 2019, this scenario excludes the impact and uncertainties caused by the ongoing pandemic, providing businesses with the “what it would have been” scenario.
Probable Case Based on Current Data Scenario
This is a best estimate of the future based on current data. In the current COVID-19 business environment, this scenario often reflects the assumption of an economic slowdown and reduction in revenue. However, there are several industries (e.g., tech companies with delivery services) that are expected to experience growth because of the pandemic and related lockdowns.
Within the COVID-19 “probable case” scenario, business leaders should further evaluate several possible outcomes that could result from actions taken by federal, state or local governments:
- Social distancing rules will be relaxed in the near term, allowing a return to near term within 30 days.
- Social distancing rules will be relaxed in the mid-term, allowing a return to near term within 30 – 90 days.
- Social distancing rules will not be relaxed, preventing a return to near term for at least 90 – 120 days.
Worst Case Scenario
This scenario should reflect a sustained economic fallout and delay in economic recovery due to COVID-19 and how this disastrous situation would result in a perpetual revenue decline and accumulation of unforeseen expenses. In this case, the pandemic continues for several months or years until a vaccine is developed. Thoughtful scenario analysis and planning is particularly critical here so that business leaders can choose the best course of action to ensure their businesses can operate or even survive in such an adverse and unforgiving environment.
Beyond Basic Scenario Analysis
After evaluating the level of economic activity under each of these scenarios, business leaders need to examine assumptions and key business drivers for each as well. These assumptions and key business drivers could include:
- Cash inflow: Growth and recovery assumptions, proceeds from the stimulus bill, sales of non-essential assets, debt and equity fundraising, recovery of account receivables, proceeds from new business opportunities arising from the crisis
- Cash outflow: PTO/severance payments, contract renegotiations, amendments to trading terms, turnaround and restructuring services expenses
Businesses that are adversely impacted by COVID-19 should focus on cash preservation. Under all scenarios, a robust model will help business leaders understand their immediate liquidity needs and guide the creation of strategies to meet those needs. Ultimately, the real benefit of scenario analysis is that it reinforces awareness and encourages the identification and evaluation of potential actions indicated under all possible outcomes.
With that in mind, let’s now look at a specific scenario analysis example in Microsoft Excel.
Scenario Analysis Example: Setting Up in Microsoft Excel
You don’t need to be a Microsoft Excel guru to create a scenario analysis that will give you great insights into your business.
Below is a scenario analysis example that shows how a finance professional can use Excel to examine a company’s revenue (Line 13) forecast. Please note that this approach can be replicated for all other financial statement line items such as other income or cost of sales and expenses.
Step 1
As noted above, our team recommends creating these scenarios:
- Pre-COVID-19 management plan (Line 5)
- Probable case based on current data (Line 6)
- Worst case (Line 7)
In our imaginary company, Company ABC, total revenue (Line 13) consists of the sales of two products, Product A and Product B.
Step 2
Company ABC’s management needs to evaluate what impact the COVID-19 crisis is expected to have under different scenarios.
For example, after deliberations, Company ABC’s management expects sale units and sale price of Product A under the probable case to remain stagnant (Line 22, 28), while under the worst case, sale units of Product A are expected to drastically decline (Line 23) and sale price is expected to remain unchanged (Line 29).
Step 3
Create an operating case selector (line 4) using the OFFSET Excel function.
Step 4
Under all our assumptions and drivers (i.e., units sold and sale price), create a “selected case” line (line 24, 30, 41, 47) that will extract the assumptions for the scenario selected in the selector toggle. This can be achieved using the OFFSET Excel function discussed in the above step.
Step 5
The total revenue (Line 13) is calculated based on the selected case’s drivers and assumptions. For example, under the probable case, total revenue is calculated based on the sum of units sold (Line 17, 34) multiplied by the sale price (Line 18, 35).
Excel Tip: It is best to use the OFFSET Excel function for scenarios when possible because OFFSET is very versatile for many purposes, can return cell range (i.e., array functions and graphs) and keep the source range of data dynamic.
If this scenario analysis example has made you want to conduct your own analysis, 8020 can help. One of our experienced consultants can ensure that your analysis is structured to reflect today’s challenging business environment and give you the strongest possible planning foundation. You can also learn more about our relevant services by downloading the guide below:
About the Author
Prior to joining 8020 Consulting, Joe was an Investment Banker with experience in capital and debt markets, M&A, IPO, credit, distressed assets and financial assurance. His key responsibilities include buy-side and sell-side M&A, subsidiary spin-offs, capital raising through the debt and equity markets, structuring of hybrid securities, turnaround and restructuring and due diligence support. Over the years, Joe has advised and raised capital for REITs, property developers and major palm oil players. Joe has also lived and worked in a few of the world’s major financial cities such as Los Angeles, London, Singapore and Hong Kong. Joe graduated from the University of Southern California, and he is Chartered Accountant with the Institute of Chartered Accountants in England and Wales. Joe also holds the following FINRA licenses: Series 24, Series 63 and Series 79.
