Example 15.3 Supplying Power at Midwest Electric

1. Example 15.3Supplying Power at Midwest Electric Logistics Model

2. Objective To use LP to find the least-cost method of sending power from supplying plants to customers.

3. Background Information • Midwest Electric has three electric power plants that supply the power needs of four cities. • Each power plant can supply the amounts shown here (in millions of kilowatt-hours of electricity).

4. Background Information -- continued • The peak power demand (again in millions of kwh) at each city is given in the following table: • Finally, the cost (in dollars) of sending 1 million kwh from each plant to each city is given in the next table.

5. Background Information -- continued • Midwest Electric wants to find the lowest-cost method for meeting the demands of the four cities.

6. Solution • To set up a spreadsheet model for Midwest Electric’s power distribution problem we need to keep track of the following: • power shipped (in millions of kwh) from each plant to each city • total power shipped out of each plant • total power received by each city • total shipping cost incurred

7. TRANSPORT1.XLS • The spreadsheet model for this problem appears below and can be developed by following these steps:

8. Developing the Model • Inputs. Enter the unit shipping costs for each plant to each city in the UnitCost range. Also, enter the capacities of the plants in the Capacity range and the demands for the cities in the Demand range. • Amounts shipped. Enter any trial values for the shipments from each plant to each city in the Shipped range. These are the changing cells. • Shipping totals. Calculate the amounts shipped out of the various plants in the TotShipped range by summing across rows of the Shipped range. Similarly, calculate the amounts shipped into the various cities in the Received range by summing down columns of the Shipped range.

9. Developing the Model -- continued • Total shipping cost. Calculate the total cost of shipping power from the plants to the cities in the TotCost cell with the formula=SUMPRODUCT(C6:F8,Shipped)The formula simply sums all products of unit shipping costs and amounts shipped.

10. Using Solver • The Solver dialog box is quite straightforward, as shown. We minimize shipping cost, subject to staying within capacities and meeting demands.

11. Using Solver • The optimal solution of the model is illustrated graphically. A minimum cost of $1020 is incurred by using the shipments listed. Except for these six routes listed, no other routes are used.

12. Sensitivity Analysis • In the current model note that total capacity for the plants equals total demand for the cities. Therefore, capacities are all used up, and demands are met exactly. • An interesting sensitivity analysis is to see how total cost decreases if capacities increase. • However, instead of increasing a single plant’s capacity, we’ll increase them all at once by the same amount.

13. Sensitivity Analysis -- continued • This presents a problem for SolverTable because it is able to vary at most two inputs simultaneously. • However, it is possible to work around this constraint.

14. TRANSPORT2.XLS • The spreadsheet model shows the constraint work around.

15. Work Around • The trick is to create a new input in cell C24 with initial value 0. • The Capacity range is then modified to account for this extra capacity. • For example, we enter =35+C24 in cell I13 and similar formulas in I14 & I15. Then we set up the table in rows 27-30 with C24 as the column input cell. • When extra capacity is 10, each plant obtains an extra capacity of 10. The optimal total cost decreases by $15 for each increment of 5 for extra capacity.