please complete the following problems/excel sheet based on information provided

Global Procurement Decision Sheila Austin, a buyer at Autolink, a Detroit-based producer of subassemblies for the automotive market, has sent out requests for quotations for a wiring harness to four prospective suppliers. Only two of the four suppliers indicated an interest in quoting the business: Original Wire (Auburn Hills, MI) and Happy Lucky Assemblies (HLA) of Guangdong Province, China. The estimated demand for the harnesses is 5,000 units a month. Both suppliers will incur some costs to retool for this particular hamess. The harnesses will be prepackaged in 24126-inch cartons. Each packaged unit weighs approximately 10 pounds. Quote 1 The first quote received is from Original Wire. Auburn Hills is about 20 miles from Autolink's corporate headquarters, so the quote was delivered in person. When Sheila went down to the lobby, she was greeted by the sales agent and an engineering representative. After the quote was handed over, the sales agent noted that engineering would be happy to work elosely with Autolink in developing the unit and would also be interested in future business that might involve finding ways to reduce costs. The sales agent also noted that they were hungry for business, as they were losing a lot of customers to companies from China. The quote included unit price, tooling, and packaging. The quoted unit price does not include shipping costs. Original Wire requires no special warehousing of inventory, and daily deliveries from its manufacturing site directly to Autolink's assembly operations are possible. Original Wire Quote: - Unit price =$30 - Packing costs =$0.75 per unit - Tooling $6,000 one-time fixed charge - Freight cost $5.20 per hundred pounds Quote 2 The second quote received is from Happy Lucky Assemblies of Guangdong Province, China. The supplier must pack the harnesses in a container and ship via inland transportation to the port of Shanghai in China, have the shipment transferred to a container ship, ship material to Seattle, and then have material transported inland to Detroit. The quoted unit price does not include international shipping costs, which the buyer will assume. Quote 2 The second quote received is from Happy Lucky Assemblies of Guangdong Province, China. The supplier must pack the harnesses in a container and ship via inland transportation to the port of Shanghai in China, have the shipment transferred to a container ship, ship material to Seattle, and then have material transported inland to Detroit. The quoted unit price does not include international shipping costs, which the buyer will assume. HLA Quote: - Unit price =$19.50 - Shipping lead time = Eight weeks - Tooling =$3,000 In addition to the supplier's quote, Sheila must consider additional costs and information before preparing a comparison of the Chinese supplier's quotation: - Each monthly shipment requires three 40-foot containers. - Packing costs for containerization =$2 per unit. - Cost of inland transportation to port of export =$200 per container. - Freight forwarder's fee =$100 per shipment (letter of credit, documentation, etc.) - Cost of ocean transport =$4,000 per container. This has risen significantly in recent years due to a shortage of ocean freight capacity. - Marine insurance =$0.50 per $100 of shipment. - U.S. port handling charges =$1,200 per container. This fee has also risen considerably this year, due to increased security. Ports have also been complaining that the charges may increase in the future. - Customs duty =5% of unit cost. - Customs broker fees per shipment =$300. - Transportation from Seattle to Detroit = $18.60 per hundred pounds. - Need to warehouse at least four weeks of inventory in Detroit at a warehousing cost of $1.00 per cubic foot per month, to compensate for lead time uncertainty. Sheila must also figure the costs associated with committing corporate capital for holding inventory. She has spoken to some accountants, who typically use a corporate cost of capital rate of 15% - Cost of hedging currency-broker fees =$400 per shipment - Additional administrative time due to international shipping = 4 hours per shipment x$25 per hour (estimated) - At least two five-day visits per year to travel to China to meet with supplier and provide updates on performance and shipping =$20,000 per year ( estimated ) - Customs broker fees per shipment =$300. - Transportation from Seattle to Detroit =$18.60 per hundred pounds. - Need to warehouse at least four weeks of inventory in Detroit at a warehousing cost of $1.00 per cubic foot per month, to compensate for lead time uncertainty. Sheila must also figure the costs associated with committing corporate capital for holding inventory. She has spoken to some accountants, who typically use a corporate cost of capital rate of 15% - Cost of hedging currency-broker fees =$400 per shipment - Additional administrative time due to international shipping = 4 hours per shipment x$25 per hour (estimated) - At least two five-day visits per year to travel to China to meet with supplier and provide updates on performance and shipping =$20,000 per year ( estimated) The international sourcing costs must be absorbed by Sheila, as the supplier does not assume any of the additional estimated costs and invoice Sheila later or build the costs into a revised unit price. Sheila feels that the U.S. supplier is probably less expensive, even though it quoted a higher price. Sheila also knows that this is a standard technology that is unlikely to change during the next three years, but which could be a contract that extends multiple years out. There is also a lot of "hall talk" amongst the engineers on her floor about next generation automotive electronics, which will completely eliminate the need for wire hamesses, which will be replaced by electronic components that are smaller, lighter, and more reliable. She is unsure about how to calculate the total costs for each option, and she is even more unsure about how to factor these other variables into the decision. ASSIGNMENT 1. Calculate the total cost per unit of purchasing from Original Wire. 2. Calculate the total cost per unit of purchasing from Happy Lucky Assemblies. 3. Based on the total cost per unit, which supplier should Sheila recommend? 4. Are there any other issues besides cost that Sheila should evaluate? 5. Based on this case, do you think international purchasing is more or less complex than domestic purchasing? Why? Is it worth the additional effort? Comparison of total cost of purchasing: domestic vs. international sources Global Procurement Decision Sheila Austin, a buyer at Autolink, a Detroit-based producer of subassemblies for the automotive market, has sent out requests for quotations for a wiring harness to four prospective suppliers. Only two of the four suppliers indicated an interest in quoting the business: Original Wire (Auburn Hills, MI) and Happy Lucky Assemblies (HLA) of Guangdong Province, China. The estimated demand for the hamesses is 5,000 units a month. Both suppliers will incur some costs to retool for this particular harness. The harnesses will be prepackaged in 24126-inch cartons. Each packaged unit weighs approximately 10 pounds. Quote 1 The first quote received is from Original Wire. Auburn Hills is about 20 miles from Autolink's corporate headquarters, so the quote was delivered in person. When Sheila went down to the lobby, she was greeted by the sales agent and an engineering representative. After the quote was handed over, the sales agent noted that engineering would be happy to work closely with Autolink in developing the unit and would also be interested in future business that might involve finding ways to reduce costs. The sales agent also noted that they were hungry for business, as they were losing a lot of customers to companies from China. The quote included unit price, tooling, and packaging. The quoted unit price does not include shipping costs. Original Wire requires no special warehousing of inventory, and daily deliveries from its manufacturing site directly to Autolink's assembly operations are possible. Original Wire Quote: - Unit price =$30 - Packing costs =$0.75 per unit - Tooling $6,000 one-time fixed charge - Freight cost $5.20 per hundred pounds Quote 2 The second quote received is from Happy Lucky Assemblies of Guangdong Province, China. The supplier must pack the harnesses in a container and ship via inland transportation to the port of Shanghai in China, have the shipment transferred to a container ship, ship material to Seattle, and then have material transported inland to Detroit. The quoted unit price does not include international shipping costs, which the buyer will assume. Quote 2 The second quote received is from Happy Lucky Assemblies of Guangdong Province, China. The supplier must pack the harnesses in a container and ship via inland transportation to the port of Shanghai in China, have the shipment transferred to a container ship, ship material to Seattle, and then have material transported inland to Detroit. The quoted unit price does not include international shipping costs, which the buyer will assume. HLA Quote: - Unit price =$19.50 - Shipping lead time = Eight weeks - Tooling =$3,000 In addition to the supplier's quote, Sheila must consider additional costs and information before preparing a comparison of the Chinese supplier's quotation: - Each monthly shipment requires three 40-foot containers. - Packing costs for containerization =$2 per unit. - Cost of inland transportation to port of export =$200 per container. - Freight forwarder's fee =$100 per shipment (letter of credit, documentation, etc.) - Cost of ocean transport =$4,000 per container. This has risen significantly in recent years due to a shortage of ocean freight capacity. - Marine insurance =$0.50 per $100 of shipment. - U.S. port handling charges =$1,200 per container. This fee has also risen considerably this year, due to increased security. Ports have also been complaining that the charges may increase in the future. - Customs duty =5% of unit cost. - Customs broker fees per shipment =$300. - Transportation from Seattle to Detroit =$18.60 per hundred pounds. - Need to warehouse at least four weeks of inventory in Detroit at a warehousing cost of $1.00 per cubic foot per month, to compensate for lead time uncertainty. Sheila must also figure the costs associated with committing corporate capital for holding inventory. She has spoken to some accountants, who typically use a corporate cost of capital rate of 15% - Cost of hedging currency-broker fees =$400 per shipment - Additional administrative time due to international shipping = 4 hours per shipment x$25 per hour (estimated) - At least two five-day visits per year to travel to China to meet with supplier and provide updates on performance and shipping =$20,000 per year (estimated) - Customs broker fees per shipment =$300. - Transportation from Seattle to Detroit = $18.60 per hundred pounds. - Need to warehouse at least four weeks of inventory in Detroit at a warehousing cost of $1.00 per cubic foot per month, to compensate for lead time uncertainty. Sheila must also figure the costs associated with committing corporate capital for holding inventory. She has spoken to some accountants, who typically use a corporate cost of capital rate of 15% - Cost of hedging currency-broker fees =$400 per shipment - Additional administrative time due to international shipping = 4 hours per shipment x$25 per hour (estimated) - At least two five-day visits per year to travel to China to meet with supplier and provide updates on performance and shipping =$20,000 per year ( estimated) The international sourcing costs must be absorbed by Sheila, as the supplier does not assume any of the additional estimated costs and invoice Sheila later or build the costs into a revised unit price. Sheila feels that the U.S. supplier is probably less expensive, even though it quoted a higher price. Sheila also knows that this is a standard technology that is unlikely to change during the next three years, but which could be a contract that extends multiple years out. There is also a lot of "hall talk" amongst the engineers on her floor about next generation automotive electronics, which will completely eliminate the need for wire harnesses, which will be replaced by electronic components that are smaller, lighter, and more reliable. She is unsure about how to calculate the total costs for each option, and she is even more unsure about how to factor these other variables into the decision. ASSIGNMENT 1. Calculate the total cost per unit of purchasing from Original Wire. 2. Calculate the total cost per unit of purchasing from Happy Lucky Assemblies. 3. Based on the total cost per unit, which supplier should Sheila recommend? 4. Are there any other issues besides cost that Sheila should evaluate? 5. Based on this case, do you think international purchasing is more or less complex than domestic purchasing? Why? Is it worth the additional effort