In this lesson, you'll learn how to use a what-if analysis tool called Goal Seek. Optional: You can download this example for extra practice. When you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way: It lets you start with the desired result , and it calculates the input value that will give you that result.
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 go ahead and write a formula or function that calculates the final grade.
Similarly, we can check for different targeted values. This goal seeks to help to find the EMI calculations etc. Now we will see the Data table. We will consider a very small example to understand better. We have to get the percentages in each combination. In these situations, the Data table will help to find the output for a different combination of inputs. Now we will see how to achieve this. First, create a formula to perform this. If we observe the above screenshot, the part marked with a box is the example.
In A3, we have the formula to find the percentage from A1 and A2. So inputs are A1 and A2. Now take the result of A3 to A1 as shown in the below screenshot. Now select the entire table to apply the Data table of What if Analysis as shown in the below screenshot. Detect errors in formulas. Logical functions. Excel functions alphabetical. Excel functions by category. Table of contents. Next: Common uses of functions. Need more help?
Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! In the above scenarios, you want to know the degree to which each change affects the overall outcome. For this reason, a what if analysis is also known as a sensitivity analysis. To help you do a what if analysis, Excel uses commands from the Forecast command group on the Data tab to prepare simple forecasts or advanced business models.
Use this free Excel file to practice what if analysis along with the tutorial. The simplest sensitivity analysis tool in Excel is Goal Seek. Assuming that you know the single outcome you would like to achieve, the Goal Seek feature in Excel allows you to arrive at that goal by mathematically adjusting a single variable within the equation.
The amount to be paid each month is also calculated and shown in cell B6. By using the Goal Seek command, we can indicate a desired outcome and Excel will determine the adjustment we need to make to a single variable. In the example above, cell B5 is dependent on the variables in cells B1, B2, and B3. Cell B6 is dependent on cells B3 and B5. Excel can work backwards to change either cell B1, B2, or B3 to reach that goal. Practically speaking, we may not have much control over the interest rate, so it is more likely that we have the option of adjusting the amount we borrow, or the repayment period.
Excel will change the principal B1 based on the number we enter as the new value for cell B6. Points to note:. Another what if analysis tool is the Scenario Manager. This option is somewhat more advanced than Goal Seek in that it allows the adjustment of multiple variables at the same time. Some other noticeable differences between Goal Seek and Scenario Manager are listed below:.
As a best practice, the original worksheet data should be saved as a scenario so that you can revert to it after all the experiments have been completed. Click OK. As described in our scenario name and comments, we only adjust cell B3 by changing the value to To experiment with adjusting multiple variables within one scenario, the steps are the same as above, with the exception that the desired changes would be made in the Scenario Values window.
To see the outcome of each adjustment on the output cell s , either double click on a scenario name, or highlight a name and click Show. There are two report types available — Scenario summary and Scenario PivotTable report.
Result cells are the cells that will be displayed in the summary. Ideally, these should include all cells which were adjusted as well as result cells. Note that if named ranges were created for Changing or Result Cells, range names will be displayed instead of cell references.
Selecting the Scenario PivotTable report type will create a pivot table report in a new worksheet. Learn more about pivot tables from our Resource Library. The third what if analysis tool from the Forecast command group is the Data Table. Data tables allow the adjustment of only one or two variables within a dataset, but each variable can have an unlimited number of possible values. Data tables are designed for side-by-side comparisons in a way that makes them easier to read than scenarios, once they are set up correctly.
0コメント