Assignment:
You have been hired as Supply Manager by True Titletown Sports (TTS), a leading wholesaler of sports equipment based in Boston. TTS has a single warehouse located in Jamaica Plain (just outside of Boston), from which it sells baseball bats, hockey sticks, football helmets and thousands of other products to retailers across Massachusetts. One of the main suppliers of TTS is Northeast Athletics, a large distributor of sporting goods based in New York. TTS places orders to its suppliers almost every day. In the case of orders placed to Northeast before 4PM on a given day, the goods are shipped early the next business day from New York and usually arrive at TTS's warehouse in Jamaica Plain later that afternoon.
Although True Titletown Sports are pretty sure that placing each order costs about the same, the company is not sure about how much this cost is exactly. After pestering your colleagues at Sales for weeks, the best guess you have obtained is that the cost of placing an order should be "somewhere between $10 and $150" per order. You didn't have much better luck when you spoke with the Finance department to ask about the estimated cost of carrying inventory: the best they could tell you was "It depends!" They insist that the cost is equal for all products, but they could not give you an exact figure. "Depending on what you include, it is between 5% and 25% of the product cost per year."
You were very relieved to find out that the guys at Logistics could provide you - at least - with some firm data: they gave you the annual demand and unit cost of the fifty class B items (SKUs) that TTS buys from Northeast Athletics, in a spreadsheet
1) Identify in the spreadsheet the line for SKU 1A. What is the demand for SKU 1A, Di, in units?
2) What is the cost per unit for SKU 1A, ci in dollars?
3) For SKU 1A, what is the value of (Di)(ci)?
4) For SKU 1A, what is the value of (Di)(ci)?
5) Calculate the value of (Di)(ci) for each SKU.
6) What is the sum of all these values? Round to the nearest integer.
7) Based on the sum of all the (Di)(ci) values, you set out to build an exchange curve for the cycle stock. You know that the company is currently carrying $50,000 worth of cycle stock for products from that list, and making around 300 replenishments from Northeast Athletics per year. TTS's VP of Supply Chain suspects that the number of replenishments could be reduced, and has asked you to look into the matter.Maintaining the same budget of $50,000 for cycle stock from this supplier, how many replenishments per year would you recommend? Give your answer rounded to the nearest multiple of 10.
8) After much pestering on your part, and some additional pressure from the top, you have finally received more accurate figures for the holding charge and the ordering cost. The holding charge, you are told, has been put at 20% of the product cost per year, and the ordering cost at $40 per order. With this new information, how many replenishments would you recommend per year?
9) You have explained to your boss that, for the given value of cth=200, the recommended budget would be $45,000. He asks you: What would be the value of the cth fraction that would justify a $50,000 budget? Round to the closest integer.
10) After you present your calculations in a meeting with the VPs of Sales, Finance and Supply Chain, they agree that before a decision is made, the company should make an effort to better calculate the values of ct and h . A team is created to conduct the calculation. After a week of work, they come to you with a revised value for ct=$80 . The holding charge stays the same. For the revised value of cth=400 , how many replenishments should TTS do from Northeast Athletics every year? Round your answer to the nearest integer.
11) For the revised value of cth=400, how much should TTS set aside as a budget for the cycle stock for products from Northeast Athletics? Round your answer to the nearest multiple of $100.
| SKU ID |
Annual Sales |
Unit Cost |
| 1A |
576 |
$ 55,00 |
| 1B |
60 |
$ 30,00 |
| 1C |
2520 |
$ 5,12 |
| 1D |
324 |
$ 7,07 |
| 1E |
120 |
$ 37,05 |
| 1F |
144 |
$ 86,50 |
| 1G |
576 |
$ 14,66 |
| 1H |
1404 |
$ 49,92 |
| 1I |
144 |
$ 47,50 |
| 1J |
1128 |
$ 31,24 |
| 1K |
48 |
$ 84,03 |
| 1L |
84 |
$ 65,00 |
| 1M |
24 |
$ 51,68 |
| 1N |
48 |
$ 56,00 |
| 1O |
144 |
$ 49,50 |
| 1P |
24 |
$ 59,60 |
| 1Q |
1200 |
$ 28,20 |
| 1R |
24 |
$ 29,89 |
| 1S |
60 |
$ 86,50 |
| 1T |
720 |
$ 58,88 |
| 1U |
96 |
$ 58,45 |
| A |
2064 |
$ 27,73 |
| B |
96 |
$ 110,40 |
| C |
36 |
$ 60,60 |
| D |
48 |
$ 19,80 |
| E |
24 |
$ 134,34 |
| F |
180 |
$ 160,50 |
| G |
48 |
$ 49,48 |
| H |
36 |
$ 8,46 |
| I |
48 |
$ 40,82 |
| J |
12 |
$ 34,40 |
| K |
2544 |
$ 23,76 |
| L |
48 |
$ 53,02 |
| M |
144 |
$ 71,20 |
| N |
24 |
$ 67,40 |
| O |
24 |
$ 37,70 |
| P |
12 |
$ 28,80 |
| Q |
48 |
$ 78,40 |
| R |
144 |
$ 33,20 |
| S |
36 |
$ 72,00 |
| T |
216 |
$ 45,00 |
| U |
600 |
$ 20,87 |
| V |
228 |
$ 24,40 |
| W |
12 |
$ 48,30 |
| X |
120 |
$ 33,84 |
| Y |
324 |
$ 210,00 |
| Z |
396 |
$ 73,44 |
| AA |
12 |
$ 28,80 |
| AB |
48 |
$ 78,40 |
| AC |
144 |
$ 33,20 |