The Two Variable Data Table is another important data analysis tool which helps you to calculate different situations based while you change 2 different variables to affect the calculation. It is not widely used. But it can helpful to calculate for 10 to 20+ variables at once. Here is an example that helps you to understand this type of Data Table.
Problem:
Assume that, you want to take a loan from Bank about BDT 1000000. The Bank Interest Rate is 8.50%. Maximum Years to settle the loan is 6 (6 * 12 = 72 Months). Now you need to calculate Monthly Installment vs Terms amount. If terms increases, then what will be the installment.
Solution:
Plot the above problem in your Excel sheet like below image:
Image 1: Problem Plotting
Calculate B5 cell by using =B4/12 and B7 cell =B6*12. Finally in B9 cell =PMT(B5,B7,B3). After calculation it will looks like this:
Image 2: Monthly Installment Calculated
Now to analyze your Monthly Installments vs Interest Rates design a table like below image:
Image 3: Designed for Two Variable Data Table Analysis
Now Select D5 Cell and use this formula =B9 to link with the original table. Select D5:L16 cell. Click on Data ➪ What-If Analysis ➪ Data Table. Data Table dialog box will appeared like below image:
Image 4: Data Table dialog box
For Row input cell, Select B4 and for Column input cell, Select B3 and Click on Ok. This will show the final result:
Image 5: Result of Two Variable Data Table
Now notice that, you can easily take a decision that, how much money you can pay par month as installment and what will be the Interest Rate and Total Loan amount for that installment. This is why we need to take help from Two Variable Data Table.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw