How to Get Sensitivity Report in Excel Solver

Excel's Solver add-in can feel like a magic button for complex decisions, instantly finding the optimal solution to your problems. But the real power isn't just getting the "what" - it's understanding the "why." This is where the Sensitivity Report comes in, transforming Solver from a simple calculator into a powerful tool for strategic analysis. This article will walk you through how to generate and, more importantly, decode the Sensitivity Report to see how small changes can impact your optimal results.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is a Sensitivity Report?

Before diving into the "how-to," let's quickly clarify what we're working with. Excel Solver is an add-in used for optimization. You give it an objective (like maximizing profit or minimizing cost), a set of decision variables (like how many of each product to make), and a list of constraints (like budget limits or resource availability). Solver then finds the best possible combination of your variables to meet that objective.

A Sensitivity Report is one of the outputs Solver can create after finding a solution. Its purpose is to show you how "sensitive" your optimal solution is to changes in the model's inputs. It answers critical "what-if" questions without you having to re-run the entire model over and over again. For example, it can tell you:

• By how much can the price of a product change before my optimal production plan needs to be adjusted?
• How much extra profit would I make if I could get one more hour of labor?
• Which of my resource constraints are actually limiting my potential profit?

In short, it provides the context you need to make smarter, more resilient business decisions.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Setting Up a Simple Solver Model (A Quick Refresher)

To generate a sensitivity report, you first need an optimized Solver model. Let's use a classic product mix example. Imagine a small bakery that makes two popular items: Chocolate Mousse Cheesecakes and a gluten-free Energy Bar. Their goal is to maximize their total daily profit.

They have three main constraints: baking oven hours, specialized decorating station hours, and a limited supply of premium chocolate.

Here’s how you would set this up in Excel:

1. Structure Your Data

First, lay out your data clearly. This is the cornerstone of a readable and error-free model.

• Decision Variables (B3:B4): These are the cells Solver will change to find the solution. In our case, it's the Number to Make for each product. We leave these blank or set to 0 to start.
• Objective Cell (C7): This cell contains the value you want to maximize or minimize. Here, we want to maximize total profit. We use the SUMPRODUCT formula to calculate it:

=SUMPRODUCT(C3:C4,B3:B4)

This formula multiplies the number of Cheesecakes by their profit and the number of Energy Bars by their profit, then adds them together.

• Constraints (Cells G3:G5): These rows calculate the total resources used. We again use SUMPRODUCT to calculate the total usage for each resource based on the number of products made. For example, the formula in cell G3 for total oven hours is:

=SUMPRODUCT(E3:E4,B3:B4)

The cells in column I (I3:I5) contain the physical limits for each resource. We can’t use more than what’s available.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

2. Configure the Solver Add-In

If you don't see "Solver" in your "Data" tab, you'll need to enable it. Go to File > Options > Add-ins > Excel Add-ins (Go...) and check the box for "Solver Add-in."

Now, go to the Data tab and click on Solver. Here's how to fill out the dialog box:

1. Set Objective: Select your objective cell, C7. Set it to "Max" since we're maximizing profit.
2. By Changing Variable Cells: Select your decision variable cells, B3:B4.
3. Subject to the Constraints: Click "Add" to add your constraints.
4. Select a Solving Method: Choose Simplex LP. This is critical! The Sensitivity Report can only be generated for "linear" problems, and Simplex LP is the method for that.

How to Generate the Sensitivity Report

This is the easy part. Once your model is configured, simply click the Solve button. After a moment, Excel will find the optimal solution and the Solver Results dialog box will pop up.

Instead of just clicking "OK," look at the "Reports" box on the right. Select Sensitivity and then click OK.

Excel will keep the solution in your sheet and automatically create a new worksheet named "Sensitivity Report 1." Now, let's figure out what it all means.

Deconstructing the Sensitivity Report

The sensitivity report looks intimidating at first, but it’s just two simple tables. Let’s break each one down.

Table 1: Variable Cells

This upper table gives you information about your decision variables - in our case, the number of cheesecakes and energy bars to make.

• Final Value: This is the optimal solution Solver found. Here, it tells us the ideal production plan is 32 Cheesecakes and 25 Energy Bars.
• Reduced Cost: This is a key metric, especially for variables with a Final Value of zero. It tells you how much the objective coefficient (profit) of a variable must improve before it's worth including in the solution. Since both our products are profitable to make, their Reduced Cost is 0. If Solver decided not to make Energy Bars (Final Value = 0), a Reduced Cost of -2 would mean the profit for Energy Bars would need to increase by $2 before Solver would consider making even one.
• Objective Coefficient: This is simply the profit per unit that you originally entered into your model ($24 for Cheesecakes, $9 for Energy Bars).
• Allowable Increase & Allowable Decrease: This is the golden nugget! This range tells you how much the profit per unit (Objective Coefficient) can change without changing the optimal production plan (the Final Value). For example, the Cheesecake profit can increase by $10.50 or decrease by $6 without changing the plan to make 32 units. The total profit will change if you adjust the price, but the optimal mix of 32 Cheesecakes and 25 Bars will remain the same as long as you stay within this range.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Table 2: Constraints

The lower table is all about your constraints - your resources.

• Final Value: This shows how much of each resource was used in the optimal solution. You can see we used all 60 of our Decoration Station hours and all 150 kg of our Premium Chocolate. That means these are "binding" constraints, they are holding us back from making more profit. We only used 50 of our 80 oven hours, so that's a "non-binding" constraint.
• Shadow Price: This might be the single most important number in the entire report. The shadow price is the amount your objective value (total profit) will increase for a one-unit increase in that constraint.
• Constraint R.H. Side: This stands for "Right-Hand Side" and is simply the limit you set for your constraint (e.g., 60 Decoration Hours, 150 kg Chocolate).
• Allowable Increase & Allowable Decrease: Similar to the table above, this range shows how much you can change the constraint limit while the shadow price remains valid. For Decoration Hours, the shadow price of $1.91 is valid if you increase the available hours by up to 10 or decrease them by up to 26.6. If you found a way to add 15 more hours, the shadow price would change because some other constraint would become the new bottleneck.

Common Mistakes and Final Tips

Keep these points in mind as you work with sensitivity reports:

1. Must Use Simplex LP: If the "Sensitivity" option is grayed out, it's almost certainly because you chose a different solving method from Simplex LP. Sensitivity analysis is a key benefit of linear programming.
2. "1E+30" Means Infinite: In the Allowable Increase/Decrease columns, you might see a value like "1E+30". This is Excel's way of saying "a very large number," and you can interpret it as infinity. It means there is essentially no upper or lower limit.
3. One Change at a Time: The allowable ranges are only valid when you change one variable or constraint at a time. The report doesn't tell you what happens if you increase cheesecake profit and get more oven hours simultaneously.

Final Thoughts

The Excel Solver Sensitivity Report takes you from just getting an answer to truly understanding your model's dynamics. It shows you which assumptions are stable, where your biggest opportunities lie (binding constraints with high shadow prices), and provides a data-backed foundation for making strategic changes to your business.

Of course, building, running, and interpreting sophisticated models like this in spreadsheets takes time, and not everyone on your team will be an Excel wiz. We founded Graphed to solve this very problem. Instead of being stuck wrangling spreadsheet formulas, you can connect your business data and simply ask questions in plain English, like "Show me my most profitable product mix considering our inventory limits" or "What would be the revenue impact if we increase our ad spend by 15% on our top campaign?" Instantly, you get back easy-to-understand dashboards and insights, no Solver required.