Question

Shoney Video Concepts produces a line of videodisc players to be linked to personal computers for video games. Videodiscs have much faster access time than tape. With such a computer/video link, the game becomes a very realistic experience. In a simple driving game where the joystick steers the vehicle, for example, 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 guard rail, for example), the disc moves virtually instantaneously to that segment and the player becomes part of an actual accident of real vehicles (staged, of course). 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 this is not the lowest-cost alternative. The forecast for the next 12 months is

MONTH	FORECAST DEMAND	MONTH	FORECAST DEMAND
January	550	July	150
February	750	August	150
March	850	September	250
April	550	October	650
May	350	November	750
June	250	December	830

Manufacturing cost is $200 per set, equally divided between materials and labor. Inventory storage cost is $4 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. Eight labor hours are required per videodisc player. The workday is seven 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.)

January

February

March

April

May

June

July

August

September

October

November

December

Forecast

550

750

850

550

350

250

150

150

250

650

750

830

Beginning inventory

Available production

Ending inventory

Costs

Total

Lost sales

Inventory

Total

**Please Help** I having trouble could you show steps please!!

Answer 1

Total annual demand net of beginning inventory = 550+750+850+550+350+250+150+150+250+650+750+830-200 = 5880

Total production hours required = 5880 * 8 hours per unit = 47040 hours

Total number of workdays in the year = 23*11+8 = 261

Annual production hours available per worker = 261*7 = 1827

Constant workforce required = Total production hours required / Production hours availableper worker = 47040/1827 = 26  (rounded-up)

Available Production each month, except July = 26*23*7/8 = 523 (rounded-down)

Available production in July = 26*8*7/8 = 182  (rounded-down)

The aggregate plan based on constant workforce as determined above is following:

N O 3 Beginning inventory 4 Available Production 5 Ending inventory 173 173 851 478 1124 997 770 523 523 523 523 523 5935 523 182 523 463 173 327 173 478 1124 770 7 Costs 8 Inventory 9 Lost Sales Total 4,496 692 692 1,784 1,912 3,404 3,988 3,0801,852 21,900 1,134 6,867 8,568 10 Total 692 1134 6,867 0,567 0,692 1,7841,912 3,404 3,988 3,080 1,852 30,468 4,496 12 Working days 13 Production hours available 161161 14 15 Total production hours requ 47040 16 Constant workforce require 26 17 23 23 23 23 23 23 23 161 16111656 161 161 161 161 161 1827

FORMULAS:

Cell	Formula	Copy to
C3	=MAX(0,B5)	C3:M3
B4	=ROUNDDOWN($B$16*B13/10,0)	B4:M4
B5	=B4+B3-B2	B5:M5
B8	=MAX(B5,0)*4	B8:M8
B9	=MAX(-B5,0)*21	B9:M9
B10	=SUM(B8:B9)	B10:M10
B13	=B12*7	B13:M13
B15	=(N2-B3)*8
B16	=ROUNDUP(B15/N13,0)
N13	=SUM(B13:M13)

Total cost of the plan = $ 30,468