- Please solve in Excel if possible- if not proceed normally thank you :)

The Burdell Wheel and Tire Company assembles tires to wheel rims for use on cars during the manufacture of vehicles by the auto industry. Burdell wants to find a low-cost supplier for the tires they use in the assembly operation. The supplier will be chosen on the basis of total annual cost to supply Burdells needs. Burdells annual requirements are for 25,000 tires, and the company operates for 250 days a year. The following data are available to compare the two suppliers. The administration costs do not depend on shipping quantity or shipping frequency and are fixed for a given supplier. The inventory hold costs are per unit per year.

1. What is the annual cost-difference between the two suppliers?

2. If the costs per shipment are $1500 and $1000 respectively from Lexington Tire and Irmo auto, and you are allowed to choose your optimal shipping quantities, what is the new annual cost-difference between the two suppliers?

SHIPPING QUANTITY ANNUAL PRICE INVENTORY LEAD PER SHIPPING TIRE HOLD COSTS TIME SHIPMENT COSTS (p) (H) (DAYS) ADMIN. COSTS SUPPLIER LEXINGTON TIRE IRMO AUTO 2,000 $18,000 $30 $6.00 6 6 $15,000 1,000 $25,000 $29 $5.80 14 $18,000