Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Shadow Price Linear Programming in Excel

In this article, we will learn how to calculate shadow price linear programming in Excel. The Shadow Price is the change in the value of the objective function per unit increase in the constraint. It is a very important tool for financial calculations. In industry, we need to know these parameters before producing products. We can easily do this task in Excel. So, without any delay, let’s start the discussion.

Calculating Shadow Price Linear Programming in Excel


Download Practice Workbook

To practice by yourself, download the following workbook.


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

To demonstrate the method, we have taken the following dataset. The dataset contains information on a wood shop where Wooden Sofas and Wooden Beds are made. The shop gets 200 and 300$ benefits per Sofa and Bed respectively. But, there are some constraints. From this dataset, we will find out the maximum profit. After that, we will calculate the shadow price with linear programming.

Dataset for Calculating Shadow Price Linear Programming in Excel

Let’s follow the steps below to learn the method.


STEP 1: Prepare Dataset

First, we need to prepare our dataset with some modifications to solve the problem. We will link up between cells using the formulas.

  • Firstly, we have to insert the formula of total profit.
  • To do so, write down the following formula in the E6 cell:
=(C6*C5)+(D6*D5)
  • Then, press Enter to get the result.
  • Now, it is showing 0.
  • But after solving this, we will get the maximum profit in this cell.

Inserting Total Profit Formula for Calculating Shadow Price Linear Programming in Excel

  • Now, write down the following formula in the E7 cell:
=C7*C5+D7*D5
  • Then, press Enter to proceed.
  • From this cell, we will get the LHS (Left Hand Side) of a constraint.

Inserting Formula of LHS of Constraints for Calculating Shadow Price Linear Programming in Excel

  • Now, write down the following formula in the E7 cell:

=C8*C5+D8*D5

  • Then, press Enter to proceed.
  • From this cell, we will get the LHS (Left Hand Side) of another constraint.

Inserting Formula of LHS of Constraints for Calculating Shadow Price Linear Programming in Excel

  • Now, write down the following formula in the E7 cell:
=C9*C5+D9*D5
  • Then, press Enter to proceed.
  • From this cell, we will get the LHS (Left Hand Side) of another constraint.

Inserting Formula of LHS of Constraint

Here, 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.

Read More: How to Solve Integer Linear Programming in Excel (With Easy Steps)


STEP 2: Use Solver Feature to Calculate Shadow Price Linear Programming

In this step, we will insert the Solver Feature to maximize the objective function. As the by-product of maximization, we will get the shadow price.

  • Now, we will add a solver in Excel to solve the data.
  • To do so, click on Data >> Solver to add the solver.

Inserting Solver Feature

  • Instantly, you can see a window named Solved Parameters.
  • Then, we will insert parameters into this window.
  • So, write $E$6 in the Set Objective field.
  • Here, the E6 cell indicates the profit of the shop.
  • After that, in the By Changing Variable Cells field, write down $C$5:$D$5.
  • Here, these 2 cells are variables.
  • Then, click on the Add button.

Setting Parameters Using Solver Feature

  • As a result, the Add constraint window will come out.
  • After that, write according to the following figure.
  • In the Cell Reference field, write $E$7:$E$9.
  • And, in the Constraint field, write $G$7:$G$9.
  • Here, we have declared the constraints.
  • Then, press OK to proceed.

Setting Parameters in Add Constraint Box

  • In this step, we will select the solving method.
  • So, from the drop-down menu of the Select a solving method, select Simplex LP.
  • Then, press Solve to solve the problem.

Selecting Simplex LP as Solving Method to Calculate Shadow Price in Excel

  • Instantly, you can see a Solver Results window.
  • Now, in the Reports field, select Answer and Sensitivity and press OK.

Generating Reports of Answer and Sensitivity in Excel Worksheet

Read More: How to Use Excel Solver for Linear Programming (With Easy Steps)


STEP 3: Get Answer & Sensitivity Report

We will get 2 Excel Sheets of Answer and Sensitivity reports automatically. From these reports, we will extract our desired information.

  • Now, you can observe the solution.
  • Here, you can see the number of Wooden sofas and Wooden Beds for maximum profit.
  • And, you can watch the total benefit is 107500$.

Answer after Maximization Objective Function

  • In the Excel workbook, 2 worksheets have appeared.
  • First, go to Answer Report 1.
  • In this report, you can observe the Final Value.
  • Here, the Final Value means the maximum profit of the shop after maximization.
  • And, the Cell Value is representing the LHS value of the problem constraint.

Output of Maximizing Objective Function

Here, Machine 2 LHS and Labor LHS are binding or limited by the optimization model as Slack is 0. For this reason, if you change these values, then, the optimization parameters will be changed, and maximum optimization will change. So, it will have a shadow price. But, Machine 1 LHS is not binding, so it will not have any shadow price.
  • After that, go to Sensitivity Report 1.
  • Then, look for the Shadow Price column.
  • Here, 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.

Output of Shadow Price Linear Programming in Excel

Read More: How to Do Linear Programming with Sensitivity Analysis in Excel


Practice Section

There is a practice Excel Sheet available. You can practise from this sheet.

Exercise for Shadow Price Linear Programming in Excel


Conclusion

In this article, we have demonstrated how to calculate shadow price linear programming in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles 

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo