sol4

C$%P&E' 4 Sol(tion 1. E)* CO*P%, CO*P%, Completed &ale

Elm Co. Sales and Operations Planning Spreadsheet

Sales Forecast

Actual Di% Mont& 'umulative

(in Million $) (in units) (in units)

History October 0.80 800 8! 26

Operations lan

(in units) (in  em*loyees)  Number +or,in +or,in- DaysMo. Actual (in units) Di% Mont& 'umulative

Inentor! lan

(in units) (in 000 $)

Actual (in units) Days o 2u**ly

November 0.85 850 85" 1 27

December   0.90 900 9#9 49 76

800 ! / 98 -2 -2

800 8 "9 80 +2 0

800 8 "9 800 0 0

1"0 10"

100 70

0 0

"

/ 1.6

1! -1."

#.4

#1"

2. &'%PPE' )% E3IP*E& CO*P%,

4evise *lan% 6ra**er 7an :ui*ment 'om*any 2ales an O*;s lannin- 2*reas&eet 1 4iin- Moers rouct
Forecast

Oct

Nov

Dec

3an

Feb

Mar

".50

"0.00

"!.5

5.00

5.00

.50

(units)

5000

#000

!500

000

000

/000

(units)

#/8#

/!!

!0!5

1!"!

1/#

1#/5

1990

1"#5

5000

#000

!500

0

""6

#2"0



0

""#

0

9

47

/

"9

"9

0

"

/

5!#9

#09"

9

!#9

9"

9

#0

"5"9 "00

"00

7"0

(M$)

Actual

lan

Di% Mont& 'umulative

 

Operations

lan

(units) ( em*loy)

 +or, DaysMo. Actual

(units)

Di% Mont& 'umulative

&arget OS In/

"

Inentor!

lan

Actual Days o 2u**ly

(units)

"0

"0

"0

1944

"00

7"0

(000$)

/

/

/

#402

7"

1#1#

(units)

!5

/0

/9##

"0

"5

"/

19

"

"

a 6ar-et inventory levels or t&e t&ree mont&s base on 5 ays o su**ly%

3anuary = 5 > 000  0 = 500? February = 5 > 000  0 = 500? Marc& = 5 > /000  0 = 50 lanne buil or eac& mont& re:uire to ac&ieve t&e tar-et accountin- or t&e orecast eman an t&e inventory in t&e *revious *erio% @uil *lan = orecast eman  tar-et inventory B *revious mont& inventory 3anuary *lanne buil is Cero since /9## units remain in inventory at t&e en o December. February *lane buil = 000  500 B "9## = 55! Marc& *lanne buil = /000 50 B 500 = /50

#1

 b) ualitative actors% 6&e *lan inicates no *rouction in 3anuary an very li-&t *rouction in February. 6&is coul be im*lemente as a *lant s&uton t&at may be very isru*tive to or, orce moral an cause an em*loyee retention *roblem. Et can also &ave :uality an *rouctivity issues as more *roblems are li,ely at s&uton an start1u*. ey s,ills are not *ractice. A better alternative mi-&t be to maintain some *rouction belo customer eman an -raually reuce inventory levels. 'onsier -oin- to a # ay or ot&er orm o s&orten or,ee,. 4estrict t&e use o overtime. 'onsier t&e use o a *lanne s&uton urin- t&e summer vacation season or orce t&e use o accrue vacation time to reuce t&e number o or,ers available.

#. &'%PPE' )% E3IP*E& CO*P%, 'E5ISI&E

a) 6&e avera-e orecast error is calculate as t&e ierence beteen t&e total orecast an actual eman ivie by t&e total orecast. En t&is caseG since t&e / mont& cumulative error is -iven in t&e table% Forecast error  = 1"#5  (5000  #000  !500) > "00 = 19. 4eucin- eac& o t&e orecast values by 9. or 3anuary to 3une yiels t&e *roIecte values units sales an resultin- inventory levels an ays o su**ly s&on in t&e table belo.

#1/

