Excel Transportation Budget - DOC

Document Sample
Excel Transportation Budget - DOC Powered By Docstoc
					          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)




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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
                                                Assumptions – proportionality, additivity
                                        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)
        9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.doc – Page 2
      SATURDAY

      5. Interfaces presentations

                                  NBC-Universal Uses a Novel Qualitative Forecasting Technique to Predict
WALLACE, CHRISTOPHER E            Advertising Demand.
                                  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




      9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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.




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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?




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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

9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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




        9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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.




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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 ----------

                     Added      Solution   Solution
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


Original Problem w/answers ----------

              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




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.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.

9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.doc – Page 12
                             Sample fares are below:

 Your requested flight
 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
                    To:     Philadelphia, PA (PHL)                    10:37pm



 Your requested flight
 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
                    To:     Philadelphia, PA (PHL)                    10:37pm


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




9fdfbc8f-fd15-45f4-854f-55753ee8fd9a.doc – Page 13

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:73
posted:2/3/2011
language:English
pages:13
Description: Excel Transportation Budget document sample