Question

Mr. Min, a company that sells machine sets, has an ordering cost of $40 for the BB-1 set. (BB-1 stands for Body Beautiful Number 1). The carrying cost for BB-1 is $5 per set per year. To meet demand, Mr. Beautiful orders large quantities of BB-1 seven times a year. The stockout cost for BB-1 is estimated to be $50 per set. Over the past several years, Mr. Beautiful has observed the following demand during the lead time for BB-1:

Demand During Lead Time	Probability
40	0.1
50	0.2
60	0.2
70	0.2
80	0.2
90	0.1

The reorder point for BB-1 is 60 sets. What level of safety stock should be maintained for BB-1? Solve In excel.

Answer 1

Reorder point	60
Annual carrying cost	5
Stockout cost	50
Orders per year	7
Probability	0.1	0.2	0.2	0.2	0.2	0.1	Expected Cost
SS \ Demand	40	50	60	70	80	90
0	0	0	0	3500	7000	10500	3150
10	50	50	50	50	3550	7050	1450
20	100	100	100	100	100	3600	450
30	150	150	150	150	150	150	150	(Min)
40	200	200	200	200	200	200	200
50	250	250	250	250	250	250	250

Safety stock = 30

Note: the hidden columns are to be copy-pasted from the earlier column.