Application Problems
Harry and Melissa Jacobson produce handcrafted furniture in a workshop on their farm. They have obtained a load of 600 board feet of birch from a neighbor and are planning to produce round kitchen tables and ladder-back chairs during the next 3 months. Each table will require 30 hours of labor, each chair will require 18 hours, and between them they have a total of 480 hours of labor available. A table requires 40 board feet of wood to make, and a chair requires 15 board feet. A table earns the couple $575 in profit, and a chair earns $120 in profit. Most people who buy a table also want four chairs to go with it, so for every table that is produced, at least four chairs must also be made, although additional chairs can also be sold separately.
Formulate and solve an integer programming model to determine the number of tables and chairs the Jacobson’s should make to maximize profit.
Solution: Let_{}and_{}be the number of tables and chairs produced, respectively. We have the following restrictions:
- Labor:
_{}
- Material:
_{}
- At least 4 chairs per table:
_{}
- Objective Function:
_{}
The problem we need to solve is therefore:
_{}
Using Excel’s solver we obtain the following results:
_{} |
_{} |
|||||
4 |
20 |
Restrictions |
||||
Coefficients |
575 |
120 |
0 | |||
-140 | ||||||
Z |
4700 |
-4 |
Microsoft Excel 10.0 Answer Report |
||||||
Worksheet: [Eileen 2 - LP.xls]Question 1 |
||||||
Report Created: 8/7/2006 6:50:04 PM |
||||||
Target Cell (Max) |
||||||
Cell |
Name |
Original Value |
Final Value |
|||
$C$7 |
Z X_T |
4700 |
4700 |
|||
Adjustable Cells |
||||||
Cell |
Name |
Original Value |
Final Value |
|||
$C$3 |
X_T |
4 |
4 |
|||
$D$3 |
X_C |
20 |
20 |
|||
Constraints |
||||||
Cell |
Name |
Cell Value |
Formula |
Status |
Slack | |
$H$5 |
Coefficients |
0 |
$H$5<=0 |
Binding |
0 | |
$H$6 |
-140 |
$H$6<=0 |
Not Binding |
140 | ||
$H$7 |
Z |
-4 |
$H$7<=0 |
Not Binding |
4 | |
$C$3 |
X_T |
4 |
$C$3=integer |
Binding |
0 | |
$D$3 |
X_C |
20 |
$D$3=integer |
Binding |
0 |