# Excel Transportation Budget - DOC by fou24498

VIEWS: 336 PAGES: 13

• pg 1
```									          EMBA – LP: Using Excel; Transportation & Assignment; Integer Programming

Learning goals

Modeling in Excel using Solver
 Understand the importance of optimization (linear programming) via Excel’s Solver
 Understand how to model simple linear programs using Excel and Solver
 Understand the power of Solver to perform nonlinear and integer programming

The Transportation Model
 Understand the structure and assumptions of the transportation model
 Understand the relationship between the transportation model and linear programming
 Understand the advantage of using a more general model (linear programming) rather
than a specific model (transportation)

The Assignment Model
 Understand the structure and assumptions of the assignment model
 Understand the relationship between the assignment model and the transportation model
and thereby linear programming
 Understand the advantage of using a more general model (transportation or linear
programming) rather than a specific model (transportation)

Integer Programming
 Understand the slight difference between modeling linear programs and integer linear
programs
 Understand the major difference between the solution of linear programs and integer
programs
 Understand the additional modeling we can do using 0/1 variables
 Understand some standard integer programming models (capital budgeting, set covering)

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 1
EMBA – LP: Using Excel; Transportation & Assignment; Integer Programming
Agenda

FRIDAY
1. Interfaces Presentations
Guess, Sharon               Against Your Better Judgement? How Organizations Can Improve Their Use of Management Judgem
JACOBSON, RANDOLPH S        Contract Optimization at the Texas Children's Hospital,
REYES, CESAR A              Warner Robins Air Logistics Center Streamlines. Aircraft Repair and Overhaul
2. Material we did not get to last time - We will use our QM for Windows Lego.lin file for the
following
 Graph
 Possible outcomes
o Unique solution (original example)
o Multiple solutions (change profit from 17 to 20)
o Unbounded solution (change <= constraints to >= constraints)
o No feasible solution (add constraint # tables >=10)
3. Lecture – (File: lp.excel.trans.assign.xls)
Part 1 – Creating linear programming models in Excel (Worksheet: Lego)
Software: Excel including Excel’s Solver add-in (see Tools menu or Data tab)
Excel – recreate the Lego problem in Excel
– examine Solver’s reports
– interpretation of the dual in more detail (Worksheet: Lego – revisited)
– demonstration of using Solver for nonlinear problems (Worksheet: errors–
revisited)
Part 2 – The Transportation and Assignment Problems
Software: QM for Windows and Excel QM
Transportation Model
Description (Example 1 – page 4)
Model
Supplies, demands, shipping costs
Solution
Starting method
Shipments
Marginal costs (improvement values)
Unbalanced problems - (Example 2)
Suppose that the demand at Houston drops by 10 units
More modeling – (Example 3)
Suppose that shipping from Austin to Ft. Worth is not permitted
QM for Windows, Excel QM
Formulation as linear program (see page 5)
Results, Reduced costs, Dual Values
Assignment
Description (Example 2 – page 5)
Model
0/1 variables
Solution values
formulation as transportation model (Example 3)
formulation as linear program by transitivity
4. Return Forecasting Projects (forecasting and LP)
1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 2
SATURDAY

5. Interfaces presentations

NBC-Universal Uses a Novel Qualitative Forecasting Technique to Predict
Integrating Excel, Access and Visual Basic to Deploy Performance
DEVINE, JOHN G                   Measurement and Evaluation at the American Red Cross.

6. Lecture
Part 3 - Integer Programming
Model – Example 1 – page 9
Does rounding off the LP solution to integer answers solve the Int. Prog.?
How do the LP and IP solution methods compare?
Solution as Integer Program via QM for Windows
Comparison to LP - The iterations
Capital Budgeting – Example 2
Set Covering – Example 3

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 3
Transportation examples

Example 1

A Texas company is shipping units from three factories to 5 distribution centers. The supply at
each of the factories and the demands at each of the centers are given. The total supply and the
total demand are equal. The cost to ship one unit from each factory to each distribution center is
given. The information is below.

Unit shipping                         Fort                              Supply
costs Dallas El Paso         Worth Galveston Houston           (units)
Austin    \$20      \$48           \$10      \$30     \$40                 100
Beaumont      \$70      \$60           \$55      \$90     \$70                   80
Corpus Christi    \$45      \$80           \$50      \$30     \$60                 150
Demand
(units)    50       70              65         55         90   330\330

Either QM for Windows or Excel QM will find the shipments that should be made to minimize
the total shipping cost but QM for Windows will give us some extra useful information so we
will use QM for Windows.

Example 2 – unbalanced problems

Consider the problem above but suppose the demand at Houston is 80 rather than 90.

Example 3 – more modeling

Suppose that shipping from Austin to Ft. Worth is not permitted.

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 4
Making assignments examples

Example 1

The records of five clients must be examined by one of five accountants. Since the clients’ needs and
the accountants’ skills are different, the lengths of time for any accountant to work on the records for
any client vary. The table below contains the time (in hours) for each accountant working on each
client.

Accountant Accountant Accountant Accountant Accountant
A          B          C          D          E
Client 1            16         28         36         18         20
Client 2            32         32         35         24         16
Client 3            16         10         26         20         10
Client 4            31         20         37         17         11
Client 5            32         44         23         37         13

1. By hand or in Excel determine which accountant should work for each client in order to
minimize the total amount of hours that the work for the 5 clients need.
2. What is the total time that is needed?

Now in an assignment problem, each accountant must have one client and each client must have one
accountant. This makes the problem somewhat more difficult but we have software that solves this
easily.

Example 2 - The assignment problem

3. Which accountant should work for each client in order to minimize the total amount of hours
that the work for the 5 clients need?
4. What is the total time that is needed?

Example 3, relaxed rules

Suppose we relax the rules and allow any accountant to work on up to two clients. That is, our total
demand will be 5 jobs (clients) but our total supply will be 10 (account-jobs). This now is a
transportation problem.

5. Which accountant should work for each client in order to minimize the total amount of hours
that the work for the 5 clients need?
6. What is the total time that is needed?

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 5
Module/submodel: Transportation
Problem title: Example
Starting method: Any starting method
Objective: Minimize
Data and Results ----------

Original Data
Fort                          Supply
Dallas    El Paso   Worth     Galveston Houston   (units)
------------------------------------------------------------------------------
Austin              20        48        10        30        40        100
Beaumont            70        60        55        90        70        80
Corpus Christi      45        80        50        30        60        150
Demand (units)      50        70        65        55        90

Shipments

Fort
Dallas    El Paso   Worth     Galveston Houston
-----------------------------------------------------------------------
Austin              35                  65
Beaumont                      70                            10
Corpus Christi      15                            55        80
Total cost = 13375

Marginal Costs ----------

Fort
Dallas    El Paso   Worth     Galveston Houston
-----------------------------------------------------------------------
Austin                        23                  25        5
Beaumont            15                  10        50
Corpus Christi                30        15

Module/submodel: Linear Programming
Objective: Maximize
Problem and Results ----------
Note: AD = # of units shipped from A to D; other variables have similar
definitons

OPTIMIZE: 20AD + 48AE + 10AF + 30AG + 40AH + 70BD + 60BE + 55BF + 90BG + 70BH
+ 45CD + 80CE + 50CF + 30CG + 60CH
Supply at A: 1AD + 1AE + 1AF + 1AG + 1AH <= 100
Supply at B: 1BD + 1BE + 1BF + 1BG + 1BH <= 80
Supply at C: 1CD + 1CE + 1CF + 1CG + 1CH <= 150
Demand at D: 1AD + 1BD + 1CD = 50
Demand at E: 1AE + 1BE + 1CE = 70
Demand at F: 1AF + 1BF + 1CF = 65
Demand at G: 1AG + 1BG + 1CG = 55
Demand at H: 1AH + 1BH + 1CH = 90

Solution from QM for Windows
AD AE AF AG AH BD BE BF BG BH CD CE CF CG CH     RHS Dual
Minimize       20 48 10 30 40 70 60 55 90 70 45 80 50 30 60
Supply at A     1 1 1 1 1                                   <=   100 35
Supply at B                    1 1 1 1 1                    <=    80   0
Supply at C                                   1 1 1 1 1 <=       150 10
Demand at D     1              1              1             =     50 -55
Demand at E        1              1              1          =     70 -60
Demand at F           1              1              1       =     65 -45
Demand at G              1              1              1    =     55 -40
Demand at H                 1              1              1=      90 -70
Solution->     35 0 65 0 0 0 70 0 0 10 15 0 0 55 80            13375

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 6
Ranging Table
Reduced         Original    Lower             Upper
Variable       Value         Cost            Val         Bound             Bound
AD                     35               0           20                10              25
AE                      0              23           48                25   Infinity
AF                     65               0           10   =-Infinity                   20
AG                      0              25           30                 5   Infinity
AH                      0               5           40                35   Infinity
BD                      0              15           70                55   Infinity
BE                     70               0           60   =-Infinity                   83
BF                      0              10           55                45   Infinity
BG                      0              50           90                40   Infinity
BH                     10               0           70                60              80
CD                     15               0           45                40              55
CE                      0              30           80                50   Infinity
CF                      0              15           50                35   Infinity
CG                     55               0           30   =-Infinity                   55
CH                     80               0           60                50              65
Dual                          Original    Lower             Upper
Constraint     Value         Slack/Surplus   Val         Bound             Bound
Supply at A             35               0         100            100                 110
Supply at B              0               0          80             80      Infinity
Supply at C             10               0         150            150                 160
Demand at D            -55               0          50             40                  50
Demand at E            -60               0          70              0                  70
Demand at F            -45               0          65             55                  65
Demand at G            -40               0          55             45                  55
Demand at H            -70               0          90             80                  90

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 7
h

C:\Documents and Settings\Howard\My Documents\Courses\MSOMEMBA\Linear
Programming\EXAMPLE.ASS
02-02-2006 15:54:19

Module/submodel: Assignment
Problem title: (untitled)
Objective: Minimize

Data and Assignments ----------

Original Data
Accountant Accountant Accountant Accountant Accountant
A           B           C           D           E
----------------------------------------------------------------------
Client 1 16          28          36          18          20
Client 2 32          32          35          24          16
Client 3 16          10          26          20          10
Client 4 31          20          37          17          11
Client 5 32          44          23          37          13

Assignments

Accountant Accountant Accountant Accountant Accountant
A           B           C           D           E
----------------------------------------------------------------------
Client 1 ASSIGN
Client 2                                                 ASSIGN
Client 3             ASSIGN
Client 4                                     ASSIGN
Client 5                         ASSIGN

Total cost = 82

Marginal Costs ----------

Accountant Accountant Accountant Accountant Accountant
A           B           C           D           E
----------------------------------------------------------------------
Client 1             7           5                       8
Client 2 12          7                       2
Client 3 11                      6           13          9
Client 4 16                      7
Client 5 24          31                      27          9

Assignment List ----------

Row      Column         Cost
-------------------------------------
Client 1 Accountant A   16
Client 2 Accountant E   16
Client 3 Accountant B   10
Client 4 Accountant D   17
Client 5 Accountant C   23

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 8
Integer Programming

Example 1
max           2x + 3y
st           195x + 273y <= 1365
4x + 40y <= 140
x            <= 4
x, y >= 0
x,y integer

(QM for Windows – output is on page 11)

NOTE: In this example closest integer is not the optimal integer.
NOTE: Look at iterations (Method is branch and bound) each subproblem is a linear
program
NOTE: A sensitivity report is not meaningful for problems with integer constraints

Example 2 - Capital Budgeting (0/1 variables)

A company has the opportunity to invest in n projects. Each of the projects has a cost and yields a
return. Either the entire investment is made or none at all. The company has a total budget that
cannot be exceeded. The goal is to invest the capital in a way that maximizes the return.

An example with 5 projects is given below. For example, project 1 requires an investment of
\$20,000 and returns \$28,000 (a profit of 40%).

a) Model this situation as an integer program.
b) Suppose that if project 2 is selected then project 3 can not be selected. Write the
constraint for this.
c) Suppose that if project 1 is selected then project 4 must be selected. Write the constraint
for this.

Cost          Return
Project       (thousands)   (thousands)
1            20            28
2            40            50
3            70            82
4            50            56
5            30            33

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 9
Example 3 – Set Covering

Vohra, Walling, Zhang (VWZ) is a private corporation that builds and manages drug
rehabilitation centers. VWZ is currently trying to get a contract for the state of Pennsylvania.
VWZ has divided the state into seven regions and is considering five possible sites for the
treatment centers. One of the requirements of the state is that each region will be within 1.5 hours
driving distance of at least one of the centers. The table below indicates which possible centers
are within 1.5 hours of each region and the cost to build the center.

Possible center     Philadelphia       Pittsburgh        Harrisburg         Scranton           Erie
Region
1                                       yes                                                     yes
2                    yes                                  yes                yes
3                    yes                                  yes
4                                       yes
5                    yes
6                                                         yes                yes                yes
7                    yes                                  yes
Cost (\$000)          400                250               500                200                350

Integer Programming Software Notes

QM for Windows
Integer programming (all variables must be integers)
Mixed integer programming (variables may be real, integer or 0/1)
Excel
Integers or 0/1 (binary) variables are entered through the constraints

NOTE: Sensitivity report is not meaningful for problems with integer constraints.

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 10
C:\Courses\MSOMEMBA\EXAMPLE.INT                  02-06-2001   13:26:22

Module/submodule: Integer Programming
Problem title: Integer Programming Example
Objective: Maximize

Results ----------

Solution value
14

Variable     Value
x             4
y             2

Iteration Results ----------

Iteration Level      constraint type       value      x          y
----------------------------------------------------------------------------
Optimal     14         4          2
----------------------------------------------------------------------------
1        0                    NONinteger 14.65116    2.4419     3.2558
2        1         x<= 2      NONinteger 13.9        2          3.3
3        2         y<= 3      INTEGER     13         2          3
4        2         y>= 4      Infeasible
5        1         x>= 3      NONinteger 14.57143    3          2.8571
6        2         y<= 2      INTEGER     14         4          2
7        2         y>= 3      Infeasible

x          y                     RHS
----------------------------------------------------------
Maximize      2          3
Constraint 1 195         273       <=          1,365
Constraint 2 4           40        <=          140
Constraint 3 1           0         <=          4
----------------------------------------------------------
4          2                     14

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 11
Transportation and Assignment Homework Problems

Reminder: Aside from the couple of problems below you have linear programming formulations
from Chapter 8 as homework.

Chapter 10, page 438 – you may use either software package.

Problem 11      a. Solve using the transportation module of software.

b. Suppose that the route from Des Moines to Cleveland becomes
unavailable. What are the repercussions?

c. Suppose that the route from Des Moines to Boston becomes
unavailable. What are the repercussions?

d. Suppose that your brother-in-law owns the trucking company that runs
between Ft. Lauderdale and Albuquerque and that your spouse insists that
you ship as much as possible along this route. Resolve the problem using
the transportation module.

Problem 28.     a. Solve this problem using the assignment module of the software

The following two questions (which are somewhere between difficult and
tricky) refer to the Linear Programming model for the assignment
problem.

b. Model the following: There is a restriction that if W is assigned to A12
then B2 must be assigned to X.

c. Model the following: There is a restriction that if W is assigned to
territory A12 then B9 can not be assigned to X.

Application. a. Model and solve the following as an assignment problem. Data is from
2001 but principle still may hold on some airlines. As far as I can tell it no longer holds on US
Air between PHL and ORD but it may hold between PHL and LAX.

Jim Denyer is a consultant for a software firm. Jim (and the other
consultants) are assigned to projects that run anywhere from 4 to 24 weeks
in any city in the country. Jim lives near Philadelphia and flies in and out
of Philadelphia International Airport. Jim’s next assignment is in Chicago
where he will be from February 5 through March 2. The company pays the
airfare for its employees to fly back and forth between their homes and
their consulting locations. Thus, Jim will fly out at company expense from
Philadelphia to Chicago on early morning flights on each of the next four
Mondays and will return on late Thursday flights (he works 10-12 hour
days) on each of the following Thursdays. Assume that the airfare is
\$319.50 but that if you stay over a Saturday night then the airfare is
\$237.50. The company would like to spend as little money as possible for
Jim’s trips between Philadelphia and Chicago. Model this as an
assignment problem and determine what tickets should be purchased.

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 12
Sample fares are below:

All prices are in U.S. dollars.
Total: USD
Price: 1 adult @ USD 319.50 (includes taxes/fees/airport charges)
319.50
Flight: US Airways flight 337 on a Boeing 737-400
From: Philadelphia, PA (PHL)     Monday, Feb.       6:30am
26
To:     Chicago OHare (ORD)                         7:50am

Flight: US Airways flight 1076 on a Boeing 737-400
From: Chicago OHare (ORD)       Thursday, Mar. 7:44pm
1

All prices are in U.S. dollars.
Total: USD
Price: 1 adult @ USD 237.50 (includes taxes/fees/airport charges)
237.50
Flight: US Airways flight 337 on a Boeing 737-400
From: Philadelphia, PA (PHL)     Thursday, Feb.     6:30am
22
To:     Chicago OHare (ORD)                         7:50am

Flight: US Airways flight 1076 on a Boeing 737-400
From: Chicago OHare (ORD)       Monday, Feb.       7:44pm
26

Integer Programming Homework

Chapter 11 - Page 487

1. Problem 14, 15 - Read the problems and answer the following for each. What makes this an
integer program rather than a linear program?
2. Solve problem 14 using linear programming and solve problem 15 using integer programming.
3. Problem 17
3. Problem 18
4. Problem 19, 20

1647d012-a1cf-4496-bf5c-ee8f94c7b30c.doc – Page 13

```
To top