Please answer it in Excel file and show the use of formulas in it.

SOLVER PROBLEM B Sycamore Plastics (SP) is a manufacturer of polyethylene plastic pellets used as a raw material by manufacturers of plastic goods around the U.S. SP currently operates four manufacturing centers in Philadelphia PA, Atlanta GA, St. Louis MO, and Salt Lake City UT. The plants have different capacities and production costs as indicated in the table below. Plant Philadelphia Atlanta St. Louis Salt Lake City Maximum Capacity (x 100,000 lbs.) 11 9.5 11 9 Prod. Costs (per 1,000 lbs.) $300.00 $280.00 $305.00 $320.00 SP currently has six contract customers located in New York City, Birmingham AL, Terre Haute IN, Dallas TX, Spokane WA, and San Diego CA. Transportation costs between the plants and various customers, as well as contracted demand from each customer is shown in the table below. # San Diego $85 From/To Philadelphia Atlanta St. Louis Salt Lake City Total Demand (x 1,000 lbs.) NYC $45 $55 $57 $72 Birmingham $52 $42 $60 $71 Transport Costs per 1,000 lbs. Terre Haute Dallas $56 $62 $58 $59 $50 $54 $67 $65 Spokane $78 $80 $65 $52 $82 $70 $66 525 415 925 600 390 410 Using the spreadsheet template at the Blackboard site, create a solver model to help SP develop a distribution plan that will minimize costs to supply the customers' demand. 1 2 Name: 3 Type your name here! 4 This sheet contains the data from your problem and the template to build a Solver model. 5 6 Mammum Canarita (100,000 lbs.) 11 9.5 11 Plant Philadelphia Atlanta St. Louis Salt Lake City Prod. Costs (1.000 lbs.) $300.00 $280.00 $305.00 $320.00 7 8 9 9 10 11 12 13 14 15 16 17 From/To Philadelphia Atlanta St. Louis Salt Lake City Total Demand IX 1,000 NYC $45 $55 $57 $72 525 Birmingham $52 $42 $60 $71 415 Transport Costs per 1,000 lbs. Terre Haute Dallas $56 $62 $58 $59 $50 $54 $67 $65 925 600 Spokane $78 $80 $65 $52 390 San Diego $85 $82 $70 $66 410 Combined Costs per 1,000 lbs. Terre Haute Dallas NYC Birmingham Spokane San Diego 18 19 20 21 22 23 24 25 26 From/To Philadelphia Atlanta St. Louis Salt Lake City Total 27 Shipped B C D G H J J 20 E F Combined Costs per 1,000 lbs. Terre Haute Dallas 21 NYC Birmingham Spokane San Diego 22 23 24 From/Tol Philadelphia Atlanta St. Louis Salt Lake City 25 26 Total Shipped 27 Decision Variables Terre Haute Dallas From/To NYC Birmingham Spokane San Diego 28 29 30 31 32 Philadelphia Atlanta St. Louis 33 34 35 Salt Lake City Total Supplied: 36 37 Solution Costs NYC Birmingham Terre Haute Dallas Spokane San Diego 38 39 40 41 From/Tol Philadelphia Atlanta St. Louis Salt Lake City 42 43 Total Solution Cost