How To Do A What If Analysis Data Table In Excel

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 tabular array. In this commodity, 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

You can download this What-If Analysis Excel Template here – What-If Analysis Excel Template

#1 Scenario Manager in What-If Analysis

As a business organisation head, it is of import to know the different scenarios of your future projection. Based on the scenarios, the business caput will make decisions. For example, you are going to undertake one of the important projects. Y'all have washed your homework and listed out all the possible expenditures from your end, and below is the listing of all your expenses.

The expected cash menstruation from this project is 75 million, which is there in the jail cell C2. Full expenses incorporate all your stock-still 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 greenbacks inflow.

This is the bones scenario of your project. Now you lot need to know the profit scenario if some of your expenses increase or decrease.

Scenario ane

On a general case scenario, you have estimated the Project License cost to exist at 10 million, but for certain, you are anticipating it to exist at xv million
Raw material toll to exist increased by 2.v million
Other expenses Other expenses comprise all the non-operating costs incurred for the supporting business organisation operations. Such payments similar rent, insurance and taxes have no direct connection with the mainstream business activities. read more to be decreased past 50 thousand.

Scenario two

Projection toll to be at 20 million.
Labor daily wages to be at 5 1000000
Operating toll to be at three.five million.

Now you have listed out all the scenarios in the form. Based on these scenarios, you need to create a scenario table about how it is going to touch your profit & turn a profit %.

In club to create what-if analysis scenarios, follow the below steps.

Go to DATA > What-If Assay > Scenario Manager.
Once yous click on Scenario Manager, it volition show y'all below the dialogue box.
Click on ADD. Give Scenario a name.
In changing cells, select the first scenario changes yous have listed out. The changes are Project toll (cell C10) at 15 million, Raw fabric cost (prison cell C7) at 11 meg, and other expenses (cell C11) at iv.5 million. Mention these 3 cells hither.
Click on, OK. Information technology volition ask you lot to mention the new values, mention new values as listed in scenario 1.
Exercise not click on OK just click on OK Add. Information technology will salve this scenario for you.
Now, it volition ask you to create one more scenario. Every bit nosotros listed in scenario 2, brand the changes. This time we need to modify Project price (C10), Labour Price (C8), and Operating Cost (C9)
Now add new values here.
Now click on, Ok. It volition show all the scenarios we accept created.
Click on SUMMARY it will inquire you which effect cells you want to change. Here nosotros demand to alter the Total Expense Cell (C12), Total Profit Prison cell (C14), and Turn a profit % prison cell (C16).
Click on, OK. It will create a summary study for you in the new worksheet.

Full excel has created 3 scenarios fifty-fifty though we accept supplied simply 2 scenario changes considering excel will prove existing reports equally one scenario.

From this table, we can easily see the bear on of changes in cascade profit %.

#2 Goal Seek in What-If Assay

Now we know the scenario manager's advantage. What-if Analysis Goal tin can tell yous what you lot have to practise to achieve the set target.

Andrew is a class ten^th educatee. His target is to achieve an average score of 85 in the final exam, and he has already completed 5 exams and left with simply ane exam. In the completed v exams, the anticipated scores are as below.

In order to calculate the electric current average, apply the Average formula in B7 cell.

The current average is 82.2.

Andrew'south GOAL is 85, and his current average is 82.two and short by 3.8, with one exam to become.

Now the question is how much he has to score in the terminal exam to get an overall boilerplate of 85 eventually. This can notice out by what-if analysis GOAL SEEK tool.

Pace 1: Go to Information > What-If Analysis > Goal Seek.

Pace two: It will show yous below the dialog box.

Stride 3: Here, we need to fix the cell kickoff. Set Cell is nix but which cell we need the last outcome, i.eastward., our overall boilerplate cell (B7). Next is To Value. This is nothing but for what value we need to ready the prison cell, i.e., Andrew'due south overall average GOAL (85).

The side by side and terminal part is by changing which cell you want to see the impact. So we demand to change the prison cell B6, which is the jail cell for the final subject area's score.

Pace 4: Click on, OK. Excel will have a few seconds to complete the process, but eventually, it shows the end effect like the below one.

Now we accept 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 Assay in Excel. The data table can create different scenario tables based on the alter of variable. We accept two kinds of data tables here "1 variable data table" and "Two-variable data table A two-variable data table helps analyze how two different variables impact the overall data table. In unproblematic terms, it helps decide what effect does changing the two variables have on the result. read more ." In this article, I will bear witness you One variable data table in Excel 1 variable information table in excel ways irresolute one variable with multiple options and getting the results for multiple scenarios. The data inputs in one variable data tabular array are either in a single column or across a row. read more .

Assume you are a selling a 1000 product at 15 rupees, your total anticipated expense is 12500 and turn a profit is 2500.

You are not happy with the profit you lot are getting. Your anticipated turn a profit is 7500. Yous are decided to increase your Per Unit Toll to increase your profit, only y'all don't know how much you demand to increase.

Data tables can help you. Create a below table

Now in the cell, F1 gives a link to the Total Profit Prison cell, i.e., B6.

Step 1: Select the newly created table.

Step 2: Get to DATA > What-if Analysis > Data Table.

Footstep three: Now, you will run into below dialogue box.

Step four: Since we are showing the outcome vertically, leave the Row input jail cell. In the cavalcade input cell, select the cell B2, which is the original selling price.

Stride 5: Click on Ok to become the results. It will listing out turn a profit numbers in the new tabular array.

And so we have our data table ready. If you desire to make a turn a profit from 7500, you need to sell at xx per unit of measurement.

Things to Remember

The what-if assay data table can be performed with two variable changes. Refer to our article on what-if analysis 2-Variable Data Table.
What-if Analysis Goal Seek takes a few seconds to perform calculations.
What-if Analysis Scenario director can requite a summary with input numbers and current values together.