In Problems 10.11 and 10.12, it was necessary to change the equation of the objective function and/or a constraint equation. If this model were to be used to explore a variety of possible scenarios, then this method would be time consuming and error-prone. Modify your spreadsheet for this problem so that all of the input quantities (capacities, production costs, order quantities, customer prices, and shipping costs) are entered at the top of the sheet, as in the shaded cells in Figure P10.13. Modify your equations to show the total revenue (sales), cost, and profit associated with each plant/customer combination, as well as the total sales, cost, and profit for the week. Also add cells to show the total production for each plant, the pounds shipped to each customer, the excess capacity of each plant, and the profit broken down by plant and by customer. Modify your Solver model so that the total profit is maximized by changing the volumes shipped from each plant to each customer. Modify the constraint equations so that they refer to the input values of capacities and sales. These changes will enable you to leave your Solver model unchanged when you change one of the input parameters.

Check your modified spreadsheet by verifying the solution to the scenario presented in Section 10.6. Use your spreadsheet to evaluate the change to the weekly profit if:

Problems 10.11

Revisit the production scheduling problem shown in Section 10.6. An engineer at the Portland plant implements a process improvement that will result in a production cost savings of $0.10 per gallon of adhesive. How will this change the optimal production plan?

Problems 10.12

Revisit the production scheduling problem shown in Section 10.6. West Coast Corp. has offered to increase their order to 22,000 gallons per week, but is demanding a $0.50 per gallon price reduction. How will this change the optimal production plan?

a. production capacity at the New Orleans facility is temporarily cut in half, to 9,000 lb/week

b. Texas Manufacturing Company increases their order to 18,000 lb/week

c. a new labor contract with employees in Buffalo increases production costs to /lb