Please complete provided Excel Spreadsheet before answering below five questions.

You are expected to fill out yellow highlighted cells in columns B and C of the spreadsheet. For each Per Unit cost cell highlighted in column B, you are expected to either gather data from the case or use the given formulas in column A to calculate corresponding cost. Column C values are simply the annual total of values in Column B. You are encouraged to insert formulas in the highlighted cells using provided formulas.

Your final recommendations will be based on total cost values in rows 9 and 29 along with comparison values in rows 31 and 32 which already have built-in formulas

(PLEASE SEND SCREENSHOTS OF EXCEL SPREADSHEET)

Wiring Harness Example The Global Sourcing Wire Harness 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, Michigan) and Happy Lucky Assemblies (H1.A) 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 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 representative 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 warchousing 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 HLA 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 informaion 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