How to Use What If Analysis in Excel
What-if analysis is the process of finding an input that will result in a specific output. In other words, what-if analysis can be considered as the opposite of formulas. Formulas calculate an output by providing inputs, while what-if analysis helps you find out what input will result in a specific output.
You can use What-If analysis to estimate your monthly savings that meet your retirement goals, find the return on investment, plan your budget and so on.
Key Takeaways
- What-if analysis is the process of finding an input that will result in a specific output.
- There are three different tools available in Excel to perform what-if analysis: scenarios, data tables, and Goal Seek.
- You can use what-if analysis to estimate your monthly savings, find the return on investment, and plan your budget.
What is What-If Analysis?
There are three different tools available in Excel to perform what-if analysis: scenarios, data tables, and Goal Seek.
- Scenario Manager allows you to create and compare different scenarios based on different sets of input values.
- Data Tables allow you to see how changing two or more input values will affect the output value.
- Goal Seek allows you to find the input value that will result in a specific output.
How to Use What-If Analysis
To use what-if analysis, you first need to enter your data and formulas in the appropriate cells. Then, you can use the following tools to perform what-if analysis:
Scenario Manager
Scenario Manager allows you to create and compare different scenarios based on different sets of input values. Here’s how to use it:
- Enter your data and formulas in the appropriate cells.
- Click on the Data tab in the Excel Ribbon, and then click on the What-If Analysis button.
- Select Scenario Manager from the drop-down menu.
- Click on the Add button to create a new scenario.
- In the Add Scenario dialog box, enter a name for the scenario and specify the input values you want to change.
- Repeat the process for each additional scenario you want to create.
- To compare the scenarios, click on the Summary button in the Scenario Manager dialog box.
Data Tables
Data Tables allow you to see how changing two or more input values will affect the output value. Here’s how to use it:
- Enter your data and formulas in the appropriate cells.
- Click on the cell where you want to display the results of the data table.
- Click on the Data tab in the Excel Ribbon, and then click on the What-If Analysis button.
- Select Data Table from the drop-down menu.
- In the Data Table dialog box, specify the input values you want to change and the cell containing the formula you want to calculate.
- Excel will create a table showing how changing the input values will affect the output value.
Goal Seek
Let’s have a look at Goal Seek function. Using Goal Seek function, you can change the data in formula and analyze how it affects the end result.
Have a look at the data given below. We will be using this data to perform What-If analysis.
Example 1 Monthly EMI calculation
Suppose a person takes a loan for $25,000 from a bank at the interest rate of 20% per annum and he agrees to pay off the loan in one and a half years (18 months).
All remaining data including monthly loan payment, monthly payment towards principal, monthly interest payment and remaining balance to be paid each month are calculated using financial formulas.
In this case, the person has to pay an amount of $1619.08 every month for 18 months to complete the loan. Assume that the person is capable of paying $2500 every month so that he can pay off the loan faster. So, how can we find out in how many months the person will be able to complete the loan if he pays $2500 monthly. Here comes the Goal Seek function into play.
Perform What-If analysis
Open Excel, copy the data shown above and save your file as What-If analysis.xlsx. Go to Data (main menu) –> What-If Analysis (in the Data Tools group) and select Goal Seek.
You will see a window like this:
Click in the textbox next to Set cell and click the cell E1 as we need to analyze the result by changing the monthly payment. In the To value: textbox, enter 2500 as this is our new value. After clicking in the By changing cell: textbox, click the cell B2 as we want to find out the change in number of payments (number of months).
Click OK.
If you analyze the value in cell B2, you could find that it is 11.03. It means that you can complete the loan in less than a year (in 11.03 months) if you make a payment of $2,500 per month.
Example 2 Notes average calculations
Suppose you are a student who plans to score an average of 80 in your semester exam. You scored 82, 70, 83 and 76 in the subjects English, Mathematics, Computer Science and Mechanics respectively. You have Statistics exam remaining and you want to calculate the marks you need to score in Statistics to achieve an average of 80.
Select the Goal Seek function to open the window. Click in the textbox next to Set cell: and click the cell that contains the average score (here the cell containing the value 77.5). In the To value: textbox, enter 80 as this is your target average.
After clicking in the By changing cell textbox, click the cell that will contain the score of Statistics (here the cell just above the cell with 77.5).
From this data, it is clear that you have to score 89 in Statistics to achieve an overall average of 80. Similarly, you can use Goal Seek function in many different scenarios to analyze what input should be given in order to get a specific output.
In conclusion, What-If Analysis is a powerful feature in Microsoft Excel that enables users to explore different scenarios and see how changes in input values affect their data.
By using tools such as Goal Seek, Scenario Manager, and Data Tables, users can make informed decisions and improve their business or personal processes.
Leave a Reply