|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 2 |
>Instructions
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| Product |
ion Department
|
|
|
|
|
|
|
| (Labor-Hours per Unit) |
Product
Don't use plagiarized sources. Get Your Custom Essay on
Math Project Using Linear programming in Excel
Just from $13/Page
|
|
|
|
|
|
| Fabrication |
|
|
|
|
|
|
| Assembly |
|
|
|
|
|
|
| Finishing |
|
|
|
|
|
|
| Profit/Unit |
|
|
|
|
|
|
| Orders |
|
|
|
|
|
|
|
| Small Table |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 1 |
1 1
|
|
| $ 8.
| 0 |
0
|
|
|
|
|
|
| 200 |
|
|
|
|
|
|
|
| Regular Chair |
1 2
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 3 |
|
|
|
|
|
|
| $ 17.00 |
|
|
|
|
|
|
| 160 |
|
|
|
|
|
|
|
| Rocking Chair |
2 2 3
$ 2
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 4 |
.00
|
|
|
|
|
| 180 |
|
|
|
|
|
|
|
| Chaise Lounge |
3 4 2
|
|
|
|
|
|
| $ 31.00 |
|
|
|
|
|
| 240 |
|
|
|
|
|
|
|
| Large Table |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 5 |
4 5
|
|
|
|
|
| $ 52.00 |
|
|
|
|
|
|
| 100 |
|
|
|
|
|
|
|
| Available Labor-Hours |
|
|
|
|
|
| 2500 |
|
|
|
|
| 300 |
0
|
|
|
|
|
|
| 3500 |
Project 02 – Using Linear Programming to Make Business Decisions
The basic scenario: A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Complete Problems 1 – 8. If when completing Problems 3 – 8, you decide to copy/paste from Problems 1 & 2, you will have to re-set all of the parameters in the Solver tool box.
Each problem is worth 10 points. You must use Excel formulas and the Solver tool in order to earn credit.
Problem 1 and 2
|
|
|
|
|
|
| Production Department |
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1
|
| $ 8.00 |
200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3
|
|
|
|
|
| $ 24.00 |
180
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours 2500
| 3000 |
3500
| Problem 1: (10 pts.) |
Construct a mathematical model (define your variables, write an objective function and constraints). |
| Problem 2: (10 pts.) |
Use Excel’s Solver tool to determine the optimal solution that will maximize profit. Summarize your results. In the Solver toolbox, choose “Simplex LP”. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Problem 3
Production Department
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1
| $ 12.00 |
200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3 $ 24.00 180
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours 2500 3000 3500
| Problem 3: (10 pts.) |
Discuss the effect on the optimal solution in Problem 2 if the profit on a small table increases to $12. In the Solver toolbox, rchoose “Simplex LP”. If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Problem 4
Production Department
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1 $ 8.00 200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3 $ 24.00 180
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5
| $ 30.00 |
100
Available Labor-Hours 2500 3000 3500
| Problem 4: (10 pts.) |
Discuss the effect on the optimal solution in Problem 2 if the profit on a large table decreases to $30. In the Solver toolbox, rchoose “Simplex LP”. If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Problem 5
Production Department
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1 $ 8.00 200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3 $ 24.00 0
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours 2500 3000 3500
| Problem 5: (10 pts.) |
Discuss the effect on the optimal solution in Problem 2 if the orders for the rocking chairs are canceled (and the cancellation came before the rocking chairs went into production). In the Solver toolbox, choose “Simplex LP”. In the Solver toolbox, choose “Simplex LP”. If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Problem 6
Production Department
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1 $ 8.00 200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3 $ 24.00 180
Chaise Lounge 3 4 2 $ 31.00 300
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours 2500 3000 3500
| Problem 6: (10 pts.) |
Discuss the effect on the optimal solution in Problem 2 if the orders for the chaise lounges are increased to 300. In the Solver toolbox, choose “Simplex LP”. In the Solver toolbox, choose “Simplex LP”. If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Problem 7
Production Department
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1 $ 8.00 200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3 $ 24.00 180
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours 2500
| 3300 |
3500
| Problem 7: (10 pts.) |
Discuss the effect on the optimal solution in Problem 2 if the available hours in the assembly department are increased to 3300. In the Solver toolbox, choose “Simplex LP”. In the Solver toolbox, choose “Simplex LP”. If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
Problem 8
Production Department
(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1 $ 8.00 200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3 $ 24.00 180
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours
| 2800 |
3000 3500
| Problem 8: (10 pts.) |
Discuss the effect on the optimal solution in Problem 2 if the available hours in the fabrication department are increased to 2800. In the Solver toolbox, choose “Simplex LP”. In the Solver toolbox, choose “Simplex LP”. If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox. |
Project 02 – Using Linear Programming to Make Business Decisions
A company manufactures a line of outdoor furniture consisting of small tables, regular chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing. The table below summarizes the amount of time each piece must spend in each department, the labor-hours available in each department, the profit from each piece, and the existing orders for each product. The company must produce a significant number of pieces to fill all of the existing orders. How many pieces of each must be manufactured in order to maximize profit?
