How to Do Sensitivity Analysis in Excel (3 Suitable Examples)

When working with financial models or business data in Excel, it’s important to understand how small changes in key variables can impact your results. That is where sensitivity analysis comes in. It allows you to test different values and see how they impact your outcome, without changing your original model. In this guide, we will walk you through how to perform sensitivity analysis in Excel using tools like Data Tables, Goal Seek, and Solver.

What Is Sensitivity Analysis?

Sensitivity analysis is a method used to evaluate how different values of input variables affect the output. In Excel, it helps you test various what-if scenarios by changing one or more inputs and observing how those changes impact your results. This technique is especially useful in business, finance, and forecasting, where decision-making often depends on uncertain or variable factors.

In this section, we will perform a sensitivity analysis using the Data Table for a retirement fund. Suppose we have a dataset with the following inputs for retirement fund: Annual Contribution: $6,000 (in cell B2), Annual Interest Rate (%): 3% (in cell B3), Number of Years: 20 (in cell B4). Here, we will examine how changing one variable (interest rate) and two variables (interest rate and number of years) affect your total retirement fund amount.

One Variable Sensitivity Analysis

First, we will calculate the Final Retirement Fund value. To do so,

➤ Select cell B5, put the following formula, and press ENTER.

Here,

B3 is the interest rate per period (annual rate).
B4 is the total number of payment periods (number of years).
-B2 is the payment made each period (annual contribution), entered as a negative value because it’s an outflow.
0 represents the present value (since we’re starting with no initial fund).
1 indicates that payments are made at the beginning of each period.

This will display the projected final retirement fund value.

Now, let’s create the structure for our sensitivity analysis data table.

➤ In cell D1, enter “Annual Interest Rate (%)” and in cell E1, enter “Final Retirement Fund”.
➤ Below “Annual Interest Rate (%),” list the various interest rates you want to analyze, for example, from 3% to 10% in column D (cells D2:D9).

➤ Enter the formula in cell E2 and press ENTER.

This ensures the data table references your primary retirement fund formula. It links the cell to your original “Final Retirement Fund” calculation.

➤ Select cells D2 to E9, containing the column of varying interest rates and the “Final Retirement Fund” formula reference.
➤ Navigate to the Data tab in the Excel ribbon.
➤ In the Forecast group, click on What-If Analysis.
➤ From the dropdown menu, select Data Table.

This will open the Data Table dialog box.

➤ In the Data Table dialog box:

Leave “Row input cell” blank, as our varying input is in a column.
For “Column input cell“, select the cell that holds your original Annual Interest Rate (%), which is B3.

This tells Excel to substitute the values from column D into cell B3 to calculate the new retirement fund values.

➤ Click OK.

Finally, your data table will populate, clearly showing the calculated “Final Retirement Fund” for each “Annual Interest Rate” you specified. This allows for a straightforward sensitivity analysis of your retirement planning.

Two Variable Sensitivity Analysis

Two variable sensitivity analysis lets you examine how changes in two input variables affect a single output in your model. In this part, we will demonstrate how changing “Annual Interest Rate” and “Number of Years” simultaneously impact the “Final Retirement Fund” value.

Just like the previous data, ensure your “Annual Contribution,” “Annual Interest Rate (%),” and “Number of Years” inputs are set up. Your “Final Retirement Fund” in cell B5 should also be calculated using the FV function, referencing these inputs.

Now, let’s prepare the grid for our two-variable data table. Along the top row, starting from cell B7 and moving rightward, list the different numbers of years you want to analyze, for example, 20, 22, 24, …, 30. Down the first column, starting from cell A8 and moving downward, list the various Annual Interest Rates (%) you want to analyze, for example, 3%, 4%, …, 10%.

➤ In cell A7, link this cell to your “Final Retirement Fund” by entering the formula.

This is the intersection point for your two varying inputs.

➤ Select cells A7 to G15 and go to the Data tab in the Excel ribbon.
➤ In the Forecast group, click on What-If Analysis.
➤ From the dropdown menu, select Data Table.

This will open the Data Table dialog box.

➤ In the Data Table dialog box:

For “Row input cell“, select the cell that holds your original Number of Years, which is B4.
This tells Excel to substitute the values from your top row (years) into cell B4.
For “Column input cell“, select the cell that holds your original Annual Interest Rate (%), which is B3.
This tells Excel to substitute the values from your first column (interest rates) into cell B3.

➤ Click OK.

Finally, Excel will generate the entire two-variable data table, providing the amount of “Final Retirement Fund” for every combination of “Annual Interest Rate” and “Number of Years”. This allows for a detailed sensitivity analysis, revealing how both variables collectively impact your long-term retirement savings.

Goal Seek is a built-in Excel tool that helps you find the right input value to get a specific result. It’s useful in sensitivity analysis when you want to see how changing one input affects your outcome.

Suppose we have a dataset containing various components like Selling Price per Unit as $300, Cost per Unit as $180, Fixed Cost as $10,000, and Units Sold as 150. Here, we will calculate how many units we need to sell to reach a net profit of $15000.

➤ In cell B7, enter the formula.

It will calculate the Total Revenue by multiplying the Selling Price per Unit by the Units Sold.

