Please use Microsoft Excel's Solver Function to solve this in the format provided below. Include equations and input data if possible.

here is the answer, I just need assistance formatting it into the formatting above... just need a snip of the formulas and the set up in the solver screen...

2 Drylce, Inc is a manufacturer of air conditioners that has seen its demand grow significantly. The company anticipates nationwide demand for the next year to be 180,000 units in the South, 120,000 units in the Midwest, 110.000 units in the East and 100,000 units in the West. Managers at Drylce are designing the manufacturing network and have selected four potential sites-New York, Atlanta, Chicago, and San Diego Plants could have a capacity of either 200,000 or 400,000 units. The annual fixed costs at the four locations are shown in Table 5-50 along with the cost of producing and shipping an air conditioner to each of the four markets. Where should Dryice build its factories and how large should they be? Table 5-5 Production and Transport Costs for Drylce, Inc. New York Atlanta Chicago San Diego Annual fixed cost of 200,000 plant S6 million $5.5 million $5.6 million $6.1 million Annual fixed cost of 400,000 plant $10 million $9.2 million $9.3 million $10.2 million East $211 $232 $238 $299 South $232 $212 $230 $280 Midwest $240 $230 $215 $270 West $300 $280 $270 $225 H24 B D E F G H I J M 4 A 1 DryIce Inc. Facilities Planning AWN Shipped from NY Shipped from Atlanta Shipped from Chicago Shipped from San Diego New York Atlanta Chicago San Diego Requirements Supply Fixed Costs 200k 400K Variable Costs East 6,000,000 10,000,000 5,500,000 9,200,000 5,600,000 9,300,000 6,100,000 10,200,000 211 232 240 300 232 212 230 280 238 230 215 270 299 280 270 225 110,000 180,000 120,000 100,000 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Midwest West Large (400k) Small (200K) Capacity/Cost South TOTAL SYSTEM COST Cells that are changed All non-negative integers low high New York Atlanta Chicago San Diego Objective function : =SUMPRODUCT(B10:313,02:35)+SUMPRODUCT(C10:C13,K2:K5)+SUMPRODUCT(B2:E5,816:E19) East South Midwest West Supply 5 $ S 5 211S 23215 2405 300S 200.000 400.000 2325 2125 5 2805 200 000 400 000 1 2385 230S 2155 2705 200,000 400.000 Demand 110.000 180.000 120.000 100.000 20 270 225 200 000 400.000 New York Atlanta Chicago San Diego Annualued cost of Anul twed costo 200 000 art 400,000 6.000.000 5 20.000.000 5 5.500.000 9.200 000 5 5.600.000 9.300.000 5 6.100.0005 10.200.000 Low capacity high capacity 0 0 10 11 12 13 New York Allanta Chicago San Diego 2 1 o 14 13 New York 310000 San Diego 10 Allanta 0 1000 0 0 200.000 Chicago . O 120000 0 400.000 South Midwest West Supply Demand 110,000 180,000 120.000 100.000 o 11 19 20 20 0 O 200.000 100000 200.000 110000 180000 120000 100000 5136570.000 Solver Parameters Ses Objective: 58524 To Mia O value of Changing Variable Cells $E$10.58513,SB516:SES19 Subject to the Constraints: SB510.58513 - binary SB522-SES22 SF52.5755 Add Change Delete Beset All Load/Save Make Unconstrained variables Non-Negative Sglect a Solving Method GRG Nonlinear Options