1. Sensitivity Analysis for LP - How to obtain sensitivity report from Excel Solver Add in

Summary

This video explains how to perform a sensitivity analysis on a product mix problem using Excel Solver. The company aims to maximize profit by determining the optimal production of aquaspa and hydrax hot tabs, subject to resource constraints such as pumps, labor hours, and tubing. It demonstrates how to set up Solver, solve the linear model, and generate reports. The video walks through interpreting the answer and sensitivity reports, including binding constraints, slacks, shadow prices, and allowable changes to profit coefficients. It provides valuable insights into resource utilization and optimizing production plans for maximum profit.

Takeaways

• 😀 The goal of the analysis is to determine the optimal product mix that maximizes profit while adhering to resource constraints (pumps, labor hours, and feet of tubing).
• 😀 Decision variables represent the number of units of AquaSpa and HydraX hot tubs to produce, which need to be optimized for profit maximization.
• 😀 The linear programming model includes objective coefficients (profits for each product) and constraints (available resources like pumps, labor hours, and tubing).
• 😀 Excel’s Solver tool is used to solve the optimization problem, with the Simplex LP method selected for linear programming problems to generate a full sensitivity analysis report.
• 😀 Non-negativity constraints ensure that the number of units produced is always non-negative, reflecting real-world production limits.
• 😀 After solving the optimization problem in Solver, the solution displays the optimal production quantities and the resulting total profit.
• 😀 The Answer Report provides details on the solution, including the status of constraints (whether they are binding or non-binding) and the remaining slack for each resource.
• 😀 A binding constraint prevents further improvement in profit, meaning the company used up all available resources for those constraints, such as pumps and labor hours.
• 😀 The Sensitivity Report shows the impact of changes in objective coefficients (product profit) and constraint values (resource limits) on the optimal solution.
• 😀 The report also includes 'shadow prices,' which indicate the value of an additional unit of a resource (such as a pump or labor hour), showing how profit would change if more resources became available.
• 😀 Slack values indicate unused resources after the optimal production plan is implemented, with zero slack meaning full utilization of a given resource, and positive slack showing unused resources.

Q & A

What is the main objective of the product mix problem described in the video?

The main objective of the product mix problem is to determine the optimal number of units of two products (aquaspa hot tabs and hydrax hot tabs) to produce in order to maximize profit, while adhering to resource constraints like pumps, labor hours, and feet of tubing.

What are the decision variables in this linear programming problem?

The decision variables are X1 and X2, where X1 represents the number of aquaspa hot tabs and X2 represents the number of hydrax hot tabs to produce.

What are the constraints in the product mix problem?

The constraints in the product mix problem are limited resources, including the number of available pumps, labor hours, and feet of tubing. There are also non-negativity constraints ensuring that the number of products produced cannot be negative.

How does Excel Solver help solve the linear programming problem?

Excel Solver is used to maximize the objective function (profit) by adjusting the decision variables (X1 and X2), subject to the given constraints. It finds the optimal solution by using the Simplex LP method and generates sensitivity analysis reports to evaluate the effect of changes in the model.

Why is it important to select the Simplex LP method in Solver?

Selecting the Simplex LP method is important because it allows the Solver to generate a full sensitivity analysis report, which is not available with other methods like GRG, the default method.

What does the 'Answer Report' provide in the Solver output?

The 'Answer Report' provides details about the solution, including the optimal number of units to produce, the total profit, and information about the constraints. It also indicates whether each constraint is binding or non-binding, helping to understand which resources limit the solution.

What is the significance of a binding constraint in linear programming?

A binding constraint is significant because it prevents further improvement of the objective function (profit). It means that the resource associated with that constraint is fully utilized in the optimal solution, and no more of that resource is available for allocation.

How is slack calculated in the sensitivity report?

Slack is calculated as the difference between the available resources (right-hand side of the constraint) and the resources used by the production plan (left-hand side of the constraint). If the difference is zero, the constraint is binding; if there is a positive difference, the constraint is non-binding.

What does the 'Shadow Price' indicate in the sensitivity report?

The 'Shadow Price' indicates the change in the objective value (profit) for each one-unit increase in the right-hand side of a constraint. It shows how much the objective value would improve if additional resources were made available.

What is the 'Allowable Increase and Decrease' in the context of sensitivity analysis?

The 'Allowable Increase and Decrease' refers to the range within which the objective coefficients or the right-hand side values of the constraints can change without affecting the optimal solution. It helps assess the stability of the solution to changes in the model.