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 = 2Simulation 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.83/11/2023 Page 1/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Plant 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 Shares70.02%0.00%29.98%Volume3,747,12901,604,734Avg Transit Time4.010.004.04Min Transit Time2.000.002.00Max Transit Time8.000.008.00Weeks of Requirements for Safety Stock 1.5Average Inventory at Plant168,942Maximum Inventory at Plant425,726Minimum Inventory at Plant0Fill Rate at the Plant100.00%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 Shares82.40%0.00%17.60%Volume1,682,4140359,274Avg Transit Time5.940.006.34Min Transit Time2.000.002.00Max Transit Time12.000.0012.00Weeks of Requirements for Safety Stock 2.6Average Inventory at Plant76,345Maximum Inventory at Plant228,360Minimum Inventory at Plant0Fill Rate at the Plant95.37%3/11/2023 Page 2/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Plant 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 Shares70.91%6.53%22.56%Volume1,515,589139,545482,063Avg Transit Time4.004.024.09Min Transit Time2.002.002.00Max Transit Time7.006.009.00Weeks of Requirements for Safety Stock 1.8Average Inventory at Plant70,810Maximum Inventory at Plant177,541Minimum Inventory at Plant0Fill Rate at the Plant99.83%3/11/2023 Page 3/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Plant Location: DenverProduction Planning and ExecutionPeriodScheduledProduced11,443,2001,325,10521,043,200916,71431,259,2001,167,40741,384,0001,314,31751,552,4001,545,46462,060,0002,001,69972,029,6001,973,87582,096,4002,014,36392,078,8002,033,145102,090,8001,849,243112,097,2001,919,947121,932,0001,745,498131,691,2001,601,199________________________22,758,00021,407,975Lost Production Due to RM Shortages 1,350,025 Plant Finished Goods Warehouse StatisticsWeeks of Requirements for Safety Stock: 2Average 428,301Maximum Inventory2,136,009Minimum Inventory9Volume shipped to DCs21,407,200Fill Rate of FG81.024%3/11/2023 Page 4/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Distribution Center StatisticsDC Location: Detroit MISupplying Plant: DenverCost InformationInventory InformationFixed Costs:$35,160.00Throughput:2,646,643Variable Costs:$43,828.42Avg 212,525Inv Holding Costs:$63,402.68Max 397,766Freight into DC:$71,459.37Min 0Fill Rate:99.91%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.79Max Transit Time:6.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Cleveland OH1,207,3651,206,317$0.90662.64 daysDetroit MI1,441,5781,440,327$0.91702.16 days3/11/2023 Page 5/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Distribution Center StatisticsDC Location: Los Angeles CASupplying Plant: DenverCost InformationInventory InformationFixed Costs:$31,180.00Throughput:4,596,786Variable Costs:$86,327.64Avg 347,455Inv Holding Costs:$103,493.66Max 652,671Freight into DC:$116,942.23Min 0Fill Rate:99.80%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.38Max Transit Time:5.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Los Angeles CA2,749,1602,743,718$0.88033.83 daysSan Francisco CA1,279,1281,276,596$0.84695.41 daysPortland OR577,615576,471$0.82266.58 days3/11/2023 Page 6/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Distribution Center StatisticsDC Location: Dallas TXSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$33,960.00Throughput:3,088,769Variable Costs:$34,841.32Avg 250,896Inv Holding Costs:$73,720.99Max 472,868Freight into DC:$37,065.23Min 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.Birmingham AL343,603342,822$0.88234.73 daysMobile AL48,62748,516$0.88744.49 daysHouston TX524,053522,861$0.92122.90 daysDallas TX992,784990,526$0.98111.00 daysSan Antonio TX99,49799,271$0.91223.32 daysShreveport LA107,562107,317$0.92942.52 daysNew Orleans LA255,257254,676$0.89774.00 daysLittle Rock AR130,610130,313$0.91353.26 daysOklahoma City OK593,818592,467$0.92462.74 days3/11/2023 Page 7/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Distribution Center StatisticsDC Location: Chicago ILSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$38,000.00Throughput:3,912,212Variable Costs:$61,578.21Avg 303,533Inv Holding Costs:$90,006.74Max 575,596Freight into DC:$82,156.45Min 0Fill Rate:99.81%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.27Max Transit Time:5.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Chicago IL1,012,9071,010,941$0.95051.00 daysPeoria IL378,277377,543$0.92692.01 daysFort Wayne IN143,979143,699$0.92412.14 daysIndianapolis IN303,449302,860$0.91892.38 daysEvansville IN150,313150,021$0.89713.40 daysMilwaukee WI505,023504,043$0.93541.63 daysSt Louis MO342,324341,659$0.89823.35 daysColumbus OH587,083585,944$0.89543.48 daysCincinnati OH496,465495,502$0.90053.24 days3/11/2023 Page 8/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Distribution Center StatisticsDC Location: Denver COSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$21,280.00Throughput:6,359,294Variable Costs:$50,874.35Avg 460,440Inv Holding Costs:$133,832.66Max 1,145,917Freight into DC:$9,157.38Min 0Fill Rate:99.67%Transit Time Statistics from Plant Mode Used:TL4Average Transit Time:2.77Max Transit Time:6.00Min Transit Time:2.00Customer Regions ServedMarketDemandSoldProfit Contr.Transit Time--Cust.Omaha NE559,051557,201$0.90734.20 daysKansas City MO725,007722,608$0.90024.53 daysWichita KS300,295299,302$0.91253.96 daysEl Paso TX227,180226,429$0.90034.53 daysAlbuquerque NM49,91749,752$0.92253.48 daysPhoenix AZ718,391716,014$0.85656.62 daysSalt Lake City UT675,894673,658$0.91903.65 daysSeattle WA870,294867,415$0.82488.14 daysSpokane WA218,354217,631$0.82488.14 daysButte MT280,315279,387$0.85696.60 daysDenver CO1,755,7051,749,896$0.95591.94 days3/11/2023 Page 9/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Distribution Center StatisticsDC Location: Minneapolis MNSupplying Plant: DenverCost InformationInventory InformationFixed Costs:$33,000.00Throughput:1,164,634Variable Costs:$16,071.95Avg 119,543Inv Holding Costs:$35,508.47Max 469,275Freight into DC:$26,413.90Min 0Fill Rate:98.73%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 MN739,420730,052$0.95081.00 daysDes Moines IA440,159434,582$0.90363.10 days3/11/2023 Page 10/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2Activity Based Costs and PerformanceTotal CostsUnit CostsAverage Time (days)Plant In-boundPurchase Costs$16,065,643.93$0.738Freight Costs$154,483.23$0.0071In Transit to Plant$79,100.31$0.00364.84Raw Mtl. Holding$154,323.49$0.00718.62Production RelatedPlant Fixed Cost$4,800,000.00$0.2205Plant Var. Cost$4,073,224.51$0.1871WIP Holding$212,435.44$0.009810.00Plant Out-boundPlant Warehouse FixCost
$213,000.00$0.0098Plant Warehouse Var
Cost
$417,447.95$0.0192Plant Warehouse Holding
$124,305.96$0.00575.20In Transit Holding to
DC
$61,316.07$0.00282.57DistributionDC Freight In$343,194.57$0.0158DC Fixed$192,580.00$0.0088DC Var$293,521.89$0.0135DC Holding$499,965.19$0.02320.58Freight to Customer$2,159,578.99$0.0992In Transit to Customer$85,736.98$0.00393.48AdministrativeControl System Costs--Base$100,000.00$0.0046Control System Costs--DRP$40,000.00$0.0018Control System Costs--Colb$25,000.00$0.0011--------------Totals$30,094,858.51$1.374955.293/11/2023 Page 11/12 MAS SUPPLY CHAIN SIMULATION RESULTSPrinted by Simulator 1.0 Calendar Year 2023Copyright 2023 by Alan J. Stenger, all rights reservedSCENARIO YEAR = 2SUPPLY CHAIN FINANCIAL STATEMENTSSupply Chain Income Statement (does not include Marketing and G & A)Total Annual DollarsDollars / UnitTotal Sales$43,536,677.16$2.0000Total Fixed$5,370,580.00$0.2467Total Variable$23,507,095.07$1.0799Total Inventory $1,217,183.44$0.0559-----------------Profit Contribution $13,441,818.65$0.6175Supply Chain Assets EmployedPlants$13,000,000.00Plant Warehouse$750,000.00Distribution $1,925,800.00Inventory$4,057,278.14-----------------Total Assets Employed$19,733,078.14SUPPLY CHAIN PERFORMANCE MEASURESCustomer ServiceTargeted Fill Rate:100.00%Achieved Fill Rate99.72%Order Cycle Time to Customer (days)3.48FlexibilityAverage Time through Supply Chain (days)55.29Average Production Capacity Utilization78.42%FinancialSupply Chain Profit Margin30.87%Supply Chain Return on Assets68.12%3/11/2023 Page 12/12
