Math Project Using Linear programming in Excel

8 problem project in excel, using Linear programming and solver tool. 

2

>Instructions

ion Department

Product

1 1

0

1 2

2 2 3

.00

3 4 2

4 5

0

																								Product
							(Labor-Hours per Unit)
							Fabrication								Assembly								Finishing								Profit/Unit								Orders
							Small Table																																1				$ 8.	0								200
							Regular Chair																								3								$ 17.00								160
							Rocking Chair	$ 2																4							180
							Chaise Lounge								$ 31.00							240
							Large Table																5							$ 52.00								100
							Available Labor-Hours							2500						300								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

(Labor-Hours per Unit)
Product Fabrication Assembly Finishing Profit/Unit Orders
Small Table 1 1 1

200
Regular Chair 1 2 3 $ 17.00 160
Rocking Chair 2 2 3

180
Chaise Lounge 3 4 2 $ 31.00 240
Large Table 5 4 5 $ 52.00 100
Available Labor-Hours 2500

3500

Production Department

$ 8.00

$ 24.00

3000

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

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

$ 12.00
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

100

Available Labor-Hours 2500 3000 3500

$ 30.00
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

3500

3300
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

3000 3500

2800
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?