Continuing Problem 5 Create a Two way Data Table for Expected Profit With Order Quanityt
How to Create a Two-Variable Data Table in Excel?
A two-variable data table helps us analyze how combining two variables impacts the overall data table. The word itself suggests two variables involved in this data table. In simple terms, when the two variables change, what is the impact on the result? For example, in the one-variable data table, only one variable changes. But here, two variables change simultaneously.
Table of contents
- How to Create a Two-Variable Data Table in Excel?
- Examples
- Example #1
- Example #2
- Things to Remember
- Recommended Articles
- Examples
Examples
Let us take examples to see how we can create a two-variable data table in Excel.
You can download this Two-Variable Data Table Excel Template here – Two-Variable Data Table Excel Template
Example #1
Assume that you are taking a bank loan and discussing your interest rate and the repayment period with the bank manager. You need to analyze the different interest rates. And, at different repayment periods, what is the monthly EMI amount you need to pay?
Also, assume that you are a salaried person, and after all your monthly commitments, you can save a maximum of ₹18,500/-.
The initial proposal forms the bank as below.
At 22% p.a. interest rate, the monthly EMI for 3 years is ₹19,095.
Create a table like this.
Now, to cell F8, give a link to cell B5 (which contains EMI calculation).
Select the data table that we have created for creating scenarios.
Go to the "Data" tab, then select "What-if Analysis" and "Data Table."
Now, click on "Data Table." It will open up the below dialog box.
We have arranged our new tables like different interest rates vertically and other years horizontally.
In our original calculation, the interest rate is in cell B4, and the number of years is in cell B2.
Therefore, for the row input cell,give a link to B2 (that contains years. In our table, years are there horizontally). For the column input cell,please provide a link to B4 (that has the interest rate and in our table interest rate is there vertically).
Now, click on "OK." It would create a scenario table instantly.
So now, you have all the scenarios in front of you. Your monthly savings is ₹18,500 per month.
Option 1: If you do not want some spare cash.
You need to negotiate with the bank for an interest rate of 18.5% per annum for 3 years. You need to pay a monthly EMI of rupees if you can negotiate for this rate. ₹18,202.
Option 2: If you need some spare cash.
In this volatile world, you need some cash all the time. So, cannot spend all the ₹18,500 savings money for your salary.
If you want to let us say ₹3,000 per month as spare cash, you need to negotiate with the banker for a maximum of 15.5% for 3.5 years. In this case, you need to pay a monthly EMI of ₹15,499.
Such a useful tool we have in Excel. We can analyze and choose the plan or idea according to our wishes.
Example #2
Assume you are investing money in mutual funds through SIP planning. For example, monthly, you are investing ₹4,500. It would help if you analyzed to know what the return on an investment after certain years is.
You are unsure when to stop investing money and what percentage you expect.
Below are the basic details to do the sensitivity analysis.
| Particulars | Amount |
|---|---|
| SIP Amount Monthly | 4500 |
| Number of Years | 25 |
| Payment Per Year | 12 |
| Expected Interest Rate | 10.50% |
| Future Value | ??? |
Apply the FV function to know the future value after 25 years of investment.
Ok, the future value of your investment after 25 years is 65 lakhs.
Now you need to know at different years and at different rates what would be the return on investment. Create a table like this.
Now give a link to the cell F4 from B5 (which contains the future value for our original investment).
Select the table we have created.
Go to the "Data" tab, then select "What-if Analysis" and "Data Table."
Now, click on "Data Table." It will open up the below dialog box.
In the ROW, input cell select links to cell B2 (that contains no., years). We have selected this cell because we have created a new table. In that table, our years are in the row format horizontally.
In the COLUMN, input cell select links to cell B4 (which contains the expected return percentage). We have selected this cell because we have created a new table. In that table, our expected percentages are in column format, vertically.
Click on "OK." It will create a scenario table for you.
Look at the cells that we have highlighted. In the first attempt, we need to wait 25 years to get the sum of ₹65 lakh at a 10.5% return. However, at a 13% return rate, we get that amount in 22 years. Similarly, at a 15% return rate, we get that amount in just 20 years.
Like this, we can do a sensitivity analysis using the two-variable data table in Excel.
Things to Remember
- We cannot undo the action (Ctrl + Z) taken by the Data Table. However, you can manually delete all the values from the table.
- We cannot delete one cell at a time because it is an array formula Array formulas are extremely helpful and powerful formulas that are used in Excel to execute some of the most complex calculations. There are two types of array formulas: one that returns a single result and the other that returns multiple results. read more .
- The Data Table is a linked formula that does not require manual updating.
- It is helpful to look at the result when two variables change simultaneously.
Recommended Articles
This article is a guide to creating Two-Variable Data Table Excel. Here, we discuss creating a two-variable Data Table in Excel using examples and downloadable Excel templates. You may also look at these useful Excel tools: –
- How to use PPMT Function in Excel?
- Excel Data Table A data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section. read more
- MS Excel Course
- One-Variable Data Table Excel One variable data table in excel means changing one variable with multiple options and getting the results for multiple scenarios. The data inputs in one variable data table are either in a single column or across a row. read more
- CSV Files into Excel It is easy to read the data presented in Comma Separated Values format by opening with excel but scrambled data makes hard to read. There are also a few problems associated, like unexpected data changes, by changing format when the CSV file is opened directly. Opening a CSV file in excel is a simple 5 step process and there are three ways top do it. read more
Source: https://www.wallstreetmojo.com/two-variable-data-table-in-excel/
0 Response to "Continuing Problem 5 Create a Two way Data Table for Expected Profit With Order Quanityt"
Post a Comment