How to Calculate Shadow Price Linear Programming in Excel

Here’s an overview of shadow price calculations.

Calculate Shadow Price Linear Programming in Excel: Step-by-Step Procedures

The sample dataset contains information on a wood shop where Wooden Sofas and Wooden Beds are made. The shop gets 200 and 300 in benefits per Sofa and Bed, respectively. But, there are some constraints. From this dataset, we will find out the maximum profit. We will calculate the shadow price with linear programming.

STEP 1 – Prepare the Dataset

Use the following formula in the E6 cell:

=(C6*C5)+(D6*D5)

Press Enter to get the result.

Use the following formula in the E7 cell:

=C7*C5+D7*D5

Press Enter to proceed.
We will get the LHS (Left Hand Side) of a constraint.

Use the following formula in the E7 cell:

=C8*C5+D8*D5

Press Enter to proceed.
We will get the LHS (Left Hand Side) of another constraint.

Use the following formula in the E7 cell:

=C9*C5+D9*D5

Press Enter.
We will get the LHS (Left Hand Side) of another constraint.

Total Profit, which is our objective function, comes from the summation of profits of wooden sofas and beds. The total profit of wooden sofas comes from the multiplication of profit per sofa and the number of sofas. Similarly, we can get the profit of the wooden bed by multiplication. After summation of these 2, we can get the total profit. By using the same formula, we have calculated the LHS for these constraints depending on the number of sofas and beds of the constraints.

STEP 2 – Use the Solver Feature

Click on Data and choose Solver to add the solver.

You’ll get a window named Solved Parameters.
Put $E$6 in the Set Objective field. The E6 cell indicates the profit of the shop.
In the By Changing Variable Cells field, insert $C$5:$D$5. These 2 cells are variables.
Click on the Add button.

The Add constraint window will appear.
In the Cell Reference field, put $E$7:$E$9.
In the Constraint field, put $G$7:$G$9.
Press OK to proceed.

Select the solving method. From the drop-down menu of the Select a solving method, select Simplex LP.
Press Solve to solve the problem.

You get a Solver Results window.
In the Reports field, select Answer and Sensitivity and press OK.

STEP 3 – Get the Answer and Sensitivity Report

You get the number of Wooden sofas and Wooden Beds for maximum profit.

Go to Answer Report 1. In this report, you can observe the Final Value.
The Final Value means the maximum profit of the shop after maximization.
The Cell Value represents the LHS value of the problem constraint.

Machine 2 LHS and Labor LHS are binding or limited by the optimization model as Slack is 0. If you change these values, the optimization parameters will be changed, and maximum optimization will change. This will be the shadow price. But, Machine 1 LHS is not binding, so it will not have any shadow price.

Go to Sensitivity Report 1.
Look for the Shadow Price column.
The Shadow Price is the change in the value of the objective function per unit increase in the constraint’s bound.
As Machine 1 LHS is not binding, it has not any shadow price.