How To Build Scenario Analysis in Excel in 6 Steps using the INDEX, CHOOSE, or OFFSET functions

Building scenario analysis in Excel using the INDEX, CHOOSE, or OFFSET functions can be a powerful way to create dynamic and flexible financial models. This method allows users to easily switch between different scenarios and observe the impact on key metrics without manually adjusting each input variable. Here's a step-by-step guide on how to implement scenario analysis using these functions: 

Step 1: Set Up Your Model 

Start by setting up your financial model in Excel, including all necessary input variables, calculations, and output metrics. Organize your model in a logical and structured manner, with inputs on one sheet, calculations on a different sheet, and output on another. For a financial model, the output will be your three (3) financial statements: Income Statement, Balance Sheet, and Cashflow Statement. 

Remember you can decide to have a model built with a vertical tab structure or horizontal tab structure.  

Step 2: Define Scenarios 

Identify the different line items you want to build scenarios for in your model. Identify the different conditions you want to use for each line item, these could include base-case, best-case, worst-case, or any other scenarios relevant to your analysis. Assign a numerical identifier to each scenario (e.g., 1 for base-case, 2 for best-case, 3 for worst-case). Highlight the three cells and give them a name. E.g . S_List.  

Step 3: Create a Scenario Selector 

On your scenario sheet, create a dropdown list or input cell where users can select the scenario they want to analyze. This dropdown list will be used to switch between scenarios dynamically. You can use data validation but preferably you can draw up a combo box and format it where the input range will be the three scenarios that were grouped and named S_List and the cell link will be the cell that will contain the switch named S_Switch.  

Data validation can also be used to achieve the same objective but it has a limitation. Let’s talk about the limitations of data validation. 

• The main limitation of using data validation is that you can't tell if there is a drop-down for the scenario in a cell because the little angle will not show until you click on the cell.  
• In using Data validation to create a scenario in your model, you can only make use of the CHOOSE, INDEX, and OFFSET functions with the help of the MATCH function in Excel because data validation will effectively work with text and not numbers. You need the MATCH function to help automate the number argument in those functions.  

Step 4: Create the input scenario cases 

Bring in the actual assumption from the case study or model which will represent the base case, and afterward create an assumption for the the best case and worst case.  In this case, we are working with a cost of sales assumption.

Step 5i: Using the INDEX Function 

The INDEX function returns the value of a cell in a specified range based on the row and column number. You can use the INDEX function to dynamically select values from a predefined range based on the scenario index. The INDEX request for the argument (Array, Row_num, Column_num)  

For example, if cell L24 contains the scenario switch, you can use the INDEX function to calculate the scenario that will be used in the model. =INDEX – the first argument is the Array which will be the scenario assumption for the three cases in year 1 – L27, L28, and L29, then the Row_num which is the number of times the cell reference should move down which will feed from the scenario switch.  

Step 5ii: Using the CHOOSE Function 

Your calculations will be performed with the CHOOSE function in Excel. Where scenario-specific values are used, replace direct references with the CHOOSE function which will be used in the model. The CHOOSE function allows you to select a value from a list of options based on a specified index number. 

For example, if cell L35 contains the scenario switch (1 for base case, 2 for best case, and 3 for worst case) you can use the CHOOSE to select the appropriate value. =CHOOSE(L35 --- which was renamed to S_Switch --- then you select the scenario cases one after the other Base Case - L38,Best Case - L39,Worse Case - L40. 

=CHOOSE(S_Switch, L38,L39,L40) 

This means when the scenario switch shows 1 I want a base case, 2 for the best case, and 3 for worst case. 

Step 5iii: Using the OFFSET Function 

The OFFSET function returns a reference to a range of cells offset from a starting point by a specified number of rows and columns. You can use the OFFSET function to dynamically select a range of values based on the scenario switch. 

For example, if cell L43 contains the scenario switch, you can use the OFFSET function to calculate the scenario that will be used in the model. =OFFSET – the OFFSET function requests for a reference which is the start point before it moves to any cell.  

The next argument is the Rows, asking you how many rows downward you want it to move. In this case, you can reference cell L46 which we renamed as S_Switch, and for the Cols, Height, and Weight command you don’t need it so you can add a comma sign and press Enter.  

Step 6: Test and Validate 

Once you've set up your scenario analysis using the INDEX, CHOOSE, or OFFSET functions, test it rigorously to ensure accuracy and reliability. Switch between different scenarios using the scenario Switch and verify that the model responds correctly to changes. 

Conclusion 

By using the INDEX, CHOOSE, and OFFSET functions in Excel, you can create dynamic and interactive scenario analysis models that enable users to explore different scenarios and make informed decisions based on the results. You could also decide to use other names for your scenario such as moderate scenario, optimistic scenario, and pessimistic scenario.