Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Demand (units/year) Plant 1 Plant 2...

Transcribed Image Text:

Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Demand (units/year) Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Excess Capaity Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Total Cost Market 1 Market 1 0 0 0 0 0 Market 2 Market 3 $600.00 $1,300.00 $2,000.00 $1,400.00 $300.00 $1,200.00 $1,400.00 $2,100.00 $1,300.00 $600.00 $2,000.00 $1,400.00 $1,300.00 $1,000.00 $2,200.00 270 200 120 Market 2 0 0 0 0 0 Market 3 0 0 0 0 0 Unmet Demand Market 1 Market 2 Market 4 Market 5 $1,200.00 $1,700.00 $1,400.00 $1,300.00 $2,100.00 $900.00 $800.00 $2,100.00 $2,300.00 $800.00 100 190 Market 4 0 0 0 0 0 Market 3 Market 5 0 0 0 0 0 Market 4 Capacity tons/Year Open? 0 0 0 0 0 Market 5 Annual Fixed Cost 200.00 475.00 50.00 400.00 $2,000,000 $3,576,750 $837,000 $2,922,400 160.00 $1,472,000 INSTRUCTIONS The goal of the assignment is to determine which plants should be openand which markets they should serve by minimizing total costs. 1. Enter the Excess Capacity and Unmet Demand formulas in the spreadsheet. 2. Using the Sum Product function enter the total cost formula. 3. In Solver: a. Specify the objective function b. Specify the change cells c. Enter the 4 needed contraints d. Make sure the Ignore Integer Constraints option is deselected. 4. Solve the problem. It should solve using Simplex LP. 5. Analyze potential problems with the solution you developed. Type your analysis in a textbook on this spreadsheet. This is the critical step in your homework. Make sure you do a careful, thoughtful analysis. Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Demand (units/year) Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Excess Capaity Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Total Cost Market 1 Market 1 0 0 0 0 0 Market 2 Market 3 $600.00 $1,300.00 $2,000.00 $1,400.00 $300.00 $1,200.00 $1,400.00 $2,100.00 $1,300.00 $600.00 $2,000.00 $1,400.00 $1,300.00 $1,000.00 $2,200.00 270 200 120 Market 2 0 0 0 0 0 Market 3 0 0 0 0 0 Unmet Demand Market 1 Market 2 Market 4 Market 5 $1,200.00 $1,700.00 $1,400.00 $1,300.00 $2,100.00 $900.00 $800.00 $2,100.00 $2,300.00 $800.00 100 190 Market 4 0 0 0 0 0 Market 3 Market 5 0 0 0 0 0 Market 4 Capacity tons/Year Open? 0 0 0 0 0 Market 5 Annual Fixed Cost 200.00 475.00 50.00 400.00 $2,000,000 $3,576,750 $837,000 $2,922,400 160.00 $1,472,000 INSTRUCTIONS The goal of the assignment is to determine which plants should be openand which markets they should serve by minimizing total costs. 1. Enter the Excess Capacity and Unmet Demand formulas in the spreadsheet. 2. Using the Sum Product function enter the total cost formula. 3. In Solver: a. Specify the objective function b. Specify the change cells c. Enter the 4 needed contraints d. Make sure the Ignore Integer Constraints option is deselected. 4. Solve the problem. It should solve using Simplex LP. 5. Analyze potential problems with the solution you developed. Type your analysis in a textbook on this spreadsheet. This is the critical step in your homework. Make sure you do a careful, thoughtful analysis.