1 / 18

Using Microsoft Excel Solver Function

Using Microsoft Excel Solver Function. A quick review of the power of Solver. What is “Solver”. Solver is a built-in function (routine) Solver is good for what-if, trend, and other relationships Using historical data Solver will try to optimize a solution

perry
Download Presentation

Using Microsoft Excel Solver Function

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Microsoft Excel Solver Function A quick review of the power of Solver

  2. What is “Solver” • Solver is a built-in function (routine) • Solver is good for what-if, trend, and other relationships • Using historical data Solver will try to optimize a solution • You can use Solver much like regression analysis

  3. Where is Solver located? Select Solver

  4. You might need to add Solver through Add-Ins…

  5. Solver Dialog-box

  6. What Do All Those Options Mean? • Set Target Cell: • This is the cell where you will have the computer optimize the calculation • Equate To: • Choice of Max, Min, or Value of… (typically this is set to Min)

  7. What Do All Those Options Mean? • By Changing Cells: • These are the “weighting factors” (Parameter Values) that the computer will use to fit the equation • Subject to the Constraints: • You can set logical statements like A>B, or A>0

  8. Fitting Terms Error Calculations Raw Data Model Values FITDATA.XLS

  9. Minimize the sum of the errors Parameter Values Area where you are able to add constraints (like A>B, A>0 Solver Dialog-box: FITDATA.XLS

  10. This Cell is minimized And Solver does this by solving (calculating) the target cell value as small as possible buy adjusting the “Parameter Values” of the Changing Cells FITDATA.XLS Example • Solver will try to minimize the sum of the squares of errors (this example) • This means the better the fit of the “Parameter Values” the less error introduced into the solution

  11. Enter Equation Then Fill Down Y_Model, sq. error, and sq. devs. Columns (for each row you have raw data) Enter Equation (one you determine, or want to try)

  12. Then Verify Solver Settings Settings: Minimize Sum of Errors To minimize Parameters A, B, & C_ will be changed by Solver

  13. Click Solve

  14. Solver will Run Yielding this Result This example yielded a perfect fit, note R2 & graph

  15. Compared to Regression

  16. Solver ~ Regression • The Two ~ Match • Solver can do more complex equations and relationships • The following example is Prepayments • Seven major terms were used • COFI, 30-Yr , COFI Teaser, Spread between COFI & 30-Yr, Spread between COFI & COFI Teaser, Seasonality • Then a three month look-back for each rate was added

  17. Prepayments in Solver Note 16-Different X-terms mapping to 1-Y term (prepayments)! Very Complex! But easy to do!

  18. Empowered • Now that you are empowered with the Power of Excel Solver what do you want solve? ?

More Related