Assignment: Transportation Cost Optimization for a Retail ChainObjective: A regional grocery retailer operates $20$ stores and is evaluating whether to continue using a single distribution center $($DC$)$ in Chicago or to add a second DC in Columbus, OH to optimize transportation costs. Your task is to compare transportation costs under both scenarios and determine whether adding a second DC is a cost$-$effective decision.Scenario:The retailer is considering two distribution models:Single DC Model: Continue operating only the Chicago DC$,$ serving all stores.Two DC Model: Open an additional Columbus DC and allocate stores to the nearest DC to reduce transportation costs.The dataset provided $($ Dataset Transportation Costs$-1.$xlsx Download Dataset Transportation Costs$-1.$xlsx$)$ includes:Store locations and demand forecasts $($units$/$week$).$Distances from each store to Chicago and Columbus DCs$.$Transportation cost per mile $($$$).$Part $1$: Spreadsheet Analysis $($Excel or Google Sheets$)$Current Scenario $($Blue Column$)$:Calculate transportation cost for a single DC $($Chicago Only$)$:Transportation cost $=$ Demand $\backslash$times Distance to Chicago $\backslash$times Cost per unit shippedTO BE Scenario $($Green Column$)$ Two$-$Step Analysis:Step $1$: Demand Allocation for Two DCsAssign each stores demand to the nearest DC $($Chicago or Columbus$)$ based on the provided distances.If the distance to Chicago DC is smaller, allocate the store's demand to Chicago.If the distance to Columbus DC is smaller, allocate the store's demand to Columbus.Step $2$: Calculate Transportation CostsFor the Single DC Model $($Chicago Only$)$:Transportation cost $=$ Demand $\backslash$times Distance to Chicago $\backslash$times Cost per unit shippedFor the Two DC Model $($Chicago Columbus$)$:Transportation cost for Chicago$-$handled stores $=$ Demand $\backslash$times Distance to Chicago $\backslash$times Cost per unit shippedTransportation cost for Columbus$-$handled stores $=$ Demand $\backslash$times Distance to Columbus $\backslash$times Cost per unit shippedTotal transportation cost $=$ Sum of both calculations Compare the Two StrategiesCalculate the total transportation cost for both models.Analyze whether the two DC model leads to cost savings.You can use a bar chart or some other visual aids $($but not required$)$ to visually compare transportation costs in both scenarios.Part $2$: Decision$-$Making Assignment: Transportation Cost Optimization for a Retail ChainObjective: A regional grocery retailer operates $20$ stores and is evaluating whether to continue using a single distribution center $($DC$)$ in Chicago or to add a second DC in Columbus, OH to optimize transportation costs. Your task is to compare transportation costs under both scenarios and determine whether adding a second DC is a cost$-$effective decision.Scenario:The retailer is considering two distribution models:Single DC Model: Continue operating only the Chicago DC$,$ serving all stores.Two DC Model: Open an additional Columbus DC and allocate stores to the nearest DC to reduce transportation costs.The dataset provided $($ Dataset Transportation Costs$-1.$xlsx Download Dataset Transportation Costs$-1.$xlsx$)$ includes:Store locations and demand forecasts $($units$/$week$).$Distances from each store to Chicago and Columbus DCs$.$Transportation cost per mile $($$$).$Part $1$: Spreadsheet Analysis $($Excel or Google Sheets$)$Current Scenario $($Blue Column$)$:Calculate transportation cost for a single DC $($Chicago Only$)$:Transportation cost $=$ Demand $\backslash$times Distance to Chicago $\backslash$times Cost per unit shippedTO BE Scenario $($Green Column$)$ Two$-$Step Analysis:Step $1$: Demand Allocation for Two DCsAssign each stores demand to the nearest DC $($Chicago or Columbus$)$ based on the provided distances.If the distance to Chicago DC is smaller, allocate the store's demand to Chicago.If the distance to Columbus DC is smaller, allocate the store's demand to Columbus.Step $2$: Calculate Transportation CostsFor the Single DC Model $($Chicago Only$)$:Transportation cost $=$ Demand $\backslash$times Distance to Chicago $\backslash$times Cost per unit shippedFor the Two DC Model $($Chicago Columbus$)$:Transportation cost for Chicago$-$handled stores $=$ Demand $\backslash$times Distance to Chicago $\backslash$times Cost per unit shippedTransportation cost for Columbus$-$handled stores $=$ Demand $\backslash$times Distance to Columbus $\backslash$times Cost per unit shippedTotal transportation cost $=$ Sum of both calculations Compare the Two StrategiesCalculate the total transportation cost for both models.Analyze whether the two DC model leads to cost savings.You can use a bar chart or some other visual aids $($but not required$)$ to visually compare transportation costs in both scenarios.Part $2$: Decision$-$Making