Solver: Constraint Satisfaction and Dialogue Box
Linear Programming: Using the Excel Solver
Outline:
We will use Microsoft Excel Solver to solve the four LP examples discussed in last class.
1. The Product Mix Example
The Outdoor Furniture Corporation manufactures two products: benches and picnic tables for use in yards and parks. The firm has two main resources: its carpenters (labor) and a supply of redwood for use in the furniture. During the next production period, 1200 hours of manpower are available under a union agreement. The firm also has a stock of 5000 pounds of quality redwood. Each bench that Outdoor Furniture produces requires 4 labor hours and 10 pounds of redwood; each picnic table takes 7 labor hours and 35 pounds of redwood. Completed benches yield a profit of $9 each, and tables a profit of $20 each. We formulated the following linear program to solve this problem: Decision Variables:
Objective Function:
Constraints:
We will now solve this LP using the Excel Solver.
1.1
Getting Started
To begin using Excel, double-click on the Excel icon. Once Excel has loaded, enter the input data and construct relationships among data elements in a readable, easy to understand way. When building this foundation for your model, think ahead about the optimization model you will be developing. Make sure there is a cell in your spreadsheet for each of the following: • the quantity you wish to maximize or minimize • every decision variable • every quantity that you might want to constrain If you don’t have any particular initial values you want to enter for your decision variables, you can start by just entering a value of 0 in each decision variable cell.
The cell below “Total” and the cells below “LHS” in the spreadsheet are defined by using the SUMPRODUCT function in the manner seen below.
If you are not familiar with “sumproduct”, you can use “= B3*B5+C3*C5” instead of “sumproduct(B3:C3,B5:C5)”, and so on. See example below. Both serve the same purpose.
1.2
Constructing an
Outline:
We will use Microsoft Excel Solver to solve the four LP examples discussed in last class.
1. The Product Mix Example
The Outdoor Furniture Corporation manufactures two products: benches and picnic tables for use in yards and parks. The firm has two main resources: its carpenters (labor) and a supply of redwood for use in the furniture. During the next production period, 1200 hours of manpower are available under a union agreement. The firm also has a stock of 5000 pounds of quality redwood. Each bench that Outdoor Furniture produces requires 4 labor hours and 10 pounds of redwood; each picnic table takes 7 labor hours and 35 pounds of redwood. Completed benches yield a profit of $9 each, and tables a profit of $20 each. We formulated the following linear program to solve this problem: Decision Variables:
Objective Function:
Constraints:
We will now solve this LP using the Excel Solver.
1.1
Getting Started
To begin using Excel, double-click on the Excel icon. Once Excel has loaded, enter the input data and construct relationships among data elements in a readable, easy to understand way. When building this foundation for your model, think ahead about the optimization model you will be developing. Make sure there is a cell in your spreadsheet for each of the following: • the quantity you wish to maximize or minimize • every decision variable • every quantity that you might want to constrain If you don’t have any particular initial values you want to enter for your decision variables, you can start by just entering a value of 0 in each decision variable cell.
The cell below “Total” and the cells below “LHS” in the spreadsheet are defined by using the SUMPRODUCT function in the manner seen below.
If you are not familiar with “sumproduct”, you can use “= B3*B5+C3*C5” instead of “sumproduct(B3:C3,B5:C5)”, and so on. See example below. Both serve the same purpose.
1.2
Constructing an