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. Data Tables. Using Solver for capital budgeting. Using Solver to determine the optimal product mix. Define and solve a problem by using Solver. Analysis ToolPak Add-in. Overview of formulas in Excel. How to avoid broken formulas. Detect errors in formulas. Keyboard shortcuts in Excel. Excel functions alphabetical. Excel functions by category.
Changing cells 2. Result cell 1. Result cell If several people have specific information in separate workbooks that you want to use in scenarios, you can collect those workbooks and merge their scenarios. Cells B1, B2, and B3 are the values for the loan amount, term length, and interest rate. Use Data Tables to see the effects of one or two variables on a formula.
Need more help? Expand your skills. 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. If the only variable to be considered in our loan example were the amount being borrowed, we could set up a one-variable data table. Step 1 - make a list of all possible principal loan amounts. The list may be by column or row.
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. Once we use Goal Seek, cell B6 will show us the minimum grade we'll need to make on the final assignment. Let's say you need a loan to buy a new car. However, you're not sure yet what the interest rate will be. However, Goal Seek can do this automatically by starting with the result and working backward.
For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. Rather than start from the desired result and working backward like with Goal Seek, you can use these options to test multiple values and see how the results change.
For more information on scenarios, check out this article from Microsoft. For more information on data tables, check out this article from Microsoft. Function calculating the monthly payment. 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.
This is a guide on What if Analysis in Excel. Here we discuss three different tools in What if Analysis and the examples and downloadable excel template. You may also look at the following articles to learn more —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy. Forgot Password? This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy.
By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy.
Download What if Analysis Excel Template. Popular Course in this category.
0コメント