1
1.
Excel Worksheet Ex 12-1 illustrates these computations.
2. Excel Worksheet Ex 12-2 illustrates these computations.
2
3. Excel Worksheet Ex 12-3 illustrates these computations
DL = LD = (2)(300) = 600
L
=
D
L
=
)200(2
= 283
ss = FS
−1 (CSL)
L = FS
−1 (0.95) 283 = 465 (where, FS
−1 (0.95) = NORMSINV (0.95))
ROP = DL + ss = 600 + 465 = 1,065
4. Excel Worksheet Ex 12-4 illustrates these computations
DT+L = (T+L) D = (2+3)(300) = 1,500
TL
+
=
D
LT +
=
)200(32 +
= 447
ss = FS
−1 (CSL)
L = FS
−1 (0.95) 447 = 736 (where, FS
−1 (0.95) = NORMSINV (0.95))
OUL = D(T+L) + ss = 1,500 + 736 = 2,236
5. Excel Worksheet Ex 12-5 illustrates these computations.
DL = LD = (2)(300) = 600
L
=
D
L
=
)200(2
= 283
ESC = (1 − fr)Q = (1 − 0.99)×500 = 5
We use the following expression to determine the safety stock (ss):
ESC = −ss[1 − NORMDIST(ss/
L, 0, 1, 1)] + L NORMDIST(ss/
L, 0, 1, 0).
We utilize the GOALSEEK function in EXCEL in determining safety stock (ss) by using ss as the
changing value that results in an ESC value of 5.
Goal Seek set-up:
)()](1[
L
ss
6. Excel Worksheet Ex 12-6 illustrates these computations.
DL = LD = (2)(250) = 500
L
=
D
L
=
)150(2
= 212
ss = ROP − ss = 600 − 500 = 100 (Cell C24)
CSL = F(DL + ss, DL,
L) = F(600, 500, 212) = NORMDIST (600, 500, 212, 1) = 0.68 (Cell C25)
ESC = −ss[1 – NORMDIST(ss/
L, 0, 1, 1)] + L NORMDIST(ss/
L, 0, 1, 0) = 43.86 (Cell C26)
Fill rate (fr) = 1 − (ESC/Q) = 1 − (43.86/1000) = 0.96 (Cell C27)
7. Excel Worksheet Ex 12-7 illustrates these computations.
DL = LD = (2)(250) = 500
2
2+
2 2 2
4
8. Excel Worksheet Ex 12-8 illustrates these computations.
9. Excel Worksheet Ex 12-9 illustrates these computations.
GOALSEEK is used to obtain the above results with
5
10. Excel Worksheet Ex 12–10 illustrates these computations.
6
11. Excel worksheet Ex 12–11 illustrates these computations.
7
12. Excel worksheet Ex 12–12 illustrates these computations.
0.5).
8
13. Excel worksheet Ex 12–13 illustrates these computations.
Offering the printer online reduces the required safety inventory by 23,685 if the correlation
coefficient is 0.
9
14. Excel Worksheet Ex 12–14 illustrates these computations. Following are the evaluations for
the Khaki pants:
ss per store = FS
L = FS
(0.95))
Aggregated Option:
kD
DC=
= (900)(800) = 720000
k
C
D=
=
)100(900
= 3000
DL = LDC = (4)(800)(900) = 2,880,000
L
=
C
D
L
=
)3000(4
= 6000
ss = FS
−1 (CSL)
L = FS
−1 (0.95) 6,000 = 9,869 (where, FS
−1 (0.95) = NORMSINV (0.95))
Total safety inventory = 9,869 (Cell C35)
Total value of safety inventory = (9,869)(30) = $296,070
Total annual safety inventory holding cost = (296,070 )(0.25) = $74,018
Holding cost per unit sold = 74,018/(800)(900) = $0.1
Savings in the holding cost per unit sold from aggregation = $3.08 − $0.1 = $2.98 (Cell C44)
10
ss per store = FS
L = FS
(0.95)) (Cell E21)
Note: the above are a bit different from the worksheet because of rounding.
Aggregated Option:
kD
DC=
= (900)(50) = 45000
k
C
D=
=
)50(900
= 1500
DL = LDC = (4)(50)(900) = 180000
L
=
C
D
L
=
)1500(4
= 3000
ss = FS
−1 (CSL)
L = FS
−1 (0.95) 3000 = 4935 (where, FS
−1 (0.95) = NORMSINV (0.95))
Total safety inventory = 4,935 (Cell E35)
Total value of safety inventory = (4935)(100) = $493,456
Total annual safety inventory holding cost = (493456)(0.25) = $123,364
Holding cost per unit sold = 123364/(50)(900) = $2.74
Savings in the holding cost per unit sold from aggregation = $82.84 − $2.74 = $79.50 (Cell E44)
Copyright © 2019 Pearson Education, Inc.
11
Centralization results in savings for both products, but it is evident that savings in holding cost
per unit sold from aggregating Cashmere Sweaters is higher than Khaki pants. Therefore,
Cashmere Sweaters are better for centralization.
15. Excel Worksheet Ex 12–15 illustrates these computations.
Disaggregated Option:
France:
DL = LD = (8)(3,000) = 24,000
L
=
D
L
=
8
× 2,000 = 5,657
−1 (CSL)
−1 (0.95) 5657 = 9,305
L
=
C
D
L
=
)22.4445(8
= 12,573 (Cell C26)
−1 (CSL)
−1 (0.95) 12573 = 20,681 (where, FS
−1 (0.95) = NORMSINV (0.95))