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.

## 8 Comments

That is a cool magic ðŸ™‚

I'm gonna plow through all of there, again this is gr8!

Not: Excel is Super expensive when you don't haw money or a Job xD

You know what Mike, I think I speak for everyone who enjoys your Excel tutorials when I say that you really are a World Treasure. Thank you for selflessly sharing your own learning with every one of us. It has enriched my own life and I am sure that of tens of thousands of others. Greetings from Scotland.Â Graham

Thanks for this valuable video. Cool

i've been looking for an explanation to the data analysis bar because i knew that there is something awesome behind it thank you so much Mr. Mike

Nice trick! I wish I knew it before.

I love coming back for revision… This is awesome channel

That is great Mike. Do you offer any customised coaching as per the requirements. Please let me know.