Excel Tools: Solver and Goal Seek

1. Excel Tools: Solver and Goal Seek A Brown Bag discussion for N-81 26 Sept 2012 THIS PRESENTATION IS UNCLASSIFIED

2. Purpose • This Talk promises to: • (re)introduce some powerful tools in Excel • Optimization – centric functions • Goal seek • Solver • To show practical examples of how they may be used • This Talk will not: • Be a course in Linear Optimization

3. References • Albright, S. C. (2007) VBA for modelers Thompson – Brookstone. • Walkenbach, J Microsoft Excel 2010 Bible Wiley. • Ragsdale, C. Decision Analysis with Spreadsheets • Balakrishnan, N. Managerial Decision Modeling with Spreadsheets • Frontline Systems: www.solver.com

4. Block 0: Goal Seek • Goal Seek: A Line Search algorithm that iteratively seeks to find the (User set) goal value. • Goods: Robust, fast, and easy to use. Can work on non-linear problems • Bads: Goal Seek is not an optimization algorithm, but rather a regulated form of iterated guessing. Sometimes doesn’t work. Can miss multiple/optimal solutions • Others: Will only take literals as the goal value. • For Nerds: Is it Binary or Golden Section? Microsoft doesn’t specify in documentation

5. The important thing to know about Goal Seek: • G/S simply automates the ‘trial – and – error’ approach to problem solving. • In strict terms, Goal Seek is Guessing.

6. Invoking Goal Seek • Data -> What If Analysis -> Goal Seek Set Cell: The ‘target’ The desired value of the Set Cell. Must be a ‘literal’. “By Changing”

7. Goal Seek Example • Given a maintenance department with two ‘shops’, if aircraft break at a rate of 1 per day, at what rate does each shop need to fix planes in order to have 80% chance that at any given moment there are no down planes? • This is a queuing problem

8. Solving • The hard way: Break out your stochs textbook, and try to formulate this as a Markov Chain • The Easy way: Q.xls and Goal Seek!

9. Goal Seek Example

10. Goal Seek Example II: Military Application • Recall Lanchester’s Aimed Fire model of combat from our last lunchtime series: • Given , find the value of that results in 70 blue survivors when the Red side is eliminated.

11. Approach 1 TOO EASY! Just solve:

12. Approach II • Use Goal Seek!

13. Summary • Goal seek is ideal in situations where you want answers, not proofs! • DISCLAIMERS: • You will not know if there are multiple solutions • You will not get any sensitivity analysis

14. Some useful excel functions

15. Named Cells • Sometimes, you want to use cells, but D$3$ can become cumbersome to type. • Also, you might want to be able to understand the formulae without an interpreter. • Naming Cells is very handy for this! • How to? • Use the Name box!

16. Naming Cells Name Box

17. Which is easier to understand?

18. Formula Auditing • Sometimes, you want to know what the ‘goezintas’ for a particular answer are. There’s a great function for this: Formula Auditing • Formulas -> Formula Auditing

19. Example I cannot overstress how useful this is for reviewing someone else’s spreadsheet!

20. SUMPRODUCT() • Sometimes you have data in two columns, and you wish to find the total the product of the two sides. • Such as finding the expectation and variance of a discrete variable manually • Tallying up costs by aircraft type • Excel has a special function for this: SUMPRODUCT()

21. SUMPRODUCT() II • Takes two arrays, X and Y as inputs • Must be equally sized • Returns a single number, which is: • In English: Multiply all these by all those and add up • Very useful for the objective function of a LP.

22. SUMXMY2() • Sometimes you want to find the squared Euclidian distance between two points or sets of points. • Such as distance between ships in the example we will work later • Or OLS (Ordinary Least Squares) • SUMXMY2: • Why did they name it that? I don’t know! • SUM of XMinus Y Squared (2)

23. SUMXMY2() II • Takes two arrays, X and Y and returns the sum of square differences (i.e. squared Euclidian measure)

