Sourcing of a new sub-component Your company, based near Wilmington, N.C., needs to source a new sub-component that will be a key element in a new product that is due to start production next year. Your boss contacted three producers of the sub-component and obtained the cost information below. Your boss has asked you to provide a detailed spreadsheet of the cost and provide a recommendation on which firm would be able to provide the sub-component at the best price. She needs to brief the CEO next week. Since your firm has worked with all three of the producers before, and all have a good record of providing sub-components, the decision will be solely based on the lowest cost. Additional Shipping Information 10,000 units of the sub-component will fit into a 40-foot shipping container, so to keep the shipping cost down; all three firms will ship full containers each time an order is placed. Details on the Producer Firms Producer A Producer A is located in Nashville, Tennessee, and can provide the sub-component for $3.25 each. The producers warehouse is 600 miles from your production facility, and truck transportation will cost $1.25 per mile. There is a container-handling fee of 1% of the total product cost that is assessed by the shipping company, as well as a required $200 insurance policy. Producer B Producer B is located in Europe and can provide the sub-component for $2.00 each. The producers warehouse is 100 KMs from the port and the truck transportation will be $1.50 per kilometers. The trucking cost from the United States port to your production facility is 35 miles and the transportation will cost $1.25/mile. From Europe to the United States, the sea shipping will run $2,000 with a handling fee of 1.25% of the total product cost. Since the sub-component must be imported into the United States, there is a 10% import duty assessed on the total cost of the product. Finally, there will be a fee of $850 for sea and surface transportation insurance. Producer C Producer C is located in Asia, the furthest away from your facility however; their cost per unit is the lowest at $1.50. The factory for Producer C is located 150 kilometers from the closest port and costs $0.20 per KM to move the container to the port. Sea shipping runs $2,500, with a handling cost of 1.25% of the total cost of the product. As with Producer B, once the container reaches the United States, it will be assessed import duties of 10%. Final transportation to your facility will run $43.75. Finally, there will be a fee of $900 for sea and surface transportation insurance. Considerations Additional information to Consider While both Producer B and C have a lower cost per unit, they have a higher shipping cost. Also, since both Producer B and C are so far away, and delivery time is uncertain, then your firm will want to hold $3,000 worth of safety stock. Finally, part of the reason that Producers B and C sub-components are cheaper is that some additional work will be needed on their components once they are received. Your production office has determined that it will cost $.50 each to finish off the sub-component for Production B and $1.10 each to finish the sub-component from Producer C. Deliverables Include the following in your submission Provide a professional looking detailed spreadsheet with all of the costs outlined.