Open In App

What-If Analysis with Data Tables in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

What-if analysis is the option available in Data. In what-if analysis, by changing the input value in some cells you can see the effect on output. It tells about the relationship between input values and output values. In this article, we will learn how to use the what-if analysis with data tables effectively. 

What is What-if Analysis? 

What-if analysis is a procedure in excel in which we work in tabular form data. In the What-if analysis variety of values have been in the cell of the excel sheet to see the result in different ways by not creating different sheets. There are three tools of what-if analysis.

Tools of what-if analysis

There are three tools in what-if analysis:

  1. Goal seek 
  2. Scenario manager
  3. Data Table

Goal seek

In goal seek we already know our output value we have to find the correct input value. For example, if a student wants to know his English marks and he knows all the rest of the marks and total marks in all subjects.

Step 1: Write all subjects and their marks in an excel sheet and do the sum by applying the formula sum.

adding data set for goal seek

 

Step 2: Go into the data tab of the Toolbar.

data tab for what if analysis  goal seek

 

Step 3: Under the Data Table section,  Select the What-if analysis.

what-if analysis in data table

 

Step 4: A drop-down appears. Select the Goal Seek.

goal seek  in the drop down

 

Step 5: The dialogue box appears in the first column write the name of the cell in which you apply the formula sum. Type D10 in Set cell

goal seek dialogue box,set cell option

 

Step 6: In the second column write the value of the target. The target value for this example is 440

goal seek dialogue box to value

 

Step 7: In the third column write the name of the cell in which you want to get marks in English. Provide absolute cell reference, i.e. $D$5

goal seek dialogue box, by changing cell

 

Step 8: Click ok and see the result. The estimated marks for English are 71

final output using goal seek.

 

Scenario Manager

In scenario manager, we create different scenarios by proving different input values for the same variable than by comparing scenarios to choose the correct result. For Example, To check the cost of revenue for three different months.

Step 1: Given a data set, for Revenue Cost of Jan, with Expenses and Cost as its columns. 

data set for scenario manager in what if analysis

 

Step 2: Select the numerical value cell and Go to the Data.

data tab for what if analysis, scenario  manager

 

Step 3: Under the forecast section, click on the What-if analysis.

under forecast, what if analysis

 

Step 4: A drop-down appears. Select the Scenario manager.

scenario manager in drop down, in what if analysis

 

Step 5: A dialog box appears in the dialog box select add option.

scenario manager dialogue box, add button

 

Step 6: A new dialog appears to write the name of the new scenario in the first column. Under Scenario name, write “Revenue of Feb”

adding scenario name in edit scenario dialogue box.

 

Step 7: In the second column select the changing cell. The changing cells for this example, are $E$5:$E$9.

"changing cells" in edit scenario

 

Step 8: A new dialogue box name Scenario Values appears to write the changed value in the box. Enter the values as per shown in the image. Click Ok

scenario value dialogue box.

 

Step 9: Repeat step5, step6, and step8.

scenario manager dialogue box, add button

 

add scenario, scenario name

 

Scenario Values

 

Step 10: Click Ok then select summary.

scenario manager dialogue box, summary...

 

Step 11: A new Dialog box name Scenario Summary appears. Select Result cells: $E$10

result cells, in scenario  summary

 

Step 12: See the result.

output of scenario manager.

 

Data Table

In data, we create a table with different input values for the same variables. It is one of the most helpful features in what-if analysis. One can change different values in x and can achieve different outputs accordingly for research as well as business-driven purposes. 

A data table is of two types:

Data table in one Variable

In the data table in one variable, we can change only one input value either in a row or in a column. It includes only one input cell. For example, a company wants to know about its revenue by changing the cost of raw materials by using a data table. Given a data set, with material and their cost

Step 1: Create a table of revenue cost.

data table in one variable, dataset

 

Step 2: Copy the last cell in which you get output in another cell. D7 for this example. 

copy last cell to add data table in one variable

 

Step 3: Write the values in the cell for which you want to make a change in a column or in rows. 

adding new values in data table in one variable data set

 

Step 4: Go to the data tab of the Toolbar.

data tab, for data table, what if analysis

 

Step 5: Under the data table section, Select the what-if analysis. 

data table, what if analysis

 

Step 6: A drop-down appears. Select the Data Table.

data table, in drop down.

 

Step 7: A dialogue box name data table appears then select the cell in which you want to change the input value in a row or in the column. Input the value of the Column input cell to be $D$3. Click Ok. Your data table is ready. 

data table dialogue box, column input cell

 

Data table in two Variable

In the Data table in two variables, we can change two input values in both row and column. It includes two input cells. For example, A person wants to know about per month installments of loan by the different rates of interest and for the different time periods for the same principal amount.  

Step 1: Create a table to find PMT.

data table in two variable, data set

 

Step 2: Copy the last cell in which you get output in another cell

copy last cell, for data table in two variable

 

Step 3: Write both values you want to change in both columns and rows.

changing columns and rows value for data table in two variable

 

Step 4: Go to the Data tab of the toolbar.

data tab, for data table in two variables, in what-if analysis.

 

Step 5: Select the what-if analysis.

under forecast, what if analysis

 

Step 6: Select the Data Table.

drop down, data table, under forecast.

 

Step 7: A dialogue box appears in which you have to select the cell in which you want to change the value in both row and column. The Row input cell value is $D$5 and the column input cell value is $D$6.

data table dialogue box, row input cell, and column input cell.

 

Step 8: Click ok and see the result.

output of the data table in two variables.

 



Last Updated : 16 Oct, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads