Self referencing IF statements allow you to present the results of multiple scenarios regardless of the individual scenario being run in the model. Financial analysts use scenarios in financial models to establish how a model’s output will change based on various inputs changing. These inputs can be stored as scenarios. For example, you may want to establish the results of various earnings metrics (such as EBIT, EBITDA and Net Income) based on different sales growth scenarios. The illustration below provides an example of how three sales growth scenarios may be incorporated into a three statement model.
Scenario analysis excel template
The three sales growth scenarios are located in cells D4:H6. CHOOSE functions located in cells D8:H8 will return the corresponding growth rate of one of these scenarios using a numerical switch located in cell C2. Sales growth formulas are located in cells D14:H14 and reference the corresponding growth rates in cells D8:H8. The numbers in the illustration are based on the “best case” scenario as “1” has been input into the switch cell C2.
The results of these scenarios can be presented in three tables using “Self Referencing If Statements” regardless of the scenario being run in the main model. This is a great way of showing the results based on all three scenarios without having to have three separate models. This scenario output is illustrated below:
Creating scenarios in Excel
- Build the model shown in the first illustration or download it.
- Enter the labels shown in cells B25:B38.
- Enter the following “Self Referencing If” formulas as shown in the illustration below:
The formula in cell D26 returns year 1 sales if scenario 1 is active or itself if any other scenario is active. The formulas in cells D27 and D28 do the same thing, but for EBIT and EBITDA. The formula in cell D31 returns year 1 sales, if scenario 2 is active or itself if any other scenario is active. The formulas in cells D32 and D33 do the same thing, but for EBIT and EBITDA. And so on!
- Copy the formulas across.
- To fully populate the tables:
- Ensure iterations are active (activate these in the Excel Options dialog box)
- Run all the scenarios by entering 1, 2 and 3 in cell C2 one after the other.
- There are a number of issues to be aware of with Self Referencing Ifs:
- Whenever any of the assumptions of the model change, the scenarios need to be rerun as described in point 5 above.
These tables create intentional circularity. It is therefore advisable to add a circular toggle to the Self Referencing If statements. An example of this is shown below:
When the toggle is used, the scenarios need to be run again. Also the circular toggle should be dedicated to the Self Referencing Ifs and not be used for the main part of the model.
- Download the Self Referencing IFs Scenario Template
- Download the Self Referencing IFs Scenario Answer
Please do not hesitate to contact us, if you are having trouble viewing or accessing this article.
Copyright© 2016 AMT Training
