We're going to start this one-variable data table demo by first explaining a simple revenue model.
Here, revenue is calculated as price multiplied by quantity sold. We start with a price input of 100, which increases by 10% each year. This 10% is an input – the annual price increase percentage – and each year's price is calculated based on the previous year's.
Similarly, quantity sold starts at 800 units, and this grows by 25% annually. That 25% is the quantity sold increase percentage, and again, each period builds on the one before.
However, we don’t always know exactly what these input values should be. For instance, we might not be sure whether we’re going to sell 800, 900, or 1,000 units in Year 1. And that uncertainty will have knock-on effects for future years' revenues.
This is where a data table becomes really useful. We can use it to test how a specific output – in this case, Year 5 revenue – changes under different input scenarios for quantity sold.
So, down here, I’m going to enter the possible Year 1 quantity inputs: 800, 900, and 1,000.
In the cell next to them, I’ll link to the output we're interested in – which is the Year 5 revenue figure.
Next, I select the entire area including the input values and the linked output cell, then go to Data > What-If Analysis > Data Table.
Because this is a one-variable data table, I only need to fill in either the row input cell or the column input cell. My inputs are arranged in a column, so I select the column input cell, and I link it to the original input cell that these values relate to – in this case, that’s cell C14, where the Year 1 quantity sold is entered.
Click OK, and now Excel runs the calculation for each scenario.
Here’s what it’s doing in the background:
-
It starts by using 800 (which is already the original value), and calculates a Year 5 revenue of 285,957 – which matches what we see above.
-
Then it replaces that input with 900, recalculates the entire model, and gives us 321,722.
-
It does the same for 1,000, giving us 357,446.
We can double-check this by manually typing 900 into cell C14, and you'll see that the Year 5 revenue updates to 321,722 – exactly what the data table showed. Same goes for 1,000: we get 357,446.
That’s how a one-variable data table lets you quickly explore different input scenarios in Excel – really useful for sensitivity analysis in financial models.
