If you're interested in breaking into finance, check out our Private Equity Course and Investment Banking Course, which help thousands of candidates land top jobs every year.
Overview
Whether you are working in investment banking, private equity or corporate finance at a Fortune 500 company, you'll likely be asked to build a financial model with multiple cases at some point. This kind of model will employ a range of operating assumptions to determine the financial profile of a company under different situations. The process of running these sensitivities is often referred to as a scenario analysis or as building out cases.
Why Develop Different Cases?
There are many benefits to building different cases and sensitizing your projection assumptions. Predicting the future is nearly impossible, but it is prudent common practice to evaluate several different cases to determine a range of outcomes of a potential investment. This kind of modeling is the industry standard at all private equity firms and hedge funds.
The three most common cases used in finance are:
Upside Case – The Upside Case is usually the most optimistic scenario where everything in the future that can go right for the company does. It is common for the Upside Case to incorporate margin expansion and the realization of all growth opportunities. Upside case assumptions typically come from the management teams of the company you are analyzing and are thus collectively referred to as the Management Case. Most investment professionals take these assumptions with a grain of salt given how frequent it is to hear a company miss their forecast. Management teams often work with sell-side professionals to prepare an ambitious Upside Case that can serve as the starting point for deal negotiations. Investment professionals will often “haircut” the different drivers and assumptions in the Upside Case to something more reasonable.
Base Case – The Base Case is a moderate financial forecast that is supposed to represent the most likely situation. A good Base Case will be based on reasonable and factual drivers that can be easily achieved by the business. The Base Case can be thought of as an average and relatively conservative scenario that will happen in the future. An example of this would be if the revenue growth of a business remained stable and in line with historical levels or if the company grew in line with an industry benchmark (e.g. you forecast a company’s revenue CAGR to be in line with the industry’s forecasted GDP).
Downside Case – Think of this scenario as the most severe and conservative case. A Downside Case could reflect when revenue growth stalls, business units fail to cut costs, or a recession hits. For example, if the company you are working with is in the home builder sector, a Downside Case financial model could look like a similar performance during another housing downturn and financial crisis similar to what happened in 2008 – 2009.
The cases you spend more time with can also depend on what side of a transaction you are working on. A CFO of the company is more incentivized to view the Upside Case as a certainty as they have a vested interest in the forecast being correct and for their business to grow indefinitely.
A private equity investor who is looking to take an ownership stake in a business may focus more on developing a conservative and data-driven Base Case. On the contrary, a lender or debt investor tends to focus more on understanding what a true Downside Case would look like since their returns are capped at the interest rate they are earning, but their downside risk is losing all their invested capital.
Building Case Functionality
In Excel, building case functionality is really just a matter of organizing different drivers in a way that can easily be toggled and switched. And you'll want to make sure that you've optimized your Quick Access Toolbar in Excel before starting any modeling.
1. Create an assumptions / case section
It can be very helpful to carve out a section specifically for assumptions and case drivers. Putting all of the important assumptions into one place will also make things much easier to review and audit. Lay out a few rows for each of the important drivers you want to sensitize. Lay out the different case drivers for each assumption.
Next, add a few rows under each assumption. Each row will correspond to a different case. Here, we will create three new rows to outline the assumptions for an Upside Case, Base Case and Downside Case.
2. Create a “Case Toggle”
We will determine the active case of the model with a “Case Toggle”. This is a cell that we will point our driver assumptions to. In practice, this is just going to be a cell with a hardcoded value of 1, 2 or 3. Each number will correspond to the case that we want to be active. We will input a different value for this cell depending on which case we want. We will set this cell to 1 if we want the Upside Case, 2 for Base Case and 3 for Downside Case.
It is considered good model hygiene to name this cell something like “Case”. This makes it easier to audit (vs. having the case be an alphanumeric cell).
3. Link the Case Logic
Lastly, we are going to add the actual case logic that connects our drivers and the Case Toggle. In each of these assumption rows, we are going to enter the following formula:
=OFFSET(CELL,0,’Case’)
Here, CELL refers to whatever cell the formula is in, i.e. it is a self-referential cell. In the example below, this is cell E14. Essentially what we are doing here is creating logic such that one of the below rows will be selected. OFFSET is a formula that pulls the information from a nearby cell, and we are determining which cell that is by using the ‘Case’ cell.
When we have ‘Case’ as a value of 2, the OFFSET will pull the values that are 2 rows below, which is where we have input our Base Case assumptions. This way, we can directly edit the assumptions for each case in the assumptions section. If we want to switch between different Cases, all we have to do is switch between 1, 2 or 3.
Note: In our opinion, OFFSET is meaningfully better than the other popular CHOOSE formula. CHOOSE is extremely error-prone because it makes adding new cases extremely annoying. To add a new case using OFFSET, all you have to do is add a new row for each assumption. CHOOSE requires you to edit every single driver row and the linking required can be very cumbersome.
How to Develop a Downside Case Thesis
The COVID-19 pandemic continues to impact businesses across multiple industries and create uncertainty on when the economic environment will return to normal. As such, investors and finance professionals are now taking more a conservative approach towards evaluating investment opportunities and how they predict the future performance of those investments.
This sort of thinking is integral to investing in private credit.
The most common approach when creating a Downside Case is to mirror performance from the last recession. While this approach works for some sectors and allows you to forecast the company’s performance if another financial crisis just like 2008-2009 occurs, the COVID-19 pandemic has shown us that not all recessions are created equally. Some businesses are actually performing worse today than they did in 2008-2009.
The following steps provide a general framework in helping to develop a defensible Downside Case.
Identify Risks Affecting the Company – The first step in developing a Downside Case is understanding what key risks could impact performance. What key risks and headwinds are Management worried about? Using a home builder company as an example, one of the top risks to this business and sector is how sales of new homes are correlated to general macroeconomic conditions. Generally speaking, if the U.S. economy is performing poorly, families have less disposable income, and there are fewer new home sales.
Incorporate Risks into Financial Models – The top risks you incorporate into the Downside Case should have the most material impact on financial performance if they were to occur. Consider the impact of COVID-19 on a consumer goods retailer. Your Downside Case would likely incorporate a steep decline from in-store sales, but perhaps include an increase in e-commerce sales. A thoughtful revenue forecast might incorporate how many missed days of sales will occur and what the maximum store occupancy is. You want to try to use drivers that you have reliable data for and that are highly correlated with financial performance.
Consider Operating Levers – Just because you are modeling a Downside Case does not mean it cannot have any upside drivers. During periods of financial distress, companies will usually have operating levers they can pull to help maintain some level of profitability as it weathers a downturn. Some example of operating levers that can be executed to preserve cash and profitability in a downturn are:
Reducing headcount (e.g. layoffs and furloughs of non-essential employees)
Company-wide hiring freezes
Facility consolidation (e.g. moving redundant manufacturing plants into one central location)
Eliminating or deferring discretionary spending (e.g. dividends, share buybacks)
Reducing capital expenditures - Cutting back spending on growth capex and spending only on capex that is truly mandatory (replace worn-down machinery parts instead of buying new equipment)
Divesting non-core business assets to generate cash for the short-term (extreme case but can be done if companies become distressed)
It goes without saying that no one can predict the future and no matter what assumptions you put into your model, they are not going to be a perfect mirror of how a company performs.
That being said, developing cases and stressing the financial viability of a business is an important function in determining what makes a company a good acquisition target or investment opportunity. A scenario analysis enables businesses or finance professionals to quantifiably take the best- and worst-case future probabilities into account and analyze those outcomes to make an informed business or investment decision.