Nash Auto has two plants, two warehouses, and three customers. The plants are in Detroit and Atlanta, the warehouses are in Denver and New York, and the customers are in Los Angeles, Chicago, and Philadelphia. Cars are produced at plants, then shipped to warehouses, and finally shipped to customers. Detroit can produce 800 cars per week, and Atlanta can produce 400 cars per week. Los Angeles requires 450 cars per week, Chicago requires 300, and Philadelphia requires 250. It costs $10,000 to produce a car at each plant. The costs of shipping a car between various cities are listed in the file P05_56.xlsx. Assume that during a week, at most 250 cars can be shipped from a warehouse to any particular city. Determine how to meet Nash's weekly demands at minimum cost.

( please responsed with excel screenshots and steps)

 

Q23 A 1 Logistics at Nash Auto B D E F G J K M N P Q R Range names 2 ArcCaps =Model!$F$22:$F$27 3 Unit production cost at each plant $0 Capacities =Model!$L$18:$L$19 4 CostMatrix =Model!$C$8:$I$14 5 Unit shipping costs Costs =Model!$C$18:$C$27 6 To Demands =Model!$L$22:$L$24 7 Detroit Atlanta Denver NY LA Chicago Philly Dests =Model!$B$18:$B$27 8 From Detroit $1,253 $637 Flows =Model!$D$18:$D$27 9 Atlanta $1,398 $841 Flows1 =Model!$D$22:$D$27 10 Denver $1,059 $996 $1,691 Inflows =Model!$J$22:$J$24 11 NY $2,786 $802 $250 NetFlows =Model!$I$20:$I$21 12 LA Origins =Model!$A$18:$A$27 13 Chicago Outflows =Model!$I$18:$J$19 14 Philly TotCost =Model!$B$29 15 UnitProdCost =Model!$C$3 16 Network formulation Flow balance constraints 17 Origin Destination Cost Flow Capacities Location Index Net flow in/out Reqt 18 1 3 1253 65 Detroit 200