Question

Problem 8-13

Shoney Video Concepts produces a line of video streaming servers that are linked to personal computers for storing movies. These devices have very fast access and large storage capacity.

    Shoney is trying to determine a production plan for the next 12 months. The main criterion for this plan is that the employment level is to be held constant over the period. Shoney is continuing in its R&D efforts to develop new applications and prefers not to cause any adverse feelings with the local workforce. For the same reason, all employees should put in full workweeks, even if that is not the lowest-cost alternative. The forecast for the next 12 months is

MONTH	FORECAST DEMAND	MONTH	FORECAST DEMAND
January	600	July	200
February	800	August	200
March	900	September	300
April	600	October	700
May	400	November	800
June	300	December	900

     Manufacturing cost is $200 per server, equally divided between materials and labor. Inventory storage cost is $5 per month. A shortage of servers results in lost sales and is estimated to cost an overall $20 per unit short.

    The inventory on hand at the beginning of the planning period is 200 units. Ten labor hours are required per server. The workday is eight hours.

Develop an aggregate production schedule for the year using a constant workforce. For simplicity, assume 22 working days each month except July, when the plant closes down for three weeks’ vacation (leaving seven working days). Assume that total annual production capacity is greater than or equal to total annual demand (i.e., compute workforce level based on annual demand and annual capacity). (Leave no cells blank - be certain to enter "0" wherever required. Indicate monthly shortages using a negative ending inventory level. Round up the "number of workers" to the next whole number and round down your "monthly production rates" to the nearest whole number.)

January

February

March

April

May

June

July

August

September

October

November

December

Total

Forecast

600

800

900

600

400

300

200

200

300

700

800

900

Beginning inventory

Available production

Ending inventory

Costs

Total

Lost sales

Inventory

Total

Answer 1

Calculations:

F G 1 Beginning inventory 200 2 Hours per day 3 No. of days in July 4 No. of days in other months 22 5 Unit carrying cost 5 6 Unit shortage cost 20 7 Hours per unit 8 9 Net annual demand =N18-B1 10 Hrs. required =B9%B7 hours 11 No. of days available =B4*11+B3 12 Capacity per worker =B1l%B2 hours 13 No. of workers required =ROUNDUP (B10/B12,0) 14 Production in July =ROUNDDOWN (B13%B3*B2/B7,0) units 15 Production in other months =ROUNDDOWN (B13*B4B2/B7.0 units 16 Jan Feb 18 Forecast 600 800 19 Beginning inventory =B1 =MAX (0, B21) 20 Available production =$B$15 =$B$15 21 Ending inventory =B19+B20-B18 =C19+C20-C18 Mar 900 =MAX (0,021) =$B$15 =D19+D20-018 Apr 600 =MAX (0,021) = $B$15 =E19+E20-E18 May 400 =MAX (0, E21) =$B$15 =F19+F20-F18 Jun 300 =MAX (0, F21) =$B$15 =G19+G20-618 22 23 Costs 24 Lost sales 25 Inventory 26 Total =MAX (0,-B21) *$B$6 =MAX(O,B21) *$B$5 =B24+B25 =MAX(0, -C21)*$B$6 =MAX(0,-D21) $B$ 6 =MAX(0,-E21) *$B$6 =MAX (0,-F21) $B$6 =MAX(0,-G21) *$B$6 =MAX(0,C21) *$B$5 =MAX (0, D21) *$B$5 =MAX (0, E21) $B$5 =MAX (0, F21) *$B$5 =MAX (0, G21) *$B$5 =C24+C25 =D24+D25 =E24+E25 =F24+F25 =G24+G25


H M Jul Oct 17 18 200 19 =MAX (0,621) 20 =$B$14 21 =H19+H20-H18 Total =SUM (B18:M18) Aug 200 =MAX(0, H21) =$B$15 =I19+120-118 Sep 300 =MAX(0,121) =$B$15 =J19+J20-J18 L Nov 800 =MAX (0,K21) =$B$15 =L19+L20-L18 700 =MAX(0, J21) = $B$15 =K1 9+K20-K18 Dec 900 =MAX(0, L21) = $B$15 =M19+M20-M18 22 23 24 =MAX(0, -H21) *$B$ 6 =MAX(0, -121) $B$6 25 =MAX (0, H21) *$B$5 =MAX (0,121) *$B$5 26 =H24+H25 =I24+125 =MAX(0, -J21) $B$6 =MAX (0,J21) *$B$5 =J24+J25 =MAX(0,-K21) $B$6 =MAX(0, K21) *$B$5 =K24+K25 =MAX(0, -L21) $B$6 =MAX (0, L21) *$B$5 =L24+L25 =MAX(0,-M21) $B$6 =MAX (0,M21) *$B$5 =M24+M25 Total =SUM(B24:M24) =SUM (B25:M25) =N24+N25

Results:

Aug May 400 Total 6700 Jan Forecast 600 Beginning inventory200 Available production 580 Ending inventory 180 Feb 800 180 580 -40 Mar 900 0 580 -320 Apr 600 0 580 -20 Jun 300 180 580 460 Jul 200 460 184 444 200 444 580 824 Sep 300 824 580 1104 Oct 700 1104 580 984 Nov 800 984 580 764 Dec 900 764 580 444 580 180 Costs Lost sales Inventory Total $0 $900 $900 $800 $0 $800 $6,400 $0 $6,400 $400 $0 $400 $0 $900 $900 $0 $2,300 $2,300 $0 $2,220 $2,220 $0 $4,120 $4,120 $0 $5,520 $5,520 $0 $4,920 $4,920 $0 $3,820 $3,820 $0 $2,220 $2,220 Total 7600 $26,920 $34,520

----