Math Project Using Linear programming in Excel

Project02-BusinessDecisions.xlsx

Home >Mathematics homework help >Math Project Using Linear programming in Excel

Instructions

	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

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.

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.

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.

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.

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.

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.