Goal Seek If you know the result that you want from a formula, but are not sure what value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal. Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in discussed at the end of this manual. Scenarios Scenario Manager allows you to create and save multiple if” scenarios (such as best “what case, most likely, and worst cases scenarios). You can also create a summary table of the scenario results in seconds. It is particularly useful for worksheets such as budgets in which users have often saved multiple copies of the same worksheet to accomplish the same objective. An example is shown below. In this example, a tire company has prepared a revenue budget for the coming year, and has created three alternative scenarios to generate the revenues that will result given a variety of mark up assumptions in this case – 100%, 110% and 120% markups. Pressing the summary button in the scenario manager dialog box will create the following Pivot Table of possible alternative results. Here we see detailed revenue projections for all tires and labor fees given all three possible scenarios of 100%, 110%, and 120% markup. With a few simple copy paste commands, the newly created data can be positioned and formatted next to the original projections as shown in the screen below. Of course the scenarios could have been created easily using simple formulas instead of using the scenario manager tool as described above. This underscores that best purpose of scenario manager which is to keep track of older and changing data through time, rather than producing what-if scenarios. For example, a complex projection containing scenarios based on original assumptions, revised assumptions, and final assumptions will allow management to go back and review the assumptions used throughout the project, and see how those assumptions changed as project planning progressed.
Goal Seek If you know the result that you want from a formula, but are not sure what value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal. Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in discussed at the end of this manual. Scenarios Scenario Manager allows you to create and save multiple “what if” scenarios (such as best case, most likely, and worst cases scenarios). You can also create a summary table of the scenario results in seconds. It is particularly useful for worksheets such as budgets in which users have often saved multiple copies of the same worksheet to accomplish the same objective. An example is shown below. In this example, a tire company has prepared a revenue budget for the coming year, and has created three alternative scenarios to generate the revenues that will result given a variety of mark up assumptions – in this case 100%, 110% and 120% markups. Pressing the summary button in the scenario manager dialog box will create the following Pivot Table of possible alternative results. Here we see detailed revenue projections for all tires and labor fees given all three possible scenarios of 100%, 110%, and 120% markup. With a few simple copy paste commands, the newly created data can be positioned and formatted next to the original projections as shown in the screen below. Of course the scenarios could have been created easily using simple formulas instead of using the scenario manager tool as described above. This underscores that best purpose of scenario manager which is to keep track of older and changing data through time, rather than producing what-if scenarios. For example, a complex projection containing scenarios based on original assumptions, revised assumptions, and final assumptions will allow management to go back and review the assumptions used throughout the project, and see how those assumptions changed as project planning progressed.
2009%20Excel%20Functions%20Macros%20and%20Data%20Commands%20Manual.docx