Question

UNIVERSITY CERAMICS (25 POINTS) University Ceramics manufactures plates, mugs, and steins that include the university name and logo for sale in campus bookstores. They have formulated a linear program to determine the production levels that would maximize profit. The solved spreadsheet model and corresponding sensitivity report are shown below. A B C D E F G 1 Plates Mugs Steins 2 Unit Profit $9.60 $12.30 $14.60 3 4 Resource Required per Unit Used Available 5 Molding (minutes) 4 3 6 1200 <= 1200 6 Finishing (minutes) 8 12 14 3600 <= 3600 7 Clay (ounces) 5 3 4 1350 <= 1500 8 9 Plates Mugs Steins Total Profit 10 Production 150 200 0 $3,900.00 Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $B$10 Production Plates 150 0 9.6 6.8 1.2 $C$10 Production Mugs 200 0 12.3 2.1 4.5 $D$10 Production Steins 0 -1.5 14.6 1.5 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $E$5 Molding (minutes) Used 1200 0.7 1200 100 300 $E$6 Finishing (minutes) Used 3600 0.85 3600 1200 1200 $E$7 Clay (ounces) Used 1350 0 1500 1E+30 For each of the following problems, answer the question as specifically and completely as is possible without re-solving the problem with the Solver. All problems are independent (i.e., any change made in one problem part does not apply for the other problem parts). Type all the answers in the provided boxes on the data spreadsheet. For full credit, you must justify your answers by utilizing the results in the sensitivity report (e.g., indicate what number was used in the report to justify your answer). a. Suppose the profit per plate increases from $9.60 to $11.00. Will this change the optimal production quantities? What can be said about the change in total profit? b. Suppose the profit per plate decreases by $0.60 and the profit per mug increases by $1.50. Will this change the optimal production quantities? What can be said about the change in total profit? c. Suppose a part-time worker in the molding department calls in sick, so that now four fewer hours are available in the molding department. How much would this affect total profit? Would it change the optimal production quantities? d. Suppose one of the workers in the molding department is also trained to do finishing. Would it be a good idea to have this worker shift some of her time from the molding department to the finishing department? Indicate the rate at which this would increase or decrease total profit per minute shifted. How many minutes can be shifted before this rate might change? e. The allowable decrease for the steins objective coefficient and for the available clay constraint were both deleted from the sensitivity report. Based on the nature of the solution, you should be able to infer what numbers were there. For each, indicate what number should be there and, in a sentence or two, explain the intuition as to why you know that particular number should be there.

UNIVERSITY CERAMICS (25 POINTS)
University Ceramics manufactures plates, mugs, and steins that include the university name and logo for sale in campus bookstores. They have formulated a linear program to determine the production levels that would maximize profit. The solved spreadsheet model and corresponding sensitivity report are shown below.

A B C D E F G
1 Plates Mugs Steins
2 Unit Profit $9.60 $12.30 $14.60
3
4 Resource Required per Unit Used Available
5 Molding (minutes) 4 3 6 1200 <= 1200
6 Finishing (minutes) 8 12 14 3600 <= 3600
7 Clay (ounces) 5 3 4 1350 <= 1500
8
9 Plates Mugs Steins Total Profit
10 Production 150 200 0 $3,900.00

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$10 Production Plates 150 0 9.6 6.8 1.2
$C$10 Production Mugs 200 0 12.3 2.1 4.5
$D$10 Production Steins 0 -1.5 14.6 1.5

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$5 Molding (minutes) Used 1200 0.7 1200 100 300
$E$6 Finishing (minutes) Used 3600 0.85 3600 1200 1200
$E$7 Clay (ounces) Used 1350 0 1500 1E+30

For each of the following problems, answer the question as specifically and completely as is possible without re-solving the problem with the Solver. All problems are independent (i.e., any change made in one problem part does not apply for the other problem parts). Type all the answers in the provided boxes on the data spreadsheet. For full credit, you must justify your answers by utilizing the results in the sensitivity report (e.g., indicate what number was used in the report to justify your answer).

a. Suppose the profit per plate increases from $9.60 to $11.00. Will this change the optimal production quantities? What can be said about the change in total profit?
b. Suppose the profit per plate decreases by $0.60 and the profit per mug increases by $1.50. Will this change the optimal production quantities? What can be said about the change in total profit?
c. Suppose a part-time worker in the molding department calls in sick, so that now four fewer hours are available in the molding department. How much would this affect total profit? Would it change the optimal production quantities?
d. Suppose one of the workers in the molding department is also trained to do finishing. Would it be a good idea to have this worker shift some of her time from the molding department to the finishing department? Indicate the rate at which this would increase or decrease total profit per minute shifted. How many minutes can be shifted before this rate might change?
e. The allowable decrease for the steins objective coefficient and for the available clay constraint were both deleted from the sensitivity report. Based on the nature of the solution, you should be able to infer what numbers were there. For each, indicate what number should be there and, in a sentence or two, explain the intuition as to why you know that particular number should be there.

Added by Melissa S.

Question

Please give Ace some feedback