Question: 513 excel application performance bicycle parts ted stevens owns an...
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?