This blog will shed light on how to make switches in MS Excel.
Financial modeling commonly involves stress testing critical assumptions to sensitize forecasts. This includes testing the financial model’s numbers under different scenarios. These 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 should build switches in a model to offer flexibility for the user to switch between different sets of assumptions or model cases, as it is known in the financial space. The variables that have the greatest impact on business and the ones with uncertain and contrasting future paths are identified as scenarios. As a good modeling practice, these scenarios should be realistic and descriptive, and should not outnumber 3–4 cases. Each scenario should clearly depict the modeler’s views on the macro or micro issues related to the business. While the names should be descriptive, we often resort to labels such as upside case, management case, and downside case to capture 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 company’s revenue drivers – 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 has 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.
- 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”, 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 iffunction. 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)))
- 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,)
- 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)
- 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.