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.852.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 Teds 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?

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 FIGURE 5-22 Suppliers for Performance Bicycle Parts. Performance Bicycle Partsadx-Excel FILE HOME INSERT PAGE LAYOUT FORMULASDATA REVIEW VEW cut Wrap Tet PesteCopy B 1 !Merge & Center. $. % , 4% conditional Form etas' ell Format PainterBI Formatting Table Styles Styles Total Unit Per Unit Per Unit Per Unit Average Average Inventory Per Unit Per Unit Gross Gross Cost Profit Profit ShippingBase Shipping Import Import Inventory Inventory Carrying | Supplier | Days | Cost Cost 3.90 $ 0.25 20$1.70$0.80 60 $ 1.60$ 1.10 40 $ 1.35 $ 0.95 O| $ 1.55 | $ 0.75 $ 1.65 $ 0.85 |Duty %|Duty Cost| Cost (Units) | Value Cost 2 United States 3 South Korea India 5 Russia 6 Vietnam 7 China