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.