➤ In cell B8, enter the formula below.

This will determine the Total Variable Cost by multiplying the Cost per Unit by the Units Sold.

➤ In cell B9, write down the following formula.

This way, we will get the Total Cost by summing the Fixed Cost and the Total Variable Cost.

➤ In cell B10, insert the formula.

Thus, computing the Net Profit by subtracting the Total Cost from the Total Revenue.

Now, we will perform a sensitivity analysis using the Goal Seek feature. This will help us determine the Units Sold needed to achieve a target Net Profit of $15,000.

➤ Navigate to the Data tab from the menu bar.
➤ Click on What-If Analysis in the Forecast group.
➤ Select Goal Seek from the dropdown menu.

The Goal Seek dialog box will appear.

➤ In the Set cell field, enter B10. This is our Net Profit cell.
➤ In the To value field, type 15000. This is our target profit.
➤ In the By changing cell field, enter B5. This is the cell representing Units Sold that we want to change.
➤ Finally, click OK.

As a result, Excel will calculate the necessary Units Sold (approximately 208) to reach a Net Profit of $15,000, and the values in your spreadsheet will update accordingly.

Solver is an advanced Excel tool that lets you find the best possible result by changing multiple inputs while following specific rules or limits. In sensitivity analysis, you can use Solver to maximize profit, minimize cost, or meet a target value by adjusting several variables at once. Here, we will use Solver to find the best combination of Mountain and Road bikes that maximizes total profit, while staying within the limits of available labor hours and materials.

Suppose we have a dataset of Parameters, Constraints, and Target Production for Mountain Bikes and Road Bikes.

Here are the Parameters for both Mountain Bike and Road Bike:
Profit per Unit: $65 (Mountain Bike), $50 (Road Bike)
Labor Hours per Unit: 3 (Mountain Bike), 2 (Road Bike)
Material per Unit (kg): 5 (Mountain Bike), 4 (Road Bike)

For the Constraints:
Total Labor Hours Available: 250
Total Material Available (kg): 380

Below we have an “Items” section with “Mountain Bike” and “Road Bike” to define the Target Production.

➤ First, set the values to 0 for “Mountain Bike” and “Road Bike” in the “Items” section.

Now, we will calculate the Total Profit based on the number of units produced for each bike type.

➤ In cell B10, put the formula down.

Next, we will determine the Total Labor Hours Used for production.

➤ In cell B11, input the formula.

Similarly, we will calculate the Total Material Used during the production process.

➤ In cell B12, enter the formula.

Now, we will use the Solver add-in to determine the optimal production of Mountain Bikes and Road Bikes to maximize Total Profit while adhering to labor and material constraints.

➤ Go to the Data tab in the Excel ribbon.
➤ In the Analyze group, click on Solver.

The Solver Parameters dialog box will appear.

➤ In the Set Objective field, select cell B10 (Total Profit).
➤ Choose Max to maximize the profit.
➤ In the By Changing Variable Cells field, select cells B15 and B16 (Target Production for Mountain Bike and Road Bike).
Now, we need to add the constraints.
➤ Click the Add button next to “Subject to the Constraints“.

A new Add Constraint dialog box will open. Here, we will add a labor hour constraint.

➤ In the Cell Reference box, select cell B11 (Total Labor Hours Used).
➤ From the dropdown, choose the <= operator.
➤ In the Constraint box, select cell B7 (Total Labor Hours Available).
➤ Click Add to add this constraint.

As you can see, we have successfully added the constraints in the Solver Parameters.

➤ Similarly, we will add the rest of the constraints in the box.

For Cell Reference B11, choose the <= operator. In the Constraint box, select cell B7.
For Cell Reference: B15, choose int (integer).
For Cell Reference: B16, choose int (integer).
For Cell Reference: B15, choose >= and enter 1 in the Constraint box.
For Cell Reference: B16, choose >= and enter 1 in the Constraint box.

➤ After adding all the constraints, finally, click Solve.

As a result, the Target Production values in cells B15 and B16 will update to show the optimal number of Mountain Bikes and Road Bikes to produce, which will also maximize the Total Profit in cell B10 while respecting all your defined constraints.

Frequently Asked Questions

What is the difference between what-if analysis and sensitivity analysis in Excel?

What-if analysis is a broader term that includes tools like Scenario Manager, Goal Seek, and Data Tables. Sensitivity analysis is a specific type of what-if analysis that focuses on how changes in input variables affect your model’s outcome.

Can I use Goal Seek for multiple variables?

No, Goal Seek can only change one input cell to reach a desired result. If you need to adjust multiple variables, use the Solver tool instead.

Do I need to enable Solver manually in Excel?

Yes, Solver is not enabled by default. Go to File > Options > Add-ins > Excel Add-ins > Go, then check the box for Solver Add-in and click OK.

Concluding Words

Above, we have covered the key techniques to perform sensitivity analysis in Excel, including one-Variable and two-Variable Data Tables, Goal Seek, and Solver. These methods help you understand how changes in your inputs affect the results. Whether you’re analyzing profits, costs, or any other outcome, sensitivity analysis gives you a clear picture of your model’s behavior. If you have any questions, feel free to share them in the comment section below.