2) A company has six plants that can produce liquid sanitizing products with the characteristics shown...

Transcribed Image Text:

2) A company has six plants that can produce liquid sanitizing products with the characteristics shown in the following table. For the next production period, the manager must decide which plant to activate and the amount to produce (for plants that are activated). To produce any amount at a given plant, the plant must be activated and a fixed startup cost is incurred. The total production volume for the next production period must be at least 9,500 gallons. Fixed Startup Costs Variable Cost ($/gallons) Max. Capacity (gallons) Plant A $3,200 $1.50 1570 Plant B $4,300 $1.25 2950 Plant C $2,150 $1.75 3200 Plant D $3,750 $1.75 1400 Plant E $2,400 $1.00 3700 Plant F $1,900 $1.30 2550 Formulate a spreadsheet model for this problem, then use Solver to solve and determine the optimal solution that will minimize their overall costs. For full credit, the model must be linear except for the use of binary variables (i.e., no multiplication of changing cells, no IF statements, no MAX statements, etc.). Note: Do not include the integer constraints in the model. 2) A company has six plants that can produce liquid sanitizing products with the characteristics shown in the following table. For the next production period, the manager must decide which plant to activate and the amount to produce (for plants that are activated). To produce any amount at a given plant, the plant must be activated and a fixed startup cost is incurred. The total production volume for the next production period must be at least 9,500 gallons. Fixed Startup Costs Variable Cost ($/gallons) Max. Capacity (gallons) Plant A $3,200 $1.50 1570 Plant B $4,300 $1.25 2950 Plant C $2,150 $1.75 3200 Plant D $3,750 $1.75 1400 Plant E $2,400 $1.00 3700 Plant F $1,900 $1.30 2550 Formulate a spreadsheet model for this problem, then use Solver to solve and determine the optimal solution that will minimize their overall costs. For full credit, the model must be linear except for the use of binary variables (i.e., no multiplication of changing cells, no IF statements, no MAX statements, etc.). Note: Do not include the integer constraints in the model.