Question

Below is a table of sales

Boxes	week
11	1
12	2
3	3
5	4
8	5
7	6
4	7
6	8
10	9
15	10
17	11
20	12
25	13
30	14
25	15

Unfortunately, every week, the company ran out of boxes and estimate typical lost sales of 10 to 50 percent of boxes sold.

As they make $100 per box, this is a considerable inconvenience. They are studying what to recommend as an inventory

level for next year. Explain your recommendation and what makes it acceptable.

Answer 1

EXCEL FORMULAS:

For Average =AVERAGE(B2:B16)

For Std Dev =STDEV(B2:B16)

Average weekly sales, d = 13.2

Standard deviation of weekly sales, s = 8.51

If they accept 10% lost sales, then service level = 90%

z value = NORMSINV(.9) = 1.2816

Safety stock = z*s = 1.2816*8.51 = 11 boxes

Average lost sales = (10+50)/2 = 30%

Recommended inventory level for the next year = Average weekly sales * 52 weeks per year * ( 1+30%)

= 13.2*52*1.3

= 892 boxes

Safety stock = 11 boxes