Excel Solver Exercise Power Furnace Inc. is a national-wide company that manufac
ID: 3807470 • Letter: E
Question
Excel Solver Exercise
Power Furnace Inc. is a national-wide company that manufactures furnaces. Currently they produce 4 different models of furnaces (A, B, C, and D). To make a Model A, it requires 60 labor hours, and the unit profit of Model A is $585. To make a Model B, 65 labor hours are needed, and the unit profit is $742.5. To make a Model C, 31 labor hours are needed, and the unit profit of Model C is $843. To make a Model D, 89 labor hours are needed, and each unit contributes $750 in profit. The company has a total of 6200 labor hours to manufacture these furnaces. They want to use the capacity as much as possible. Other production factors such as machines and materials do not pose any challenge and have been considered in the above factors. The company decides to make at least 5 units of each model in order to maintain the production skills in making those models. Meanwhile, they realize that the market demand for each model would not exceed 75 units. Help them make the decisions based on the following conditions.
1. Given the above factors, how many units of Models A, B, C, and D furnaces should they make? What are the total labor hours needed? What is the expected profit? Solve the problem without imposing the integer constraints. Create the answer report and sensitivity report. Also save your model on the worksheet.
2. Revise the above model to add the integer constraints and re-solve the problem. Compare your result with the above one. Create the answer sheet, and save the model on the current worksheet.
Explanation / Answer
1. Labour Hour for A - 60
Profit for A - $585
Labour Hour for B - 65
Profit for B - $742.5
Labour Hour for C - 31
Profit for C - $843
Labour Hour for D - 89
Profit for D - $750
Labour Hour and Profit for 5 units are
A - 300 & $2925
B - 325 & $3712.5
C - 155 & $4215
D - 445 & $3750
Remaining labour hours are 6200 - 1225 = 4975
since furnace C has least Labour Hour and Max profit hence add 70 more pieces of C
Hence total labour hour for c is 2170
Remaining Labour Hour = 4975-2170 = 2805
add 43 more pieces of B
Hence total pieces are - A- 5
B- 48
C- 75
D- 5
Labour Hour - 6190
Profit - $105540
2.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.