&rapper )an E(ipment Compan! Sales and Operations Planning Spreadsheet 'iding *oers Prod(8t ro(p :*a;e-to-Sto8; History lan Sales Oct Nov Dec 3an Forecast (M$) ".50 "0.00 "!.5 5.00 (units) 5000 #000 !500 000 Actual   Pro
Actual Days o 2u**ly 

(units) (000$)

&arget OS In/ "0 "0 / /

(units) Pro
!5 "0

/0 "5

Feb 5.00 000 116

Mar .50 /000 2724

A*r "0.00 #000 #6#2

May ".50 5000 4"40

3un ".50 000 6#"6

000 / "

/000 #/ /

#000 ! 0

5000 ! 

000 "0! 

" "0 /

"00 412 7224

"00 4#12 7"46

7"0 4" 02

1000 49"6 672

12"0 "416 9476

17"0 6060 10602

/9## "/

46

"0

#9

27

26

21

 Options >or 8onsideration

'&an-e t&e orecast. 6&is oul re:uire t&e mar,etin- an *rouction man-ers comin- to a-reement on &at t&e ne orecast s&oul be. AIust t&e *rouction *lan to com*ensate or t&e act t&at t&e orecast seems to &ave a relatively consistent ne-ative bias. 6&is o*tion &as little ris, in t&e near term since inventory levels are relatively &i-&.

#1#

4. S?I @ SE%A IC.

a. 7evel lan A--re-atin- t&e orecast uarter 3et 2,is 2nomobiles 6otal

"



"0G000 9G000 "9G000

/

"5G000 G000 G000

#

"!G000 "9G000 /5G000

6otal

/G000 "0G000 "/G000

##G000 #5G000 89G000

#

6otal

Determinin- t&e *rouction rate% (6otal orecast 1 be-innin- inventory)  # :uarters (89G000 1 "G000)  # = G000 units *er :uarter  6&e lan an its costs% uarter

"



/

Deman "9G000 G000 /5G000 rouction G000 G000 G000 @e-innin- Enventory "G000 #G000 #G000 nin- Enventory #G000 #G000 0 Avera-e EnventoryJ G500 #G000 G000 @ac,orers 0 0 9G000 J(be-innin- inventory  enin- inventory)   'osts 4e-ular time Enventory @ac,orers 6otal

$"5.00 × $ /.00 × $#.00 ×

'onse:uences% 7o levels o inventory 2ubstantial bac, orer in :uarter /  b. 'umulative '&art

#15

88G000 8G500 9G000

"/G000 G000 0 0 0 0

6otal = $ "G/0G000 =$ 5G500 = $ "!G000 $ "G5!"G500

89G000 88G000

8500 9000

100,000

Cumulative Forecast 80,000

CumulativeOutput

60,000

   s     t     i    n    u    n     i     d    n    a    m    e     d     d    n    a

40,000

   n    o     i     t    c    u     d    o    r    p  .    m    u     C

20,000

Quarter 1

Quarter 2

Quarter 3

Quarters

c. Enventory 2*ace = 0 cubic eet> #000 = 80G000 cubic eet . Envestment = $ !00.00 > #G000 = $ G#00G000

#1!

Quarter 4

". I5%' BO'ESO

a. Overtime uarter

"



/

#

6otal

3et 2,is "0G000 "5G000 "!G000 /G000 ##G000 2nomobiles ""G000 G000 "9G000 "0G000 #G000 6otal "G000 G000 /5G000 "/G000 9"G000 rouction rate = (9"G000 1 "G000)  # = G500 units *er :uarter  uarter

"



/

#

Deman "G000 G000 /5G000 Overtime 500 500 500 4e-ular G000 G000 G000 Out*ut G500 G500 G500 @e-innin- Enventory "G000 G500 /G000 nin- Enventory G500 /G000 0 Avera-e EnventoryJ "G50 G50 "G500 @ac,orers 0 0 9G500 J(be-innin- inventory  enin- inventory)   'osts 4e-ular time Overtime Enventory @ac,orers 6otal  b.

c.

88G000 G000 !G000 9G500

$"5.00 × $.50 × $ /.00 × $#.00 ×

2ubcontractin2ubcontractin- 'ost Overtime 'ost  Net Encrease(Decrease)  Ne 6otal 'ost

Hirin- a Ne +or,er  Hirin4e-ular  Overtime 'ost  Net EncreaseDecrease  Ne 6otal 'ost

$ /0.00 × $ .50 ×

$ /00.00 × $ "5.00 × .5 ×

#1

"/G000 500 G000 G500 0 0 0 0

9"G000 G000 88G000 90G000

!000 9500

6otal = $ "G/0G000 =$ #5G000 =$ "8G000 = $ 8G000 $ "G!""G000

G000 G000

" G000 000

=$ !0G000 =$ #5G000 $ "5G000 $ "G!!G000

=$ /00 =$ /0G000 =$ #5G000 $ ("#G00) $ "G59!G/00

10. BO%S BO,O3S %&3'E DOO

a. 3oan s&oul *rouce "/5 units eac& mont&. K("0  "!0 1 "0) = "/5L 600

