4. To prevent layoffs management wants the percent of the available capacity used for production be equal among all plants (for example, capacity in plant 1 is 1200 units. If this plant produces 500 units, it uses 500/1200 = 41.67% of its capacity. Then all the plants must use 41.67% of their capacity - that is, equal workload for all plants). Write the constraints needed to secure this requirement (in the Answer sheet). Plant1 Plant2 Capacity avail 1200 units 900 Storage avail 13000 sqft 12000 Required starage per unit 20 sq-ft Plant3 850 5000 12 15 Small Large $1000 Medium 1100 Profit/unit 700 5. The sensitivity report for this model is provided in the Answers sheet. Use it to answer the following sensitivity questions. 5.1 How much the unit profit of the large size product produced in plant 1 can decrease, before the current production changes? a. Any drop in the unit profit will change the optimal production plan b. From the sensitivity report the change is 588.23. c. Two coefficients are changing. (i) Plant 1= 1200.; (ii) Large product = 900 Therefore we cannot answer. 5.2 How much should the company be willing to pay for 100 sq-ft of additional storage in each plant, when each addition is considered individually? Remember: The objective is profit maximization a. The company should not pay any amount in plant 1, not pay any amount in plant 2, and 289.2157 dollars in plant 3. b. The company should pay any amount charged in plant 1, pay any amount charged in plant 2, and 289.2157 ollars in plant 3. c. The company should only pay 5000/100*289.2157 in plant 3. d. None of the above. 5.3 Management now believes that the sales estimate of the medium size product were too optimistic (too high) by 10%. Management wants to know what is your maximum new profit if you correct the overestimation of the sales mentioned. Do you need to rerun your model with the new sales estimates to answer this question? Answer Yes or No in the Answers sheet. Variable Cells Allowable Increase 100 0 Cell $T$25 $U$25 $V$25 $T$26 $U$26 $V$26 $T$27 $U$27 $V$27 0 Name Large Plant 1 Large Plant2 Large Plants Medium Plant 1 Medium Plant2 Medium Plant 3 Small Plant 1 Small Plantz Small Plants Final Value 346.0784 0 0 242.1569 441.1765 416.6667 0 0 0 Reduced Objective Cost Coefficient 0 1000 0 1000 0 1000 0 1100 0 1100 0 1100 -300 700 -300 700 -300 700 DODOC 0 1E+30 1E+30 300 300 300 Allowable Decrease 0 1E+30 1E+30 100 0 0 1E+30 1E+30 1E+30 Constraints BE Cell $T$28 $U$28 $V$28 $T$32 $U$32 $T$37 $U$37 $V$37 $W$25 $W$26 Sw$27 $w$25 $w$26 $w$27 Name Production in Plant 1 Production in Plant2 Production in Plant3 Equal % Plant 1,2 Equal % Plant 2,3 Space Occupied Planti Space Occupied Plant2 Space Occupied Plants Large Production Medium Production Small Production Large Product sales Medium Product sales Small Product sales Final Value 588.2353 441.1765 416.6667 0.490196 0.490196 11764.71 6617.647 5000 346.0784 1100 Shadow Constraint Price R.H. Side 0 1200 0 900 0 850 1200000 0 2100000 0 0 13000 0 12000 289.2156863 5000 0 1000 100 1100 700 900 1200 750 Allowable Allowable Increase Decrease 1E+30 611.7647 1E+30 458.8235 1E+30 433.3333 0.051470588 0.288399 0.051470588 0.164799 1E+30 1235.294 1E+30 5382.353 1196.61 1E+30 653.9216 100 242.1569 1E+30 700 1E+30 553.9216 1E+30 100 1E+30 750 525 0 346.0784 1100 0 OOOO 4. To prevent layoffs management wants the percent of the available capacity used for production be equal among all plants (for example, capacity in plant 1 is 1200 units. If this plant produces 500 units, it uses 500/1200 = 41.67% of its capacity. Then all the plants must use 41.67% of their capacity - that is, equal workload for all plants). Write the constraints needed to secure this requirement (in the Answer sheet). Plant1 Plant2 Capacity avail 1200 units 900 Storage avail 13000 sqft 12000 Required starage per unit 20 sq-ft Plant3 850 5000 12 15 Small Large $1000 Medium 1100 Profit/unit 700 5. The sensitivity report for this model is provided in the Answers sheet. Use it to answer the following sensitivity questions. 5.1 How much the unit profit of the large size product produced in plant 1 can decrease, before the current production changes? a. Any drop in the unit profit will change the optimal production plan b. From the sensitivity report the change is 588.23. c. Two coefficients are changing. (i) Plant 1= 1200.; (ii) Large product = 900 Therefore we cannot answer. 5.2 How much should the company be willing to pay for 100 sq-ft of additional storage in each plant, when each addition is considered individually? Remember: The objective is profit maximization a. The company should not pay any amount in plant 1, not pay any amount in plant 2, and 289.2157 dollars in plant 3. b. The company should pay any amount charged in plant 1, pay any amount charged in plant 2, and 289.2157 ollars in plant 3. c. The company should only pay 5000/100*289.2157 in plant 3. d. None of the above. 5.3 Management now believes that the sales estimate of the medium size product were too optimistic (too high) by 10%. Management wants to know what is your maximum new profit if you correct the overestimation of the sales mentioned. Do you need to rerun your model with the new sales estimates to answer this question? Answer Yes or No in the Answers sheet. Variable Cells Allowable Increase 100 0 Cell $T$25 $U$25 $V$25 $T$26 $U$26 $V$26 $T$27 $U$27 $V$27 0 Name Large Plant 1 Large Plant2 Large Plants Medium Plant 1 Medium Plant2 Medium Plant 3 Small Plant 1 Small Plantz Small Plants Final Value 346.0784 0 0 242.1569 441.1765 416.6667 0 0 0 Reduced Objective Cost Coefficient 0 1000 0 1000 0 1000 0 1100 0 1100 0 1100 -300 700 -300 700 -300 700 DODOC 0 1E+30 1E+30 300 300 300 Allowable Decrease 0 1E+30 1E+30 100 0 0 1E+30 1E+30 1E+30 Constraints BE Cell $T$28 $U$28 $V$28 $T$32 $U$32 $T$37 $U$37 $V$37 $W$25 $W$26 Sw$27 $w$25 $w$26 $w$27 Name Production in Plant 1 Production in Plant2 Production in Plant3 Equal % Plant 1,2 Equal % Plant 2,3 Space Occupied Planti Space Occupied Plant2 Space Occupied Plants Large Production Medium Production Small Production Large Product sales Medium Product sales Small Product sales Final Value 588.2353 441.1765 416.6667 0.490196 0.490196 11764.71 6617.647 5000 346.0784 1100 Shadow Constraint Price R.H. Side 0 1200 0 900 0 850 1200000 0 2100000 0 0 13000 0 12000 289.2156863 5000 0 1000 100 1100 700 900 1200 750 Allowable Allowable Increase Decrease 1E+30 611.7647 1E+30 458.8235 1E+30 433.3333 0.051470588 0.288399 0.051470588 0.164799 1E+30 1235.294 1E+30 5382.353 1196.61 1E+30 653.9216 100 242.1569 1E+30 700 1E+30 553.9216 1E+30 100 1E+30 750 525 0 346.0784 1100 0 OOOO