Teaching Spreadsheet Optimization TMSW, July 2004

1. J. D. Camm University of Cincinnati QAOM Department Cincinnati, Ohio 45221-0130 Jeff.Camm@uc.edu Teaching Spreadsheet OptimizationTMSW, 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 ProgrammingCalhoun 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.