Scenario Analysis

How to Perform Scenario Analysis in Excel (Step-by-Step)

Everyone knows that their boss (or client) frequently changes his or her mind on a daily, if not hourly, basis. Part of your job as a good employee is to anticipate such shifts in opinion or expectations, and prepare for the worst! When it comes to financial modeling, why not make your life much easier by anticipating such changes and incorporating several scenarios into your model.

• How does including various scenarios into the model make your life easier you ask?
• Won’t my financial model be even larger and more unwieldy than before?

Great questions, but let me now introduce you to the “offset” function and the scenario manager!

Dynamic Scenario Analysis Using the “Offset” Excel Function

The offset function is a fantastic tool in Excel and will make it very easy for you to adjust your model for changing expectations. All you really need to know is that the offset function asks you for three things:

1. Set a reference point anywhere in your model
2. Tell the formula how many rows you would like to move down from that reference point
3. Tell the formula how many columns you would like to move to the right of the reference point. Once you have provided that information, Excel will pull the data from the desired cell.

Scenario Analysis Example: Excel Model with Operating Scenarios

Let’s take a look at an actual example:

Operating Case Selection: Strong, Base and Weak

In the screenshot above, we have a scenario manager that provides us with several revenue scenarios titled

1. “Strong case”
2. “Base case”
3. “Weak case”

This allows us to input revenue growth assumptions that may be slightly above or below your client’s expectations and essentially stress-test your model.

Above this, we have an area titled “Income statement assumptions” that will actually “drive” our revenue projections in our model and link to the actual income statement. By setting up a scenario manager and using the offset function, we can easily switch from one revenue case to another, simply by changing one cell.

Selecting Your Operating Scenario (Dynamic Case Toggle)

When we use the offset function in cell E6 to help pick out an appropriate revenue growth scenario, we are telling the model to do the following:

• 1) Set our beginning reference point in cell E11
• 2) From cell E11, I would like to move down the equivalent number of rows as stated in cell C2 (in this case, “1” row)
• 3) Move “0” columns to the right.

I have told Excel to pick the value found in cell E12, the cell that is one row below, and 0 columns to the right of my reference point. If I were to input a “2” into cell C2, the offset formula would have selected the value of 6% found in cell E13, the cell that is located “2” rows below and “0” columns to the right of my reference point.

Scenario Analysis Excel Tutorial Conclusion: Case Closed!

This offset formula in cell E6 can be copied across for each projected year, but make sure to lock cell C2 in place with dollar signs (as pictured). This way, it is always referenced in your formula, telling the offset function how many rows to go down from the reference point for each individual year.

It should be obvious by now that by incorporating a scenario manager into your model and taking advantage of the offset function, you can quickly adjust and manipulate your model simply by changing a single cell (in this case, cell C2). We can input a “1”, “2”, or “3” into cell C2 and tell the offset function to select any of our identified operating cases.

This scenario manager can be extended to include not only revenue assumptions, but gross profit margin, EBIT margin, capital expenditure, tax, and financing assumptions, just to name a few!

As always, best practices such as these should be incorporated into any financial model, not only to create a more dynamic model, but to save you and your boss valuable time! In the next article, we aim to highlight the benefits of a sensitivity (what-if) analysis when it comes to financial modeling and any valuation analysis that you may perform.

Learning to effectively use the tools that Excel provides you for financial modeling will enable you to spend less time worrying about the mechanics of building a model, and more time focusing on the actual scenario analysis. Wall Street Prep is here not only to make you a more efficient financial modeler, but more importantly, to make you a better Analyst/Associate or Executive!