Optimization Project: Network Flow and Excel Dashboard

The following optimization problem is a nice (realistic) business application that students will build into a portfolio (excel document) to develop modeling and analysis skills.

The Bold title should be the name of a different tab in a single Excel workbook.

While there is very powerful software available to solve optimization problems, you will solve all of these on the Excel Solver.  (In many cases, spreadsheet modeling is more efficient than using commercial solvers, although frequently it is not as effective.)

Optimization Project Descriptions

Each problem should be its own tab in Excel
    
NOTE:  Many of the data tables in this document can be readily pasted into excel.

 
Network Flow (and the Excel Dashboard)

The U.S. natural gas pipeline network is a highly integrated transmission and distribution grid that can transport natural gas to and from nearly any location in the lower 48 States. The natural gas pipeline grid comprises: 
More than 210 natural gas pipeline systems. 
305,000 miles of interstate and intrastate transmission pipelines. 
More than 1,400 compressor stations that maintain pressure on the natural gas pipeline network and assure continuous forward movement of supplies. 
More than 11,000 delivery points, 5,000 receipt points, and 1,400 interconnection points that provide for the transfer of natural gas throughout the United States. 
24 hubs or market centers that provide additional interconnections. 
400 underground natural gas storage facilities. 
49 locations where natural gas can be imported/exported via pipelines. 
8 LNG (liquefied natural gas) import facilities and 100 LNG peaking facilities. 
This project will only analyze a miniscule subset of this massive system, focusing on only one of the local networks.  It will, however, use the same techniques that are used to solve the Nation’s LNG network flow problem on a constant basis.

Old Dominion Energy’s pipeline network between its natural gas depots allows it to transport gas between eleven different storage facilities for use in power generation.  Flow among the pipes is bi-directional.  The transmission capacities of the pipes range from 10,000 cubic feet per day to 50,000 cubic feet per day.  

These are costs associated with shipping LNG along the pipelines, where the costs range from $.28 per 1000 cubic feet to $.52 per 1000 cubic feet.

When ODE has a need for LNG at one of their locations, they look for excess capacity at their other locations, and then must determine if it is economical to meet the demand by shipping along their pipeline system.  This is based on what the receiving locations are willing to pay (per 1000 cubic feet) and what the costs will be to ship through the pipeline system.

If it is economical to ship the gas, then the optimal path may be determined in order to maximize profit.

Figure 1 shows the pipeline system, with the pipeline capacities (X 1000 cubic feet) and the costs (in dollars per 1000 cubic feet) to transport gas along the pipelines.

REQUIREMENT:  Build an Excel “Dashboard” that ODE can use to determine whether or not they should meet a demand with their own pipeline system and unused capacity, and what set of pipes and stations they should use to transport the gas.  The Dashboard should have the following characteristics:

1.  Gas demands should be entered for each station (in 1000 cu. ft.). 
2.  Excess capacity should be entered for each station (in 1000 cu. ft.).
3.  The price each station is willing to pay should be entered (in $/1000cuft).
4.  The Dashboard should include instructions for implementing the Excel Solver to obtain a solution.
5.  The output should include the profit achieved by transporting the gas.
6.  The output should include the pipelines used and amounts to be transported along each pipeline.
7.  The output should include any unmet demand.

Use the dashboard to solve the following scenario:

ODE currently has 100,000 cf of gas in storage at Katy.  Customers in Joliet are willing to pay $4.35 per thousand cf for up to 35,000 cf of gas, and customers in Leidy are offering $4.63 per thousand cf for up to 60,000 cf of gas.  

Ensure the model can be used to analyze other scenarios by changing the values of excess supply, demand, and payment and then re-running the optimizer.