Basic Excel Business Analytics #07: X-Y Scatter Chart: Fixed Cost Variable Cost Model

Basic Excel Business Analytics #07: X-Y Scatter Chart: Fixed Cost Variable Cost Model


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.

12 Comments

  1. khaled abd elrahman says:

    THANKS

  2. ibrahim visa says:

    thank you very much

  3. Cosmas Ezeuko says:

    Beautiful…

  4. Rafael Fontanillas says:

    HELLO MASTER, im calculating cost for an entrepeneur proyect myself i need to calculate cost, sales price and the return of the investment , whic of the thousands of vids can help me to do it all ?

  5. Brandon Barquin says:

    i like this

  6. Rick Darcy says:

    Amazing! I will have to create my own scenario with data & test my execution. This vid is comprehensive with biz application & was articulated very well. You're talented.

  7. Tarik Lazri says:

    Amazing. Thank you.

  8. akadimas says:

    why am i going to college when my curriculum existing on youtube

  9. Ismail Ismaili says:

    it's really awesome it helps us to see if we change the costs and prices what will happens thank you sir

  10. Quang Minh says:

    Thank you very much Sir, for your great sharing.
    In addition, may I have one question: instead of calculating the Y value=FC+UBE*R (=17,430+9.62*2,662=43,038.44$), I take: Y= R*q*(1-D) (=16.5*2.662*(1-2%)=43,045$.
    My question is: 1. Can I use that formula? 2. If yes, why there is a small gap (7$)?
    Once again, thank you a lot and wish you good health 🙂

  11. Rada Petkova says:

    I absolutely love your videos! First time Excel is understandable and actually fun.

  12. Rodrigo Custodio says:

    Thank you, Mike, for one more outstanding video!

Leave a Reply

Your email address will not be published. Required fields are marked *