Deliverable Instructions

Your submission should consist of only those answer exhibits that are specifically asked for in a single word or pdf document. Please ensure that each exhibit is well edited and appropriately formatted. You may assume that fractional answers are acceptable throughout.

SLOP Becomes Hearty GLOP

Ann is a plant manager at Private Foods. Her plant makes Hearty Glop, a microwaveable dinner that is particularly popular in winter. Ann has to minimize her total annual cost while meeting demand each month. Monthly demand for Glop for the next year is forecasted as shown in the table below and in the GlopData.xlsx.(THIS FILE IS ATTACHED)

• The plant can make at most 2,500 units of Glop in any month.
• The main raw material for making Glop is an ingredient called Slop. Each unit of Glop requires 8 units of Slop. Private Foods purchases Slop from a number of different vendors. The price of Slop is fairly volatile. The accompanying table shows Ann’s forecasts of the monthly prices per unit for Slop for the next year.
• Each unit of Glop also requires 2 units of labor. Unit labor costs for the next year are forecasted as shown in the table. The plant can change the labor employed from month to month without additional costs.
• Up to 2,500 units of unsold Glop can be stored in the plant at a monthly cost of $20 per unit. Unused Slop cannot be stored because it congeals into an inedible form.
• The plant has 50 units of Glop available at the beginning of the first month.

 

Note that the sequence of events in each period is: production, then sales, then storage.

 

MonthGlop DemandSlop PriceLabor Price

11100$60$15

21200$60$16

31300$65$17

41000$70$18

5800$50$19

6500$40$19

7200$20$19

8200$10$18

9400$25$17

10700$35$17

11900$45$16

121000$50$16

 

Prepare a linear decision model to help Ann develop her production plan.

Note:

• In answering questions a. – c., use the guidelines developed in class handouts. In particular, the reader should be able to write out all formulations using your answers on these pagesonly(without referring to the Excel exhibit).
• Functions such as integer, maximum, minimum, if/then, etc. arenotlinear functions.

1. 1. Specify the decision variables.
   2. Specify the objective. This must be based on the inputs specified in the problem (the parameters), the decision variables specified in (a), and/or intermediate variables/values that you choose to specify.
   3. Specify the constraints. These must be based on the input numbers specified in the problem (the parameters), the decision variables specified in (a), and/or intermediate variables/values that you choose to specify.
   4. Include a picture/print of your spreadsheet formulation asExhibit 1. Please note that in order to receive credit, yourspreadsheet must be sufficiently annotated so that it is readable. Fit it to one page; if necessary, use landscape orientation to print. What is the optimal production schedule?
   5. Ann would like to investigate the impact of the monthly unit storage cost on the optimal monthly production schedule, total annual cost, annual storage cost, annual Slop cost, and annual labor cost. Use SolverTable to exhibit this impact by varying the monthly storage cost from $0 per unit to $140 per unit in steps of $20. Includewell-formatted, well-labeledtable asExhibit 2. Fit to one page; use landscape orientation if needed. How would you describe the impactofstorage costs on the production schedule