Production&Inventory by akgame

VIEWS: 3 PAGES: 3

• pg 1
```									Optimization Network

Production and Inventory Planning
Cumulative Shipments Supply to June (Reg) Required Shipments -99 27 15 13 Supply to July (Reg) Supply to June (OT) Borrow August to July 0 0 1 -1 99 \$10 2 Supply to July (OT) Supply to August (Reg) Supply to August (OT) Storage July to August Storage June to July Borrow July to June 0 1 -1 0 99 \$10 7

Supply June July August Capacity Cost Shipments Total Cost

-1 1 0 0 15 \$100 15 \$5,790

-1 1 0 0 5 \$120 5

-1 0 1 0 15 \$100 15

-1 0 1 0 5 \$120 5

-1 0 0 1 15 \$100 15

-1 0 0 1 5 \$120 0

0 -1 1 0 99 \$8 0

0 0 -1 1 99 \$8 0

-55 27 15 13

Target Cell: Sumproduct of Cost & Shipments -1 = shipment sent 0 = Not affected 1 = shipment received

The Shipment Row is changing data that represents the

Solver: Target Cell: Total Cost (Minimize) Changing Cells: Shipment Row Constraints: Shipments <= Capacity Cumulative Shipments >= Required Shipments

The Cumulative Shipments Column is a Result Column that reflects the sumproduct of the Shipment Row and the Corresponding (1, 0 , -1) Row

Raw Data given in the problem. The "99" represents an infinite number that the will ultimately not affect the solver

JUNE

Regular \$100
\$120 SUPPLY OT

0-15 0-5 \$100
borrow \$10 0-15 JULY 0-5 storage \$10 \$8

\$120

\$100
\$120 0-15

Problem: A company has signed contract to deliver 27 units of their product in June, 15 units in July, and 13 units in August. They begin June with no inventory. They can produce 15 units per month on regular time shifts at a unit cost of \$100 and 5 additional units per month on OT at a unit cost of \$120. They can store units at \$8/unit/month. They also have the option to "borrow" units from a similar producer at a cost of \$10/unit/month until they can repay him in units from their own production. This allows them to deliver units ahead of the time they produce them. They wish to minimize total cost.

\$8 Answer: Found in Shipments Row June: 15 (Reg) + 5 (OT) + 7 (Borrow) = 27 units July: 15 (Reg) - 7 borrowed + 5 OT + 2 (Borrow) = 15 units Aug: 15 (Reg) - 2 (Borrow) = 13 units

0-5

AUGUST

Microsoft Excel 9.0 Sensitivity Report Worksheet: [Chapter19Production&Inventory.xls]Commuter Traffic Report Created: 2/7/02 12:00:21 AM Bottlenecks: Whenever the Reduced Cost is Adjustable Cells Cell \$B\$12 \$C\$12 \$D\$12 \$E\$12 \$F\$12 \$G\$12 \$H\$12 \$I\$12 \$J\$12 \$K\$12 \$L\$12 \$M\$12 \$N\$12 \$O\$12 \$P\$12 \$Q\$12 Constraints Final Shadow Cell Name Value Price \$R\$2 Downtown Cumulative 0 1 \$R\$3 2 Cumulative 0 1 \$R\$4 3 Cumulative 0 0 \$R\$5 4 Cumulative 0 0 \$R\$6 5 Cumulative 0 0 \$R\$7 6 Cumulative 0 0 \$R\$8 7 Cumulative 0 0 \$R\$9 8 Cumulative 0 0 \$R\$10 Suburbia Cumulative 0 0 Constraint R.H. Side 0 0 0 0 0 0 0 0 0 Allowable Increase 50 50 50 50 50 50 50 0 50 Allowable Decrease 0 0 0 0 0 0 0 0 0 Route D2 D3 D4 25 36 45 47 46 5S 58 57 78 67 6S 8S SD Final Reduced Objective Value Cost Coefficient 250 0 0 100 1 0 600 1 0 250 1 0 100 0 0 200 0 0 200 0 0 200 0 0 250 0 0 200 0 0 0 0 0 500 0 0 300 0 0 0 0 0 700 0 0 950 0 1 Allowable Increase 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Allowable Decrease 1 1 1 1 1 0 1 0 0 0 0 0 0 0 0 1

Transportation Network Model Commuter Traffic
Downtown 2 3 4 5 6 7 8 Suburbia Capacity Traffic Flow D2 -1 1 0 0 0 0 0 0 0 300 250 D3 -1 0 1 0 0 0 0 0 0 100 100 D4 -1 0 0 1 0 0 0 0 0 600 600 25 0 -1 0 0 1 0 0 0 0 250 250 36 0 0 -1 0 0 1 0 0 0 600 100 45 0 0 0 -1 1 0 0 0 0 200 200 47 0 0 0 -1 0 0 1 0 0 500 200 46 0 0 0 -1 0 1 0 0 0 200 200 5S 0 0 0 0 -1 0 0 0 1 300 250 58 0 0 0 0 -1 0 0 1 0 200 200 57 0 0 0 0 -1 0 1 0 0 250 0 78 0 0 0 0 0 0 -1 1 0 500 500 67 0 0 0 0 0 -1 1 0 0 300 300 6S 0 0 0 0 0 -1 0 0 1 400 0 8S 0 0 0 0 0 0 0 -1 1 750 700
Target Cell

SD 1 0 0 0 0 0 0 0 -1 9999 950

Cumulative 0 0 0 0 0 0 0 0 0

Bottlenecks: See Sensitivity Report
-1 = traffic leaving 0 = Not affected 1 = traffic arriving

Problem: The network diagram shows the major thoroughfares in a congested urban area. The numbers beside each link show the number of vehicles per hour that can pass through each thoroughfare. Determine the maximum number of vehicles per hour that can pass from downtown to suburbia and identify the bottleneck.

KEY POINT: Conceptually, a new column is inserted (SD) or Surburbia to Downtown to create a Circulation Matrix. The vehicles are not actually traveling from Surburbia back to the Downtown this just allows solver to count all the cars that can go through the entire system in an hour.

sumproduct (data rows, traffic flow)

an articifically high number is inserted here so solver will not be constrained by it.

Solver: Target Cell: Traffic Flow SD (Maximize) Changing Cells: Entire Traffic Flow Row Constraints: Traffic Flow <=Capacity Cumulative = 0 [no cars are allowed to stop]

2 300

250 200

5 250 200 7 300 6 500 300 8 400 750

Rows represent the boxes and Columns represent the arrows in the diagram

Downtown 100

600

4

500 200

Suburbia

3

600

```
To top