How to Do Linear Programming with Sensitivity Analysis in Excel

Get FREE Advanced Excel Exercises with Solutions!

Linear programming has been used to determine the optimal value that will satisfy all of the constraints and conditions specified by the problems. Although the existing linear programming is already been quite useful, excel moves one step forward and provides an additional tool named sensitivity analysis reports. These reports are very useful if you want to vary the variable and see how the outcomes are behaving. In this article, we will discuss how you can run linear programming in Excel and generate sensitivity analysis.

linear programming sensitivity analysis excel


Introduction to Linear Programming

Linear Programming is an important aspect of Statistics and Applied Mathematics. You can perform predictive analysis with prevalent data variables. It helps us in the optimization of resources. We must have some constraints and an objective function for that purpose. The Excel Solver can quickly figure out the solutions to Linear Programming problems in Excel.


What Is Sensitivity Analysis?

We can use Excel to carry out many different mathematical operations. Linear Programming is an aspect of Statistics and Applied Mathematics. This has huge practical applications. Solving Linear Programming problems manually can seem a hassle. On the other hand, we have Excel Solver which can find out the solutions to those problems very readily. In this article, we’ll show you the step-by-step procedures to Use Excel Solver for Linear Programming.


Perform Linear Programming with Sensitivity Analysis in Excel: Step-by-Step Procedure

To illustrate, we’ll use the following business problem as an example.

Suppose,  a manufacturer has two kinds of products, ‘A’ & ‘B’. A single unit of product A requires three raw materials, P 25 kg, Q 35 kg, and R 10 kg. Similarly, B requires P 15 kg, Q 20 kg, and R 15 kg. The Manufacture needs a minimum of P 500 kg, Q 850 kg, and R 300 kg. If A costs $35 per unit and B costs $30 per unit, how many units of each product should the manufacturer blend to meet the minimum raw material requirements at a low cost as possible, and what is the price?

Now, to solve this problem, go through the below steps carefully and also learn how to use Excel Solver for Linear Programming.


STEP 1: Enable Solver in Excel

The Solver is an MS Excel add-in program. It stays deactivated by default. So, you need to enable it for using the program. Therefore, follow the process to perform the task.

  • First, go to File ➤ Options.
  • Then, select the Add-ins tab.
  • After that, choose Excel Add-ins from the Manage drop-down.
  • Subsequently, press Go.

Enable solver from options

  • As a result, the Add-ins dialog box will pop out.
  • Now, check the box for Solver Add-in.
  • Next, press OK.

solver add-ins inclusion

  • Thus, you’ll see the Solver program in the Analyze section under the Data tab.

Solver initiation

Read More: How to Use Excel Solver for Linear Programming


STEP 2: Input Constraints

In this step, we’ll input the Constraints and the Objective Function in the Excel worksheet. According to the problem, suppose, we’ll blend x units of product A and y units of B. So the total cost will be $35x + $30y. This is our objective function and we want to minimize this cost. At the same time, we have to meet the requirements. 25x + 15y >= 500, 35x + 20y >= 850, 10x+15y >= 300, x >= 0 and y >= 0 are our constraints. Now, we’ll input these.

  • First of all, type the per-unit costs of A and B.
  • See the following picture to understand better.
  • Next, input the materials under the respective products.
  • Insert the minimum required amounts.

Input Constraints to execute linear programming sensitivity analysis excel


STEP 3: Create Excel Formula

  • We’ll insert the value of x in cell C5 and y in cell D5.
  • Firstly, select cell E6.
  • Then, type the formula:

=($C$5*C6)+($D$5*D6)

  • Press Enter.
  • It’ll return 0 or blank as the C5 and D5 cell values are empty for the moment.

Create Excel Formula to linear programming sensitivity analysis excel

  • Afterward, select the cell E9 to type the formula:

=($C$5*C9)+($D$5*D9)

  • Consequently, press Enter to return the values.
  • Use the AutoFill tool to complete the rest.
  • For now, the results are 0 as C5 and D5 are empty.

the finalized formula for all products for the optimum cost


STEP 4: Solve Linear Programming with Sensitivity Report

  • Now, select the Solver program under the Data tab.
  • Accordingly, the Solver Parameters dialog box will emerge.
  • Next, choose cell E6 in the Set Objective box.
  • After that, check the circle for Min.
  • Select the range C5:D5 as variable cells.
  • The following image demonstrates the process clearly.
  • Then, press Add for adding the constraints.

Solve Linear Programming with Sensitivity Report in Excel

  • The Add Constraint dialog box will appear.
  • Choose the range C5:D5 and click the >= (greater than or equal to) symbol from the drop-down.
  • Type 0.
  • Press Add afterward.

add constraint

  • Moreover, choose the range E9:E11 for minimum requirement constraints.
  • Click the >= symbol from the drop-down.
  • Select the range G9:G11 in the Constraint field.
  • Press OK.

change constraint

  • Hence, you’ll see the desired constraints.
  • Press Solve.

choose solving method

  • You’ll get a dialog box about the solved results.
  • Check the circle for Keep Solver Solution.
  • And also select Sensitivity in Reports section.

selection of Sensitivity from reports

  • Press OK.
  • Lastly, it’ll return the precise results in the appointed cells.

Finalized sensitivity report with linear programming.

Read More: How to Find Optimal Solution in Linear Programming Excel


Interpretation of Sensitivity Analysis in Excel

The most important goal of the sensitivity analysis reports is to give people an idea of how their variables can be changed or altered to what extent. In other words, with Excel’s sensitivity analysis, you may change the model’s underlying assumptions and examine the output for a variety of potential outcomes. All investing is statistical since you can’t know with certainty what will happen in 5, 10, or 15 years, but you may think of a plausible range of possible outcomes.

Here, allowable increases and decreases denote how much the optimal value outcome can be altered so that the model still remains optimal. User can enter their preferable value and test this model and report.

Sensitivity analysis with explanations


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to execute linear programming with sensitivity analysis reports in excel as demonstrated here following the above-described procedures. Feel free to ask any questions or feedback through the comment section.


Related Articles 


<< Go Back to Excel Linear Programming | Solver in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo