Twitterfox949

Sensitivity report in Excel LP for problems 7-31 and 33; Quantitative Analysis for Management.

J E Beasley

OR-Notes are a series of introductory notes on topics that fall under the broad heading of the field of operations research (OR). They were originally used by me in an introductory OR course I give at Imperial College. They are now available for use by any students and teachers interested in OR subject to the following conditions.

• Mar 03, 2020 Load the Solver Add-in in Excel. In Excel 2010 and later, go to File Options. Click Add-Ins, and then in the Manage box, select Excel Add-ins. In the Add-Ins available box, select the Solver Add-in check box, and then click OK. After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.
• Apr 18, 2021 (The solver is controlled via VBA and calulates the correct result, no problem.) BUT if I then want to accept the solver solution and click on the limits report to generate it, the column width of my sheet is changed and also the values from some cells are deleted. This does not happen with the other reports (Answer and Sensitivity).

A full list of the topics available in OR-Notes can be found here.

Linear programming - sensitivity analysis - using Solver

Recall the production planning problem concerned with four variants of the same product which we formulated before as an LP. To remind you of it we repeat below the problem and our formulation of it.

Production planning problem

A company manufactures four variants of the same product and in the final part of the manufacturing process there are assembly, polishing and packing operations. For each variant the time required for these operations is shown below (in minutes) as is the profit per unit sold.

• Given the current state of the labour force the company estimate that, each year, they have 100000 minutes of assembly time, 50000 minutes of polishing time and 60000 minutes of packing time available. How many of each variant should the company make per year and what is the associated profit?
• Suppose now that the company is free to decide how much time to devote to each of the three operations (assembly, polishing and packing) within the total allowable time of 210000 (= 100000 + 50000 + 60000) minutes. How many of each variant should the company make per year and what is the associated profit?

Production planning solution

Variables

Let:

x_i be the number of units of variant i (i=1,2,3,4) made per year

T_ass be the number of minutes used in assembly per year
T_pol be the number of minutes used in polishing per year
T_pac be the number of minutes used in packing per year

where x_i >= 0 i=1,2,3,4 and T_ass, T_pol, T_pac >= 0

Excel Solver Not Generating Sensitivity Report In Excel

Constraints

(a) operation time definition

T_ass = 2x₁ + 4x₂ + 3x₃ + 7x₄ (assembly)
T_pol = 3x₁ + 2x₂ + 3x₃ + 4x₄ (polish)
T_pac = 2x₁ + 3x₂ + 2x₃ + 5x₄ (pack)

(b) operation time limits

The operation time limits depend upon the situation being considered. In the first situation, where the maximum time that can be spent on each operation is specified, we simply have:

T_ass <= 100000 (assembly)
T_pol <= 50000 (polish)
T_pac <= 60000 (pack)

In the second situation, where the only limitation is on the total time spent on all operations, we simply have:

T_ass + T_pol + T_pac <= 210000 (total time)

Objective

Presumably to maximise profit - hence we have

maximise 1.5x₁ + 2.5x₂ + 3.0x₃ + 4.5x₄

which gives us the complete formulation of the problem.

Solution - using Solver

Below we solve this LP with the Solver add-in that comes with Microsoft Excel.

If you click here you will be able to download an Excel spreadsheet called lp.xls that already has the LP we are considering set up.

Look at Sheet A in lp.xls and to use Solver do Tools and then Solver. In the version of Excel I am using (different versions of Excel have slightly different Solver formats) you will get the Solver model as below:

but where now we have highlighted (clicked on) two of the Reports available - Answer and Sensitivity. Click OK and you will find that two new sheets have been added to the spreadsheet - an Answer Report and a Sensitivity Report.

As these reports are indicative of the information that is commonly available when we solve a LP via a computer we shall deal with each of them in turn.

Answer Report

The answer report can be seen below:

This is the most self-explanatory report.

We can see that the optimal solution to the LP has value 58000 (£) and that T_ass=82000, T_pol=50000, T_pac=60000, X₁=0, X₂=16000, X₃=6000 and X₄=0.

Note that we had three constraints for total assembly, total polishing and total packing time in our LP. The assembly time constraint is declared to be 'Not Binding' whilst the other two constraints are declared to be 'Binding'. Constraints with a 'Slack' value of zero are said to be tight or binding in that they are satisfied with equality at the LP optimal. Constraints which are not tight are called loose or not binding.

Sensitivity Report

The sensitivity report can be seen below:

This sensitivity report provides us with information relating to:

• changing the objective function coefficient for a variable
• forcing a variable which is currently zero to be non-zero
• changing the right-hand side of a constraint.

