How to Incorporate Scenario and Sensitivity in a Model

Mastering Scenario and Sensitivity Analysis in Financial Modeling 

In the world of financial modeling, predicting the future can feel like peering into a crystal ball—exciting but fraught with uncertainty. Luckily, tools like scenario and sensitivity analysis help us navigate this uncertainty, making our predictions more robust and reliable. Let’s dive into the essentials of these techniques and learn how to implement them effectively in Excel. 

Understanding Scenario and Sensitivity Analysis 

Scenario Analysis involves evaluating the impact of different changes in variables on the output or performance of a business or investment. It’s like setting up multiple "what-if" situations to see how different factors affect your bottom line. For instance, you might want to know how changes in price, quantity produced, or customer behavior could impact your revenues. 

Sensitivity Analysis, on the other hand, assesses how the variation in one specific input affects the output. It’s a bit like tweaking one knob at a time to see how it changes the music. This helps identify the most influential factors affecting business performance. 

Why Incorporate These Analyses? 

• Reduce Risks and Uncertainty: By testing various assumptions and scenarios, you can foresee potential risks and prepare accordingly. 
• In-depth Examination of Outcomes: Understand the potential impact of different situations and make informed decisions. 
• Identify Influential Factors: Sensitivity analysis helps pinpoint which variables most significantly impact your business. 

Practical Steps to Implementing Scenario Analysis 

Create a Scenario List and Switch: 

• Scenario List: Define different scenarios (e.g., base case, best case, worst case). 

• Scenario Switch: Use an input cell in Excel to switch between these scenarios. 
• Use Excel Functions: 

• CHOOSE Function: Select a value from a list of values based on an index number. 

• INDEX Function: Returns the value of an element in a table or array, selected by row and column index numbers. 

• OFFSET Function: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. 
• Build the Model: 

• Input your price per unit, volume produced, and other relevant data for each scenario. 

• Use the functions mentioned to dynamically switch between scenarios and  

Hands-On Demo: Incorporating Scenario and Sensitivity Analysis in Excel 

During the webinar, we walked through a practical demonstration of incorporating scenario and sensitivity analysis in Excel. Here’s a step-by-step breakdown of the demo: 

Scenario Analysis 

1. Setting Up the Scenario List and Switch 

• Scenario List: 

• We created a list of scenarios including Base Case, Best Case, and Worst Case. 

• Each scenario had different values for key variables such as price per unit, volume produced, and costs. 

• Scenario Switch: 

• We designated an input cell in Excel as the "Scenario Switch" to allow users to toggle between different scenarios. This cell was formatted and named scenario_switch for easy reference. 

2. Using Excel Functions to Implement Scenarios 

• CHOOSE Function: 

• We used the CHOOSE function to select a value from a list based on the index number provided by the Scenario Switch. For instance, =CHOOSE(scenario_switch, BaseCasePrice, BestCasePrice, WorstCasePrice) dynamically selected the price per unit based on the selected scenario. 

• INDEX Function: 

• We employed the INDEX function to reference the appropriate value from a range. For example, =INDEX(ScenarioRange, scenario_switch) picked the corresponding value from a pre-defined range of scenario values. 

• OFFSET Function: 

• The OFFSET function was used to return a reference to a cell that is a specified number of rows and columns from a given reference. This allowed us to dynamically adjust values based on the scenario switch. 

3. Building the Model 

• Input Data: 

• We input different values for each scenario. For instance, the base case had a price per unit of 20, the best case had 25, and the worst case had 15. 

• Similar values were set for volume produced and costs. 

• Linking Scenarios to the Model: 

• Using the functions mentioned, we linked the scenario values to the main financial model. This ensured that switching between scenarios dynamically updated the model outputs like revenue, costs, and operating profit. 

4. Creating a Dropdown for Easy Scenario Switching 

• Combo Box: 

• To make it user-friendly, we added a Combo Box (dropdown) using Excel’s Developer tools. 

• The Combo Box was linked to the scenario_switch cell, allowing users to select the desired scenario from a dropdown menu. 

Sensitivity Analysis 

1. Goal Seek 

• Setup: 

• We used a simple financial model with inputs for price per unit, quantity produced, and costs. 

• The operating profit was calculated based on these inputs. 

• Using Goal Seek: 

• To determine the required quantity to achieve a target operating profit, we used the Goal Seek function (Data > What-If Analysis > Goal Seek). 

• For example, we set the operating profit cell to a target value (e.g., 4000) by changing the quantity produced cell. Goal Seek iterated to find the required quantity to meet the target profit. 

2. Data Table for Sensitivity Analysis 

• Setup: 

• We created a data table with different combinations of price per unit and quantity produced. 

• Creating the Data Table: 

• The table was set up with price per unit values in the top row and quantity values in the first column. 

• The intersection cell (top-left) was linked to the operating profit formula. 

• Using Data Table Function: 

• We highlighted the entire table range and used the Data Table function (Data > What-If Analysis > Data Table). 

• For the row input cell, we selected the price per unit input. For the column input cell, we selected the quantity produced input. 

• Excel then populated the table with the calculated operating profit for each combination of price and quantity, showing how sensitive the profit was to changes in these variables. 

• see their impacts. 

Bringing It All Together 

By combining scenario and sensitivity analyses, you can create a robust financial model that accounts for various possibilities and uncertainties. This dual approach not only enhances your predictive capabilities but also strengthens your decision-making process. 

Bonus Tip: To streamline your analysis, use Excel’s developer tools to create a dropdown menu for easy scenario switching. This user-friendly feature makes it simpler to toggle between different cases and observe the impacts in real time. 

Want to learn more? Join our upcoming Advanced Financial Modeling course and become a pro at building world-class financial models. Whether you're an aspiring analyst or a seasoned professional, our training sessions will equip you with the skills to excel in the field.