COMP4041-LDO Workshop 5 Network Flow Optimization

Purpose: Write and solve LP models for various network flow optimization problems using both LP-Solve and the Excel solver.

The Excel file for this workshop contains two spreadsheet models (not yet complete) for the MINIMUM COST FLOW problem from the Lecture 5 notes. Please complete both models and corroborate the optimal solution by also solving the problem with LP- Solve. Note that each of these two spreadsheet models have different layout but the corresponding LP-Solve model should be exactly the same for both.

The Excel file for this workshop contains a spreadsheet model (not yet complete) for the MAXIMUM FLOW problem from the Lecture 5 notes. Please complete the model and corroborate the optimal solution by also solving the problem with LP-Solve. Note that the layout of a spreadsheet model can influence not only the visualisation of the problem and solution but also the easiness of setting the parameters in the solver dialog box.

Develop the optimization model in Excel and LP-Solve to solve the following network flow optimization problem.

TAKA Problem

Company TAKA has 3 suppliers of material A1 and the price of each load of A1 from

each supplier is shown in the Table. In addition, each supplier charges a shipping cost per load as shown also in the Table. Shipments from the suppliers go to one of the 2 warehouses of the company and the distances in miles are shown in the Table. Each supplier can provide up to 10 loads of A1 per month.

Price Per Load of A1 22.5 22.7 22.3

Shipping Charge Per Load of A1 30+0.4/mile 20+0.5/mile 50+0.2/mile

Distance To Warehouse 1 150

Distance To Warehouse 2 40

Then, the company transports loads of material A1 from its warehouses to its 2 factories. The monthly demand of each factory and the transportation costs are given in the Table.

Transportation Cost Per Load of A1

Warehouse 1

Warehouse 2

Monthly Demand (Loads of A1)

There is a limitation on the number of loads that can be transported from a supplier to a warehouse and from a warehouse to a factory: 6 loads per month. The company wants to determine the distribution plan to minimize the total cost.

Develop the optimization model in Excel and LP-Solve to solve the following network flow optimization problem.

AQUEDUCTS Problem

The diagram below depicts the system of aqueducts that originate at 3 rivers (R1, R2 and R3) and terminate at a major town (T), where the other nodes are junction points in the system. The maximum amount of water that can be pumped through each aqueduct per day is indicated. The town water manager wants to determine a flow plan to maximise the flow of water to the town.

E 190 T 130