Document Sample

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

OTHER DOCS BY fou24498

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.