1 / 44

Teaching Spreadsheet Optimization TMSW, July 2004

2. They facilitate analysis. Easy model manipulation.Graphics Allow for the simultaneous use of multipleThey are everywhere!Spreadsheets are the work environment.. Advantages of Spreadsheets. 3. Data ManagementForecastingOptimizationSimulationGraphicsProgramming Capability (VBA). Spreadsheets Provide an Integrated Environment for OR.

avi
Download Presentation

Teaching Spreadsheet Optimization TMSW, July 2004

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


    1. J. D. Camm University of Cincinnati QAOM Department Cincinnati, Ohio 45221-0130 Jeff.Camm@uc.edu Teaching Spreadsheet Optimization TMSW, July 2004

    2. 2 They facilitate analysis. Easy model manipulation. Graphics Allow for the simultaneous use of multiple They are everywhere! Spreadsheets are the work environment. Advantages of Spreadsheets

    3. 3 Data Management Forecasting Optimization Simulation Graphics Programming Capability (VBA) Spreadsheets Provide an Integrated Environment for OR

    4. 4 Linear Programming (Simplex Algorithm) Linear Integer Programming (B&B) Nonlinear Programming (GRG2) Nonlinear Integer Programming (B&B) Evolutionary Algorithm (GA) Spreadsheets Provide a Comprehensive Environment for Teaching Optimization

    5. 5 Disadvantages of Spreadsheets Large Models become cumbersome. Dimensionally challenged. Debugging is sometimes difficult. ? bugs in some of the tools!

    6. 6 Using SS in your class Decide on a structure! data section model section use color Teach them early how to print formulas! Beware that SOLVER & DA are options! Audit tool

    7. 7 Optimization Modeling What am I trying to decide? What is my objective? How am I constrained?

    8. 8 Spreadsheet Optimization Begin by developing models and testing for feasibility......performing manual what-if or using data tables Trial and error optimization

    9. 9 The Fundamentals: Tools - Solver Solver Dialog Box Set Cell Changing Cells Options

    10. 10 Premium Solver Dialog Box

    11. 11 Premium Solver Options Dialog Box:

    12. 12 Constraint Dialog Box:

    13. 13 Linear Programming Sumproduct performs the dot product of two arrays e.g. sumproduct(a1:a3,d5:d7)

    14. 14 15 Products Dobbie Looms, Regular Looms What to Make – What to Outsource? Data in Calhoun.xls Example 1: Linear Programming Calhoun Mills Make-or-Buy

    15. 15 Data ti = internal hours/yard needed to produce fabric i ci = cost per yard to produce fabric i si = cost per yard to outsource fabric i demi = demand (yds) for fabric I R = total regular loom hours available D = total dobbie loom hours available

    16. 16 Decision Variables ri = yards of fabric i to produce on a regular loom di = yards of fabric i to produce on a regular loom oi = yards of fabric i to outsourcer

    17. 17 The Algebraic Model

    18. 18 Lesson Learned Linear Programming simultaneously considers the objective function and the constraint set and the tradeoff between the two.

    19. 19 Integer Programming

    20. 20 Example 2: The Ohio Banking Location Problem Bank Location Principal Place of Business (ppb) Coverage Matrix Defined by County Adjacency (88x88) Set Cover

    21. 21 The Algebraic Model

    22. 22 How might we attempt to solve this problem?

    23. 23 Lesson Learned Greedy is not always optimal. Optimization models and algorithms operate on the system in its entirety.

    24. 24 Model Extension Some models are easily extended. Ohio Banking Problem – revised What is the maximum population that can be reached by “k” ppbs?

    25. 25 The Revised Algebraic Model

    26. 26 Lessons Learned Models are often easily revised to model different situations. Models should be used to perform analysis.

    27. 27 Nonlinear Programming Known Models Portfolio Optimization Estimating relationships for model building

    28. 28 Example 3: Nonlinear Programming Phillips Marketing Budget Allocation

    29. 29 Phillips Marketing Problem we have $500,000 for advertising at least $50,000 to each product let xA = amount ($) to allocate to A let xB = amount ($) to allocate to B

    30. 30 Max f(xA, xB) s.t. xA + xB ? 500000 xA ? 50000 xB ? 50000

    31. 31 Add Trendline Option:

    32. 32 Max 1.2712 ln(xA) + .397 ln(xB) + 33.523 s.t. xA + xB ? 500000 xA ? 50000 xB ? 50000

    33. 33 Lesson Learned Sometimes you do not know the functional form, so it has to be estimated from available data of expert judgment.

    34. 34 Evolutionary Algorithm Genetic Algorithm Population Random Mutations Crossover Survival of the fittest

    35. 35 Evolutionary Solver Options

    36. 36 Example 4: A “nasty” NLP: Max x[sin(5px)] + y[sin(5py)]

    37. 37 Using the nonlinear solver:

    38. 38 Visual Basic Simulation – Optimization (iterate) Stochastic Optimization

    39. 39 Lesson Learned VBA enables you to develop some powerful tools in the spreadsheet environment.

    40. 40 If you teach in spreadsheets, then your students will use “IF”

    41. 41 Bank Location Principal Place of Business (ppb) Coverage Matrix Defined by County Adjacency Maximize Population Reached Maximal Set Cover Example 5: Students and the “if” Sun Bank Location Problem (revisited)

    42. 42 Max Cover Model

    43. 43 The “natural” IF way If sumproduct(B6:CK6,B100:CK100) >=1 then add population to the total population reached Maximize the total population reached Solve using the Evolutionary Solver

    44. 44 Lesson Learned IF doesn’t work well with traditional optimization algorithms Use the Evolutionary Algorithm if you use IF

    45. 45 References: Camm, J.D. and J.R. Evans, Management Science & Decision Technology, South-Western, Cincinnati, OH, 2000. Camm., J.D., Dearing, P.M. and S.K Tadisina, “The Calhoun Textile Mill Case: An Exercise on the Significance of Linear Programming Model Formulation,” IIE Transactions, Vol. 19, No. 1, pp. 23-28, March, 1987. Sweeney, D., Mairose, R. and R.K. Martin, “Strategic Planning in Bank Location,” American Institute for Decision Sciences Proceedings, November, 1979.

More Related