Building Scenarios with Data Table in Excel
$
$
FOUNDER
What is Data Table?
$
$
• “Data Table” in Excel is a simple yet effective tool that can be used for Financial and Data analysis in Excel • It can also be used to create scenarios in financial modeling • To use a data table we need a few inputs that would result in an output. • We can use Data Table with ONE or TWO variables
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example
Let us discuss with an example
$
$
Email: Support@skillfinlearning.com
• • • •
John wants to take a loan of USD 100,000. Interest rate on the loan is 5%. Repayment tenure is 20 years. Before deciding whether to take the loan, John wants to analyze how the EMI changes with change in interest rate (from 3% to 7%)
Mobile: 9650010925
Let’s get started
First, calculate EMI on the loan amount
$
$
Loan Amount
USD
100,000
Interest rate
Percent
5%
Tenure
Years
20
EMI
USD
660
EMI can be calculated using PMT function in excel. For more details, please refer Calculating EMI on Home Loan
Email: Support@skillfinlearning.com
Mobile: 9650010925
Creating data table with single variable (1/4) In Excel, create a table range with the interest range (3-7%) – as shown below 3.0% 3.5%
Interest rate
4.0% 4.5% 5.0% 5.5% 6.0%
Link the output (which is EMI in our example) to the top right cell of your data table
6.5% 7.0%
Email: Support@skillfinlearning.com
Mobile: 9650010925
Creating data table with single variable (2/4) Select the data table range (Cell H7:I16 in this example)
Email: Support@skillfinlearning.com
Go to Data, Select What if analysis, Select Data Table from the drop down
Mobile: 9650010925
Creating data table with single variable (3/4) You get the following Data Table input box.
Email: Support@skillfinlearning.com
Leave Row Input Cell blank and link the interest rate (Cell D4) in the example to column input cell (as it is the input required to calculate the output ie. EMI). Then Press OK.
Mobile: 9650010925
Creating data table with single variable (4/4)
You get the following Data Table as output
Email: Support@skillfinlearning.com
•
On analyzing the output, we see The EMI reduces with decrease in interest rate Increases with increase in interest rates
•
John can now plan the impact of interest rate on EMI and his monthly cash flows
Mobile: 9650010925
Creating data table with two variables (1/4)
Link the output (which is EMI in our example) to the left corner of your data table
John wants to see the change in EMI with - Change in interest rates (3-7%) - Change in Loan tenure (10-20 years)
Create a data table range with interest rates in y-axis and repayment tenure in xaxis (as shown here)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Creating data table with two variables (2/4)
Select the data table range (Cell N7:Y16 in this example)
Email: Support@skillfinlearning.com
Go to Data, Select What if analysis, Select Data Table from the drop down
Mobile: 9650010925
Creating data table with two variables (3/4)
• Link the “Row Input Cell” to tenure (“Cell D5” in the example) • Link “Column Input Cell” to the interest rat (“Cell D4” in the example) See Screen shot • Then press OK
Email: Support@skillfinlearning.com
Mobile: 9650010925
Creating data table with two variables (4/4) •
On analyzing the output, we see The EMI reduces with decrease in interest rate and vice-versa Decreases with increase in repayment tenure and vice-versa
•
At 3% interest rate and 20-year repayment tenure, the EMI payment amount is the lowest Whereas at 7% interest rate and 10- year repayment tenure, the EMI amount is the highest
Here is the Data table you get
•
• EMI at different repayment tenure and interest rates Email: Support@skillfinlearning.com
Mobile: 9650010925
John can plan the impact on interest rate and tenure change on EMI and on his monthly cash flows and take appropriate financial decision
Want to learn more? Explore our “Financial Analysts Skills Training” (FAST) course Master the complete financial analytics and Excel modeling skills to get trained as a Financial analyst by an Expert instructor Includes: Annual (1 year) access Take the program anytime anywhere Direct access to the instructor Weekly mentorship assistance 10 hours on-demand HD videos 7 real life case illustrations 7 Practice assignment Access on Mobile and Tablet Certificate of Completion 30-day money back guarantee Email: Support@skillfinlearning.com
You get a Personalized Training Experience: Unlimited access to video content 1 on 1 coaching from expert instructor Individual mentorship assistance Dedicated doubts resolution sessions Practice on real world projects from industry experts Evaluation and feedback on assignments For more details please visit; Financial Analyst Skills Training (FAST)
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925 Email us: support@skillfinlearning.com