We'll use a few examples to show how to use Goal Seek. Let's say you're enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class. In the image below, you can see that the grades on the first four assignments are 58 , 70 , 72 , and Even though we don't know what the fifth grade will be, we can write a formula—or function—that calculates the final grade.
A scenario report displays all the scenario information in one table on a new worksheet. Note: Scenario reports are not automatically recalculated.
If you change the values of a scenario, those changes will not show up in an existing summary report. Instead, you must create a new summary report. If you know the result that you want from a formula, but you're not sure what input value the formula requires to get that result, you can use the Goal Seek feature. For example, suppose that you need to borrow some money.
You know how much money you want, how long a period you want in which 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 must secure in order to meet your loan goal. Note: Goal Seek works with only one variable input value. If you want to determine more than one input value, for example, the loan amount and the monthly payment amount for a loan, you should instead use the Solver add-in.
For more information about the Solver add-in, see the section Prepare forecasts and advanced business models , and follow the links in the See Also section.
If you have a formula that uses one or two variables, or multiple formulas that all use one common variable, you can use a Data Table to see all the outcomes in one place. Using Data Tables makes it easy to examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy to read and share in tabular form.
If automatic recalculation is enabled for the workbook, the data in Data Tables immediately recalculates; as a result, you always have fresh data. Cell B3 contains the input value. A Data Table cannot accommodate more than two variables. If you want to analyze more than two variables, you can use Scenarios. Although it is limited to only one or two variables, a Data Table can use as many different variable values as you want.
A Scenario can have a maximum of 32 different values, but you can create as many scenarios as you want. If you want to prepare forecasts, you can use Excel to automatically generate future values that are based on existing data, or to automatically generate extrapolated values that are based on linear trend or growth trend calculations.
You can fill in a series of values that fit a simple linear trend or an exponential growth trend by using the fill handle or the Series command. To extend complex and nonlinear data, you can use worksheet functions or the regression analysis tool in the Analysis ToolPak Add-in.
Although Goal Seek can accommodate only one variable, you can project backward for more variables by using the Solver add-in. By using Solver, you can find an optimal value for a formula in one cell—called the target cell—on a worksheet.
Solver works with a group of cells that are related to the formula in the target cell. Solver adjusts the values in the changing cells that you specify—called the adjustable cells—to produce the result that you specify from the target cell formula. You can apply constraints to restrict the values that Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula. You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Goal Seek. If the team expects that corrective measures will need to be taken, the risk is unacceptable. If it is not likely that further measures will be needed, the risk is acceptable and the scenario can be chosen. By the way, it is a good idea to not discard the other scenarios yet in case the chosen solution fails after all. The choice is made for the previously selected solution. It is important to evaluate along the way and check if the scenario goes as predicted. Sometimes, it can be necessary to make adjustments or follow up on things.
Get Toolshero updates on new methods, models and theories! Join us. What do you think? Is the What-If Analysis or Scenario Analysis applicable in your personal or professional environment? Do you recognize the practical explanation or do you have more suggestions? What are your success factors for good decision making?
How to cite this article: Mulder, P. What-If Analysis. Your rating is more than welcome or share this article via Social media! Vote count: 3. No votes so far! Be the first to rate this post. By making access to scientific knowledge simple and affordable, self-development becomes attainable for everyone, including you! Join our learning platform and boost your skills with Toolshero. Try Toolshero for free! Corporate Plans Publish your research my account Try us for free. Did you find this article interesting?
0コメント