Executive summary for the below :

Problem: Cases of product must be delivered from the supplier located in Stillwater, OK to all 8 DCs either by shipping directly without using a cross-dock or by using only 1 cross-dock. The goal is to minimize cost DC locations are given below in several tables. Consider the following 3 potential cross-dock locations: Waxahachie, TX; Chicago, IL; Louisville, KY. Answer the questions at the bottom starting in Row 71. Product Information: A 48" x 40" standard pallet can hold 55 cases of product (capacity is 55 cases per pallet). A case weighs 20 pounds and is 1.35 cubic feet. The supplier can deliver the product to the Waxahachie, TX cross-dock at a cost of $86 per case. (cost includes the cost of goods (COGS) and tranportation cost) The supplier can deliver the product to the Chicago, IL cross-dock at a cost of $82 per case. (cost includes the cost of goods (COGS) and tranportation cost) The supplier can deliver the product to the Louisville, KY cross-dock at a cost of $83 per case. (cost includes the cost of goods (COGS) and tranportation cost) The annual pallet forecast demand by DC is given below. Pallets contain cases of product along with a cubic amount based on the pallet case capacity and case cube. Annual Pallet DC # DC Location Forecast 1 St. Louis, MO 444 2 Denver, CO 3 Austin, TX 356 4 Chicago, IL 327 5 Philadelphia, PA 354 6 Columbia, SC 302 Miami, FL 429 8 Tulsa, OK 395Cost per cube from the Waxahachie, TX cross-dock to each DC (includes handling and transportation cost) DC # DC Location Cost per Cube 1 St. Louis, MO $5.08 N Denver, CO $2.29 3 Austin, TX $5.54 4 Chicago, IL $4.86 5 Philadelphia, PA $4.61 Columbia, SC $3.74 7 Miami, FL $4.81 8 Tulsa, OK $5.06 Cost per cube from the Chicago, IL cross-dock to each DC (includes handling and transportation cost) DC # DC Location Cost per Cube 1 St. Louis, MO $4.10 IN Denver, CO $3.70 3 Austin, TX $5.23 4 Chicago, IL $6.46 5 Philadelphia, PA $4.30 6 Columbia, SC $8.71 7 Miami, FL $7.72 8 Tulsa, OK $4.26Cost per cube from the Louisville, KY cross-dock to each DC (includes handling and transportation cost) DC # DC Location Cost per Cube 1 St. Louis, MO $4.39 IN Denver, CO $7.24 3 Austin, TX $9.94 Chicago, IL $6.66 UT Philadelphia, PA $8.81 Columbia, SC $8.45 Miami, FL $7.22 8 Tulsa, OK $3.88 DC locations are given in the table below along with the direct shipment cost per case from the supplier to each DC without using a cross-dock. Delivered Cost per Case from the DC # DC Location Shipper to the DC 1 St. Louis, MO $102.07 2 Denver, CO $101.36 W Austin, TX $105.13 Chicago, IL $97.63 Philadelphia, PA $101.15 6 Columbia, SC $105.90 Miami, FL $103.12 8 Tulsa, OK $111.83Questions: Show all work in answering the questions below. i need to see all Excel operations/formulations similar to the inclass cross-clocking example that we did in Week 5. 1. What is the total annual cost to ship from the supplier to each DC using the Waxahachie, TX crossdock? $15,219,886.74 2. What is the total annual cost to ship from the supplier to each DC using the Chicago, IL crossidock? $14,771,326.63 3. What is the total annual cost to ship from the supplier to each DC using the Louisville, KY cross-dock? 51526537118 4. What is the total annual cost to ship directly from the supplier to each DC without using a crossdock? $17,116,653A 5. Which shipping option is the least costly and what is the cost per case for the cheapest option? Supplier located in Stillwater, OK shipping the case product using a cross-dock located in Chicago, IL is the cheapest option. 6. In addition to cost, what are some other factors that need to be considered when evaluating the different shipment opons? It is also important to consider both quality' and delivery time