Attached to file.
Instructions Production Department (Labor-Hours per Unit) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00240 Large Table545$ 52.00100 Available Labor-Hours250030003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00240 Large Table545$ 52.00100 Available Labor-Hours250030003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 12.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00240 Large Table545$ 52.00100 Available Labor-Hours250030003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00240 Large Table545$ 30.00100 Available Labor-Hours250030003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.000 Chaise Lounge342$ 31.00240 Large Table545$ 52.00100 Available Labor-Hours250030003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00300 Large Table545$ 52.00100 Available Labor-Hours250030003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00240 Large Table545$ 52.00100 Available Labor-Hours250033003500 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) ProductFabricationAssemblyFinishingProfit/UnitOrders Small Table111$ 8.00200 Regular Chair123$ 17.00160 Rocking Chair223$ 24.00180 Chaise Lounge342$ 31.00240 Large Table545$ 52.00100 Available Labor-Hours280030003500 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?