24. Block 1: Solver • What is it: an optimization package. Can handle LP, MIP, NLP. It’s not anyone’s favorite package, but it’s the one we have. • Goods: Graphical interface with spreadsheets can make formulation more visually appealing • Bads: There are some problems where it fails to converge. Limits on number of variables, and number of Integer/Binary variables • Others: Some important things ‘under the hood’ that should be considered (upcoming slides) • For Nerds: I’ve never used Evolutionary Solver mode.

25. Invoking Solver • To use Solver, it must be installed • Why? Because Bill Gates Hates you, that’s why! • To use Solver in VBA, it must be installed there as well • Why? See above!

26. Installing Solver – Main Body

27. Installing Solver- VBA

28. Block II: How to use Solver

29. Solver Differences - 2010 • Excel 2010 has a different (and not better) interface. • Report options are now available after the solution is complete • Used to be an option in the dialog box Report Options

30. A Toy Force Structure Problem • You need to put together a force that is able to carry 15 helicopters, using a mix of ships at a minimum cost. • Data Given: Use at least 1 LHA, 2 LCS, and no more than 4 of any type of ship.

31. Switch to Spreadsheet

32. Some Pointers • I like to use color: • Objective function GREEN • Constraints RED • Variables BLUE • This makes it easy to see ‘what’s what

34. Sensitivity Report

35. Okay, well, that answer was great…(but what does .5 ships mean?) • In order to go forward, we need to solve as a Mixed Integer Program. • In Excel, we add another constraint… … and a whole universe of complexity WARNING! It is NOT GENERALLY true that the solution to a problem with Integer constraints is ‘close’ to the associated Linear Program!

36. Solver – Pro Tips • Frontline solvers claims that the limit on variables is 200, • But it might crash much, much sooner! • Solutions: • A. Upgrade solver • B. Use MATLAB solver • C. Ask us (NPS) for help.

37. Solver: what can go wrong • Here’s something that can go wrong: • Max X^2, unconstrained, with an incumbent solution of zero.

38. Practical Example: Optimum placement of Ships • http://puzzlor.editme.com/Relief-Mission

39. Problem Description • Given the map shown and the ability to place two ships, what is the optimum placement to minimize the maximum distance to a target? • Real-world applications: • Ballistic Missile Defense • Medical Facility Placement • Logistics Hubs • Others?

40. First, an easier Problem • What if there was only one ship to be placed?

41. Now, the Placement Problem • This is actually two problems • If we knew which ship was associated with each Target , this would be easy • If we knew where the two ships were stationed, it would be easy to know which Target to associate with them • We have to figure both out, simultaneously • This is a hard problem – but one that is probably worth the effort

42. Formulation

43. Why does this work? • Because for at one and only one of the constraints, the RHS is negative, and therefore irrelevant. • Here we’re using constraints to shape the objective function! • While combining the constraints into an expression like looks good, it is highly non-linear and may cause solver to crash 

44. Summary • Today we talked about: • Excel Practice, generally • Goal Seek: A useful way for getting answers but Proofs • Excel Functions: • Named cells are infinitely better than R$2$:D$2$, etc. • SUMPRODUCT() • SUMXMY2() • Solver • Practical Applications.

45. Final Note: Presentation Don’ts • BEWARE what MS office does when you copy and paste! • If you copy and paste a graph from a spreadsheet, you might be pasting the entire spreadsheet. This may be handing someone else your entire analysis. • Solution: Be sure to paste graphs as pictures • BEWARE what MS office does when you use track changes. • You might leave them in. • Solution: Copy your text, paste to clipboard, then copy and paste back to word • Solution to both problems: convert to .pdf before distribution

46. Point of Contact • CDR Harrison Schramm hcschram@nps.edu schrammhc@nps.navy.smil.mil 831.656.2358 • After April, 2013 • BE922 