MAS Eastern Expansion: Part 1: New Markets I have done the NPV calculation (attached) and attached copies of the four simulations plus the assignment instructions. On the NPV calculation sheet in exce
MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Simulation Decisions:Market Forecasting: CollaborativeCurrent Markets Deleted:New Markets Opened:Alabama, MississippiActive DCs:Detroit MITL4Los Angeles CATL4Dallas TXTL4Chicago ILTL4Denver COTL4Minneapolis MNTL4DC Replenishment Planning:DC Replenishment Planning:Planning Method:DRPTarget DC Fill Rate:100%Time Fence:1 Month(s)Denver Plant:Denver Plant:Operating Capacity:2.1 (in millions)Use Plant Warehouse:TrueWeeks Safety Stock:2Cans/BottlesChemicalsCorrugatedHouston:0.70.70.7Baton Rouge:00.10.1Los Angeles:0.30.20.2Regular Resupply:TL4CL9TL4Weeks Safety Stock:1.52.61.8New Plant: New KensingtonNew Plant: New KensingtonOperating Capacity:1.5 (in millions)Use Plant Warehouse:TrueWeeks Safety Stock:2Cans/BottlesChemicalsCorrugatedPittsburgh:0.70.70.7Baton Rouge:00.10.1Baltimore:0.30.20.2Regular Resupply:TL4CL9TL4Weeks Safety Stock:1.52.61.83/11/2023 Page 1/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Plant Location: DenverRaw Material Statistics for: Cans/BottlesMode of Transport: TL4HoustonBaton RougeLos AngelesOrigin Price$1.16$1.19$1.17Freight to Plant0.01440.01940.021Planned Purchase Shares70.00%0.00%30.00%Actual Purchase Shares69.99%0.00%30.01%Volume2,761,87401,184,027Avg Transit Time4.010.004.01Min Transit Time2.000.002.00Max Transit Time8.000.008.00Weeks of Requirements for Safety Stock 1.5Average Inventory at Plant185,195Maximum Inventory at Plant397,861Minimum Inventory at Plant0Fill Rate at the Plant99.99%Raw Material Statistics for: ChemicalsMode of Transport: CL9HoustonBaton RougeLos AngelesOrigin Price$4.05$4.05$4.10Freight to Plant0.0150.01820.0193Planned Purchase Shares70.00%10.00%20.00%Actual Purchase Shares93.92%0.00%6.08%Volume1,180,313076,434Avg Transit Time5.960.006.26Min Transit Time2.000.002.00Max Transit Time12.000.0011.00Weeks of Requirements for Safety Stock 2.6Average Inventory at Plant42,146Maximum Inventory at Plant210,460Minimum Inventory at Plant0Fill Rate at the Plant79.62%3/11/2023 Page 2/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Plant Location: DenverRaw Material Statistics for: CorrugatedMode of Transport: TL4HoustonBaton RougeLos AngelesOrigin Price$0.72$0.73$0.75Freight to Plant0.01440.01940.021Planned Purchase Shares70.00%10.00%20.00%Actual Purchase Shares74.80%0.27%24.93%Volume1,180,0844,257393,361Avg Transit Time4.004.504.00Min Transit Time2.003.002.00Max Transit Time7.006.008.00Weeks of Requirements for Safety Stock 1.8Average Inventory at Plant81,373Maximum Inventory at Plant171,278Minimum Inventory at Plant0Fill Rate at the Plant99.95%3/11/2023 Page 3/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Plant Location: DenverProduction Planning and ExecutionPeriodScheduledProduced11,554,400984,84721,230,400812,36531,241,600939,99041,224,000920,54951,387,200944,24061,778,8001,678,21171,439,2001,105,59981,902,4001,886,93791,922,0001,691,574101,892,0001,659,497111,555,2001,048,554121,584,4001,047,676131,611,2001,064,775________________________20,322,80015,784,815Lost Production Due to RM Shortages 4,537,985 Plant Finished Goods Warehouse StatisticsWeeks of Requirements for Safety Stock: 2Average 90,695Maximum Inventory1,256,815Minimum Inventory9Volume shipped to DCs15,784,800Fill Rate of FG24.0922%3/11/2023 Page 4/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Plant Location: New KensingtonRaw Material Statistics for: Cans/BottlesMode of Transport: TL4PittsburghBaton RougeBaltimoreOrigin Price$1.20$1.19$1.19Freight to Plant0.00730.02840.0103Planned Purchase Shares70.00%0.00%30.00%Actual Purchase Shares69.44%0.00%30.56%Volume1,152,9180507,455Avg Transit Time3.000.003.00Min Transit Time2.000.002.00Max Transit Time4.000.004.00Weeks of Requirements for Safety Stock 1.5Average Inventory at Plant91,441Maximum Inventory at Plant204,374Minimum Inventory at Plant0Fill Rate at the Plant99.96%Raw Material Statistics for: ChemicalsMode of Transport: CL9PittsburghBaton RougeBaltimoreOrigin Price$4.05$4.05$4.10Freight to Plant0.0060.02340.0085Planned Purchase Shares70.00%10.00%20.00%Actual Purchase Shares100.00%0.00%0.00%Volume567,56900Avg Transit Time5.030.000.00Min Transit Time3.000.000.00Max Transit Time8.000.000.00Weeks of Requirements for Safety Stock 2.6Average Inventory at Plant39,613Maximum Inventory at Plant146,550Minimum Inventory at Plant0Fill Rate at the Plant85.42%3/11/2023 Page 5/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Plant Location: New KensingtonRaw Material Statistics for: CorrugatedMode of Transport: TL4PittsburghBaton RougeBaltimoreOrigin Price$0.74$0.73$0.75Freight to Plant0.00730.02840.0103Planned Purchase Shares70.00%10.00%20.00%Actual Purchase Shares96.24%0.00%3.76%Volume586,291022,932Avg Transit Time3.010.003.09Min Transit Time2.000.002.00Max Transit Time4.000.004.00Weeks of Requirements for Safety Stock 1.8Average Inventory at Plant27,058Maximum Inventory at Plant89,147Minimum Inventory at Plant0Fill Rate at the Plant91.69%3/11/2023 Page 6/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Plant Location: New KensingtonProduction Planning and ExecutionPeriodScheduledProduced1640,000420,3102544,000397,7933457,600268,2564606,400541,0605550,400400,6766675,200580,9587660,800559,5168752,000678,3289888,000854,93210756,800615,56511689,600441,44612681,600519,69313582,400365,664__________________8,484,8006,644,195Lost Production Due to RM Shortages 1,840,605 Plant Finished Goods Warehouse StatisticsWeeks of Requirements for Safety Stock: 2Average Inventory54,571Maximum Inventory584,890Minimum Inventory28Volume shipped to DCs6,648,800Fill Rate of FG22.9478%3/11/2023 Page 7/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Distribution Center StatisticsDC Location: Detroit MISupplying Plant: New KensingtonCost InformationInventory InformationFixed Costs:$35,160.00Throughput:3,340,302Variable Costs:$55,315.39Avg 211,678Inv Holding Costs:$60,079.70Max 473,679Freight into DC:$54,112.89Min 0Fill Rate:99.59%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.00Max Transit Time:3.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Cleveland OH1,239,5231,234,459$0.95502.64 daysDetroit MI1,479,9741,473,928$0.96542.16 daysColumbus OH634,507631,915$0.94373.16 days3/11/2023 Page 8/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Distribution Center StatisticsDC Location: Los Angeles CASupplying Plant: DenverCost InformationInventory InformationFixed Costs:$31,180.00Throughput:4,912,184Variable Costs:$92,250.81Avg 319,102Inv Holding Costs:$89,362.20Max 660,534Freight into DC:$124,965.95Min 0Fill Rate:99.77%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.37Max Transit Time:4.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Los Angeles CA2,942,5872,935,753$0.93973.83 daysSan Francisco CA1,369,1261,365,946$0.90635.41 daysPortland OR611,905610,484$0.88206.58 days3/11/2023 Page 9/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Distribution Center StatisticsDC Location: Dallas TXSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$33,960.00Throughput:3,283,718Variable Costs:$37,040.34Avg 237,346Inv Holding Costs:$65,510.19Max 576,260Freight into DC:$39,404.62Min 0Fill Rate:99.70%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.40Max Transit Time:4.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Birmingham AL364,680363,600$0.94174.73 daysMobile AL51,51351,360$0.94684.49 daysHouston TX560,912559,251$0.98062.90 daysDallas TX1,060,6281,057,487$1.04051.00 daysSan Antonio TX106,495106,180$0.97163.32 daysShreveport LA112,870112,536$0.98882.52 daysNew Orleans LA270,404269,604$0.95714.00 daysLittle Rock AR138,100137,691$0.97293.26 daysOklahoma City OK627,869626,010$0.98402.74 days3/11/2023 Page 10/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Distribution Center StatisticsDC Location: Chicago ILSupplying Plant: New KensingtonCost InformationInventory InformationFixed Costs:$38,000.00Throughput:3,411,195Variable Costs:$53,692.21Avg 238,970Inv Holding Costs:$67,868.78Max 573,410Freight into DC:$57,308.08Min 0Fill Rate:99.08%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.01Max Transit Time:3.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Chicago IL1,039,5801,029,977$0.99231.00 daysPeoria IL388,238384,652$0.96872.01 daysFort Wayne IN147,770146,405$0.96592.14 daysIndianapolis IN311,439308,563$0.96072.38 daysEvansville IN154,271152,846$0.93883.40 daysMilwaukee WI524,877520,029$0.97711.63 daysSt Louis MO358,857355,542$0.93993.35 daysCincinnati OH517,965513,181$0.94233.24 days3/11/2023 Page 11/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Distribution Center StatisticsDC Location: Denver COSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$21,280.00Throughput:6,712,688Variable Costs:$53,701.50Avg 432,946Inv Holding Costs:$118,126.30Max 1,069,007Freight into DC:$9,666.27Min 0Fill Rate:99.59%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.78Max Transit Time:6.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Omaha NE591,034588,599$0.96674.20 daysKansas City MO766,484763,326$0.95964.53 daysWichita KS315,074313,776$0.97193.96 daysEl Paso TX243,128242,127$0.95974.53 daysAlbuquerque NM53,42253,201$0.98193.48 daysPhoenix AZ768,821765,654$0.91596.62 daysSalt Lake City UT723,341720,361$0.97843.65 daysSeattle WA921,822918,024$0.88428.14 daysSpokane WA231,282230,329$0.88428.14 daysButte MT292,710291,504$0.91636.60 daysDenver CO1,833,3401,825,787$1.01531.94 days3/11/2023 Page 12/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Distribution Center StatisticsDC Location: Minneapolis MNSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$33,000.00Throughput:1,125,899Variable Costs:$15,537.41Avg 153,398Inv Holding Costs:$42,831.03Max 902,924Freight into DC:$25,535.39Min 0Fill Rate:91.86%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.38Max Transit Time:4.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Minneapolis MN768,323705,771$1.01021.00 daysDes Moines IA457,364420,128$0.96303.10 days3/11/2023 Page 13/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3Activity Based Costs and PerformanceTotal CostsUnit CostsAverage Time (days)Plant In-boundPurchase Costs$15,567,605.65$0.6832Freight Costs$131,249.50$0.0058In Transit to Plant$64,505.05$0.00284.13Raw Mtl. Holding$188,824.06$0.008312.62Production RelatedPlant Fixed Cost$7,900,000.00$0.3467Plant Var. Cost$4,401,254.36$0.1932WIP Holding$208,711.37$0.009210.00Plant Out-boundPlant Warehouse FixCost
$413,000.00$0.0181Plant Warehouse Var
Cost
$411,489.10$0.0181Plant Warehouse Holding
$39,929.95$0.00181.68In Transit Holding to
DC
$57,304.44$0.00252.42DistributionDC Freight In$310,993.19$0.0136DC Fixed$192,580.00$0.0085DC Var$307,537.66$0.0135DC Holding$443,778.19$0.019518.47Freight to Customer$2,267,178.72$0.0995In Transit to Customer$85,209.16$0.00373.49AdministrativeControl System Costs--Base$100,000.00$0.0044Control System Costs--DRP$40,000.00$0.0018Control System Costs--Colb$25,000.00$0.0011--------------Totals$33,156,150.41$1.447952.833/11/2023 Page 14/15 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 3SUPPLY CHAIN FINANCIAL STATEMENTSSupply Chain Income Statement (does not include Marketing and G & A)Total Annual DollarsDollars / UnitTotal Sales$45,571,969.90$2.0000Total Fixed$8,670,580.00$0.3805Total Variable$23,397,308.19$1.0268Total Inventory $1,088,262.22$0.0478-----------------Profit Contribution $12,415,819.49$0.5449Supply Chain Assets EmployedPlants$21,800,000.00Plant Warehouse$1,530,000.00Distribution $1,925,800.00Inventory$3,627,540.74-----------------Total Assets Employed$28,883,340.74SUPPLY CHAIN PERFORMANCE MEASURESCustomer ServiceTargeted Fill Rate:100.00%Achieved Fill Rate99.15%Order Cycle Time to Customer (days)3.49FlexibilityAverage Time through Supply Chain (days)52.83Average Production Capacity Utilization47.93%FinancialSupply Chain Profit Margin27.24%Supply Chain Return on Assets42.99%3/11/2023 Page 15/15
