0:03
Alex has helped the company work out time estimates.
The next thing they need to look at is
their projected estimates for their fixed expenses.
What they have in the workbook is last year's fixed expenses,
and what they think is the likely increase,
which is nine percent.
The problem is, they'd like to have a look at a range of different scenarios.
So, they might want to see what happens if it's five percent,
or how much it will be if it increases to 14%.
Where you want to see a range of different outcomes
using different inputs using just one formula,
Excel offers you a technique called a Data Table.
Data Tables can be found on the Data tab of
the Ribbon behind the What-If Analysis drop down.
There are two types of data tables you can create: a single variable,
or a dual variable,
and we are going to look at an example of each.
Let's start with our first situation where what we want to do,
is have a look at what our output is going to
be for all of these different percentage increases.
The first thing you need to do,
is to put in your different variables.
And you can lay them out either in a column or in a row.
Data tables don't mind.
Then just above or just to the left of your various percentages,
we are actually going to put in the basic calculation.
So, we're going to start by typing equals,
and we're going to put our previous fixed expense times one plus nine percent.
And we're going to click Enter,
and what that is going to give us is
the previous fixed expense plus the previous fixed expense times nine percent,
which would give us a nine percent increase.
That is how much our expenses will be if we do get a nine percent increase.
That's the first step, create the formula.
Step two is to create the data table.
For this, we're going to select that whole block.
So, making sure we've got our formula and our alternate input selected,
we're then going to come up to our What-If Analysis,
and we're going to select Data Table.
The data table offers us two options: row input or column input.
In this situation, it's the column of values we're changing, not the row.
So, make sure you've clicked Column input.
And now, what Excel is asking you is,
which of the values in your calculation do you want to replace with all of these?
And in this case,
it's the nine percent.
So, we're going to click into E17.
We're going to click OK,
and it has completed all the values for us nice and quick using only one calculation.
I'm just going to click in my data table for the moment.
And if you have a look in the formula bar,
you'll see it looks slightly unusual.
Instead of the normal formula,
we've got an =TABLE.
And whenever you see that,
it means we're dealing with a data table.
Another thing to be aware of data tables,
is if I try and delete just a single cell,
Excel that will actually tell you,
you can't change part of a data table.
So, it's a bit of all or nothing.
If I needed to delete my data table,
I'd need to make sure I've selected all of those results.
And then it's quite happy to remove them.
I'm just going to pop them back in because I don't want to get rid of them.
Let's look at another example.
This time we're going to do a two input data table.
And what we're looking to do here is have a look at what
our projected revenues will be depending on what price point we'll use for our manuals,
and how much we expect our estimated income to go up.
At this stage, we're thinking of using a sale price of $35.
Our previous sales units were 4700 and our expected increase is seven percent.
So, what we're going to do is multiply all three of these to
get our estimated income if we sell for $35 each,
and get a seven percent increase.
We're then going to create a data table using
two variables so that we can see the different outcomes for the different price points,
and the different increases.
So, once again, set it up so you've got one variable running across the top,
and one running down the left.
And we're going to put our formula in the top left corner.
Start by clicking into A26, and type equals.
And then we're going to say,
Sales Price times by our Units,
times, by open our brackets again,
one plus seven percent and click Enter.
And there's our expected revenue given those inputs.
Now, we want to create our data table.
And once again, we're going to make sure that we
select the entire block including our calculation.
Then we're going to come back to our What-If Analysis and select Data Table.
This time, we have both
a row input cell
and the column input cell.
Our row input cell is the value we're going to swap out using these.
So, that would have been our Sale Price.
I'm going to click on Sale Price.
Our column input cell is the value that we're going to replace these with.
And that was our Expected Increase.
So, I'm going to click on that,
then click OK. And that's your data table.
A very quick model showing you a whole bunch of
different options depending on your inputs.
Data tables make use of a technique in Excel called array formulas.
And we'll be looking at array formulas in a bit more detail in the next course.
But for now, you have a really quick way
of getting multiple results with a single formula.
In the next video,
we're going to look at
some even more exciting modeling tools tucked behind the What-If Analysis button.
Dr Yvonne BreyerDirector, Learning and Teaching
