Excel VBA Please:

Strategy:

1. Place a command button on this worksheet. Link the command button to your main sub.

2. Build a subroutine that uses Solver to solve the current problem as it is.

3. In the worksheet Scenarios count the number of Scenarios to be run (Use Range and .end(xlToRight) to find the number of scenarios.

4. Use a For Next Loop to go through the different scenarios.

For each Scenario do the following:

5. Copy the plant capacities in the Scenarios worksheet to cells I13, I14, and I15, respectively, in the Model worksheet.

6. Copy the city demands in the Scenarios worksheet to cells C18, D18, E18, and F18, respectively, in the Model worksheet.

7. Solve the Problem by calling the Solver subroutine built in step 2.

8. Copy the results in the range C13:F15 in the Model worksheet to the Results worksheet (Note that the results for each scenario need 6 rows)

9. Copy the Total Cost in cell B20 in the Model worksheet to the Results worksheet (Note that the results for each scenario need 6 rows)

Go to the next Scenario.

You can use "Scenario " & i in the For Next loop to enumerate the scenarios.

Write code assuming that the number of plants is 3 and the number of cities is 4, but the number of scenarios is dynamic and can change.

Model Tab:

Shipments and total cost for each scenario Scenario 1 Shipments Total cost 0 10 25 0 $1,020 45 0 5 0 0 10 0 30 Scenario 2 Possible scenarios Scenario 1 Scenario 2 Scenario 3 Scenario 4 Scenario 5 Capacities Plant 1 35 Plant 2 50 Plant 3 40 Demands City 1 45 City 2 20 City 3 30 City 4 30 65 60 70 60 40 25 35 45 50 70 40 30 20 50 50 70 60 25 35 40 35 70 75 45 30 55 40 60 Transportation model Range names used: Capacities =Model!$1$13:$1$15 Unit shipping costs Objective Function: Minimize Total Cost Demands =Model!$C$18:$F$18 To Shipped =Model!SC$13:SF$15 Total Cost = Sum of C, * X;, City 1 City 2 City 3 City 4 ShippedIn =Model!$C$16:$F$16 From Plant 1 $6 $10 59 ShippedOut =Model!$G$13:$G$15 Constraints: Plant 2 59 $12 $13 $7 TotalCost =Model!SB$20 Plant 3 $14 59 $16 $5 UnitCosts =Model!$C$6:$F$8 Xu + kjy + Xia+ X14 = City 1 Demand From Plant 1 D 10 25 35 35 X1 + X2 + Xgy >= City 2 Demand Plant 2 45 0 5 50 50 Xin + X23 + X3) >= City 3 Demand Plant 3 10 0 30 40 40 X14 + X24 + Xyq >= City 4 Demand Total received 45 20 OE 30 3= 3= X, >=0 Demand 45 20 30 30 where: Caj = Shipping Cost per Unit from Plant ito City j Total cost $1,020 Note: Objective function equation is in gray. Strategy: 1. Place a command button on this worksheet. Link the command button to your main sub. 2. Build a subroutine that uses Solver to solve the current problem as it is. Note: Decision variables (changing values) are in 3. In the worksheet Scenarios count the number of Scenarios to be run (Use Range and .end(xIToRight) to find the number of scenar green 4. Use a For Next Loop to go through the different scenarios. For each Scenario do the following: Note: Righthand side of constraints (alternative 5. Copy the plant capacities in the Scenarios worksheet to cells 113, 114, and 115, respectively, in the Model worksheet. scenario values) are in light blue 6. Copy the city demands in the Scenarios worksheet to cells C18, D18, E18, and F18, respectively, in the Model worksheet. 7. Solve the Problem by calling the Solver subroutine built in step 2