We deal with each of these in turn, and note here that the analysis presented below ONLY applies for a single change, if two or more things change then we effectively need to resolve the LP.

Changing the objective function coefficient for a variable

To illustrate this suppose we vary the coefficient of X₂ in the objective function. How will the LP optimal solution change?

Currently X₁=0, X₂=16000, X₃=6000 and X₄=0. The current solution value for X₂ of 16000 is in cell B3 and the current objective function coefficient for X₂ is 2.5. The Allowable Increase/Decrease columns tell us that, provided the coefficient of X₂ in the objective function lies between 2.5+2 = 4.5 and 2.5 - 0.142857143 = 2.3571 (to four decimal places), the values of the variables in the optimal LP solution will remain unchanged. Note though that the actual optimal solution value will change as the objective function coefficient of X₂ is changing.

In terms of the original problem we are effectively saying that the decision to produce 16000 of variant 2 and 6000 of variant 3 remains optimal even if the profit per unit on variant 2 is not actually 2.5 (but lies in the range 2.3571 to 4.50). Similar conclusions can be drawn about X₁, X₃ and X₄.

Forcing a variable which is currently zero to be non-zero

For the variables, the Reduced Cost column gives us, for each variable which is currently zero (X₁ and X₄), an estimate of how much the objective function will change if we make (force) that variable to be non-zero. Note here that the value in the Reduced Cost column for a variable is often called the 'opportunity cost' for the variable.

Hence we have the table

where we ignore the sign of the reduced cost when constructing the above table. The objective function will always get worse (go down if we have a maximisation problem, go up if we have a minimisation problem) by at least this estimate. The larger A or B are the more inaccurate this estimate is of the exact change that would occur if we were to resolve the LP with the corresponding constraint for the new value of X₁ or X₄ added.

Note here than an alternative (and equally valid) interpretation of the reduced cost is the amount by which the objective function coefficient for a variable needs to change before that variable will become non-zero.

Hence for variable X₁ the objective function needs to change by 1.5 (increase since we are maximising) before that variable becomes non-zero. In other words, referring back to our original situation, the profit per unit on variant 1 would need to increase by 1.5 before it would be profitable to produce any of variant 1. Similarly the profit per unit on variant 4 would need to increase by 0.2 before it would be profitable to produce any of variant 4.

Changing the right-hand side of a constraint

For each constraint the column headed Shadow Price tells us exactly how much the objective function will change if we change the right-hand side of the corresponding constraint within the limits given in the Allowable Increase/Decrease columns

Hence we can form the table

For example for the polish constraint, provided the right-hand side of that constraint remains between 50000 + 40000 =90000 and 50000 - 10000 = 40000 the objective function change will be exactly 0.80[change in right-hand side from 50000].

The direction of the change in the objective function (up or down) depends upon the direction of the change in the right-hand side of the constraint and the nature of the objective (maximise or minimise).

To decide whether the objective function will go up or down use:

• constraint more (less) restrictive after change in right-hand side implies objective function worse (better)
• if objective is maximise (minimise) then worse means down (up), better means up (down)

Hence

• if you had an extra 100 hours to which operation would you assign it?
• if you had to take 50 hours away from polishing or packing which one would you choose?
• what would the new objective function value be in these two cases?

Excel Solver Nt Generating Sensitivity Reporting

The value in the column headed Shadow Price for a constraint is often called the 'marginal value' or 'dual value' for that constraint.

Note that, as would seem logical, if the constraint is loose the shadow price is zero (as if the constraint is loose a small change in the right-hand side cannot alter the optimal solution).

Comments

• Different LP packages have different formats for input/output but the same information as discussed above is still obtained.
• You may have found the above confusing. Essentially the interpretation of LP output is something that comes with practice.
• Much of the information obtainable (as discussed above) as a by-product of the solution of the LP problem can be useful to management in estimating the effect of changes (e.g. changes in costs, production capacities, etc) without going to the hassle/expense of resolving the LP.
• This sensitivity information gives us a measure of how robust the solution is i.e. how sensitive it is to changes in input data.

Note here that, as mentioned above, the analysis given above relating to:

• changing the objective function coefficient for a variable; and
• forcing a variable which is currently zero to be non-zero; and
• changing the right-hand side of a constraint

is only valid for a single change. If two (or more) changes are made the situation becomes more complex and it becomes advisable to resolve the LP.

By Francisco Yuraszeck

Using a professional optimization tool like Premium Solver Pro offers multiple benefits that are quickly identified by those starting to learn about the fascinating world of Operations Research.

