What-If Analysis in Excel: The Data Table
The What-If Analysis tools in Excel are an excellent set of functions that allow you to forecast the change in your output data regarding the change in your values. Excel has three What-If Analysis tools: Scenario Manager, Goal Seek, and Data Table.
The Data Table tool lets you view how different inputs will impact the outcome of your formula. You can use the Data Table to get a prospect of your formula and see what output you can obtain from different inputs.
How the Data Table Works
The Data Table in Excel takes a set of inputs, places them in your formula, and finally creates a table of the outputs for every input.
To utilize the Data Table in excel, you need to first have a formula ready. Then, you can refer to the formula in another cell and work the Data Table on it. Finally, you can feed the Data Table two sets of data: Row input cell and Column input cell.
The Data Table will then take the values in the adjacent row as the input for the Row input cell and the values in the adjacent column as the input for the Column input cell and will create a table of the formula’s outputs.
Example 1: Two-Variable Data Table
For this example, suppose that you have six toy cars with different prices and want to know how much revenue you’ll get by selling given quantities of them.
To do this, you need to multiply the number of sold toy cars of a type by the price, then finally subtract the tax rate to achieve the revenue.
So, all in all, for this Data Table, you’ll have two variable inputs: The quantity and the price. First, let’s create the formula:
In cells A1, B1, C1, and D1, type in Price, Quantity, Tax, and Revenue, respectively. Select cells A1 and D1. In the Home tab, from the Numbers section, click the $ symbol to change the Number Formatting of these cells to Accounting (This is because these cells will house accounting values). Select cell C2. In the Home tab, from the Numbers section, click the % symbol to change the Number Formatting of this cell to Percentage. Select cell D2 under Revenue and enter the following formula in the formula bar, and press Enter: =(A2B2)-(A2B2C2) This formula will multiply the price of sold units (A2) by their quantity (B2), and then it will subtract the tax value from it (A2B2*C2).
You can go ahead and give sample values to the cells and observe as Excel calculates the revenue obtained from the sales.
Excel’s Different Inputs Data Table
To create a Data Table for different inputs, you need to have a bare table containing the two inputs.
Select cell G2 and enter the formula below in the formula bar: =D2 This will set cell G2 equal to the formula you created previously. In the cells below G2 (column G), enter the possible quantities of sold pieces. For this example, the numbers are 5, 10, 15, 20, 25, and 30. In the cells next to G2 (row 2), enter the prices of each piece. For this example, the prices are 10, 20, 30, 40, 50, and 60. Select the cells where you inserted the prices and the cells below, which will display the possible revenues, and change their Number Formatting to Accounting.
Finally, now that you have the row and the column set, it’s time to turn this table into a Data Table.
Select the table by clicking G1 and dragging it all the way to M7. Go to the Data tab, and in the Forecast section, click What-If Analysis. A list of three items will appear. From the list, select Data Table. This will bring up the Data Table dialog. In the Row input cell, enter A2. The table row contains the prices, and the price input in your original formula is cell A2. In the Column input cell, enter B2. The column in the table contains the quantities of sold pieces. Once you have set the two inputs, click OK. Excel will now generate a Data Table.
You now have a Data Table for your formula! The Data Table provides you with valuable information about your potential sales.
For instance, you can now see how you can get a revenue of $1000 or greater by looking at the six cells in the bottom right corner. Or you can compare and know that selling 25 of $20 toy cars will make you more revenue than selling 15 of $30 toy cars.
Example 2: One-Variable Data Table
Since the Data Table is, after all, a table, it can only house inputs in a row and a column. This means that you can’t have more than two variable inputs in a Data Table. However, you can certainly have less than two: A Data Table with single variable input.
For this example, keep the previous example in mind. However, this time suppose that you want to get a table of potential revenue exclusively for the $50 toy cars.
The method is still the same as the two-variable data table, albeit the positioning is slightly different.
To get started with creating the Data Table, you need to create a formula. The formula for this example is the same as the previous one. Once you have the formula ready, it’s time to set out the data.
Select Cell H1 and in the formula bar, enter the formula below and press Enter: =D2 Enter the numbers in cells G2 and below. For this example, enter 5, 10, 15, 20, 25, and 30 in cells G2 to G7.
Now it’s time to create the Data Table.
Select the table by clicking G1 and dragging it over to H7. Go to the Data tab, and from the Forecast, section click on What-If Analysis. From the What-If Analysis list, select Data Table. In the Data Table dialog, click on Column input cell and type in B2. Leave the Row input cell empty. Since the goal is to get a revenue Data Table for a fixed price, you only need to feed the Data Table the number of toy cars sold and not their prices. The quantity is set out in the Row of the Data Table, and the input for it in the formula is cell B2. Click OK. Excel will create a one-variable Data Table.
So now you can figure out how much revenue you can generate by selling $50 toy cars, minus the taxes, of course.
Set the Possibilities in a Table
The Data Table gives you a good look at the outcome of your formula with different inputs, and now you know how to create one.
The Data Table is only one of the What-If Analysis tools in Excel. There’s more to learn if you want to answer all your What-If questions with Excel.
