Model and solve it in excel please

7.19. Aberdeen Computer Corp. (ACC) is located in Aberdeen, Washington. The company has developed Jeeves, a voice-activated Internet assistant for the home. This product is manufactured at four plants, located in Atlanta, Kansas City, Aberdeen, and Austin, After production, the units are shipped to three warehouses, located in Nashville, San Jose, and Houston, ACC sells through the retail channel. In particular, five different retailers currently sell JeevesSears, Best Buy, Fry's, Walmart, and Office Depot. ACC makes weekly shipments to the main warehouses of these five retailers. The shipping cost from each plant to each warehouse, along with the production cost and weekly production capacity at each plant, are given in the table below. Shipping Cost ($/unit) Plant Nashville San Jose Houston Production Cost ($/unit) Capacity (units/week) Atlanta $30 $40 $50 $208 200 Kansas City $25 $45 $40 $214 300 Aberdeen $45 $30 $55 $215 300 Austin $30 $50 $30 $210 400 The shipping cost from each warehouse to cach customer, the variable cost (cost per unit moved through the warehouse), the capacity (maximum number of units that can be moved through the warehouse per week) for each warehouse, and the weekly demand for each customer are given in the table below. Shipping Cost ($/unit) Best Comp Office USA Max Variable Cost ($/unit) Capacity (units/week) Warehouse Sears Buy Fry's Nashville $40 $45 $30 $25 $20 $4 300 San Jose $15 $50 $25 $15 $40 $5 500 Houston $50 $35 $15 $40 $50 $5 500 100 50 75 300 150 Customer demand (per week) a. Formulate and solve a linear programming model in a spreadsheet to determine the plan for weekly production and distribution of Jeeves from the various plants, through the warehouses, to the customers that will minimize total costs. b. Now suppose that ACC is considering saving money by closing some of its production facilities and/or warehouses. Suppose there is a fixed cost to operate each plant and each warehouse as indicated in the following tables. Add binary variables to your model in part a to incorporate the decision of which plants and warehouses to keep open so Page 273 as to minimize total cost including the fixed costs for any plant or warehouse that is operated). a. Formulate and solve a linear programming model in a spreadsheet to determine the plan for weekly production and distribution of Jeeves from the various plants, through the warehouses, to the customers that will minimize total costs. b. Now suppose that ACC is considering saving money by closing some of its production facilities and/or warehouses. Suppose there is a fixed cost to operate each plant and each warehouse as indicated in the following tables. Add binary variables to your model in part a to incorporate the decision of which plants and warehouses to keep open so Page 273 as to minimize total cost (including the fixed costs for any plant or warehouse that is operated). Plant Fixed Cost ($/week) Atlanta $8,000 Kansas City $9,000 Aberdeen $9,000 Austin $10.000 Warehouse Fixed Cost ($/week) Nashville $4,000 San Jose $5,000 Houston $5,000