Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.
Financial Modeling Techniques: Selecting Operating and Financing Scenarios
How to use a scenario analysis to quickly change model assumptions and reflect important changes
Scenario analysis introduction
We would like to introduce you to an important concept in financial modeling: Scenario analysis.
This key concept takes your financial model to the next level by allowing you the flexibility to quickly change the assumptions of the model and reflect important changes that may have taken place in regard to the company’s operations. This could be due to shifts in the economy, the deal environment, or company-specific issues. We will illustrate some best practices and the importance of these financial modeling techniques below.
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 different scenarios into your model. How does including several different 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!
Getting comfortable with “Offset”
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, and 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. Let’s take a look at an actual example:
Making Your Case
In the picture above, we have a scenario manager that provides us with several different revenue scenarios titled “Strong case”, “Base case”, and “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 Case
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), and 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.
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 incorporating a scenario manager into your model and by 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 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!
The Wall Street Prep Quicklesson Series
7 Free Financial Modeling Lessons
Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.