Excel Solver Nt Generating Sensitivity Report

In my experience as an Operations Research professor, I have personally verified the benefits of computationally implementing optimization models of varying complexity in an intuitive and reliable learning environment. In this sense, students can quickly acquire the necessary skills to deal with different size optimization problems in a platform known, as it turned out.

Excel Solver Nt Generating Sensitivity Reports

Furthermore, it is worth noting that Premium Solver Pro not only allows us to solve optimization models, but also offers the opportunity to create Sensitivity Reports once we have reached the optimal solution and optimal value of the base model. In this context, the sensitivity or post optimal analysis seeks to analyze the impact that a modification of one or several parameters has on the results of a model.

A Premium Solver Pro Sensitivity Report is divided into 3 parts:

Excel Solver Nt Generating Sensitivity Reported

1. Objective Cell
2. Decision Variable Cells
3. Constraints

In the following, we present a simple model of Linear Programming, which will be computationally implemented, giving us the Sensitivity Report that we will then analyze in detail, in order to interpret it in the correct manner.

By using Premium Solver Pro to solve the previous model, we reach the optimal solution X₁ = 3 and X₂ = 6 , with an optimal value V(LP) = 342 .

In the following, we can obtain the Sensitivity Report by clicking on the module Reports > Optimization > Sensitivity, which will show us the following:

Once we request the Sensitivity Report, a new page will be generated in the Excel file in which we are working, with a report on the results. For the example proposed in this article, we get the following results:

Excel Solver Not Showing Sensitivity Report

In the following section, we will go over how to interpret each of the three parts that the Sensitivity Report gives us to solve.

1. Objective Cell (Max): The optimal value, which is to say, the value reached when evaluating the optimal solution in the objective function, is 342. This value is obtained by: V(P) = 34(3) + 40(6) = 342 .
2. Decision Variable Cells: In this section, we can identify the optimal solution (the values in the column labeled Final Value), the coefficients or parameters in the objective function (the values in the Objective Coefficient column), the allowable increase and the allowable decrease for each of the individual coefficients in the objective function, which allows us to guarantee that the current optimal solution is maintained.

For example, let’s consider the coefficient c₁ = 34 associated with the variable of decision x₁ in the maximization of the objective function. The allowable decrease for said parameter is 7,3 (equivalent to 22/3) units, while the allowable increase is 6, such that if → then we maintain the original optimal solution (note that it is assumed that for this analysis, the rest of the model parameters maintain their initial values). Similarly, we recommend that the reader check that the variation interval for keeps the current optimal solution, which is c₂ℇ [34,51] .

It is also interesting to interpret what is known as the Shadow Price of a constraint.

• The Shadow Price corresponds to the exchange rate of the Linear Programming model’s optimal value compared to the marginal modification of the right hand side (RHS) of the constraint. It is understood that a marginal modification allows the conservation of the optimal base of the problem (identical original basic variables in the case of the Simplex Method) or the geometry of the problem (maintain the original active constraints).

Given the previous definition, it is naturally understood that the Shadow Price of Constraint 3 is zero. If the right side represents the availability of a resource (for example, man hours, units of raw material, etc.), 12 of the 16 available units of the resource are used in the original optimal solution (which is consistent with the allowable decrease of 4 units and an allowable increase of 1E+30 or infinity). Additionally, since Constraint 3 is inactive, any variation on the right side of said constraint in the interval not only conserves the optimal value, but also maintains the optimal original solution.

The case of Constraints 1 and 2 is different. For example, if Constraint 1 increases on the right side in one unit, moving from 48 to 49 units (note that the allowable increase for said parameter is 6 units), the optimal value will increase proportional to the Shadow Price of said constraint: . Similarly, if, for example, instead of Constraint 1 increasing on the right side, it falls from 48 to 45 (a drop of 3 units, which is within the permitted range), the new optimal value will be: . We recommend that the reader verify these results him or herself after reoptimizing according to the proposed changes.

• In summary, it is evident that the usefulness of Premium Solver Pro lies not only in its ability to implement and computationally solve optimization models. When it comes to correctly interpreting the sensitivity analysis that the program gives us, it allows us to save time by avoiding having to re-optimize many times over. Furthermore, it allows us to better understand the structure of the optimal solution, which is not just limited to identifying values that fit the decision variables in the computational solution.

About the author Francisco Yuraszeck:

University professor in Operational Research and also the owner of the sites www.linearprogramming.info and www.gestiondeoperaciones.net (spanish) which provide in a very simple and educational way the basics and most important topics within this field.