Goal Seek Scenario Manager Solve Problems with Goal Seek Goal Seek is a method for finding one time solutions to problems It enables you to work backwards to solve a problem It enables you to set the end result to determine the input to produce the result The primary advantage is the dialog box that enables you to change your goals and parameters relatively easily 2 Solve Problems with Goal Seek Goal Seek can be found under Data What If Analysis 3 Goal seek In general goal seeking is like reverse engineering to achieve my goal or output how much input do I need Example new house purchase I can pay 1500 per month in mortgage payment what kind of houses can I afford See L3 Goal Seek Workbook xlsx for sample data 4 Goal Seek Example Note cell B6 has PMT B4 12 B5 B3 Click Tools Goal Seek Set cell B6 To value 1500 By changing cell B3 Click OK 5 Use Scenario Manager Scenario Manager enables you to specify multiple sets of assumptions called scenarios to quickly see the results of any given Scenario Represents different sets of what if conditions to be considered in assessing the outcome of spreadsheet models 6 Use Scenario Manager 7 Use Scenario Manager You can create multiple scenarios using the Add Scenario dialog box To view your scenarios click What If analysis in the Data Tools group on the Data tab select Scenario Manager and highlight the name of the Scenario you want to view in the Scenarios list and click Show A scenario summary report is a condensed version of the scenario results 8 Use Scenario Manager 9 Scenario Manager Example See workbooks L3 Scenario Mgr blank L3 Scenario Mgr Company has predictions for Year 1 sales annual sales growth and varying Year 1 sales price What will be the profit for Years 1 5 Scenarios Best 20 000 units growth of 20 Year 1 price 10 00 Most Likely 10 000 units growth of 10 Year 1 price 7 50 Worst Case 5 000 units growth of 2 Year 1 price 5 00 10 Scenario Manager Example The initial worksheet 11 Scenario Manager Example 1st screen Scenario Manager Name scenario define changing cells 12 Scenario Manager Example 2nd screen Scenario Manager Define sales growth price for Best scenario 13 Scenario Manager Example 3rd screen Scenario Manager Click Add on last dialog box and add Most Likely scenario same changing cells 14 Scenario Manager Example 4th screen Scenario Manager Define sales growth price for Most Likely scenario 15 Scenario Manager Example 5th screen Scenario Manager Click Add on last dialog box and add Worst Case scenario same changing cells 16 Scenario Manager Example 6th screen Scenario Manager Define sales growth price for Worst Case scenario 17 Scenario Manager Example 7th screen Scenario Manager After clicking OK on last Scenario Values screen the Scenario Manager shows what you ve defined 18 Scenario Manager Example 8th screen Scenario Manager Click on Summary to select the type of output from Scenario Manager We choose Scenario Summary and indicate which cells we want reported After Tax Profits for Years 1 5 NPV 19 Scenario Manager Example 9th screen Scenario Manager Click OK which runs scenarios and produces output report new worksheet 20 Scenario Manager Example 2 see L3 Scenario Mgr Example 2 Worksheet Cells to vary C23 C27 21 Scenario Manager Example 2 Define Scenario 1 Consensus 22 Scenario Manager Example 2 Define Scenario 2 Pessimistic 23 Scenario Manager Example 2 Define Scenario 3 Optimistic 24 Scenario Manager Example 2 25 Scenario Manager Example 2 Note Named Ranges appear instead of cell numbers 26
View Full Document
Unlocking...