excel tools solver and goal seek n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Excel Tools: Solver and Goal Seek PowerPoint Presentation
Download Presentation
Excel Tools: Solver and Goal Seek

Loading in 2 Seconds...

play fullscreen
1 / 46

Excel Tools: Solver and Goal Seek - PowerPoint PPT Presentation


  • 397 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Excel Tools: Solver and Goal Seek' - barth


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.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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
excel tools solver and goal seek

Excel Tools: Solver and Goal Seek

A Brown Bag discussion for N-81

26 Sept 2012

THIS PRESENTATION IS UNCLASSIFIED

purpose
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
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
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
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
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
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
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
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
Approach 1

TOO EASY! Just solve:

approach ii
Approach II
  • Use Goal Seek!
summary
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
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
Naming Cells

Name Box

formula auditing
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
Example

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

sumproduct
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
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
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
SUMXMY2() II
  • Takes two arrays, X and Y and returns the sum of square differences (i.e. squared Euclidian measure)
block 1 solver
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
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
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
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
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
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
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
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
Practical Example: Optimum placement of Ships
  • http://puzzlor.editme.com/Relief-Mission
problem description
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
First, an easier Problem
  • What if there was only one ship to be placed?
now the placement problem
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
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 
summary1
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
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
Point of Contact
  • CDR Harrison Schramm

hcschram@nps.edu

schrammhc@nps.navy.smil.mil

831.656.2358

  • After April, 2013
    • BE922 