Solve Linear Program problem in Excel (Solver)

Summary

This video demonstrates how to solve a Linear Programming (LP) problem using Excel's Solver add-in. The tutorial covers the installation process for Solver, setting up the decision variables, objective function, and constraints in Excel. It walks through creating formulas for the objective function and constraints, setting up inequality signs, and inputting right-hand-side values. Finally, it shows how to use Solver to find the optimal solution and generate reports, including the Answer, Sensitivity, and Limit reports. The tutorial provides a clear, step-by-step guide to optimizing an LP problem in Excel.

Takeaways

• 😀 Ensure the Solver add-in is installed in Excel before starting the process. If not, install it through File > Options > Add-ins.
• 😀 After installation, the Solver command will appear in the Data tab on the Excel ribbon.
• 😀 Set up the decision variables (X1, X2, X3) in Excel and shade the cells where you want the optimal solution.
• 😀 Enter the objective function coefficients (2.9, 1.9, 2.5) in the row above the decision variables.
• 😀 Define the constraints by entering the coefficients for X1, X2, and X3 in separate rows. Use zero, or leave blank if the coefficient is zero.
• 😀 Set up a formula for the objective function using the SUMPRODUCT function to multiply coefficients by their respective variable values.
• 😀 Use the SUMPRODUCT function again for the constraints, ensuring to lock variable cells with the F4 key (Windows) or 'cmd+T' (Mac).
• 😀 Define the inequality signs for each constraint: >= for Constraint 1, and <= for Constraints 2 and 3.
• 😀 Enter the right-hand side values for each constraint (81 for Constraint 1, 89 for Constraint 2, and 400 for Constraint 3).
• 😀 Once the setup is complete, open Solver from the Data tab, select the objective function, and the decision variable cells for Solver to optimize.
• 😀 After solving, the Solver will provide the optimal solution and the objective function value. You can also view various reports like Answer, Sensitivity, and Limit reports for further insights.

Q & A

What is the purpose of the Solver add-in in Excel?

The Solver add-in in Excel is used to find optimal solutions for linear programming (LP) problems, where you need to maximize or minimize an objective function while satisfying a set of constraints.

How can you install the Solver add-in in Excel if it’s not already visible?

To install the Solver add-in in Excel, go to File > Options > Add-ins. At the bottom, click Go, select Solver Add-in, and click OK. On a Mac, you can go to Tools > Excel Add-ins > Solver Add-in.

What should you do if the Solver add-in does not appear under the Data tab?

If the Solver add-in does not appear under the Data tab, you need to install it through Excel's options as described in the previous answer.

Why are the yellow cells used in the Excel setup?

The yellow cells are used to hold the variable values (X1, X2, and X3), which will be adjusted by Solver to find the optimal solution to the problem.

What is the function of the SUMPRODUCT formula in this setup?

The SUMPRODUCT formula is used to calculate the objective function and the left-hand side of the constraints by multiplying the coefficients with the corresponding variable values and summing the results.

What is the purpose of locking the variable cells using the F4 key?

Locking the variable cells with the F4 key ensures that the reference to the variable cells remains fixed when copying the formula to other cells. This is essential for maintaining correct references in the constraint formulas.

How are the constraints set up in the Excel model?

The constraints are set up by entering the left-hand side formulas (using the SUMPRODUCT function), then specifying the inequality signs (>=, <=) and the right-hand side values (e.g., 81, 89, and 400).

What does the Solver add-in require to solve the LP problem?

To solve the LP problem, the Solver add-in requires the objective function, the changing variable cells (the yellow cells), and the constraints (including the inequality signs and right-hand side values).

What happens after you click 'Solve' in Excel’s Solver?

After clicking 'Solve', Solver adjusts the variable values (X1, X2, and X3) to find the optimal solution. The colored cells are then populated with the values that optimize the objective function.

What reports can you generate after solving the LP problem in Solver?

After solving the LP problem, you can generate an Answer report (showing the optimal objective function value and decision variable values), a Sensitivity Report, and a Limit Report.