Question

JayJay Video Concepts produces a line of videodisc players to be linked to personal computers for video games. Videostick have much faster access time than tape. With such a video link, the game becomes a very realistic experience. In a simple driving game where the joystick steers the vehicle, rather than seeing computer graphics on the screen, the player is actually viewing a segment of a videodisc shot from a real moving vehicle. Depending on the action of the player (hitting a rail, is an example), the disc moves virtually instantaneously to that segment and the player becomes part of an actual accident of real vehicles but fake.

JayJay 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. JayJay is continuing in its R & D efforts to develop new applications and prefers not to cause any adverse feelings with the local workforce. All employees should put in full workweeks, even if this is not the lowest-cost alternative. The forecast for the next 12 months is

MONTH    FORECAST DEMAND MONTH    FORECAST DEMAND

January 640 July          240

February 840 August      240

March 940 Sept        340

April 640          October 740

May 440          Nov 840

June 340          Dec 940

Manufacturing cost is $200 per set, equally divided between materials and labor. Inventory storage cost is $6 per month. A shortage of sets results in lost sales and is estimated to cost an overall $21 per unit short.

The inventory on hand at the beginning of the planning period is 200 units. Twelve labor hours are required per videodisc player. The workday is nine hours.

Develop an aggregate production schedule for the year using a constant workforce. For simplicity, assume 23 working days each month except July, when the plant closes down for three weeks' vacation (leaving eight 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 next lower whole number.)

Jan Feb     Mar       Apr     May   Jun    Jul        Aug   Sept    Oct    Nov   Dec                     Total

Forecast                      640 840    940        640      440     340    240      240   340      740   840    940  

Beginning inventory

Available production               

Ending inventory

Costs

Inventory

Total       

Answer 1

Hi.

Please find below answer.

I have attached excel formula screen shot as well.

If you like the answer, please up vote. It will encourage me to contribute more.

Formulas used

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Forecast

640

840

940

640

440

340

240

240

340

740

840

940

Working Days

23

23

23

23

23

23

8

23

23

23

23

23

Per day hours

9

9

9

9

9

9

9

9

9

9

9

9

Working Hours in a month per employee

=D5*D6

=E5*E6

=F5*F6

=G5*G6

=H5*H6

=I5*I6

=J5*J6

=K5*K6

=L5*L6

=M5*M6

=N5*N6

=O5*O6

Per unit cost

Annual Demand

=SUM(D4:O4)

Production cost

200

Annual Working hours per employee

=SUM(D7:O7)

Shortage Cost

21

Hours per video disc player

12

Inventory cost

6

No of player produced by one employee

=D10/D11

No of Employee require to meet Annual demand

=ROUNDUP(D9/D12,0)

Annual Plan with 37 Resources each month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Total

Forecast

640

840

940

640

440

340

240

240

340

740

840

940

=SUM(D18:O18)

Beginning Inventory

200

=IF(D21>0,D21,0)

=IF(E21>0,E21,0)

=IF(F21>0,F21,0)

=IF(G21>0,G21,0)

=IF(H21>0,H21,0)

=IF(I21>0,I21,0)

=IF(J21>0,J21,0)

=IF(K21>0,K21,0)

=IF(L21>0,L21,0)

=IF(M21>0,M21,0)

=IF(N21>0,N21,0)

Production

=ROUNDDOWN($D$13*D5*D6/$D$11,0)

=ROUNDDOWN($D$13*E5*E6/$D$11,0)

=ROUNDDOWN($D$13*F5*F6/$D$11,0)

=ROUNDDOWN($D$13*G5*G6/$D$11,0)

=ROUNDDOWN($D$13*H5*H6/$D$11,0)

=ROUNDDOWN($D$13*I5*I6/$D$11,0)

=ROUNDDOWN($D$13*J5*J6/$D$11,0)

=ROUNDDOWN($D$13*K5*K6/$D$11,0)

=ROUNDDOWN($D$13*L5*L6/$D$11,0)

=ROUNDDOWN($D$13*M5*M6/$D$11,0)

=ROUNDDOWN($D$13*N5*N6/$D$11,0)

=ROUNDDOWN($D$13*O5*O6/$D$11,0)

=SUM(D20:O20)

Ending Inventory

=D19+D20-D18

=E19+E20-E18

=F19+F20-F18

=G19+G20-G18

=H19+H20-H18

=I19+I20-I18

=J19+J20-J18

=K19+K20-K18

=L19+L20-L18

=M19+M20-M18

=N19+N20-N18

=O19+O20-O18

Manufacturning cost

=D20*$I$9

=E20*$I$9

=F20*$I$9

=G20*$I$9

=H20*$I$9

=I20*$I$9

=J20*$I$9

=K20*$I$9

=L20*$I$9

=M20*$I$9

=N20*$I$9

=O20*$I$9

=SUM(D22:O22)

Shortage cost

=IF(D21<0,-D21*$I$10,0)

=IF(E21<0,-E21*$I$10,0)

=IF(F21<0,-F21*$I$10,0)

=IF(G21<0,-G21*$I$10,0)

=IF(H21<0,-H21*$I$10,0)

=IF(I21<0,-I21*$I$10,0)

=IF(J21<0,-J21*$I$10,0)

=IF(K21<0,-K21*$I$10,0)

=IF(L21<0,-L21*$I$10,0)

=IF(M21<0,-M21*$I$10,0)

=IF(N21<0,-N21*$I$10,0)

=IF(O21<0,-O21*$I$10,0)

=SUM(D23:O23)

Inventory Cost

=IF(D21<0,0,D21*$I$11)

=IF(E21<0,0,E21*$I$11)

=IF(F21<0,0,F21*$I$11)

=IF(G21<0,0,G21*$I$11)

=IF(H21<0,0,H21*$I$11)

=IF(I21<0,0,I21*$I$11)

=IF(J21<0,0,J21*$I$11)

=IF(K21<0,0,K21*$I$11)

=IF(L21<0,0,L21*$I$11)

=IF(M21<0,0,M21*$I$11)

=IF(N21<0,0,N21*$I$11)

=IF(O21<0,0,O21*$I$11)

=SUM(D24:O24)

Total Cost

=SUM(D22:D24)

=SUM(E22:E24)

=SUM(F22:F24)

=SUM(G22:G24)

=SUM(H22:H24)

=SUM(I22:I24)

=SUM(J22:J24)

=SUM(K22:K24)

=SUM(L22:L24)

=SUM(M22:M24)

=SUM(N22:N24)

=SUM(O22:O24)

=SUM(D25:O25)