Question

5-13. EXCEL APPLICATION: Performance Bicycle Parts Ted Stevens owns an Internet-based bicycle accessories web- site that sells bicycle tires, tubes, chains, sprockets, and seats as well as helmets and water bottles. The bicycle parts aftermar- ket is very competitive, and Ted realizes that having both a low price and sufficient inventory to offer same-day shipping are critical to his success. He has a global supply chain and relies on many diferent supplier sources for the quality products his customers demand. Ted sells more replacement tubes than any other product. For this item, customers expect high quality at a competitive price. Ted spent several months evaluating the quality and performance of six potential suppliers for the most popular replacement tube, the 29" × 1.85"-2.20" presta tube. These suppliers manufacture replacement tubes of comparable quality and performance. With the right price, quality, and availability Ted expects to sell an average of 12,000 tubes per month, or 400 tubes per day, for $6.50 each. However, he is concerned about the amount of cash or working capital required to support the level of inventory he needs to provide same-day shipping Using the information provided in Figure 5-22, createa spreadsheet to analyze the replacement tube cost structure for six potential suppliers. Per unit import duty cost equals the import duty rate multiplied by the sum of per unit base cost and the per unit shipping cost. The per unit warehouse cost is the sum of the per unit base cost and the per unit shipping cost and the per unit import duty cost. The per unit total cost is the sum of the per unit warehouse cost and the average per unit carrying cost. Required inventory levels are based on projected daily sales times the number of shipping days required for delivery from the supplier to Ted's warehouse. A longer delivery time requires Ted to maintain a higher level of inventory. Thus, he wants to include inventory carrying costs in the analysis. Ted maintains

5-13. EXCEL APPLICATION:

Performance Bicycle Parts Ted Stevens owns an Internet-based bicycle accessories website that sells bicycle tires, tubes, chains, sprockets, and seats as well as helmets and water bottles. The bicycle parts aftermarket is very competitive, and Ted realizes that having both a low price and sufficient inventory to offer same-day shipping are critical to his success. He has a global supply chain and relies on many different supplier sources for the quality products his customers demand. Ted sells more replacement tubes than any other product.
For this item, customers expect high quality at a competitive price. Ted spent several months evaluating the quality and performance of six potential suppliers for the most popular replacement tube, the 29” × 1.85”−2.20” presta tube. These suppliers manufacture replacement tubes of comparable quality and performance. With the right price, quality, and availability, Ted expects to sell an average of 12,000 tubes per month, or 400 tubes per day, for $6.50 each. However, he is concerned about the amount of cash or working capital required to support the level of inventory he needs to provide same-day shipping. Using the information provided in Figure 5-22, create a spreadsheet to analyze the replacement tube cost structure for six potential suppliers. Per unit import duty cost equals the import duty rate multiplied by the sum of per unit base cost and the per unit shipping cost. The per unit warehouse cost is the sum of the per unit base cost and the per unit shipping cost and the per unit import duty cost. The per unit total cost is the sum of the per unit warehouse cost and the average per unit carrying cost. Required inventory levels are based on projected daily sales times the number of shipping days required for delivery from the supplier to Ted’s warehouse. A longer delivery time requires Ted to maintain a higher level of inventory. Thus, he wants to include inventory carrying costs in the analysis. Ted maintains

average inventory (units) based on 150% of projected daily sales multiplied by the number of shipping days from the sup-plier. The average inventory value equals the per unit delivered cost multiplied by the average inventory (units). Inventory carrying costs include the cost of putting away
stock and moving material within the warehouse, rent and utili-ties for warehouse space, insurance and taxes on inventory, and inventory shrinkage. Ted calculates his total inventory carry-ing costs at 24% of the average inventory value. The average per unit carrying cost equals the total inventory carrying cost divided by the total number of units sold per year (144,000). Which supplier source requires the highest investment of
working capital or cash for average inventory? Which supplier source provides Ted with the highest percentage of gross profit on the presta replacement tube?

Answer 1

	A	B	C	D	E=(B+C)*D	F=B+C+E	G=400*1.5*A	H=G*F	I=H*24%	J=I/144000	K=F+J	L=6.5-K	M=L/6.5*100
Supplier	Shipping days	Per unit base cost	Per unit shipping cost	Import duty	Per unit import duty cost	per unit warehouse cost	Average inventory (units)	Average inventory value	Total inventory carrying value	Average per unit carrying cost	Per unit total cost	Gross profit	Percent gross profit
United States	4	3.9	0.25	0%	0.00	4.15	2,400	9,960	2,390	0.02	4.17	2.33	36%
South Korea	120	1.7	0.8	4%	0.10	2.60	72,000	187,200	44,928	0.31	2.91	3.59	55%
India	160	1.6	1.1	3%	0.08	2.78	96,000	266,976	64,074	0.44	3.23	3.27	50%
Russia	140	1.35	0.95	6%	0.14	2.44	84,000	204,792	49,150	0.34	2.78	3.72	57%
Vietnam	100	1.55	0.75	4%	0.09	2.39	60,000	143,520	34,445	0.24	2.63	3.87	60%
China	110	1.65	0.85	5%	0.13	2.63	66,000	173,250	41,580	0.29	2.91	3.59	55%
Calculations for per unit are reduced to two decimal points in excel computations

Highest working capital requirement will be there with supplier from India (Column H)

Supplier from Vietnam will provide highest margin (Column M)