the more you read the more you learn

8/15/2019 the more you read the more you learn

1/36

Figure 8.4 Economic order quantity (EOQ) model.

(Note that this model has been modified)

Example 9.1 - An EOQ Model for Bedrock' !ro"lem

100

Annual Demand 12,000


2/36

Figure 8. 8roduction order quantity (8OQ) model.

Example 9.( - )#e !*OQ Model and +olu%ion %o ,imo' !ro"lem.

Input Annual Demand 2,100

=etup %o&t '30.00

*nit %o&t '0.00

Annual production rate 2,00

*nit oldin$ co&t per year (to option&)

(i) in '& per year

(ii) a& o unit co&t 20.0

Annual unit oldin$ co&t '/.00

Output 8OQ 130.12

2>.14

3.3

?a@imum inentory leel 223.

Annual oldin$ co&t '/

Annual &etup co&t '/

!otal co&t '/3,3

Cell Formula Copied to

E10 ;9(E>BB,E4,ECE>)

E11 ;9(E100,Boldin$ co&t cannot "e +eroB,BB)E12 =Q!(2CECE36E10)C=Q!(E/6(E/ - E))

E1 2CE126E/

E13 2CE126E

E1 E12C(E/ - E)6E/

E1/ 0.CE10CE1

E1 ECE36E12

E14 E1/ E1 ECE

8roduction run time, o (in eeF&)

Optimal cycle time, !o (in eeF&)

A B C D E F

1

23

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31


3/36


G H I J K L M N O P

1

23

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31


4/36


.

Q

1

23

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31


5/36

Figure 8. Quantity di&count model or te eelie %ompany.

Example 9./ - A Quan%i%& 0icoun% Model for %#e #eelie 2ompan&

Input Annual Demand 1,00 User input cellsOrderin$ %o&t '40.00 are shaded

*nit oldin$ co&t per year (to option&)

(i) in '& per year


DISCOUNT TABLE *nit %o&t £10.00 £8.00 £.00

0 1000 !000

Annual unit oldin$ co&t '.00 '2.30 '1.40

Output 242.4 1/.2 /.1AdHu&ted order quantitie& 242.4 1000.0 2000.0

!otal co&t& '1,43> '1,20 '10,4/0

?inimum total co&t i& £10"80 2

Optimal order quantity i& !000.0

%ycle time i& #.$ eeF&


911 ;9(IG/BB,IGC9>,IG/) G11:11912 ;9(9110,Boldin$ co&t cannot "e +eroB,BB)

91 =Q!(2CIGCIG36911) G1:1

913 ;9(91#910,91,910) G13:13

91 IGCIG36913 0.C913C911 IGC9> G1:1

91 ?;5(91:1)

1 ?A!%(91,91:1,0) - 1

914 O99=E!(914,-3,1)

91> 2C9146G

?inimum di&count quantity, ?ini

Qi

A B C D E F G H I

1

2

3

4

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

2324

25

26

27

28

29

30

31

32


6/36

Figure 8.3 Deliery car$e model or te armer&J co-operatie.

Example 9.4 - A 0elier& 2#arge Model for %#e Farmer' 2o-opera%ie

Input Daily Demand (in tonne&) .0 User input cells*nit %o&t '100.00 are shaded

*nit oldin$ co&t per day (to option&)

(i) in '& per day '1.0

(ii) a& o unit co&t

DELI%E&' TABLE eorder %o&t £80.00 £1$0.00 £180.00

10 !0 $0

Daily unit oldin$ co&t '1.0 '1.0 '1.0

Output 1.> 22.4 2/.4AdHu&ted order quantitie& 10.0 20.0 2/.4

!otal co&t& '2 ' '30

?inimum total co&t i& £$$! 0

Optimal order quantity i& 10.0

%ycle time i& $.$ day&


91 =Q!(2CIGC9>6911) G1:1

913 ;9(916913 0.C913C911 IGCIG3 G13:1

91> 9146G

?a@imum deliery quantity, ?a@i

Qi

A B C D E F G H I

1

2

34

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

23

24

25

26


7/36

Figure 8.8 Determini&tic model it planned &tora$e&.

Example 9. - An 5nen%or& Model 6i%# +#or%age Allo6ed

Input Annual Demand 12,000=etup6Orderin$ %o&t '0.00 User input cells

*nit %o&t '2.00 are shaded

oldin$ co&t (to option&)

(i) in '& per year


=orta$e co&t per unit per year '3.00

*nit oldin$ co&t per year '.0

Output /4.2?a@imum &tocF leel 2.>

KacF-order &i+e 332.

5o. o order&6year 1.

%ycle time 2.> eeF&

Annual %o&t&LL..LL

=etup6orderin$ co&t '443./

oldin$ co&t '0.0

=orta$e co&t '/.>

8urca&e co&t '00,000

!otal co&t '01,/>


E10 ;9(E4BB,E,E4CE)

E11 ;9(E100, BEnter a alue in eiter cell E or E4B,BB)

E1 =Q!(2CECE3C(E> E10)6(E>CE10))

E13 E>CE16(E> E10)

E1 E1 - E13

E1/ E6E1

E1 26E1/

E1> ECE36E1

E20 0.CE10CE13CE136E1

E21 E>C(E1 - E13)M26(2CE1)

E22 ECE

E2 =*?(E1>:E22)

Optimal order &i+e, Qo

A B C D E F G H

1

2

3

4

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

3435

36

37

38

39


8/36

Figure 8.8 Determini&tic model it planned &tora$e&.

.

I J K L M N O P Q

1

2

3

4

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

3435

36

37

38

39


9/36

Figure 8.9 ?ultiple-product model it &tora$e &pace con&traint.

Example 9. - An 5nen%or& Model 6i%# +%orage +pace 2on%rain%

=etup co&t '1,00.0 User input cells

oldin$ co&t (a& o unit co&t) 0.0 are shaded

8roduct Demand *nit =pace EOQ Aera$e Naria"le

co&t (per unit) &pace co&t&

id$et 10,000 '14.00 0. 2.0 ./ '12,24

Gad$et 4,000 '1.00 0.2 20>.3 20.> '10,>2

8 ,000 '10.00 0.1 12.1 12>.> ',1>/

!otal& 13.3 '24,1/

8roduct Demand *nit =pace EOQ Aera$e Naria"le

co&t (per unit) &pace co&t&

id$et 03 '14.00 0. 1>>./ 2>/.> '12,>22

Gad$et /3 '1.00 0.2 1>>./ 1>3.0 '10,1

8 211/ '10.00 0.1 133. 10>.1 ',2

!otal& 00.0 '24,3>

8ercenta$e increa&e in aria"le co&t& 1.($)

S*alin+ Fa*tor , 7.37 (;nitially, &et =calin$ 9actor 1)

Sol-er arameter/

Set Tar+et Cell E2

Eual to ?a@

B2 C3an+in+ Cell/ E2

Su45e*t to Con/traint/ 14


10/36

Figure 8.9 ?ultiple-product model it &tora$e &pace con&traint.

.

K L M

1

23

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

3334

35

36

37

38

39

40

41

42

43


11/36


12/36


13/36

Figure 8.17 !e 5e&"oy pro"lem - a pro"a"ili&tic model it di&crete demand.

Example 9.3 - )#e e6"o& !ro"lem A !ro"a"ili%ic Model 6i%# 0icre%e 0emand

Input *nit %o&t, % '.00

=ellin$ 8rice, = '.00 User input cells are shaded

=crap alue, N '0.

Output

0 0.2 0. 30 '4

/ /0 0.1 0. 3 '34

0 0.1 0.1 33. '2

4 40 0.0 0.0 3 '11 3

60 7aimum pro9it , £(#


E11 =*?(D11:DI14) E12:E14

911 =*?8OD*%!(%I11:%11,DI11:D11) %11CE12 912:91

914 =*?8OD*%!(%I11:%14,DI11:D14)

G11 EI3C911 - EIC%11 EIC(%11 - 911) G12:G14

;14 ?A!%(20,G11:G14,0)

D20 O99=E!(%10,;14,0)

20 ?A(G11:G14)

proit, E8i

Demand, Di

8i

%*?i

Optimal demand" :o ,

A B C D E F G H I J

1

2

3

4

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

2324

25

26

27

28

29

30

31


14/36

Figure 8.11 8ro"a"ili&tic model it &orta$e&.

Example 9.8 - A !ro"a"ili%ic Model 6i%# +#or%age

Input oldin$ co&t, '30.00 All user input cells

=orta$e co&t, K '00.00 are shaded

K6(K ) 0.>

Output

0.0 0.>3 Optimal amount

/ 4 0.03 0.>4

> 0.01 0.>>

4 10 0.01 1


E E36(E3 E)

E11 =*?(DI11:D11) E12:E14911 ;9(E11#I),B Optimal amountB,BB)

912 ;9(A5D(E11


15/36

Figure 8.1( =erice-leel model it aria"le demand6i@ed lead-time.

Example 9.9 - A +erice-:eel Model 6i%# ;aria"le 0emand< Fixed :ead-)ime

Input ; mu/t 4e in *on/i/tent time unit/

!ime (day, eeF, mont, year) eeF

Orderin$6=etup %o&t '100.00 ?ean 00

*nit %o&t '10.00 =tandard deiation /0

oldin$ co&t (to option&) >

(i) in '& per year eeF


*nit oldin$ co&t per eeF '0.04 2

Output

eorder leel6point, 221.0 oldin$ co&t o &aety &tocF '1

Order quantity, Q 11/./ oldin$ co&t o normal &tocF '4

=aety &tocF 221.0 Orderin$6&etup co&t& '4)o%al co% per 6eek '4>


D4 E3 D10, P4, 1/

E10 ;9(E>BB,E46G10,E>CE/6G10)

G10 ;9(E3BdayB,/,;9(E3BeeFB,2,;9(E3BmontB,12,1)))

E11 ;9(E100,BEnter a alue in eiter cell E4 or E>B,BB)

D1 C4 D1

D13 =Q!(2CCE6E10)

D1 O*5D*8(5O?=;5N()C/C=Q!(4),0)

1 D1CE10

13 D13CE1062

1 ;9(D130,BB,EC6D13)

1/ =*?(1:1)

Demand i/ normall2;di/tri4uted

=erice Reel , =R

Read !ime, Rt

A B C D E F G H I J K

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

2526

27

28

29

30

31


16/36

Figure 8.1/ =erice-leel model it aria"le demand6aria"le lead-time.

Example 9.17 - A +erice-:eel Model 6i%# Fixed 0emand< ;aria"le :ead-)ime

Input ; mu/t 4e in *on/i/tent time unit/!ime (day, eeF, mont, year) eeF

Demand 00 ?ean

Orderin$6=etup %o&t '100.00 =tandard deiation 1

*nit %o&t '10.00 >

oldin$ co&t (to option&)

(i) in '& per year User input cells are shaded


*nit oldin$ co&t per eeF '0.04 2

Output Read time /./ eeF oldin$ co&t o normal &tocF '4

eorder leel6point, 22.3 Orderin$6&etup co&t& '4

Order quantity, Q 11/./ )o%al co% per 6eek '/


D13 5O?=;5N()C/

E13 E3

D1 ECD13

D1/ =Q!(2CECE/6E11)

13 D1/CE11621 E/CE6D1/

1/ =*?(13:1)

Lead;time i/ normall2;di/tri4uted

=erice Reel , =R

A B C D E F G H I J

1

2

34

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

23

24

25

26

27


17/36

Figure 8.1/ =erice-leel model it aria"le demand6aria"le lead-time.

K

1

2

34

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

23

24

25

26

27


18/36

Figure 8.14 8eriodic reie (i@ed-period) model.

Example 9. 11 - A !eriodic *eie6 =i.e. Fixed-!eriod> Model

Input ; mu/t 4e in *on/i/tent time unit/

!ime (day, eeF, mont, year) day ?ean 30Orderin$6=etup %o&t '0.00 =tandard deiation 1

*nit %o&t '10.00 >

oldin$ co&t (to option&) 4 day

(i) in '& per year '20.00 1/ day

(ii) a& o unit co&t /0

*nit oldin$ co&t per day '0.0 /

Output

eorder leel6point, 1041.0 oldin$ co&t o &aety &tocF '/./

Order quantity, Q 1021.0 oldin$ co&t o normal &tocF '2.>

=aety &tocF 121.0 Orderin$6&etup co&t& '1.>/

)o%al co% per da& '/./


D1 3C( 4) D1

D13 D1 - >

D1 O*5D*8(5O?=;5N(/)CC=Q!(4),0)

Demand i/ normall2;di/tri4uted

=erice Reel , =R

Read !ime, Rt

eie 8eriod

=tocF On-and

A B C D E F G H I J K

1

2

3

45

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23


19/36

Figure 8.1 ?ulti-period model it &eeral con&traint&.

Example 9.1( - A Mul%i-!eriod Model 6i%# +eeral 2on%rain%

Input All user input cells are shaded

Annual Demand ,/00

Orderin$ %o&t '.00 Output

*nit %o&t '2.00 EOQ 00.00

*nit oldin$ co&t per year (to option&) %ycle time

(i) in '& per year (in mont&) 1.0

(ii) a& o unit co&t 20.0 !otal co&t ',20

*nit oldin$ co&t per year '0.30

?ontly Order Endin$ %o&t per

?ont Demand Quantity ;nentory 8eriod1 230 ! 230 !


20/36


+oler !arame%er Set Tar+et Cell 92

Eual to ?in

B2 C3an+in+ Cell/ E13:E2

Su45e*t to Con/traint/ E13:E2 # 20 Quantity di&count con&traint

E13:E2


21/36


K

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29


22/36


K

30

3132

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50


23/36

Example 9.1/ - A +imula%ion Model for 5nen%or& 2on%rol

0emand %a"le :ead-%ime %a"le


24/36

Figure 8.(7 ?8 model or te Fitcen ta"le e@ample.

2ae +%ud& 9.1 - A Ma%erial *e$uiremen% !lanning =M*!> Model

T3e BO7 Ta4le

art Num4er De/*ription BO7 Id. No. o9 Lead On lanned

Le-el Code Unit/ Time ?and Order User input

!a"le 0 1 1 1 0 &el. &o@ cells are

!op A&&em"ly 1 1001 1 2 0 2 shaded

!a"le !op 2 2001 1 1 140

Draer 2 2002 1 1 200

Re$ A&&em"ly 1 1002 1 1 100 2

Re$& 2 200 3 1 20 /

=ide un$ 2 2003 2 1 0 /

%onnectin$ un$ 2 200 1 1 110 /

T3e 7& Output Ta4le

1

Ta4le Lead Time , 1

eeF 5um"er Oerdue 1 2 3 / 4

Ma%er !roduc%ion +c#edule 7 7 187 187 177 7 7 7

=ceduled eceipt& 0 0 0 0 0 0 0 0

On and 0 0 0 0 0 0 0 0

5et equirement& 0 0 10 140 100 0 0 0

8lanned Order eceipt& 0 0 10 140 100 0 0 0

8lanned Order elea&e& 0 0 10 140 100 0 0 0 0

2

Top A//em4l2 Lead Time , !


Gro&& equirement& 0 10 140 100 0 0 0 0

=ceduled eceipt& 0 177 0 0 0 0 0 0

On and 0 0 20 0 0 0 0 0

5et equirement& 0 0 1/0 100 0 0 0 0

8lanned Order eceipt& 0 0 1/0 100 0 0 0 0

8lanned Order elea&e& 0 1/0 100 0 0 0 0 0 0

Ta4le Top Lead Time , 1


Gro&& equirement& 1/0 100 0 0 0 0 0 0


On and 140 20 0 0 0 0 0 0

5et equirement& 0 40 0 0 0 0 0 0



A B C D E F G H I J K L

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

2526

27

28

29

30

31

32

33

34

35

3637

38

39

40

41

42

43

44

45

46


25/36


3

Dra@er Lead Time , 1


Gro&& equirement& 1/0 100 0 0 0 0 0 0=ceduled eceipt& 0 0 0 0 0 0 0 0

On and 200 30 0 0 0 0 0 0

5et equirement& 0 /0 0 0 0 0 0 0

8lanned Order eceipt& 0 /0 0 0 0 0 0 0

8lanned Order elea&e& 0 /0 0 0 0 0 0 0 0

Le+ A//em4l2 Lead Time , 1


Gro&& equirement& 0 10 140 100 0 0 0 0


On and 100 100 0 0 0 0 0 0

5et equirement& 0 0 140 100 0 0 0 0



/

Le+/ Lead Time , 1


Gro&& equirement& 120 20 300 0 0 0 0 0

=ceduled eceipt& 0 177 0 0 0 0 0 0On and 20 10 0 0 0 0 0 0

5et equirement& 0 3>0 300 0 0 0 0 0

8lanned Order eceipt& 0 3>0 300 0 0 0 0 0

8lanned Order elea&e& 0 3>0 300 0 0 0 0 0 0

Side &un+ Lead Time , 1


Gro&& equirement& /0 /0 200 0 0 0 0 0

=ceduled eceipt& 10 0 0 0 0 0 0 0 0

On and 0 0 0 0 0 0 0 0 5et equirement& 10 /0 200 0 0 0 0 0



4

Conne*tin+ &un+ Lead Time , 1


Gro&& equirement& 0 140 100 0 0 0 0 0


On and 110 40 0 0 0 0 0 0

5et equirement& 0 100 100 0 0 0 0 08lanned Order eceipt& 0 100 100 0 0 0 0 0


47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

7172

73

74

75

76

77

78

79

80

81

8283

84

85

86

87

88

89

90

91

92

93

94


26/36


8lanned Order elea&e& 0 100 100 0 0 0 0 0 0A B C D E F G H I J K L

95

96


27/36


M N

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

2526

27

28

29

30

31

32

33

34

35

3637

38

39

40

41

42

43

44

45

46


28/36


8a$e-"reaF M N

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

7172

73

74

75

76

77

78

79

80

81

8283

84

85

86

87

88

89

90

91

92

93

94


29/36


M N

95

96


30/36

Figure 8.(7 (cont.)

3

Dra@er Lead Time , 1


Gro&& equirement& 1/0 100 0 0 0 0 0 0


On and 200 30 0 0 0 0 0 0 5et equirement& 0 /0 0 0 0 0 0 0



Le+ A//em4l2 Lead Time , 1


Gro&& equirement& 0 10 140 100 0 0 0 0


On and 100 100 0 0 0 0 0 0

5et equirement& 0 0 140 100 0 0 0 0



/

Le+/ Lead Time , 1


Gro&& equirement& 120 20 300 0 0 0 0 0

=ceduled eceipt& 0 177 0 0 0 0 0 0

On and 20 10 0 0 0 0 0 0

5et equirement& 0 3>0 300 0 0 0 0 08lanned Order eceipt& 0 3>0 300 0 0 0 0 0

8lanned Order elea&e& 0 3>0 300 0 0 0 0 0 0

Side &un+ Lead Time , 1


Gro&& equirement& /0 /0 200 0 0 0 0 0

=ceduled eceipt& 10 0 0 0 0 0 0 0 0

On and 0 0 0 0 0 0 0 0

5et equirement& 10 /0 200 0 0 0 0 0

8lanned Order eceipt& 10 /0 200 0 0 0 0 08lanned Order elea&e& 10 /0 200 0 0 0 0 0 0

4

Conne*tin+ &un+ Lead Time , 1


Gro&& equirement& 0 140 100 0 0 0 0 0


On and 110 40 0 0 0 0 0 0

5et equirement& 0 100 100 0 0 0 0 0




47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

7374

75

76

77

78

79

80

81

82

83

8485

86

87

88

89

90

91

92

93

94

95

96


31/36

Figure 8.(1 ?odel or te part-period "alancin$ (8KK) metod.


Example 9. 14 - A Model for %#e !ar% !eriod Balancing =!!B> Me%#od

5npu% Orderin$ (or =etup) %o&t '200 User input cells*nit oldin$ co&t '1.00 are shaded

Economic part period (E88) 200

10 100 10 0 0 100 2 20

eriod" 1 ! $ 6 ( < 8 #

0 100 00 0 200 /00 1 1/0

0 100 300 300 /00 > 1 10 1>10

-1.0 -0. 1.0 1.0 2.0 .> /.> .4 4./0. 1.0 0. 1.0 1.0 2.0 .> /.> .4 4./

Order 0a%a ? 10 100

0 An/@er la*e an order 9or !(0 unit/ in period 1

0 0 1 2 3 /

-10 -100 0 0 100 22 300 12 120

0 0 0 0 100 2 2 40 >0

-1.0 -1.0 -1.0 -1.0 -0. 0./ 2./ . .>

0. 1.0 1.0 1.0 1.0 0. 0./ 2./ . .>

Order 0a%a ? 10 0 02 An/@er la*e an order 9or !00 unit/ in period $

0 0 0 0 0 1 2 3

-10 -100 -10 -1 -0 0 100 0 /0

0 0 0 0 0 0 100 10 210

-1.0 -1.0 -1.0 -1.0 -1.0 -1.0 -0. -0. 0.1

0.1 1.0 1.0 1.0 1.0 1.0 1.0 0. 0. 0.1

Order 0a%a ? 100 2 20

An/@er la*e an order 9or !!0 unit/ in period

0 0 0 0 0 0 0 0 0

%opy cell ran$e K11:R1 repeatedly don te &pread&eet, placin$ te cur&or in cell& K1>,


32/36

Figure 8.(1 ?odel or te part-period "alancin$ (8KK) metod.


A B C D E F G H I J K L M

38


33/36

8roduct 5o. in 8roduct 7ear %a&

name &tocF price lo

Gi+mo 10 '10.00 1 1

Gad$et 2 '12.0 2 3

id$et 4 '20.00 4

=procFet 30 '3.0 3 1/

Sample Fi+ure

A B C D E F G H

1

2

3

4

5

6

78

9


34/36

%olumn(K) 2 o(K)

%olumn(D:D>) 3 o(D:D>)

;5DE(K3:D,2,) '12.0 5O?=;5N(0.>)

A B C D E F G H

10

11

12

13

14


35/36

I

1

2

3

4

5

6

78

9


36/36

1./33>

I

10

11

12

13

14

the more you read the more you learn

Documents