Maxwell Company currently has one production facility in the Midwest and is concerned about the transportation costs from that facility to markets throughout the United States. It is considering opening other production facilities with the goal of decreasing transportation costs. Demand and costs are shown below in an embedded Excel spreadsheet. Double click to access data.

Worksheet 1 (Midwest): Determine the annual cost of maintaining only the Midwest plant to serve all demand.

Worksheet 2 (New Plants): Determine the low cost method of serving all demand. Using a textbox in the spreadsheet, comment on the advantages and disadvantages of this solution and whether or not you recommend implementing it or maintaining the Midwestern plant only.

Market A Midwest South Production and Transportation Cost per Unit Market B Market C Market D Market E $17.82 $15.18 $15.54 $17.26 $18.10 $17.26 $17.42 $15.18 $17.98 $17.42 $15.58 $15.54 $15.18 $17.82 $17.82 $18.22 700,000 900,000 800,000 1,000,000 $17.82 $18.10 $18.32 $14.86 500,000 Market F $17.46 $15.58 $15.14 $18.10 600,000 Capacity Fixed Cost 5,000,000 $5,000,000 2,000,000 $2,200,000 2,000,000 $2,200,000 2,000,000 $2,200,000 West Northeast Demand Market A Midwest South Production and Transportation Cost per Unit Market B Market C Market D Market E $17.82 $15.18 $15.54 $17.26 $18.10 $17.26 $17.42 $15.18 $17.98 $17.42 $15.58 $15.54 $15.18 $17.82 $17.82 $18.22 700,000 900,000 800,000 1,000,000 $17.82 $18.10 $18.32 $14.86 500,000 Market F $17.46 $15.58 $15.14 $18.10 600,000 Capacity Fixed Cost 5,000,000 $5,000,000 2,000,000 $2,200,000 2,000,000 $2,200,000 2,000,000 $2,200,000 West Northeast Demand