It needs the cost for both suppliers, I have started it on the bottom but I was unsure if I was doing it correctly.

Video Excel Online Structured Activity: Total Cost of Ownership Kuantan ATV, Inc. assembles five different models of all-terrain vehicles (ATVs) from various ready-made components to serve the Las Vegas, Nevada, market. The company uses the same engine for all its ATVS. The purchasing manager, Ms. Jane Kim, needs to choose a supplier for engines for the coming year. Due to the size of the warehouse and other administrative restrictions, she must order the engines in lot sizes of 1,000 each. The unique characteristics of the standardized engine require special tooling to be used during the manufacturing process. Kuantan ATV agrees to reimburse the supplier for the tooling. This is a critical purchase, since late delivery of engines would disrupt production and cause 50 percent lost sales and 50 percent back orders of the ATVS. Jane has obtained quotes from two reliable suppliers but needs to know which supplier is more cost-effective. The terms of sale are 4/10 net 30 for Supplier 1 and 3/10 net 30 for Supplier 2. The data related to the costs of ownership associated with two reliable suppliers has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. X Open spreadsheet Questions 1. What is the total cost of ownership for each of the suppliers? Assume the buyer will take advantage of the largest discount. Do not round intermediate calculations. Round your answers to the nearest cent. Supplier 1 Supplier 2 Total $ 2. Which supplier is more cost-effective? Total Cost of Ownership Analysis Supplier $510.00 Supplier 2 $510.00 Unit Price Requirements (annual forecast units) Lot size (Q) 14,000 1.000 1 to 999 units per order 1000 to 2999 units per order $500.00 $501.00 Weight per engine (Ibs) Order processing cost (per order) Inventory carrying rate (per year) Cost of working capital (per year) Profit margin Price of finished ATV Back-order cost (per unit) 25 3000+ units per order Tooling cost Terms (net 30) Distance (miles) Supplier Quality Rating (defects) Supplier Delivery Rating (lateness) $495.00 $22,000 $491.00 $170.00 $21,000 28% 4% 3% 15% 125 110 14% 3% 2% $5,000 1% 2% 50% Formulas -B3'E4 -E13'B8 (30/365) E13 (B8'10/365+4%) -E6 E8 B3'B5'B17/2000 B3/1000 170 Supplier 1 $7.000,000.00 $86,301.37 $308,767.12 $22,000.00 $30,625.00 $2,380.00 Supplier 2 7,014,000.00 $86,473.97 $239,244.66 $21,000.00 50% Late delivery lost sales Total engine cost Cash discount (net 30) Cash discount (early payment) - B3 F4 F13 B8 F13 (B Other Information Truckload (TL> 40,000 lbs) $0.60 per ton-mile Tooling cost Transportation cost Ordering cost Carrying cost Quality cost F6 $1.40 per ton-mile Less-than-truckload (LTL) Per ton-mile Days per year Invoice payment period (days) Discount period (days) 2,000 lbs per mile 365 #N/A 30 #N/A 10 Backorder cost #N/A Lost sales cost #N/A Total cost #N/A #N/A Lowest cost (B) 2/10 cash discount = Saving on the cost of capita and 2% discount by paying invoices on the 10t Total engine cost x (cost of capital 2% discount) $6,000,000.00 x (10% x 10/365 2%) $136,438.36 Hence, the buyer should pay invoices on the 10th day to take advantage of the $136,438.36 cash discount provided by the 2/10 term (3) Tooling Cost $22,000.00 (4) Transportation Cost Since the order size is 1,000 units, the total weight of each shipment is 22,000 pounds |(1,000 units x 22 lbs/unit) Since it is less than 40,000 pounds, the buyer must use less-than truckload shipment at $1.20 per ton-mile. Total Transportation Cost distance x quantity x weight/unit x rate/ton-mile X 125 miles x 12,000 units x 22 lbs/unit x $1.20/2,000 lbs-mile $19,800.00 Since the number of orders annual requirement/order size, (5) Ordering Cost = number of orders x order processing cost |(12,000 units/1,000 units) x $125 $1,500.00 + Video Excel Online Structured Activity: Total Cost of Ownership Kuantan ATV, Inc. assembles five different models of all-terrain vehicles (ATVs) from various ready-made components to serve the Las Vegas, Nevada, market. The company uses the same engine for all its ATVS. The purchasing manager, Ms. Jane Kim, needs to choose a supplier for engines for the coming year. Due to the size of the warehouse and other administrative restrictions, she must order the engines in lot sizes of 1,000 each. The unique characteristics of the standardized engine require special tooling to be used during the manufacturing process. Kuantan ATV agrees to reimburse the supplier for the tooling. This is a critical purchase, since late delivery of engines would disrupt production and cause 50 percent lost sales and 50 percent back orders of the ATVS. Jane has obtained quotes from two reliable suppliers but needs to know which supplier is more cost-effective. The terms of sale are 4/10 net 30 for Supplier 1 and 3/10 net 30 for Supplier 2. The data related to the costs of ownership associated with two reliable suppliers has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. X Open spreadsheet Questions 1. What is the total cost of ownership for each of the suppliers? Assume the buyer will take advantage of the largest discount. Do not round intermediate calculations. Round your answers to the nearest cent. Supplier 1 Supplier 2 Total $ 2. Which supplier is more cost-effective? Total Cost of Ownership Analysis Supplier $510.00 Supplier 2 $510.00 Unit Price Requirements (annual forecast units) Lot size (Q) 14,000 1.000 1 to 999 units per order 1000 to 2999 units per order $500.00 $501.00 Weight per engine (Ibs) Order processing cost (per order) Inventory carrying rate (per year) Cost of working capital (per year) Profit margin Price of finished ATV Back-order cost (per unit) 25 3000+ units per order Tooling cost Terms (net 30) Distance (miles) Supplier Quality Rating (defects) Supplier Delivery Rating (lateness) $495.00 $22,000 $491.00 $170.00 $21,000 28% 4% 3% 15% 125 110 14% 3% 2% $5,000 1% 2% 50% Formulas -B3'E4 -E13'B8 (30/365) E13 (B8'10/365+4%) -E6 E8 B3'B5'B17/2000 B3/1000 170 Supplier 1 $7.000,000.00 $86,301.37 $308,767.12 $22,000.00 $30,625.00 $2,380.00 Supplier 2 7,014,000.00 $86,473.97 $239,244.66 $21,000.00 50% Late delivery lost sales Total engine cost Cash discount (net 30) Cash discount (early payment) - B3 F4 F13 B8 F13 (B Other Information Truckload (TL> 40,000 lbs) $0.60 per ton-mile Tooling cost Transportation cost Ordering cost Carrying cost Quality cost F6 $1.40 per ton-mile Less-than-truckload (LTL) Per ton-mile Days per year Invoice payment period (days) Discount period (days) 2,000 lbs per mile 365 #N/A 30 #N/A 10 Backorder cost #N/A Lost sales cost #N/A Total cost #N/A #N/A Lowest cost (B) 2/10 cash discount = Saving on the cost of capita and 2% discount by paying invoices on the 10t Total engine cost x (cost of capital 2% discount) $6,000,000.00 x (10% x 10/365 2%) $136,438.36 Hence, the buyer should pay invoices on the 10th day to take advantage of the $136,438.36 cash discount provided by the 2/10 term (3) Tooling Cost $22,000.00 (4) Transportation Cost Since the order size is 1,000 units, the total weight of each shipment is 22,000 pounds |(1,000 units x 22 lbs/unit) Since it is less than 40,000 pounds, the buyer must use less-than truckload shipment at $1.20 per ton-mile. Total Transportation Cost distance x quantity x weight/unit x rate/ton-mile X 125 miles x 12,000 units x 22 lbs/unit x $1.20/2,000 lbs-mile $19,800.00 Since the number of orders annual requirement/order size, (5) Ordering Cost = number of orders x order processing cost |(12,000 units/1,000 units) x $125 $1,500.00 +