Welcome to Highline BI348 class video number five.
Hey, if you want to download this workbook, BI348 Chapter 7
Start, or the finished file click on the link
below the video.
Now we've already gotten busy in this workbook
from the start file.
We created this sheet quad fixed costs, variable cost, profit.
Created our model.
We did some work on Goal Seek.
But now we want to see a data table.
And here's the deal, we saw with Goal Seek
and by manually changing an input like quantity,
we could see the different profit possibilities.
But with a data table, we can simply tell the data table
to look at one formula in our model
and give it a whole range of new values for our decision
variable or, any particular variable.
And it will spit out all the answers.
Now I want to copy this model over to a new sheet.
I'm going to highlight this.
And before we copy it, I'm going to come over and click
on Calculate Revenue and use our keyboard Shift
F11 to insert a new sheet.
Double click, and I'm going to call this 1 space V space data
table, because there's two different types of data tables,
one variable which we're going to do here, and a two variable.
All right, so now I'm going to go over
to our sheet, Control C to copy, and Control V. Point
to our smart tag and say keep source column widths.
By the way, we're copying the sheets over
just because we're learning.
By all means, we could have done our Goal Seek in our data
tables all on a single sheet, which
is often the times what you do.
But we want to keep it simple as we're learning.
Right, now I'm going to scroll down here.
And again, here's the idea, we're actually
going to do it for a number of formulas,
and I'm going to start with this formula.
Well this formula for total revenue, of course,
depends on this 2,100.
So I want to put, for example, a 0
into this formula, a 500, 1,000-- all the way up
to say 6,000 units-- and have the doubt it able
spit out all the answers.
Below this, I'm going to create a new column of all
of the inputs-- meaning 0, 500, 1,000, 1,500, et cetera--
for our formula to look at.
So I'm typing quantity, Home Ribbon,
and I'm going to add some dark blue fill and some white font
and this border here.
Now guess what, I could type 0, 500, 1,000, 1,500--
I could even use a copy trick-- but these
are going to be formula inputs.
So I'm going to come up here to our formula input area,
data table start unit value, and that's going to be zero.
And then we're going to type data table unit increment value
and that's going to be 500.
I can already see that shouldn't have currencies
so I'm going to do Control Shift grave accent, tilda
to apply general number format.
Now I'm going to come down here and very carefully say equals
and click on our data table start value and Enter.
Now I'm going to create a formula that
looks at the previous one cell above a relative cell reference
and adds our increment.
Now we need to lock that increment so I click the F4
key to lock it, Control Enter.
Now I can copy it down.
I don't know how many to go.
6,000, I'm going to stop right at 6,000.
These are going to be our inputs for our data table.
Now I'm going to highlight with the borders.
Now those are formulas and I would like to add that,
but for the time being I'm not going to.
Now here's the thing, we have a label
in all of our new decision variables
for quantity and essence.
But in order to get this to work,
the data table is going to slap all of the new formulas
instantaneously here, but it's got
to know which formula it wants to substitute
this quantity into.
And here's the weird thing, you actually
have to put the formula.
Now normally, we would like a label here,
especially if we're going to make a chart from this What
If analysis data table.
But hey, we have to say equals and we're
going to start with total revenue-- we're actually
going up try a bunch of different of these formulas
with the data table-- and hit Enter.
Now notice, that's kind of just there all alone.
It's violating our rule that we should always
have proper labels and whatnot.
But that's the way the data table works.
And at the end what we'll do something,
we'll put a label over here, so it's not as strange,
just a lone formula here.
But here it is.
We have our inputs.
We have our formula or our reference to our formula.
Now you simply highlight all of the values
are going to be substituted in, the actual formula you want
to do the substituting into, and now we
could go up to Data, What If analysis, and to Data Table.
Now there's a keyboard in 2007 and later,
but the keyboard and earlier versions as easy.
Notice is called a Data Table so we can simply go Alt D, T,
and there it is.
Now the data table has what's called a row input cell
and a column input cell.
The way you have to memorize it is it's saying column input.
That means all of the new quantities
and because they sit in a column,
that's how they named this.
Now I have to click on the original formula input.
And data table feature will look at this formula,
it will know that it's supposed to change that formula
input by putting all these various values in.
Now when I click OK, check that out.
That is totally amazing.
Now if you try to change or delete any one of these,
this is a special type of formula.
It doesn't even say what kind of special formula in here,
it just says cannot change part of the data table.
But from our earlier studies in business 218,
we know that if we were looking up in a formula bar
and we see curly brackets that were automatically put in,
that means this is the type of an array formula.
And sure enough the process delivered a bunch of values
and entered them in as an array.
So you actually can't change any one part of it.
But there it is.
And we can see exactly at 2,000 then
it gets exactly the right value.
That's What If analysis using data table.
All of these various values are being thrown into this formula
here.
Now actually, I'm going to highlight this whole thing
and move it.
I'm going to move it by pointing to the edge,
and that cursor right there is the move cursor.
That's the selection cursor.
That's the Angry Rabbit or cross hair.
We want the move cursor, and I'm going to drag it down one.
Notice we are allowed to change the whole array, move it,
but you just can't change an individual part.
Now I'm going to add some borders here,
and I'm definitely going to put Total Revenue.
I want a label up here.
Now we're going to do something totally cool.
We're going to delete all of this.
Because yes, we have one variable in a column
and we used a one variable data table, but guess what,
I can put all sorts of different formulas
as long as they all use that decision variable.
And for each one of these columns,
if I invoke only the column input
meaning a one variable data table,
it will change all of the formulas.
So this is pretty profound here.
Now I want Total Revenue, then I want Total Costs,
Tab, then I want Fixed Costs, Tab, then I want Total Profit.
Hopefully I spelled all those right.
F7 to check if they're all spelled right.
Now I'm going to come up to C, D, E, and F,
and then click on one of them and drag.
I want to make sure they're all the same size
and they're all fitting the labels.
Now I'm definitely going to come over here,
right click, Mini Toolbar.
There's our Format Painter or Copy Formatting Only.
I'm going to click and drag.
I clicked, I didn't like go.
I click and dragged, and I want to format all of those.
Now I'm going to reference up and get my total cost formula.
There's my total cost formula tab.
My fixed cost, now this is kind of a strange thing
because we don't have a formula for fixed cost,
but I'm actually going to need it
because later I'm going to make up fixed costs variable cost
chart.
So I'm going to do some really crazy here.
I'm going to click this.
It'll actually just copy it down because the decision variable
doesn't affect, Tab, and then total profit, and Enter.
Now let's highlight all these and highlight
with some borders.
And now the trick is here's all my new inputs.
I highlight all of the formulas of the top of each column,
a bunch of empty cells below.
And now when I Alt D, T for data table--
again this is a column input because the values
are sitting in a column.
I simply-- don't click those, I click the original decision
variable that each one of these formulas is using.
And when I click OK, that is amazing.
That means I did What If analysis
on a bunch of different formulas that have
this particular variable here.
Now I want to come over.
I'm going to highlight the inside of this
and at least add that green there.
And to indicate that these are formulas,
I'm going to do Control B and I'm
going to add a label over here.
So I just put a label pointing their formulas
from original model at q equals 2,000, Control B to bold it.
That is pretty amazing.
And you can see that here's our 2,000 here,
and these are exactly the same values
that we got when our decision variable was 2,000 up here.
That is a data table.
And it is amazing, especially when the formulas
in your model-- you have many, many, many, many formulas
leaning down to some summary formula.
And using formulas-- because there's
no problem with these particular formulas
here-- we could create some formulas and not
use data table.
And actually I have an example over to the side
but you could look at of exactly that.
But when you get a complicated model,
the data table really comes to the rescue.
You put all of the new decision variables
you want and data table will slap them
into as many columns of formula as you want.
Now, in our next video, we'll see
how to have a variable listed in a column and a row,
and do a two variable data table for What If analysis.
All right, we'll see you next video.