- 118 Views
- Uploaded on
- Presentation posted in: General

Teaching Spreadsheet Optimization TMSW, July 2004

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

**1. **
J. D. Camm
University of Cincinnati
QAOM Department
Cincinnati, Ohio 45221-0130
[email protected] 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.