The Electrocomp Corporation manufactures two electrical products: air conditioners and large fans.
The assembly process for each is similar in that both require a certain amount of wiring and drilling. Each air conditioner takes 3 hours of wiring and 2 hours of drilling. Each fan must go through 2 hours of wiring and 1 hour of drilling. During the next production period, 240 hours of wiring time are available and up to 140 hours of drilling time may be used. Each air conditioner sold yields a profit of $25. Each fan assembled may be sold for a $15 profit. Use SOLVER to find the number of air conditioners and fans to produce in order to maximize profit.
Variables Units to Produce Objective Function Constraints wiring drilling
Air Cond. 40 25
Fans 60 15 $ 1,900 << Maximize Profit LHS 240 140 RHS 240 140
3 2
2 1
< <
arge fans. The illing. Each air urs of wiring and 1 and up to 140 n assembled may duce in order to
MSA Computer Corporation manufactures two models of minicomputers, the Alpha 4 and the Beta 5. Management has allocated 800 hours of labor for production. It requires 20 labor hours to assemble each Alpha 4 computer and 25 labor hours to assemble each Beta 5 model. MSA wants to see at least 10 Alpha 4s and at least 15 Beta 5s produced during the production period. Alpha 4s generate $1,200 profit per unit, and Beta 5s yield $1,800 each. Use SOLVER to determine the most profitable number of each model of minicomputer to produce during the coming month.
Variables Units to Produce Objective Function Constraints labor hours minimum
Alpha4 21.25 1200
Beta5 15 Profit 1800 $ 52,500 LHS 800 RHS 800
20 10
25 15
<
he Beta 5. assemble each least 10 Alpha 4s rofit per unit, and
The Weinberger Electronics Corporation manufactures four highly technical products that it supplies to aerospace firms that hold NASA contracts. Each of the products must pass through the following departments before they are shipped: wiring, drilling, assembly, and inspection. Table A shows each product, its profit per unit, and the amount of time it must spend in each department. shows the total production time available in each department for the month. Table C shows the minimum monthly production requirements to fulfill the firm's contracts. First, use SOLVER to determine the profit-maximizing level of production for each product. Use the template in columns I:N so that your answers will end up in the green boxes. Second, suppose that the production manager is unsure of the accuracy of the $9 unit profit figure for XJ201. Use a sensitivity analysis to determine the range of values that XJ201 unit profit can take without changing the results of your analysis. Write that answer here: Allowable increase is $2 and allowable decrease is $7.9 TABLE A Product Profit Per Unit Wiring Time Drilling Time Assembly Time Inspection Time TABLE B Wiring Time Drilling Time Assembly Time Inspection Time TABLE C XJ201 XM897 TR29 BR788
XJ201 9 0.5 0.3 0.2 0.5
XM897 12 1.5 1 4 1
TR29 15 1.5 2 1 0.5
BR788 11 1 3 2 0.5
Time Available 15000 17000 26000 12000 Minimum Required 150 100 300 400
t it supplies to aerospace firms ents before they are shipped:
each department. Table B ws the minimum monthly
t. Use the template in columns
ofit figure for XJ201. Use a ut changing the results of your
Variables Units to Produce Objective Function Constraints wiring time drilling time assembly time inspection time minimum
XJ201 19627.41 9
XM897 100 12
TR29 BR788 300 3569.593 15 11 $ 216,339.19 Profit LHS 13786.2963 17000 11469.66667 12000 RHS 15000 17000 26000 12000
0.5 0.3 0.2 0.5 150
1.5 1 4 1 100
1.5 2 1 0.5 300
1 3 2 0.5 400
< < < <
Microsoft Excel 14.0 Sensitivity Report Worksheet: [BAT Unit 5.xlsx]3 Report Created: 2/9/2013 9:59:05 PM
Variable Cells Cell $J$4 $K$4 $L$4 $M$4 Name Units to Produce XJ201 Units to Produce XM897 Units to Produce TR29 Units to Produce BR788 Final Reduced Objective Value Cost Coefficient 19627.40741 0 9 100 -17.2962963 1 300 -17.2962963 1 3569.592593 0 11 Allowable Increase 2 17.2962963 17.2962963 79 Allowable Decrease 7.9 1E+30 1E+30 2
Constraints Cell $N$8 $N$9 $N$10 $N$11 Name wiring time LHS drilling time LHS assembly time LHS inspection time LHS Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 13786.2963 0 15000 1E+30 1213.703704 17000 0.740740741 17000 6554 8557.9 11469.66667 0 26000 1E+30 14530.33333 12000 17.55555556 12000 1365.416667 8764.833333
