Welcome to Highline BI-348 class, video number seven.
Hey, if you want to download this workbook,
BI348Chapter07Start, or the finished file,
click on the link below the video.
Hey, we've been working in this workbook for five videos.
This is our sixth video in this chapter.
And guess what?
We already completed on one variable data table our formula
input area, our model, and we used the amazing data table
with one variable to calculate a bunch of answers
for total revenue, total cost, fixed cost, profit
by varying the units.
Now, our goal in this video is to chart this.
And this is pretty much a world-famous chart.
Here's the end result. We need to plot fixed cost, total cost,
revenue, and guess what?
The crossover of our revenue line and total cost,
that's the break-even point.
Now, this chart is going to be totally dynamic.
That means if we change any of our formula inputs,
this whole chart has to update.
Not only that, but we're going to make this even more awesome
than most break-even charts.
That point and the associated item in the legend--
notice it says Break-Even Points and it tells us
what the break-even points are.
So if we change any input up in our formula input area,
the lines, the marker for break-even, and even
the number in our legend has to change.
Now, back in video number five we
went ahead and created this using the data table.
And in that video I mentioned-- oh.
Below on the answer sheet would be the formulas.
So, remember, when we did this data table,
this array ready formula up here was automatically populated.
And it is amazingly convenient when your model's complicated
and you have lots of formulas.
It's faster to use the data table, which auto populates
this table array formula throughout this whole range
than it is to manually create formulas.
But it does one thing that's kind of inconvenient.
When you're creating charts and highlighting numbers, in order
to follow good spreadsheet construction,
you always have to have a label at the top.
So internally in the chart, all of the series of numbers
are labeled with the either total revenue, total cost, et
cetera.
But from this setup, it's more difficult to have
your chart do that than if we created
our formulas from scratch.
Notice, this fits our proper data set.
We have units and it has a label at the top.
We have total revenue, and here's
our formulas created from scratch without the data table
feature.
But there's our label.
No problem.
A little bit easier to make a chart if you're using formulas,
but we'll see how to create a perfect chart that
matches all of our good spreadsheet design rules
given this set up here.
Now, remember, a data table, these formulas
are pointing up to formulas that already exist.
The data table, all these formulas here,
took all of these inputs and changed the formulas
and gave us different answers for each particular unit.
Now let's go ahead and make our chart, Oh, wait a second.
What kind of chart would it be?
Well, check this out.
This is an x value.
These are y values.
They're dependent on the x.
Now, normally when we do x-y scatter charts
from raw data or sample data, we have
to use the markers so that there's dots everywhere.
But when you create a model like this,
you actually use the x-y scatter and connect the marks
with a line.
X-y scatter, x always has to be to the left,
and then you have your y values.
Not only that, but the x-y scatter chart is smart enough
to know that if you have the first column and a bunch
of other columns, it will know that this is the x value
for each one of these y's.
Now, again, we cannot highlight with column headers at the top
because then it would try to plot this,
and that is not part of our data set.
So we're going to have to do a couple extra steps.
Let's go ahead and highlight only the x's and the y's
with no labels.
We're actually going to have to manually put those labels in.
We go up to Insert.
Over in the chart group we have our dropdown for x-y scatter.
The markers are for raw data or sample data,
the lines are for models.
And I'm going to pick the one without the markers.
Damn, that's already looking pretty amazing.
It got the x correct here and it plotted all the y's.
Now, actually, we have one extra line here,
which we'll get rid of, and we actually
could have avoided that by not highlighting the total profit
column because we don't need that.
But that'll show us how to remove a line or a series.
But here's the big problem.
When you don't have field names at the top of your x
and y's, that is chart junk.
Series 1, Series 2, Series 3-- that
doesn't help articulate your message at all.
Not only that, but that blue line should say total revenue,
the orange line should say total cost,
the gray line fixed, and we'll delete that one.
Hey, it's easy to fix even though it
would have been better if we didn't have to fix it.
We want to go to Select Data.
So we can either right click anywhere in the chart
and go to Select Data or up in the-- oh, look at that.
I clicked Escape twice and now the context-sensitive chart
ribbons went away.
Click on the chart.
There they are.
Go up to Chart Tools, Design ribbon, and there it is.
Select Data.
This dialog box is the real power to charting.
We have the power to do anything we want to our series-- series
means the numbers.
Add, edit, remove.
We can also, with many charts, change our horizontal labels
or category access.
Now, let's look down here.
It's the Series 4.
That's the profit one.
I'm just going to click on that one, Remove.
Instantly it's removed.
Now we need to edit each one of these and change the title.
So I'm going to click Edit.
And, no, wait.
It left this text box empty because we
didn't have a field name.
It would have automatically named this series of numbers
with the proper name if we had had a field name.
But no problem.
That collapse button means we can connect this text
box to the cells.
So I simply click on Total Revenue.
When I click OK, instantly I can see down here it is updating.
Series 2, Edit.
Text box, Total Cost.
OK.
Series 3, Edit, text box.
I'm going to get Fixed Costs and click OK.
Click OK.
That is amazing.
Now, we do not need a chart title.
I'm going to click Delete.
And I'm going to select the legend down here.
And for any one of the elements in the charts,
you can either right click and go to Format,
whatever the element is.
Or you can simply use the keyboard Control-1.
Really, we should always use Control-1,
because that formats Chart Elements and over in the cells.
Now look at this.
Legend, I want it on top as if it is my chart title,
because that's what this is about-- total revenue,
total costs, and fixed costs.
Now I'm going to go ahead and close this.
We definitely with x-y scatter have
to have labels to tell us what these numbers mean.
The green plus is new in 2013.
I love it because it used to be much harder to add axis titles.
So I'm going to check that.
Instantly see that the y title is highlighted.
And, in this case, I don't want to link it
because these are all different categories of dollar amounts,
like total revenue, total cost, fixed cost.
So I'm simply going to start typing, and watch
as I see the solid line-- I'm just
going to start typing, Amounts.
Now, notice that it doesn't appear in the chart title.
It always appears in the actual formula bar.
But when I finish my Amounts and hit Enter, there it is.
Now I want to link this access title here
to the cell, so with the solid line I type an equal sign.
It shoots me up to the formula bar.
And now I'm going to click on Quantity and Enter.
And now I have length at.
Now, the real magic is going to start when we figure out
how to plot the break-even point as an actual new series
on our chart.
Now, think about this.
What is an x-y scatter do?
It actually has a bunch of x's and a bunch
of y's and then it draws the line.
Because this is a model and the increment is the same,
it's a perfect straight line.
But guess what?
An x-y scatter chart can also plot a single point?
Well, how do you plot a single point?
You have to have one x and one y.
So let's go ahead and do that.
I'm going to scroll up a little bit, and in my model area
I'm going to create a new column for my x value and my y value.
And I'm going to highlight both of these cells
because we're going to do a formula here.
I'm going to get that green and my outline.
What x value gives us exactly break-even?
Well, we actually already calculated it down here.
So I'm going to say equals that.
And what's the y value?
Remember, we can actually calculate
the y value various ways-- total revenue or total costs.
I'm going to select total costs equal sign.
And watch this.
I'm going to Control-Home just to jump up to the top
so that the screen jumps up.
And I'm going to say fixed cost plus-- hey,
there's the number of units times the actual variable cost.
And there we go.
Those two values will enable us to plot.
Now, the cool thing I want to do is
I want to create that little label that
says break-even units and tell us
what the break-even units are.
So down here I'm going to say break-even units label.
And then below here I'm going to make a formula.
And it's a text formula equals double quotes, because I'm
going to put some text into a formula-- break-even units
space equals space end double quote.
And I'm going to join it to using Shift-7-- the ampersand.
That's the join symbol.
And I'm going to join it to that value right there.
Now, remember, that value was already rounded down here.
If we hadn't rounded it, then we would
have had lots of extraneous decimals inside our text
formula.
Now guess what?
We have an x, a y, and a label.
So we simply come to our chart, right click, select Data,
and we want to add.
And I'm going to add series name.
Boom.
There it is.
X value, boom.
And you've got to be careful when
adding data to many charts.
If you see this internal array, which
is there in case you forget it, you
have to highlight it and hit Delete.
And then select y, and then I click OK, click OK.
Instantly I see the legend is updated, but where in the world
is my plot?
Now, there's a few ways we can do this.
You can actually go up to Format and over here
in the current selection, you can
select whichever item you want.
And there it is right there.
Or, here's a cool trick, arrow keys inside of charts
move through the chart elements.
So notice I selected the line, now I'm
going to hit the down arrow, down arrow.
That didn't work, so I'm going to hit up arrow,
up arrow, up arrow, up arrow, and there it is.
Either way, now we've selected it.
Now we can Control-1 to format that.
I'm going to scroll over so we can see it emerge.
And here I want to go to Fill.
And it's not a line.
I need to show a marker for this one.
So I had to collapse that, and this
is the kind of buried difficulty of 2013 and later charting.
I need to go over to the marker, Marker Options.
I want to say Built In and instantly
I can see some things appearing over here.
I'm going to choose-- that one's fine.
The fill for this, how about solid fill.
I'm going to pick something like bright red.
And there we have it.
Now, that line right there, if the yellow is fine,
we could actually change it by changing the chart type.
But that is pretty amazing.
Now let's go ahead and try this.
I'm going to close, scroll over, select the edge of the chart,
and I'm actually going to cut it, Control-X, and paste it up
here near our formula input area, Control-V.
And let's just change this.
I'm going to change this to $19.
And instantly when I change one of the formula inputs,
everything down in our model-- the model,
the axis elements here, data table, chart-- it all updates.
Control-Z.
I could change any one of the inputs.
If, in fact, the cost accountant said, hey, it's not $1.15,
it's actually 1.5, instantly everything updates.
Control-Z.
So in this video we saw how to make this awesome fixed cost,
variable cost chart, x-y scatter with a plotted break-even
point.
All right, that's the last video for our fixed-cost analysis
where we were learning how to build a model.
In our next two videos, we will see some useful functions
in Excel for model building.
All right, we'll see you next video.