Create Presentation
Download Presentation

Download Presentation
## Excel Tools: Solver and Goal Seek

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

**Excel Tools: Solver and Goal Seek**A Brown Bag discussion for N-81 26 Sept 2012 THIS PRESENTATION IS UNCLASSIFIED**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**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**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**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.**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”**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**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!**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.**Approach 1**TOO EASY! Just solve:**Approach II**• Use Goal Seek!**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**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!**Naming Cells**Name Box**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**Example**I cannot overstress how useful this is for reviewing someone else’s spreadsheet!**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()**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.**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)**SUMXMY2() II**• Takes two arrays, X and Y and returns the sum of square differences (i.e. squared Euclidian measure)**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.**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!**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**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.**Some Pointers**• I like to use color: • Objective function GREEN • Constraints RED • Variables BLUE • This makes it easy to see ‘what’s what**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!**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.**Solver: what can go wrong**• Here’s something that can go wrong: • Max X^2, unconstrained, with an incumbent solution of zero.**Practical Example: Optimum placement of Ships**• http://puzzlor.editme.com/Relief-Mission**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?**First, an easier Problem**• What if there was only one ship to be placed?**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**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 **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.**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**Point of Contact**• CDR Harrison Schramm hcschram@nps.edu schrammhc@nps.navy.smil.mil 831.656.2358 • After April, 2013 • BE922