What is One-Variable Data Table in Excel?
One variable Data Table in excel means by changing the one variable along with multiple options and get the results for multiple scenarios.
How to Create a One-Variable Data Table in Excel? (with Examples)
You are taking a loan of Rs. 2, 50,000 for 2 years. You are in discussion with the loan officer concerning the interest rates.
You need to analyze at different interest rates what is the monthly payment you need to pay to clear the loan. For the calculation purpose, take the base rate of 12% per anum.
|Month Per Year||12|
Below are the steps for calculation of monthly EMI using PMT function –
- Calculate the monthly EMI using the PMT functionEMI Using The PMT FunctionPMT function is an advanced financial function to calculate the monthly payment against the simple loan amount. You have to provide basic information, including loan amount, interest rate, and duration of payment, and the function will calculate the payment as a result..
- Now create a scenario table as shown in the below image.
- Select the cell E9 and give a link to the cell B6 (EMI Amount). Now the cell E9 showing the monthly EMI.
- Select the range from D9 to E22.
- Click on Data Tab, then What-if-Analysis and Data Table.
- Data Table dialogue box will come up. In the Column Input Cell, select the cell B5 (which contains basic loan interest rate).
One Variable Data Table in Excel, we always ignore either ROW input cell or Column input cell. It depends on our table structure. If our scenario table different interest rates are vertical, then we ignore the row input cell, and if our scenario table interest rates are horizontal, then we ignore the column input cell. In this example, I have ignored the Row input cell because the scenario table different interest rates are in a vertical way.
- Now click on the OK button to create different scenarios.
Now the range E10:E22 showing some new values. From the table, it is very clear that @12.5% interest rate monthly EMI will be 11,827 INR, and @13.5% interest rate monthly EMI would be 11,944 INR and so on.
This is how works one variable data table in excel. You can show this in a chart as well.
Assume you are a sales manager of a company. From the management, you have received a monthly sales target of 1, 70, 00 USD from your team. The below table shows the sales target of 6 members. You need to analyze what should be their efficiency level to hit the target of 1.7 lakh USD in a month.
Your teams’ overall target is 2.04 lakhs. You are not sure at what percentage of efficiency they need to bring in to the table to achieve the target given by the management.
Your team can give a maximum of 90% efficiency level, and you have calculated the total revenue at a 90% efficiency level.
At a 90% efficiency level, your team can achieve total revenue of 1.83 lakh USD in a month. You need to what should be the efficiency level to achieve the revenue target given by the management.
Create a scenario table, as shown in the below image.
- Step 1: Below the table, you need to create an excel file.
This table shows at different efficiency levels what will be revenue?
- Step 2: Select the cell H3 and give a link to the cell B11 (at 90% efficiency level revenue cell). Now the cell H3 cell showing the 90% efficiency level revenue.
- Step 3: Select the range from G3 to H12.
- Step 4: Now find the Data Table under the What if Analysis section.
- Step 5: Once you click on Data Table, we need to give a link in the Column Input Cell, select the cell B10 (that contains efficiency percentage).
For the Column input cell, I have given the link to the cell B10 because, based on the different efficiency levels, we are going to create the scenarios. Now, the data table understands at 90%; revenue will be 1.83 lakh USD. Similarly, it will create scenarios for 100%, 95%, 90%, 85%, 80% and so on.
- Step 6: Click on OK to create different scenarios.
Now the range G3:H12 showing scenarios. Management has given the target of 1.70 lakh USD for this month. In order to achieve that much revenue, your team needs to perform at an efficiency level of at least 85%.
In this way, you can use Data Table to create different analysis and chose a suitable scenario to achieve the goals.
Things to Remember
- Once the scenarios have been created, we cannot undo the action. It will remain the same.
- We cannot even modify the cell values because it becomes an array formula excel, and you need to delete everything, not one by one.
- In one variable data table, always leave out the Row input cell if the scenario to be shown in the vertical form. If the scenario is to be shown in the horizontal form, then leave the Column input cell.
- You can change the value in the main database to see the real-time results at different types of alternatives.
This has been a step by step guide to One-Variable Data Table Excel. Here we discuss how to create a One-Variable Data Table in Excel using examples and downloadable excel templates. You may also look at these useful excel tools –