Question

A manager is attempting to put together an aggregate plan for the coming nine months. She has obtained a forecast of expected demand for the planning horizon. The plan must deal with highly seasonal demand; demand is relatively high in periods 3 and 4 and again in period 8, as can be seen from the following forecasts:

Period	1	2	3	4	5	6	7	8	9	Total
Forecast	190	230	260	280	210	170	160	260	180	1,940

The department now has 20 full-time employees, each of whom can produce 10 units of output per period at a cost of $7 per unit. Inventory carrying cost is $7 per unit per period, and backlog cost is $16 per unit per period.

Suppose another option is to use part-time workers to assist during seasonal peaks. The cost per unit, including hiring and training, is $11. The output rate is 10 units per worker per period for all workers. A maximum of 10 part-time workers can be used, and the same number of part-time workers must be used in all periods that have part-time workers. The ending inventory in period 9 should be 10 units. The limit on backlogs is 20 units per period. Try to make up backlogs as soon as possible. Compute the total cost for this plan. Assume 20 full-time workers. (Omit the "$" sign in your response.)

Answer 1

Formulas:

C18 =B18+C17  

C22 =B22+C21

B21 =B28*$B$13

B23 =MAX(B22-B18,0)

B24 =MAX(B18-B22,0)

B28 =$B$12+B27

B29 =$B$13*B28

Copy these formulas upto period 9 (column J)

I10 =SUM(B29:J29)

I11 =SUM(B23:J23)

I12 =SUM(B24:J24)

I13 =MAX(B27:J27)

J10 =H10*I10 copy to J10:J13

J!4 =SUM(J10:J13)

Total cost of plan = $ 15,255