Answer the following questions: a) Total order handling costs for both current and new plans b) Total transportation costs for both current and new plans c) Total warehouse inventory holding costs for both plans d) Total in-transit inventory carrying costs for both plans e) Total costs combining all relevant costs for both plans f) If you were Tracy, what would you do? Explain

please provide excel set up and formulas used, thank you

Exercise 2 Tracy Anderson, Supply Chain manager for Cool-in-Motion Company has just received a rate reduction offer from Old Dominion Freight Line for shipment of small mini-split air conditioners to the company's field warehouse in Tucson, AZ. The current rate is $4 per cwt for the delivery size of 200 units (per delivery); the weight per unit is 150 pounds. The lead time is 3 days. The new proposal is a rate of $3.30 per cwt (a very attractive reduction of 17.5% in freight rate) only if Cool-in-Motion is willing to increase its order size to 400 units per delivery. One more thing attached to the offer: the new lead time would be 5 days for units to be delivered, instead of the current 3 days. By doing this, Old Dominion Freight Line can gain some efficiency/benefit in its operation; that is why the trucking company is willing to pass part of the benefit to Cool-in-Motion by charging a cheaper rate (reducing from $4 per cut to $3.30 per cwt). Here are some additional pieces of information that Tracy Anderson (her photo on the left, by the way) is analyzing before making any decision on Old Dominion's offer: Annual demand on Tucson's warehouse is 25,000 mini-splits; we can safely assume this demand rate is constant throughout the year Standard cost of one mini-split in Tucson's warehouse is $360 per unit Stock replenishment order handling costs $40 per order Both the in-transit carrying cost and the warehouse inventory carrying costs as a percentage of average value is 25% per year Answer the following questions: a) Total order handling costs for both current and new plans b) Total transportation costs for both current and new plans c) Total warehouse inventory holding costs for both plans d) Total in-transit inventory carrying costs for both plans e) Total costs combining all relevant costs for both plans f) If you were Tracy, what would you do? Explain Exercise 2 Tracy Anderson, Supply Chain manager for Cool-in-Motion Company has just received a rate reduction offer from Old Dominion Freight Line for shipment of small mini-split air conditioners to the company's field warehouse in Tucson, AZ. The current rate is $4 per cwt for the delivery size of 200 units (per delivery); the weight per unit is 150 pounds. The lead time is 3 days. The new proposal is a rate of $3.30 per cwt (a very attractive reduction of 17.5% in freight rate) only if Cool-in-Motion is willing to increase its order size to 400 units per delivery. One more thing attached to the offer: the new lead time would be 5 days for units to be delivered, instead of the current 3 days. By doing this, Old Dominion Freight Line can gain some efficiency/benefit in its operation; that is why the trucking company is willing to pass part of the benefit to Cool-in-Motion by charging a cheaper rate (reducing from $4 per cut to $3.30 per cwt). Here are some additional pieces of information that Tracy Anderson (her photo on the left, by the way) is analyzing before making any decision on Old Dominion's offer: Annual demand on Tucson's warehouse is 25,000 mini-splits; we can safely assume this demand rate is constant throughout the year Standard cost of one mini-split in Tucson's warehouse is $360 per unit Stock replenishment order handling costs $40 per order Both the in-transit carrying cost and the warehouse inventory carrying costs as a percentage of average value is 25% per year Answer the following questions: a) Total order handling costs for both current and new plans b) Total transportation costs for both current and new plans c) Total warehouse inventory holding costs for both plans d) Total in-transit inventory carrying costs for both plans e) Total costs combining all relevant costs for both plans f) If you were Tracy, what would you do? Explain