Assignment:
In the previous problem, we learned about True Titletown Sports (TTS), a wholesaler of sport equipment. TTS has been notified by its main supplier, Northeast Athletics, that it will close its warehouse in New York and will now serve all its orders from a global distribution center located in Shanghai, China. Including the transit time, the leadtime for the orders placed by TTS will now be 1 month.
As we saw in the previous problem, True Titletown Sports has determined that the cost to place an order is $5, and the holding charge is 20% of the product cost per year. At your request, the good fellows from the Logistics department have provided you the annual standard deviation of the demand of each SKU, and confirmed that it is normally distributed. Attached excel. Please enter all your numerical answers with 4 significant figures.
1) Calculate the economic order quantity for SKU 1A. What is the value of Q*? Since orders can only be placed for whole units, you must round your Q* to the nearest integer.
2) For SKU 1A, what is the standard deviation of the demand, si, in units per year? Give your answer with one decimal.
3) The lead time is one month. Assume there are 12 months in a year. For SKU 1A, what is the standard deviation of the demand over the lead time period, sDL?
4) For SKU 1A, what is the value of (sDL)(ci)?
5) For SKU 1A, what is the value of DQ?
6) For SKU 1A, what is the value of DQ(sDL)(ci)?
7) Now calculate these values for each SKU, and calculate their sums.
8) What is the sum of all the (sDL)(ci) values?
9) What is the sum of all the DQ values?
10) What is the sum of all the DQ(sDL)(ci) values? Round to the nearest multiple of a $1000.
11) For a customer service level (CSL) value of 99.99%, calculate the corresponding value of k:
12) For a customer service level (CSL) value of 80%, calculate the corresponding value of G(k):
13) For a CSL of 90%, calculate the value of the total safety stock (TSS), rounded to the nearest multiple of $100.
14) For a CSL of 90%, calculate the total value of inventory short (TVIS), rounded to the nearest multiple of $500.
15) For a TSS budget of $100,000, what is the recommended value of k?
16) For a TSS budget of $100,000, what is the value of TVIS? Round to the nearest multiple of $100.
Lead Time= 1 Month C_t= $5 h=20%
| SKU ID |
Annual Sales |
Unit Cost |
RMSE (annual) |
| 1A |
576 |
$ 55,00 |
49,2 |
| 1B |
60 |
$ 30,00 |
3,8 |
| 1C |
2520 |
$ 5,12 |
153,9 |
| 1D |
324 |
$ 7,07 |
35,6 |
| 1E |
120 |
$ 37,05 |
43,4 |
| 1F |
144 |
$ 86,50 |
38,2 |
| 1G |
576 |
$ 14,66 |
156,1 |
| 1H |
1404 |
$ 49,92 |
317,5 |
| 1I |
144 |
$ 47,50 |
36,5 |
| 1J |
1128 |
$ 31,24 |
238,4 |
| 1K |
48 |
$ 84,03 |
7,7 |
| 1L |
84 |
$ 65,00 |
32,3 |
| 1M |
24 |
$ 51,68 |
6,2 |
| 1N |
48 |
$ 56,00 |
16,5 |
| 1O |
144 |
$ 49,50 |
43,6 |
| 1P |
24 |
$ 59,60 |
5,7 |
| 1Q |
1200 |
$ 28,20 |
309,6 |
| 1R |
24 |
$ 29,89 |
5,5 |
| 1S |
60 |
$ 86,50 |
14,6 |
| 1T |
720 |
$ 58,88 |
244,9 |
| 1U |
96 |
$ 58,45 |
34,6 |
| A |
2064 |
$ 27,73 |
506,9 |
| B |
96 |
$ 110,40 |
32,2 |
| C |
36 |
$ 60,60 |
7,2 |
| D |
48 |
$ 19,80 |
7,9 |
| E |
24 |
$ 134,34 |
4,9 |
| F |
180 |
$ 160,50 |
23,8 |
| G |
48 |
$ 49,48 |
7,5 |
| H |
36 |
$ 8,46 |
11,2 |
| I |
48 |
$ 40,82 |
16,3 |
| J |
12 |
$ 34,40 |
4,2 |
| K |
2544 |
$ 23,76 |
982,1 |
| L |
48 |
$ 53,02 |
7,3 |
| M |
144 |
$ 71,20 |
49,1 |
| N |
24 |
$ 67,40 |
8,2 |
| O |
24 |
$ 37,70 |
8,8 |
| P |
12 |
$ 28,80 |
4,4 |
| Q |
48 |
$ 78,40 |
15,1 |
| R |
144 |
$ 33,20 |
19,4 |
| S |
36 |
$ 72,00 |
5,1 |
| T |
216 |
$ 45,00 |
53,5 |
| U |
600 |
$ 20,87 |
159,0 |
| V |
228 |
$ 24,40 |
50,0 |
| W |
12 |
$ 48,30 |
1,6 |
| X |
120 |
$ 33,84 |
48,3 |
| Y |
324 |
$ 210,00 |
49,2 |
| Z |
396 |
$ 73,44 |
80,3 |
| AA |
12 |
$ 28,80 |
4,4 |
| AB |
48 |
$ 78,40 |
15,1 |
| AC |
144 |
$ 33,20 |
19,4 |