Welcome to Highline BI348 class video number six.
If you want to download this, BI348 chapter 7
Start or the finished file, click on the link
below the video.
We've done a lot in this workbook so far.
On each one of these red sheets is the sheets we've completed.
And in the last video, we did on a one variable data table.
Here it is.
It was absolutely amazing what it could
do for "what if" analysis.
Now we want to talk about two variable data tables.
Now I'm going to highlight this model.
And again, normally we do all of this on the sheet,
so we have all of our variables set.
But, again, we're learning, so we put it on each sheet,
to make it a little easier to learn.
Now I'm going to copy that.
But before, I'm going to click on the Calculate Revenue sheet
and use our keyboard Shift-F11 to insert a new sheet.
Double click and call this Two Variable Data table and enter.
Click back on the sheet, Control-C. Now on Two Variable
Data table, Control-V and point to our smart tag
and say Key Source column widths.
So last video what we did is we took
a bunch of different formulas and changed one variable.
But now we're going to take total profit.
And we want to throw in a bunch of different values of quantity
or units, our decision variable, and one of our other formula
inputs.
We're going to change the number for our defect rate.
So in essence, we're going to have a column of our units
and a row filled with different defect rates.
And there will be an intersecting formula
for each one of those two variables,
calculating total profit.
Now I'm going to click in cell C33.
And here's the one formula that we're
going to need for our Data table,
to then change the column and row inputs.
So I'm going to say equals and refer up
to our total profit formula.
Now remember, as with our one variable table,
this formula is just a loan formula without a label.
But that's the way a data table has to set up.
I'm going to force the issue and put a label off to the side.
Now there is the label I created.
And if you look up on the Home ribbon,
I went ahead and right aligned this,
so that it says formula from original model,
quantity equals to 2,000.
And I'm going to add Control-B bold to that also.
Now here's the thing.
That's the one formula.
And for Data table to work, since I want many new decision
variables and many new defect rates,
I have to put all of the units or quantity below
and all of the new defect rates in a row off to the side.
All right, now I've already from our last video
created Data Table Start Unit Value
0 and the increment of 500.
And I went ahead and typed up Defect Start Rate of 1%
and a defect rate increment value of 1%,
because we want everything in the data table to be dynamic.
So our first unit is going to be 0 and enter.
Now I'm going to say equals relative cell reference
1 above plus our increment of 500, F-4 to lock that.
Control-Enter, and then copy it down until we get 6,000.
Now, off to the side.
And remember, these are going to be the column variables,
these will be the row variables.
Equals in the start, defect rate is 1%, tab,
equals one cell to my left plus the increment.
And I'm going to lock that with a F-4 key, Control-Enter
and copy it over.
So now any intersecting cell, what I really want
is total profit at 2,500 units or quantity, 2% defective rate.
Now I am bothered if things aren't properly labeled.
So I went ahead and properly labeled the formula.
But I'm going to go ahead and properly label this, also.
Units, Control-Enter.
And I'm going to now highlight this.
Control-1 to format cells, go to alignment.
And I'm going to go ahead and merge the cells
and then change the orientation.
I can never remember which one's which.
OK, so if I like it that way, I can do it, Control-1.
Otherwise, I can flip it the other way.
And I want to align it horizontally in the center
and vertically in the center.
While I'm at it, I'm going to go over
to borders and click outline.
Fill, I'm going to click a dark blue font, something
like white, click OK.
So there we go.
Now it's fine if you don't have that.
But I like to have some way to know what
this is when I'm looking at it.
Similarly over here, I'm going to say equals--
and I don't want to type this whole thing out,
and so I'm going to hit Control-Enter.
And then do something similar, except for this time maybe
I'll use home and the Merge and Center button
and then some dark fill, white, and outline.
Actually, I kind of like having that variable right there.
I'm going to click here, equal sign, and then click up.
And there's our decision variable right there,
and enter.
I'm going to highlight the whole table and add some borders.
Inside them I'm going to add some green, some of this green
down here.
And here's how it works.
We have a column filled with quantity or units,
a row filled with defect rates and a formula that depends
on both of them indirectly.
We highlight in the upper left corner,
there has to be that formula.
Variable, variable, empty cells, data, what if, data table--
or as we saw last video, the awesome keyboard
Alt-D-T for data table.
Now we have a row input.
Remember, the way to memorize which
one is which-- because I tend to think each one of these
is at the head of the row like a pivot table,
but that's not the way Data table works.
Row input cell means which row has all of the variables.
And it's not these, it's the original formula input.
So you have to scroll up, and there it is.
That's the percentage of boomerangs made that are
defective and cannot be sold.
And then for our column, it's called Column Input Cell
because it's a column filled with new numbers
that you want to substitute into that formula.
But we have to click on the original formula input,
in our case decision variable, that that formula is using.
So that formula is using both the original decision variable
and the defective rate.
But when I click OK, it will substitute
each one of the units and defective rate.
And when I click OK, you've got to be kidding me,
and there it is.
Now one thing I didn't do in the last video
is the inside of this doesn't have any formatting.
I'm going to highlight-- these are dollar amounts,
this is profit.
So Control-1, number-- let's do currency,
and I'm going to only show zero decimals.
Click OK, we don't need to see the pennies.
And there it is.
That's absolutely amazing.
For any intersecting cell, we can now
see total profit, where units are 4,000
and the defect rate is 3%.
Now remember, you could put any two variables
you want that this formula is using.
This formula for total profit is using
every single one of these.
Up here, the original variables we defined, and then down,
that decision variable.
So you could use any one of those.
And what's so nice about this is, again,
that formula is going to get pretty complicated.
And if you look below in the answer sheet,
I have the formula version of this.
Now two last things, let's highlight this and Control-1.
I'm going to add number with a comma but no decimals,
because those are units, so we have
some number formatting there.
And we can go up and change this.
That data table is not static.
So if I went up and changed the start number to 1,000,
instantly that whole table changes.
I could change the increment.
So I could say the start defect rate is 2
and the increment is 0.5.
And I'm going to put a percent, to enter actually 0.5%.
And so now we have a different set of inputs
into our data table formula.
And just as we saw in last video,
if you click in the cell, you could
see that this is really an array formula, using the Table
function, entering these two values.
You can see those curly brackets, which
mean it was already entered.
If I tried to delete one of these,
it's not going to let us do anything to the array,
unless we do something to all of the values.
That's pretty amazing.
That is a Two Variable Data table.
In our next video, we'll actually
go back to our One Variable Data table
and do our last item from our original list, which
is to create a chart.
All right, we'll see you next video.