EXCEL: How to create switches in excel for building scenarios?

Financial modeling commonly requires to stress test the critical assumptions to sensitize the forecasts. This involves testing the financial model numbers under different scenarios. These different scenarios may range from stress testing simple revenue and cost-side variables such as annual escalation in prices to complex deal or funding-related variables such as debt / equity mix, which often have a significant impact on project cash flows and consequently, IRRs.    

A financial modeler has to build switches in a model to offer flexibility for the user to switch between the different sets of assumptions or model cases as we call them. The variables that have the greatest impact on the business and the ones with uncertain and contrasting future paths are identified as scenarios. As a good modeling practice, these scenarios should be realistic, descriptive, and should not outnumber 3–4 cases. Each scenario should clearly depict the modeler’s view on the macro or micro issues related to the business. While the names should be descriptive, we often resort to labels like upside case, management case, and downside case to capture the different scenarios.

In this post, we will cover a few simple, widely used and efficient methods available in Excel to build switches in financial models. We have used the example of a brick manufacturing company to build various cases. According to the analyst, the revenue drivers for the company — the number of bricks sold and price per brick — could take different paths in future. Accordingly, the model needs to be flexible enough to capture the three cases highlighted by the analyst.

In order to use switches to pull out values from the selected scenario, we need to first populate the assumptions pertaining to the revenue build-up under different scenarios.

Step 1: Data related to the number of bricks sold under different scenarios have been populated in cells B16:I18. Similarly, the prices per brick have been spread in cells B22:I24.

Step 2: Once the data is ready, the values can be pulled out using different switches from the selected scenario.

a.        If Switch: This switch uses the “nested if” statement to pull out values from the selected scenario in rows 19 & 25. For example, if the selected case is “Management Case” then the value from cell E17 is pulled out in E19. Otherwise, the respective value under the different scenarios mentioned in cell C11 is pulled out using the if function. Similarly, the values for the price per brick are pulled out in row 25.

Formula used: =IF ($C$11="Upside Case",E16,(IF($C$11="Management Case",E17,E18)))

b.        Vlookup Switch: This switch uses the “vlookup” function to pull out values from the selected scenario in rows 14 & 20. For example, in cell E14, the vlookup function search values for the selected case, i.e., in cell C6 in column 1 of the selected array, i.e. B11:I13. Once the function finds the value in the selected array, it returns value based on the column number mentioned to retrieve value, i.e. column 4. Similarly, the values for the price per brick are pulled out in row 20.

Formula used: =VLOOKUP($C$6,$B$11:$I$13,COLUMN(E8)-1,)

c.        Choose Switch: This switch uses the “choose” function to return value from a list of values based on a given position. For example, in cell F21, the choose function returns value from the list of selected values, i.e., F18, F19 & F20 based on the given position selected in cell C10. Similarly, the values for the price per brick are pulled out in row 27.

Formula used: =CHOOSE($C$10,F18,F19,F20)

d.        Offset Switch: This switch uses the “offset” function to return value based on the specified row and/or column from the reference cell. For example, in cell F21, the offset function returns value from the reference cell, i.e., F17 based on the given row position selected in cell C10.

Formula used: =OFFSET(F17,$C$10,0)

Step 3: Once the data is pulled out for the selected scenario, the revenue model can be populated by multiplying the number of bricks sold and the price per brick for the given year.