One variable Data Table in excel means by changing the one variable along with multiple options and get the results for multiple scenarios.
Table of Contents
What is One-Variable Data Table in Excel?
Data table is a very useful tool which is available in the forecast group of the what-if analysis tab, technically there are two types of data tables one which is two variable and one which is one variable data table, in one variable data table we analyze how a single variable impacts on the total table or data.
In this article, we are going to discuss one of the most powerful data analysis technique in excel.
Data Table is a What-if-Analysis tool, which shows the different scenarios for one module. A Data Table is nothing but creating scenarios at different alternatives. Our project, investments, plan depends on variables. It could be one or two. Similarly, in excel we have two types of data tables one is depended on a single variable and another one if depended on two variables.
In this article, I will demonstrate one variable data table excel
How to create One-Variable Data Table in Excel?
This is the first of those two data tables. One variable means single factor changes and the result also changes. For example, You are running a business and you want to see the scenarios at different growth levels.
You are expecting a revenue growth of 15% from the last year. Here is 15% is the variable. If your revenue is increased by 16% what would be the scenario if your revenue increased by 17% what would be the scenario and so on.
One Variable Data Table in Excel Example #1
You are taking the loan of Rs. 2, 50,000 for 2 years. You are in discussion with the loan officer concerning interest rate.
You need to analyse 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.
Step 1: Calculate the monthly EMI using the PMT function.
Step 2: Now create a scenario table as shown in the below image.
Step 3: Select the cell E9 and give a link to the cell B6 (EMI Amount). Now the cell E9 showing the monthly EMI.
Step 4: Select the range from D9 to E22.
Step 5: Click on Data Tab then What-if-Analysis and Data Table
Step 6: 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 row input cell and if our scenario table interest rates are horizontal then we ignore column input cell. In this example, I have ignored Row input cell because scenario table different interest rates are in a vertical way.
Step 7: 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.
One Variable Data Table in Excel Example #2
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 for 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 90% efficiency level.
At 90% efficiency level your team can achieve the 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 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 Column input cell I have given the link to the cell B10 because based on the different efficiency level we are going to create the scenarios. Now, 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 One-Variable Data Table in Excel.
- Once the scenarios have created, we cannot undo 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 Row input cell if the scenario to be shown in the vertical form. If the scenario is to be shown in horizontal form then leave 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 One-Variable Data Table in Excel using examples and downloadable excel templates. You may also look at these useful excel tools –