Lecture on Linear Programming Optimization with Excel Solver

Introduction to Optimization

• Optimization is a powerful tool used in various scenarios:
  • Scheduling solutions
  • Asset allocation
  • Inventory control
  • Product mix optimization
• It can be applied to both linear and non-linear problems.

Steps in Solving Optimization Problems

1. Model Development: Building a model Solver can work with.
2. Using Solver: Finding an optimal solution using Excel Solver.
3. Evaluating the Solution: Ensuring real-world applicability and accuracy.

Example Problem: Product Mix Optimization

• Objective: Maximize profit with limited resources.
• Constraints: Include non-negativity constraints to avoid negative products.

Components of a Spreadsheet Model

1. Objective Function: The target to maximize or minimize (e.g., profit, cost, risk).
2. Input Variables: Resources used or allocated.
3. Decision Variables: Quantity to produce (e.g., how many products to make).
4. Constraints: Conditions that must be adhered to (e.g., resource limitations).

Model Setup

• Initial decision variables set to one of each product.
• Use formulas to calculate resource usage and profit.
• SUMPRODUCT Function: Simplifies calculation of total resource usage.

Solving the Problem

• Solver Tool in Excel:
  • Define the objective (e.g., total profit).
  • Input decision variables.
  • Apply constraints (e.g., resource limits, non-negativity).
• Solver provides a solution or highlights model errors (e.g., unbounded problems).

Solution Evaluation

• Optimal solution example: Produce 800 of Product A and 200 of Product B for maximum profit.
• Sensitivity Analysis:
  • Analyze changes in input impacts on the solution.
  • Reduced Cost: Impact of adding or removing products.
  • Shadow Price: Value of additional resources on profit.

Reports Generated by Solver

1. Answer Report
   • Displays initial and final values of decision variables.
   • Indicates which constraints are binding.
2. Sensitivity Report
   • Details on input variables and constraints.
   • Shows allowable increase/decrease in profit before solution changes.
3. Limits Report
   • Effect of producing none of a product on the objective function.

Conclusion

• Using Excel Solver for linear programming is effective for finding optimal solutions in resource-constrained environments.
• Understanding sensitivity and limits helps in making informed decisions based on model outputs.