What is a What-If Analysis in Excel?
What-If Analysis in Excel is a tool which helps us to create different models, scenarios, data table. In this article, we will look at the ways of using What-If Analysis.
We have 3 parts of What-If Analysis in Excel. They are as follows:
- Scenario Manager
- Goal Seek in Excel
- Data Table in Excel
#1 Scenario Manager in What-If Analysis
As a business head, it is important to know the different scenarios of your future project. Based on the scenarios the business head will make decisions. For example, you are going to undertake one of the important projects. You have done your homework and listed out all the possible expenditures from your end and below is the list of all your expenses.
The expected cash flow from this project is 75 million, which is there in the cell C2. Total expenses comprise of all your fixed and variable expenses and the total cost is 57.45 million in cell C12. Total profit is 17.55 million in cell C14 and profit % is 23.40% of your cash inflow.
This is the basic scenario of your project. Now you need to know the profit scenario if some of your expenses increase or decrease.
- On a general case scenario, you have estimated the Project License cost to be at 10 million, but for sure you are anticipating it to be at 15 million
- Raw material cost to be increased by 2.5 million
- Other expenses to be decreased by 50 thousand.
- Project cost to be at 20 million.
- Labor daily wages to be at 5 million
- Operating cost to be at 3.5 million.
Now you have listed out all the scenarios in the form. Based on these scenarios you need to create a scenario table how it is going to impact your profit & profit %. In order to create what-if analysis scenarios follow the below steps.
- Step 1: Go to DATA > What-If Analysis > Scenario Manager.
- Step 2: Once you click on Scenario Manager it will show you below the dialogue box.
- Step 3: Click on ADD. Give Scenario a name.
- Step 4: In changing cells select the first scenario changes you have listed out. The changes are Project cost (cell C10) at 15 million, Raw material cost (cell C7) at 11 million, and other expenses (cell C11) at 4.5 million. Mention these 3 cells here.
- Step 5: Click on OK. It will ask you to mention the new values, mention new values as listed in scenario 1.
- Step 6: Do not click on OK but click on OK Add. It will save this scenario for you.
- Step 7: Now it will ask you to create one more scenario. As we listed in scenario 2 make the changes. This time we need to change Project cost (C10), Labour Cost (C8), and Operating Cost (C9)
- Step 8: Now add new values here.
- Step 9: Now click on Ok. It will show all the scenarios we have created.
- Step 10: Click on SUMMARY it will ask you which result cells you want to change. Here we need to change Total Expense Cell (C12), Total Profit Cell (C14), and Profit % cell (C16).
- Step 11: Click on OK. It will create a summary report for you in the new worksheet.
Total excel has created 3 scenarios even though we have supplied only 2 scenario changes. Because excel will show existing reports as one scenario.
From this table, we can easily see the impact of changes on pour profit %.
#2 Goal Seek in What-If Analysis
Now we know the scenario manager’s advantage. What-if Analysis Goal seek can tell you what you have to do to achieve the set target.
Andrew is a class 10th student. His target is to achieve the average score of 85 in the final exam and he has already completed 5 exams and left with only 1 exam. In the completed 5 exams the anticipated scores are as below.
In order to calculate the current average apply the Average formula in B7 cell.
The current average is 82.2.
Andrew’s GOAL is 85 and his current average is 82.2 and short by 3.8 with one exam to go.
Now the question is how much he has to score in the final exam to get an overall average of 85 eventually. This can find out by what-if analysis GOAL SEEK tool.
- Step 1: Go to DATA > What-If Analysis > Goal Seek
- Step 2: It will show you below the dialog box.
- Step 3: Here we need to set the cell first. Set Cell is nothing but which cell we need the final result i.e. our overall average cell (B7). Next is To Value, this is nothing but for what value we need to set the cell i.e. Andrew’s overall average GOAL (85).
Next and final part is by changing which cell you want to see the impact. So we need to change the cell B6 which is the cell for the final subject’s score.
- Step 4: Click on OK. Excel will take a few seconds to complete the process but eventually, it shows the end result like the below one.
Now we have our results here. In order to get an overall average of 85, Andrew has to score 99 in the final exam.
#3 Data Table in What-If Analysis
We have already seen two wonderful techniques under What-If Analysis in Excel. The data table can create different scenario tables based on the change of variable. We have two kinds of data table here “One variable data table” and “Two-variable data table”. In this article, I will show you One variable data table Excel.
Assume you are a selling a 1000 product at 15 rupees, your total anticipated expense is 12500 and profit is 2500.
You are not happy with the profit you are getting. Your anticipated profit is 7500. You are decided to increase your Per Unit Price to increase your profit but you don’t know how much you need to increase.
Data tables can help you. Create a below table
Now in the cell F1 give a link to the Total Profit Cell i.e. B6
- Step 1: Select the newly created table.
- Step 2: Go to DATA > What-if Analysis > Data Table.
- Step 3: Now you will see below dialogue box.
- Step 4: Since we are showing the result vertically leave the Row input cell. In the column input cell select the cell B2 which is the original selling price.
- Step 5: Click on Ok to get the results. It will list out profit numbers in the new table.
So we have our data table ready. If you want to make a profit from 7500 you need to sell at 20 per unit.
Things to Remember
- The what-if analysis data table can be performed with two variable changes. Refer to our article on what-if analysis Two-Variable Data Table.
- What-if Analysis Goal Seek takes a few seconds to perform calculations.
- What-if Analysis Scenario manager can give a summary with input numbers and current values together.
This has been a guide to What-If Analysis in Excel. Here we discuss 3 Types of What-If Analysis in Excel such as 1) Scenario Manager, 2) Goal Seek, 3) Data Table along with practical examples, and downloadable excel template. You may learn more about excel from the following articles –