Reference no: EM131867636

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.

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.

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 pages only (without referring to the Excel exhibit). Functions such as integer, maximum, minimum, if/then, etc. are not linear functions.

a. Specify the decision variables.

b. 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.

c. 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.

d. Include a picture/print of your spreadsheet formulation as Exhibit 1. Please note that in order to receive credit, your spreadsheet 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?

e. 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. Include well-formatted, well-labeled table as Exhibit 2. Fit to one page; use landscape orientation if needed. How would you describe the impact of storage costs on the production schedule?

(Please include instructions for d and e so I can run the table myself as well)