While you need to check every final result for changing a cell or two cells values, you can use Data Table option to analyze and take your decision. Assume that, you will take a loan from a Bank. Now Bank will inform you the maximum term of loan payment. If the term is long then interest amount is much more than short term. So, you can calculate through Data Table how much you can pay per month to the bank maximum to reduce the loan payment term. This calculation you can do it by using Formulas, Scenario or Goal Seek. But if you want to do fast calculation, then you can take help from this Data Table option.
What is Data Table?
Data Table is a feature located in What-If Analysis under Data menu in Excel. In my opinion, it is nothing but a faster way than Excel formula to analyze every single situation while changing one or two cell's values that are depends the situation. For this reason, this analysis is also known as Sensitive Analysis. Because it shows you every result based on your every changes in cell that depends the final result.
For example, you might be interested to know how changes in the interest amount while you increase the monthly installment each time to take a decision about your loan settlement. Let me explain it more clearly. Assume that, if you pay 1000 BDT for your loan installment, then the maximum term of loan settlement is 100 and interest is 5 BDT. You know that, if you increase the monthly installment, then term will become shorter and as well you don't need to pay interest amount for that rest of the terms.
To check what will be the monthly installment amount while Interest Rate (One Variable) changed or what will be the monthly installment amount while Interest Rate and Term changed (Two Variable) you can take help from One Variable Data Table or Two Variable Data Table.
From the above discussion, it is clear that, Data Table can work while the value of one cell or two cells changed. Through Data Table, There are two important analysis tools are available with other tools in Excel. These are:
One Variable Data Table
and
Two Variable Data Table
In this article you will find One Variable Data Table as written below:
One Variable Data Table:
One Variable Data Table helps you to take decision while changing a single input value in many times and calculated results for each time changing the values. For clear understanding, I'm using a simple example.
Problem 1:
Assume that, you are a Service holder. Your Gross Salary is BDT 50,000. Now you wish to start savings BDT 20000 per month in your Bank (Container Bank) for next 4 years. 4 Year means 12 * 4 = 48 Months. Now simple question is how much money will you deposit after ending of 48 months?
Solution 1:
It is very simple. You can do it by a simple =cell*cell formula. Nothing else. So question arise in your mind that, why we need Data Table to do this? Ok, now guess you want to meet a target after ending of 4 years. Now you need to adjust the monthly deposit amount many times and watch the final result to see when your target will meet up and take a note for that amount. But this is disturbing, why? Because you don't see a visual scale showing there all the changing results at once so that you can take the decision easily after thinking. Please see the below image:
Image 1: After plotting the Problem 1 in Excel and used the formula =C3*C5 in C7 cell
What am I trying to say, you need to change C3 cell manually every time to meet your desire deposit amount. But if there is a scale like 15000 to 32000 monthly savings and the final deposit amount after 4 years, then you can easily take the decision how much money you can deposit per month to meet your desire deposit after the end of 4 years.
Now notice that, here only C3 cell value is changing and all are remains the same, and result showing based on formula in C5 and C7 cells. If I analyze this through Data Table then this type of Data Table will called as One Variable Data Table.
Now design your Excel sheet like below:
Image 2: After plotting for One Variable Data Table
Notice that, I've placed all the changing variable amount in E4:E21 cell, that means all are in a single column. You can place these all in a single row also, like E4:W4. This is the important part of Data Table, that where you placed your changed values each time, in a Single Row or a Single Column. Assume that, your placed as above image.
Now as a Column Headline in F3 cell use this formula =C7 or use =C3*C5 this formula to get the same result from the Original Source Data Table.
Now as a Column Headline in F3 cell use this formula =C7 or use =C3*C5 this formula to get the same result from the Original Source Data Table.
Notice, if your formula not linked directly or indirectly to the Original Source Data Table, then your One Variable Data Table will not work. So, be sure on it.
Now Select E3:F21 cells. Because F3 cell contains the formula that directly relates to Original Source Data and this F3 act as a Column Header. Click on Data ➪ What-if Analysis ➪ Data Table. The Data Table dialog box will appear.
Image 3: Data Table dialog box
Now notice that, if all of your Monthly Savings Amounts are stayed in a column, then you should use Column input cell. On the other hand if all of your Monthly Savings Amounts are stayed in a row, then you should use Row input cell in Data Table dialog box.
As all of my changing values are in a single column (E column), so, I've to use Column input cell in Data Table dialog box. Now Click on Column input cell and enter C3 because Data Table dialog box will follow the Source Data Value used in C3 cell to calculate the Total Deposit Amount after 4 Years. That is why C3 cell have to select in Data Table dialog box.
Image 4: Column input cell in Data Table
Now Click on Ok. You will see, the result like below image:
Image 5: Result of One Variable Data Table
From this table E3:F21, I can easily take the decision, how much money I need to save per month to meet my target after the end of 4 years. Not only that, You can now change the Savings Year and also take view the Deposit Amounts based on Monthly Savings Amount Scale like below image:
Image 6: Changed the number of years from 4 to 2 and it shows the result as well
Problem 2:
Assume that, You wish to take a loan BDT 10,00,000 from a Bank. The Bank inform you that Down Payment is BDT 20,000. Interest Rate is 8.50%, Maximum Term 4 Years (6 * 12 = 72 Months). Now take a decision that, how much money you wish to pay as Monthly Installment to settle the loan and minimum term to settle the loan.
Solution 2:
Plot the above problem in your Excel sheet like below image:
Image 7: Problem 2
In C5 cell use =C3-C4 to calculate the Net Loan Amount. In C7 use =C6/12 to get the Monthly Interest Rate. In C9 cell, use =C8*12 to calculate the Total Months. And after all in C11 cell use =PMT(C7,C9,C5) to calculate Monthly Installment amount. Now your calculated table will looks like below image:
Image 8: After Calculation the result of Problem 2
Now you wish to calculate the Monthly Installment vs Installment Months. To calculate design the format like below image. In the below image One Variable Data Table will calculate how much money do you need to pay as installment if you wish to settle the loan in 36 months, 48 months, 60 months etc.
Image 9: One Variable Data Table format (Row Input cells)
Before calculation, use E4 cell as Row Header and enter =C11 in E4 cell. Now the C11 cell will act as a Reference Formula in E4 cell for F4:N4.
Image 10: Row Header has set (E4)
Now to calculate the Monthly Installment against your wishing terms, Select E3:N4 cell. Click on Data ➪ What-If Analysis ➪ Data Table. This will show the Data Table dialog box. Please note that, the wishing terms are stayed in a single Row (Row No. 3). So, we need to input in Row input cell text area of this dialog box.
Though we have mentioned terms as variable, then we need to use C9 as cell reference. Now your dialog box will looks like below image:
Image 11: One Variable Data Table (Row Input Cells)
After clicking on Ok button, you will get your result like below image:
Image 12: Result of One Variable Data Table
Now you can easily take your decision based on the calculated value.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw