Network Models Ch 5 in PMS, Ch 19 in VBA

1
Network ModelsCh 5 in PMS, Ch 19 in VBA

• Using this example to illustrate making Excel
  talk to Access

2
Network Models

• Model flow of stuff on arcs between nodes
• Arcs have
• cost per unit flow
• Minimum and/or maximum flow allowed
• Node types
• Supply send stuff out
• Demand get stuff in
• Transshipment pass stuff along
• Modeling art
• Recognizing/figuring out how a problem can be
  modeled as a network

3
Transportation Problem
Transport1.xls
How much flow? (decision variables)
?
?
?
?
?
?
4
Node Types from a Net Flow Perspective
Supply Nodes
Net Outflow Flow out - Flow in gt 0
Net Inflow Flow in - Flow out gt 0
Demand Nodes
Transshipment Nodes
Net Inflow Net Outflow 0
5
Arcs Decision Variables
Minimum amount of flow
Maximum amount of flow
(min12,max12)
1
2
c12
Cost per unit flow
(0,10)
1
2
100
6
Flow Balance Constraints on Nodes
Supply Nodes (producers)
Net Outflow ? S
Total Supply S
Demand Nodes (consumers)
Net Inflow ? D,
Total Demand D
Transshipment Nodes (distributors)
Flow in Flow out
7
Observations
MCNFMMinimum cost network flow model

• Many important network problems are really linear
  programs in disguise (and vice versa)
• Minimize cost of flow on arcs (objective
  function)
• Subject to Flow balance for supply nodes
  Flow balance for demand nodes
• Flow balance on transshipment nodes
• min flowi ? flow on arci ? max flowi (for all
  arcs)

MCNFM

• If supplies, demands, arc capacities are
  integersthen flows on arcs will be integer valued

8
A Few Network Applications

• Vehicle routing
• Supply chain logistics
• Equipment replacement
• Crew scheduling
• Shift scheduling
• Throughput estimation in manufacturing facility

• Production/inventory planning
• Project management (PERT/CPM)
• Computer/communications traffic routing
• A gajillion more

9
A DSS Application based on MCNFM (Ch. 19 VBA)
NetworkFlow.mdb
1. Get node info
2. Get arc info
Main
3. Create model
3.1 Enter arc data
4. Solve problem
5. Create Report
3.2 Form constraints
3.3 Calc cost
10
Database ?Arrays
11
Quick Intro to ADO (19.7)

• Object library for working with databases
• DAO vs. ADO
• Basic steps
• Create connection to database
• Open a recordset object based on table/query
• Navigate recordset object to get at data you need