A local canning company sells canned vegetables to a supermarket chain in the Minneapolis area. A typical case of canned vegetables requires an average of 0.2 day of labor to produce. The aggregate...

A local canning company sells canned vegetables to a supermarket chain in the Minneapolis area. A typical case of canned vegetables requires an average of 0.2 day of labor to produce. The aggregate inventory on hand at the end of June is 800 cases. The demand for the vegetables can be accurately predicted for about 18 months based on orders received by the firm. The predicted demands for the next 18 months are as follows: (given)

The firm currently has 25 workers. The cost of hiring and training a new worker is $1,000, and the cost to lay off one worker is $1,500. The firm estimates a cost of $2.80 to store a case of vegetables for a month. They would like to have 1,500 cases in inventory at the end of the 18-month planning horizon.

a. Develop a spreadsheet to find a plan that hires and fires workers monthly in order to minimize inventory costs. Determine the total cost of that plan as well.

Extracted text: Forecasted Demand Forecasted Demand (hundreds of cases) (hundreds of cases) Month Workdays Month Workdays July August September October November April May June 23 21 29 20 28 14 33 22 42 20 31 21 July August September October 26 23 20 18 29 18 16 14 December 58 10 33 20 January February March 19 20 35 23 November December 28 50 17 14 18 25 20 10