What if tools in excel

The "To value" text box is where you set your desired goal. Because this is a payment goal, the value should be negative. Enter "" in the "To value" text box. Click the up arrow button next to the "By changing cell" text box and select the interest rate cell B3.

You can change values including the total loan amount and term in months to find the right results that match your goal. Each change will calculate to a new monthly payment that you can then use to change an interest rate value and determine your new payment. This tool will let you see results by changing a value until you're able to come up with a viable option for your budget.

Using the payment per month example in the Goal Seek section, suppose you want to see several different interest rate options and the results of each one.

A data table is beneficial when you want to see more than just one result. The data remains the same, but you can see how much you'll pay each month by factoring in several different interest rate options. To test this tool, you first need to set up your initial data. In this example, three rows are used to set up three possible payments.

The term in months are 60, 72 and An initial interest rate is set up to test the monthly payment for the first row. Use the same PMT function used in the previous example, but remember to change the cell references. The D2, D3 and D4 values should be the following in your data set:. But suppose we want to know how much a payment would be using the dame data but several different interest rates. This selection is where your Data Table is located.

The cells under the original payment in C7 will show alternative payment amounts based on the other interest rates.

You can set up several interest rates to see each option in the C column. A window opens asking for input cells. The column input cell should be the interest rate in C2. Since we selected the data table range and only have one input variable, click "OK. You can make additional tables to calculate differences in other variables such as loan amount and terms in month.

Data Tables are valuable when you have several different factors that can make differences in an end result. Setting up What-If reports help with several forecast and financial calculations and possibilities. These reports can often be more complex than simple formulas and calculations, but using Excel for each scenario, data table and goal seek you'll find that it's much easier and convenient than working with expensive, advanced software on the market.

Open Main Menu. Browse Courses My Classes. Sign In Subscribe Course Catalog. Using the What-If Analysis in Excel When you have several different possible results based on a collection of data, a "What-If" Analysis lets you review results based on different factors. For instance, suppose that you have two budgets based on the amount of revenue increase for the next year.

By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the various results. For example, you can do What-If Analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result.

Excel provides several different tools to help you perform the type of analysis that fits your needs. Note that this is just an overview of those tools. There are links to help topics for each one specifically.

What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Scenarios and Data tables take sets of input values and determine possible results. A Data Table works with only one or two variables, but it can accept many different values for those variables. A Scenario can have multiple variables, but it can only accommodate up to 32 values. Goal Seek works differently from Scenarios and Data Tables in that it takes a result and determines possible input values that produce that result.

In addition to these three tools, you can install add-ins that help you perform What-If Analysis, such as the Solver add-in. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel.

For more advanced models, you can use the Analysis ToolPak add-in. A Scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. For example, suppose you have two budget scenarios: a worst case and a best case.

You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario.

When you switch between scenarios, the result cell changes to reflect the different changing cell values. 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. After you have created or gathered all the scenarios that you need, you can create a Scenario Summary Report that incorporates information from those scenarios. 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 quickly move between various scenarios, and reach different results for each. You can even gather different scenarios from different users on different sheets, and merge them into a single Scenario Report.

Goal Seek tool does exactly what it says on the tin — it helps you seek a goal. Assume a value in a cell is calculated based on the value in another cell, what should the value be in that other cell, so that you can reach a target value in the first cell? This can be calculated using Goal Seek.