# Basic Excel Business Analytics #06: 2 Variable Data Table For What-If Analysis

On September 21, 2019 by Raul Dinwiddie

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.

## Leave a Reply