What if Analysis Data Table in Excel

What is What-If Analysis?

Suppose you have a cell A15 which is formula driven i.e. has a formula and the formula is based on values in different cells (A1, A2, A3, A4), what-if analysis helps us in understanding the changes to A10 when values in cells A1, A2, A3 or A4 are altered.

Let’s say person ABC has bought a car which is worth $40,000. Now ABC gave 19% of this amount as a down-payment and agreed to 48 months’ loan term at an interest rate of 5%. These 4 variables (Purchase Price, Down payment, Loan Term and Interest Rate) will have direct impact on the following variables (dependent variables) –

• Loan Amount – If the customer paid 19% as down-payment, it implies that he took loan for remaining 90%
• Monthly Payments – Monthly EMI costs based on loan taken, interest rates and loan term
• Total Interest – A monthly payment is a combination of interest payments and the actual loan amount – this variable tells us how much interest rate we are paying on the loan amount
• Total Payments – Interest rate plus loan amount

Now you can use formulas to calculate last 4 variables – make sure to give cell location rather than individual values. So, for example, Purchase Price is in cell A6 and Down Payment is in A7, to calculate ‘Loan Amount’ you can use the formula A6*(1-A7) rather than 80000*(0.8).

Similarly, you can calculate rest of the fields. Once this is done, using the following steps, you can incorporate ‘What-if Analysis’ in your work –

• Go to Data Tab and click on What-if Analysis and then select Scenario Manager
• Click on Add and a new pop-up will appear – put a Scenario name such that you can easily understand what it stands for
• Next is changing cells i.e. which cells you want to change to see the impact on ‘Loan Amount’, ‘Monthly Payments’, ‘Total Interest’ and ‘Total Payments’ – let’s say we want to see the impact of changes to down-payment on these 4 variables
• I have put Scenario Name as DP 20% which is current scenario
• In ‘Add Scenario’ – I select the cell consisting of the value corresponding to Down Payment
• And click on OK – a prompt will appear in which excel asks for the value you want to see – currently it is 0.2 – click on OK
• Add 2 more scenarios DP 30% and DP 40% following the same process and corresponding values will be 0.3 and 0.4 and then click on OK
• Now you can see 3 different scenarios – you can select anyone and see the difference it leads to the 4 values ‘Loan Amount’, ‘Monthly Payments’, ‘Total Interest’ and ‘Total Payments’

Now what if you want to see the impact of these changes to Down Payments in one table itself side-by-side? For this, we will introduce a new functionality ‘Data Tables’.

What is a Data Table?

A data-table is a range of cells which give us values of formula driven cells (dependent variables) when values in input cells are changed. In our example, input cells are Purchase Price, Down payment, Loan Term and Interest Rate.

One Variable Data Table

A data table which gives different values of dependent cells corresponding to different values of 1 input variable. In our example, let’s say, we want to look at the changes to dependent variables when we use different values of down-payment. Do note that a data table is different from a normal table -

• To create a One Variable Data Table, write different values of down-payment (input variable) in a column
• Use equal to operator to get the values of dependent variables as a header for this new table as shown in the diagram below
• Once done, select the entire table and go to ‘Data’ tab and then ‘What-if Analysis’ and then select ‘Data Table’
• A new pop-up will appear – since the different values of down-payment are in a column, select ‘Column Input Cell’ and give the location of the Down-Payment value in the original table – let the ‘Row Input Cell’ remain empty and click on OK
• All the values will be populated in the table and now you can easily compare how the 4 dependent variables will change with changing down payment value

Two Variable Data Table

Similar to One Variable Data Table, just that in here we can see the impact on only 1 dependent variable (instead of many) when we make changes to 2 different input variables at the same time - 

• Put different values of input variable in a row and for the second input variable in a column
• Use the equal to variable to get value of dependent variable on top corner of the table

Select the table and click on ‘Data Table’ and for Row, select the value corresponding to input variable in the first table and for column select the value corresponding to second input variable and click on OK