5 Ways Excel Sensitivity Analysis

Introduction to Excel Sensitivity Analysis

Excel sensitivity analysis is a powerful tool used in financial modeling and decision-making processes. It helps users understand how changes in input variables affect the output of a model. This analysis is crucial for businesses, as it enables them to identify potential risks and opportunities associated with different scenarios. In this article, we will explore five ways to perform sensitivity analysis in Excel.

What is Sensitivity Analysis?

Sensitivity analysis is a technique used to analyze how the output of a model changes when the input variables are modified. It helps users to identify the most critical variables that affect the outcome of a decision. By performing sensitivity analysis, users can test different scenarios, identify potential risks, and make informed decisions.

5 Ways to Perform Sensitivity Analysis in Excel

There are several ways to perform sensitivity analysis in Excel, including:

• Scenario Manager: This is a built-in Excel tool that allows users to create and manage different scenarios. Users can define input variables, create scenarios, and compare the results.
• What-If Analysis: This tool allows users to create a set of input values and analyze how they affect the output of a model. Users can create multiple what-if scenarios and compare the results.
• Goal Seek: This tool is used to find the value of an input variable that will produce a desired output. Users can set a target value for an output cell and Goal Seek will find the input value that achieves that target.
• Sensitivity Tables: This is a simple way to perform sensitivity analysis by creating a table that shows how the output of a model changes when the input variables are modified.
• Monte Carlo Simulations: This is a more advanced technique that uses random sampling to analyze the behavior of a model. Users can create multiple simulations and analyze the results to understand the potential risks and opportunities associated with different scenarios.

How to Perform Sensitivity Analysis using Scenario Manager

To perform sensitivity analysis using Scenario Manager, follow these steps:

• Define the input variables and output cells in your model
• Go to the Data tab and click on Scenario Manager
• Create a new scenario by clicking on the Add button
• Define the input values for the scenario
• Click on the Summary button to create a summary report

The summary report will show how the output of the model changes when the input variables are modified.

How to Perform Sensitivity Analysis using What-If Analysis

To perform sensitivity analysis using What-If Analysis, follow these steps:

• Define the input variables and output cells in your model
• Go to the Data tab and click on What-If Analysis
• Choose the type of what-if analysis you want to perform (e.g. scenario, sensitivity, or optimization)
• Define the input values and constraints for the what-if scenario
• Click on the Solve button to run the what-if analysis

The results will show how the output of the model changes when the input variables are modified.

How to Perform Sensitivity Analysis using Goal Seek

To perform sensitivity analysis using Goal Seek, follow these steps:

• Define the input variables and output cells in your model
• Go to the Data tab and click on Goal Seek
• Set the target value for the output cell
• Define the input variable that you want to adjust
• Click on the OK button to run the Goal Seek

The results will show the value of the input variable that achieves the target output value.

How to Perform Sensitivity Analysis using Sensitivity Tables

To perform sensitivity analysis using sensitivity tables, follow these steps:

• Define the input variables and output cells in your model
• Create a table that shows how the output of the model changes when the input variables are modified
• Use formulas to calculate the output values for different input scenarios
• Use charts and graphs to visualize the results

The sensitivity table will show how the output of the model changes when the input variables are modified.

How to Perform Sensitivity Analysis using Monte Carlo Simulations

To perform sensitivity analysis using Monte Carlo simulations, follow these steps:

• Define the input variables and output cells in your model
• Use a random number generator to create multiple simulations
• Run the simulations and analyze the results
• Use statistical analysis to understand the behavior of the model

The results will show the potential risks and opportunities associated with different scenarios.

📝 Note: Sensitivity analysis is a powerful tool, but it requires careful planning and execution to produce accurate results.

To illustrate the concept of sensitivity analysis, let’s consider an example. Suppose we have a company that wants to launch a new product. The company has estimated the demand for the product and the production costs. However, the company is unsure about the price of the product and wants to analyze how different price scenarios will affect the revenue and profitability.

Price Scenario	Revenue	Profitability
Low Price ($10)	$100,000	10%
Medium Price ($15)	$120,000	15%
High Price ($20)	$150,000	20%

In this example, the company can use sensitivity analysis to understand how different price scenarios will affect the revenue and profitability. The company can create a sensitivity table or use a what-if analysis tool to analyze the results.

In summary, sensitivity analysis is a powerful tool that can help businesses make informed decisions. By using the five methods outlined in this article, businesses can analyze how changes in input variables affect the output of a model and make informed decisions. Whether it’s using Scenario Manager, What-If Analysis, Goal Seek, Sensitivity Tables, or Monte Carlo Simulations, sensitivity analysis can help businesses identify potential risks and opportunities and make informed decisions.