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)
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 Judgement in Forecasting |
|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) 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