500

CumulativeOutput

400

   s     t     i    n    u    n     i     d    n    a

300

   m    e     d     d    n    a    n    o     i     t    c    u     d    o    r    p  .    m    u     C

Cumulative!emand

200

100

Mont 1

Mont 2

Mont 3

Mont 4

Monts

 b. 6&e enin- inventory or mont& # is "80 units. K("0  (# × "/5) 1 /0) = "80L c. 3oan s&oul *rouce 90 units eac& mont&. K("0  "!0  0  0 1 "0)  # = 90L

. Mont&% @e-innin- Enventory rouction Deman nin- inventory Avera-e inventory 'arryin- cost @ac,orers (cumulative) @ac,orer cost

"



/

"0 90 "0 0 5 $5 0 $"!0

0 90 "!0 0 0 $0 90 $0

0 90 0 0 0 $0 0 $"!0

6otal Enventory 'ost = $5 × 5 = $5 6otal @ac,orer 'ost = $8 × "/0 = $"0#0

#18

# 0 90 0 0 0 $0 0 $0

#19

11. O'O E) *%' CO.

a. 400

CumulativeOutput

300

200    s     d    n    u    o    p     0     0     0  ,     1    n     i  .     d    o    r    p     d    n    a

Cumulative!emand

100

    d    n    a    m    e     d  .    m    u     C

"anuar#

Fe$ruar#

Marc

Monts

 b. A *rouction rate o "00 units *er mont& is re:uire in orer to avoi bac,orers an result in no enin- inventory in Marc&. K("00  /00 1 "00)  /L

#1"0

1. EE'%) %5IOICS %%I

'&ase 2ales lan uarter  / #

2ales 8G000 !G#00 "G!00 "!G000

rouction

+or,orce

G000 !G#00 "G!00 "5G000

ninEnventory

0 !# "! "50

Overtime rouction

"G000 "G000 "G000 /G000

'ost Etem Enventory 'arryin- 'ost (/000 > $) Overtime 'ost Firin- 'ost (5# > $#00) Hirin- 'ost (0 > $00) 4e-ular ayroll 'ost ("50 > $"G00) 6otal 'ost

0 0 0 0

'ost $ !G000 0 "G!00 #G000 "80G000 $""G!00

7evel rouction lan uarter  / #

2ales 8G000 !G#00 "G!00 "!G000

rouction

+or,orce

G000 !G#00 5G 000 "8G#00

ninEnventory

50 50 50 "50

'ost Etem Enventory 'arryin- 'ost ($ > 5G#00) Overtime 'ost ($"#J > /G#00) Firin- 'ost Hirin- 'ost 4e-ular ayroll 'ost("50 > $"G00) 6otal 'ost J$"# = $" or re-ular  $ overtime *remium

#1""

"G000 "G000 /G#00 5G#00

'ost $ "0G800 #G!00 0 0 "80G000 $/8G#00

Overtime rouction G000 "G#00 0 /G#00