Welcome to Highline Excel
2016 class video number 15. Hey, if you want to
download this Excel workbook file, Busn218-Video15, or the
PDF files, or in this chapter, I actually made PowerPoints. And we want to start
over here in PowerPoint. I'm going to go to
slide number three. We actually want to locate
each type of different chart before we jump over to Excel
and start making charts. Now, I've selected
slide number three. I'm going to use Shift-F5
to start on that slide. Now, charts, also
known as graphs, are going to be pictures of
quantitative or number data. We're always going to start
with numbers and labels. In this case, we have
a crosstabulated table. So we have our column
headers and our row headers. But there's going to
be numbers and labels. Now, instead of seeing
the individual numbers, we want a quick
visual impression. We want to be able to quickly
see trends and patterns. Or see relative difference. Like I can easily see this
column, Website Sales for June, is the tallest. Now we want to go
to slide number five and talk about our
number one chart rule, which is no chart junk. Chart junk is defined as any
chart element that does not contribute to your message. This is a terrible chart, filled
with all sorts of chart junk. The title, they left the default
CHART TITLE text in there. Your title has to be
succinct and informative. Look at this. We have July, August,
September, but we also have the months over here. That is unnecessary repetition. You can decide whether
the put the label for this particular pie piece
either right next to the pie or in the legend. Look at this. They left the actual
numbers unnamed. It says Series 1. We want to make
sure and always name the actual numbers
in our chart, which for us, will come
from the field names at the top of the dataset. The colors in this
are not helping to contribute to our message. Much too busy. This looks like this
is from a candy cane factory or something. Not only that, but this
person used a warm color to pop this pie piece
out and a cool color to push this pie piece
back, to make this one looks smaller and
this one bigger. And then, probably the
most chart junkie element of all, this is a 3D pie. You never want 3D pies. That distorts the proportions. Not only that, but many
of the 3D chart elements that Excel offers you do not
help contribute to the message. So we want to always try
and eliminate chart junk. Now we want to go
to our next slide, because we have a
bunch of chart types. And here they all are. Not only that, but
over in the PDF, I have quite extensive
notes on each chart type. Now, let's look at each chart. Column chart is simply
used to compare differences across categories. The height of each column
conveys the number. Categories along the horizontal
axis, the height of the column, we can quickly see
June is the biggest. Now, if we tip a column chart
on its side-- next slide-- this is called a bar chart. Now, both the bar and the column
are used to compare differences across categories. In this case, the length of
the bar conveys the number. Now, the difference between the
column chart and the bar chart is that oftentimes when
you tip it on its side, it will more
forcefully emphasize the difference or the relative
difference between the columns. Not only that, but bar charts,
if you have really long labels, it will appear on one line,
whereas with a column chart, the text will wrap. Now, notice for both
the column and the bar, we only had one
category here listed along either the
vertical axis for the bar or the horizontal
axis for the column. Now, if we have a crosstabulated
table-- next slide-- that's the perfect job for
either clustered column or bar, or stacked column or bar. Now in this case, we
have clustered column and stacked column. Now, both of these
charts come from the same crosstabulated pivot table. But the difference is this. If you have clustered
column, you're going to emphasize the
items in the legend. We can clearly compare
each website sales or each in-store sales. Down here, we can
still do that, but it's just a little bit harder. The emphasis here is on the
height of the categories in the horizontal axis. We can clearly compare
and see that June is taller than August. Next slide, we have yet
another column chart, but notice there's no gap
between these columns. Now, this type of chart
called a histogram is great when you're counting
or adding numbers between a lower and upper limit. And the fact that
there is no gap is the visual indicator
that no number can fit between any one
of these categories. Next chart type, a
line chart, shows trend for a number variable
over a category such as time. Oftentimes, we see what
is called a time series. Here we have sales. Certainly we have
all seen stock charts where we see how the stock
price is moving over time. Next chart is the combo chart. We are certainly allowed to
combine columns and lines. And this is a great
chart because notice the axis over here is in dollars
and goes from 0 to 250,000. And over here it's percentages,
in essence, from 0 to 1. So we have two different axes,
each with a different scale. Next chart is the
xy scatter chart. This chart is used where you
have x and y data, like hours studied and test score. We go along the x a certain
distance and along the y a certain distance
and plot a mark. This chart helps us to see
whether there's a relationship between two variables. Next chart is a certain
type of xy scatter called a break even chart. And we'll see how to create
this where we have fixed cost, sales, and total cost. And the crossover point is
our break even in units. And lastly, we have
a bubble chart, which is like an xy
chart, except for we have a third variable, which
is the size of the bubble. Now, one last thing before
we jump over to Excel, we've got talk
about terminology. Now, the term that
Excel uses to describe the text or the labels, in our
case, column headers or row headers, the term that
they use is category. For the numbers, the
term they use is series. We will always name our
series, and they will usually come from the field names. And of course, we
have our chart title. And the numbers in
this chart are conveyed by the height of the column. Our next example for terminology
is from our xy scatter. Now notice, here on
the vertical axis, it shows our y-series numbers. Over here this would be
our x-series numbers. And unlike our column chart,
notice right here is a label. We're always going
to have to label the y-axis with
a label that says exactly what this number is. Same with our x-axis. So we'll have to add
both an x and y label. And then, of course, we
have our chart title. And the numbers for us
are a certain distance along the x and along
the y to give us each one of these markers. All right. Now, let's go over to Excel
and try and make these charts. Now, over here in Excel,
we're on the sheet Column, Bar, and Pie. And we want to see how to
make each one of those charts. We have our proper dataset here. And we've already summarized
by month and sales channel. There's our total revenue for
each one of these categories. The numbers will
show up as series and the different
elements will show up as a category in the
column, bar, and pie chart. Now I'm going to click in a
single cell, go up to Insert, and there's the chart group. We want a column chart, so
we go up to the Column icon. Click. We want 2D, none
of this chart junk. We want 2D. There's clustered and stacked. Now, we only have
one category, so when we click Clustered column,
there is our column chart. Now, it's looking pretty good. That title at the top
is not very helpful, so I want to click
on the chart title. And notice there's a solid line. If you click twice,
you see a dashed line. That's not what I want. I want to click once. And now I want to
link this label to some text in the cells,
so with the solid line, I'm going to type an equal sign. That shoots me up
to the Formula bar. Then I'm going to
click on the cell B2. Notice that formula
up in the Formula bar. When I hit Enter,
now that chart title is linked to some
text in the cell. That chart right there
looks pretty good. We have our numbers and our
column heights and our category labels. We could change it
up a bit if we want. We could come over
to the green plus. And when we click
it, it gives us a list of items that we
can either add or take away from the chart. I want to click Data Labels. And instantly, I get numbers
at the top of each column. Now we have some numbers in the
vertical axis and at the top. In general, you
do not need both, so I'm going to come over and
click on the vertical axis and use the Delete key. Now, different
elements-- and as I click on each
element in my chart, notice when I click
on the labels, it highlights all of them. I click on the column and
it highlights all of them. That means I can format
all of them at once. If I click a second time, then
I can format just that one element. I'm going to click on the labels
and then back on the columns to highlight all of them. And now I need to open up
the Formatting Task pane. And so I use the
keyboard Ctrl-1. Now remember, Ctrl-1, when
I highlight Chart Elements, opens up the Task pane
but back in the cells, remember when we click
in a cell and do Ctrl-1, it opens up the Format
Cells dialog box. Now, this isn't a dialog box. This is the Task pane. And there's some
icons at the top. Sometimes it's tricky to
find exactly what you want, so you have to click
through the icons. I'm going to stop on the Fill. I'm going to select Solid Fill. And then down here I'm going
to say Vary colors by point. There is a simple
column chart to show different amounts and
the relative differences across the category. Now I want to come down here in
the second summarized dataset and I'm going to create
another column chart. Insert, Charts,
Column, and I'm going to select that first 2D one. Drag it down here, click
on the chart title. Equal sign shoots me
up to the Formula bar, and then I'm going to
click on B2 and Enter. And that chart is done. Now, notice with this
chart, we changed the color and added data labels. This one we didn't
change the color and we left the numbers in
the vertical axis. That is certainly a
matter of preference, but either way you
go, both charts are articulating differences
across categories. Now I want to scroll
over a bit and actually copy both of these charts and
change them to bar charts, and then compare and contrast. Now watch this. I already have that
chart selected. I'm going to hold my Control
key, click on the second chart, and then Ctrl-C to copy. Now I'm going to click
to the right somewhere, and then Ctrl-V. Look at that. I copied those charts quickly. Now I want to change
the chart type. I'm going to click
off to the side and then select just this chart. To change the chart type
from a column to a bar, I can select the chart and go
up to Design, Change Chart Type. Or I can simply right-click, and
there it is, Change Chart Type. I'm going to come over on
the left and select Bar. Click OK. Right-click, Change Chart
Type, Bar, click OK or Enter. Now, both the bar and the column
are clearly showing differences across categories,
but the bar chart, when you tip the
bars on their side, they tend to more
forcefully emphasize the difference between
the different bar lengths than the
different column heights. Not only that, but if you
have really long labels, then the bar chart will always
put the label on one line. Now, we don't have
an example here, but if you have long
labels, they wrap. So sometimes it
looks neater when you put the longer labels on
a single line in a bar chart. Now I'm going to copy
the top chart, Ctrl-C, and I'm going to come
down here Ctrl-V. And let's change this to a pie. Right-click, Change Chart Type. And I'm going to
change it to a pie. Click OK. This too is showing differences
across categories, however, research shows that people can
judge the relative differences between column
heights and bar links much more easily than
they can pie pieces. Not only that, but over
the last five years, data analysis and business
intelligence practitioners tend to prefer either the column
or the bar over the pie chart. Now we want to go over to the
sheet Stacked and Clustered. Here is a crosstabulated
pivot table. We took our raw data, dumped
Month into our row header, Sales Channel into
our column header, and the intersecting numbers
are adding with two conditions or criteria. Now, because we have more row
headers than column headers, when we click either
the clustered column or the stacked
column, the month will be put into the horizontal
axis and the sales channel will be put into the legend. Now we click in a single
cell, Insert Charts. And here's our column dropdown. And there's our
clustered column. I'll click and
there is our chart. Notice there are the
row headers, Months. Those are the
horizontal categories. And in our legend are our column
headers in the pivot table Sales Channel. Now, we can filter either in
the chart or the pivot table. In either place, if you
filter, for example, if we filter out
Mail Order here, it does both the pivot
table and the chart. And I can filter or
unfilter in either place. Now, I don't want these buttons. I'm going to right-click. And I'm have no idea why,
in this particular version, right-clicking those should
have options for hiding. So we're going to instead go
up to Analyze, Show Hiding. Here's our field buttons. I'm going to say Hide All. Now I'm going to come over and
click the green plus, because I want to add a chart title. Then I'm going to
click the green plus. Make sure the chart title is
selected with a solid line. Equal sign shoots me
up to the Formula bar, and then I click B2 and Enter. Now, this is a clustered column. With the chart selected,
Ctrl-C. And down below, I'm going to click
in the cell, Ctrl-V. Now I want to right-click
Change Chart Type. It's got Column
already selected. I'm going to come over to
Stacked Column and click. And then click OK or Enter. Now, both charts are coming from
the same crosstabulated pivot table. However, the clustered
column is going to emphasize the
items in the legend more than the months
along the horizontal axis. For example, we can
clearly see for September that website sales are
bigger than in-store sales. We still can see the difference
between website and in-store down here, but it's a little
bit more difficult to decipher the difference. So the emphasis here
is on the legend items. Not only that, but
look down here. We can easily see that June
is the tallest, especially as compared to the
nearest total for August. Whereas up here, it's
kind of hard to see the total for June and August. So same crosstabulated
table, same overall numbers in the chart, but with
a slight difference. Clustered column emphasizes
the legend items. Stacked column emphasizes
the horizontal axis items. Now, we want to go over and look
at yet another column chart. I'm going to go over
to the sheet Histogram. And on the sheet Histogram,
we have this dataset. Boomerang Incorporated
sells boomerangs online through four
different websites. And this is our 2014 sales data. We have date and time. That's the exact date and time
that the transaction was made. Different websites-- we sell
through Amazon, eBay, Colorado, in Gel Boomerangs. Here's the product. Here's the type, whether
it was wholesale or retail. And here's the revenue. Now what we want to do is
look at this revenue column and actually count how
many dollar amounts fit into each one of
these categories. So for right here,
we need a count of all of the dollar
amounts for revenue that were greater than 400
and less than or equal to 600. When we get here, we need
all of the dollar amounts from this column
greater than 600 and less than or equal to 800. Now, notice something
about these categories. We built them carefully. There is no amount
from this column that can fit in between any
one of these categories. So anytime you have
categories like this and you're adding
or counting numbers, that means if nothing
can fit in between these, you have to make the
columns touching so there's no gaps between the columns. Hey, we remember from
video number five how to count between an
upper and lower limit. We actually could do
it with a pivot table. We could do it with count ifs. But back in video
number five, we learned how to use the
Frequency function. Now the Frequency
function only needs the upper limits and the
actual numbers it's counting, and it will do the rest. Now, I actually added
the upper limits. I also typed an extra cell
here which is not actually one of the upper
limits, but that's going to be a
label in our chart. All right. So you ready? This is an array function. It's going to deliver multiple
answers simultaneously, in essence an array of answers. So I have to highlight all
of the cells in advance. Now, right now I'm highlighting
exactly the same number of cells as upper
limits that I gave it. But that's not the
way frequency works. You always have to highlight
one more cell than you give it upper limits for counting. Then in the active cell,
you type =FREQUENCY. Data array, that's
all of the numbers you want to potentially count. So I'm going to click
on the top cell, and I when I Ctrl-Shift-down
arrow, there's like 26,000 rows here. Now, I'm using Ctrl-Backspace
to jump back to the active cell. That reference does not have
to be locked because this is an array function that
we're entering simultaneously into multiple cells. Comma bins arrays means
those are the upper limits. Now remember,
frequency is polite. If I give it these
upper limits, these are actually the categories
that it automatically creates, including that last
category that says greater than the last upper
limit you gave it. The reason it creates that extra
category right in that cell right there is because
if we accidentally weren't paying attention when
we created these categories, this would be the catch all
to catch everything above. All right, you ready? Close parentheses. And I enter it simultaneously
into all the cells with Ctrl-Shift and Enter. Immediately I look up
into the Formula bar and I verify that my curly
brackets are entered, because that is an array
formula using the array function Frequency. And there is our count. Now, when I create my
column chart histogram, I can either choose
to be very explicit and list these on
the horizontal axis. But that takes up a lot of room. So what I'm going to
do is very carefully label all of these upper
limits as Revenue Upper Limits, so the person
looking at the chart knows that this is
the upper limit. All right. So I'm going to
highlight both columns, go up to Insert Charts. And there's our column. I'll click the first one. And there is the beginnings
of our histogram. Now, the very first
thing I'm going to do is I'm going to make
sure to add both of the horizontal and vertical
labels for those axes. Click the green plus, and
there it is, Axis Titles. Solid line, I click equal sign. And this is the frequency,
so I click on cell J6, Enter. Click on the Axis Title and make
sure it's got the solid line. Equal sign, click on
Revenue Upper Limit, Enter. Click on the chart title. Equal sign, I click on Count
Transactions in cell H3, and Enter. Now, the font size
of this is too big. And the Task pane actually does
not contain the font for us to change. You can right-click,
go down to Font, or you can get it up in the
Format Charts ribbon tab, or you can just go up to Home. And right in the
Font dropdown, you can select whichever
font you want. I'm going to select 10. Now, the next thing we want
to do is click on the columns. Ctrl-1 to open up our Task pane. I better scroll over and. There's a Gap Width. I'm going to reduce it to 0. I'm going to go over to the
Paint Bucket, Fill, Solid, Vary Colors by Point. And I'm actually going
to emphasize it even more with Border, Solid
Line, and Black. If I click on the
chart, I can see the columns are not touching. Now I'm going to click on the
green plus, Add Data Labels. I'm going to choose to
show the frequencies at the top of each column, so
I don't need this vertical axis here. So I click on it and delete. Now, I actually don't
like that word so close. I'm going to click on
the Frequency label and click and drag. Now, I'd actually like to change
the alignment on these labels here. And this was one of those items
that's hard to find in here. You have to click through,
click through until you find it. It's actually under
Properties, Alignment. There's Text Direction. And we can select
whichever one you want. I'm going to select
Rotate All Text 270. And there it looks like our
histogram is coming together. Now, this is for sales data. And notice the tallest
column is the first one. And they kind of
tail off this way. This is typical for
retail sales data. Most of the customers are not
going to spend very much money. Now, one thing that's
important about this continuous quantitative data when
we don't have any gaps is the order is important
here, because if we have it from smallest to
biggest, we can clearly see the shape of the data. Now, this is one
example of a histogram. I would like to now go
back to our dataset. And what we'd like
to do is count how many transactions happen
at different hours in the day. So I'm going to click
in the single cell. And instead of doing
frequency or counting, I'm going to use a pivot table
and the Grouping feature. So I click in a single
cell, Insert, Pivot Table. Or I can use the
keyboard, Alt, N, V. I'm going to put it on
this existing sheet. Scroll over. I'm going to try and
put it in 05, click OK. Now, here's the date and time. And if we drag
this down to rows, we know from earlier in
our class in Excel 2016, it automatically groups it into
years, quarters, and months. I want to come over
to the row area. Right-click, and I'm
going to select Group. I'm going to ungroup it by
clicking on Months, Quarters, and Years. And now I'm going
to select Hours. When I click OK, I have my upper
and lower limit, in essence. And now I'd like to count. So I could draw any
one of these fields down here, because any
one of these fields will just count how many items
are in a particular hour. But I'm going to choose
to drag a text field, because that way it will default
to count, which is what I want. Now, I could go up and change
Design, Report Layout, Tabular, but I'm going to change both of
the labels here by just typing. So I'm going to type in row
labels, and type Time, tab, and here I'm going
to type Frequency. Enter. Now I have my frequency
distribution, the count for each one of our times. I'm going to go up to
Insert, Charts, Columns, and select the first one. Now I'm going to drag it
over to the side here. Close Pivot, Chart, Fields. Scroll over. I want to immediately
go up to Analyze, Field Buttons, and Hide All. I don't need this legend over
here, so I click and delete. Click on the title, equal sign. Click on 03 and enter. I'm going to expand the
chart by pointing to the edge and click and drag. Now I want to click on the
columns, Ctrl-1, Gap with 0. Go over to the Paint Bucket. I'm going to leave it
blue, but I'm definitely going to emphasize the
difference between the columns with solid line
and make sure it's a different color than the
actual inside of the column. That's looking pretty good. Now, I'm going to leave this
histogram just like this. We have our labels
along the bottom, our count along
the vertical axis, and the chart title indicates
what's happening in here. Count Transactions Between
Upper and Lower Time Limits. We clearly know this is
time, and this is count. Ctrl-X, scroll over. Click Ctrl-V. So we have two
examples of a column chart where we reduced the gap
width to indicate that nothing can fit between these
counting categories where we have an
upper and lower limit. A note about Excel
2016 charts, there's a brand new chart
called Histogram. The problem with the
built-in histogram is you don't have full
control over building the upper and lower
limits for each class. Not only that, but
this chart assumes that the dataset has a bell
shaped or normal distribution. Now, Microsoft
says that they are going to give us
the ability to set lower limit of the first
class and the increment, but at this time, this chart
does not have that ability. So we're not going to use
the built-in histogram. All right. Let's go to our next
example, the line chart. Now, the line chart
is used to show a trend for a number variable
over categories such as time. If we have time on
the horizontal axis, we call it a time series chart. Some number, and we want to see
the trend or pattern over time. Now, if we highlight
both columns with the names at the
top, we can go Insert. Over to Charts. And there's the line. Not the scatter
one, that's for xy. We want the line. And I'm simply going to select
the first one and click. And just like that, it
misinterpreted the data. Now normally, this is
some sort of category. It was trying to be
polite down here. It saw that it was
a number and thought we wanted multiple lines. This is a case where
we either highlight just the sales to start with,
or if we make this mistake, we can edit. Come up to Chart, Tools, Design. And there's Select Data button. Or on the chart, you can
right-click, Select Data. Now, this Select Data
Source dialog box is the most powerful
feature for creating charts. And the reason why is there's
our series, the numbers. There's our categories. We can add series if they're
missing from our chart. We can remove them,
which we're going to do. Or we can edit them. Now, I'm going to click
on Year and click Remove. And then I'm going
to click on Sales and click Edit, just to
see that there it is. It's linked. There's the word Sales. That's the word that
the chart engine is going to use for these numbers. And there are the numbers. Click OK. We can also, of course, edit,
which is what we want to do. Our Categories, and then
link the actual years as our category labels. And click OK. Click OK. Now, down here we
have some chart junk, so I'm going to click
on it and Delete. Now, I'm wondering about
all of this space here. If I want to change
the minimum value, I come over to
the vertical axis. Click, Ctrl-1 to open
up the Task pane. And there it is. There's Min and Max. I'm going to change it to
maybe 400,000 and enter. I missed one zero. I'm going to try one
more zero and enter. Scroll over, point to
the edge of the chart, and click and drag. Clearly, I can see the
trend over time is up. If I wanted to, I could
come to the green plus, say Add Data Labels. I could come over to the
vertical axis and delete. Click on this green plus. Click on the line, and maybe
I want to change the color. So I'm going to come over
here and say Solid Line. I'll accept the default color. Close the Task Pane
control, and roll my wheel. There is a line chart. A time series chart to
show some number value, in our case, sales over time. All right. Let's go look at the
xy scatter chart. Anytime you have xy
data, in our case, we have hours studied for
a test and test score, is there a relationship between
hours studied and test score? When you ask that question,
always the first step is to visualize. And we use an xy scatter chart. I click in a cell, Insert. Over to Chart. You want to make sure
and not try and do xy scatter with a line chart. You want to use xy scatter. Now, if you have
sample data like we do, you want to use the markers. If you have a model
like a breakeven fixed cost variable cost model,
which will be our next example, you use the line. We have sample data
that we've collected, so I'm going to
click the markers. There is an
incomplete xy scatter. The biggest problem
with this chart is that it does not have an
x label or y-axis to tell us what these numbers mean. Come up to the green
plus, Access Titles. I see that it's
got a solid line. Equal sign. And this is the y. So I click on Test
Score and enter. Click on the x-axis
label, equal sign. Hours Studied and enter. Now let's click on the
chart title, equal sign. I'm clicking in
cell E1 and enter. With the chart title selected,
I'm going to go to Home and change the font to 10. Now I can look and see, is there
a relationship between hours studied and test score? Yes, it looks like
it's going up, which means as hours
studied is increasing up, up, up, the test score, the
y variable, tends to go up. This is called a
direct relationship. Now, in a statistics
class, we'd have to calculate, doing a
lot of calculations what the best fit regression line is,
and something called r squared, the goodness of fit. But in a chart, you can simply
come over, right-click and say Add Trend Line. And just like that, it
does our best fit line. I can come over here to
the Task pane, come down, I'm going to check Display
Equation and Display R Squared. Just like that, that is
a lot of heavy lifting to calculate those
numbers and that equation and that r squared. Now I'm going to click on the
horizontal grid lines, delete. Vertical grid lines, delete. And there we go. An xy scatter chart. Excel charting engine knows
how to do these perfectly. Now let's go over and
look at a specific example for xy scatter. I'm going to click on Breakeven. Now we actually want to go
look at the answer first, so I'm going to click
on the Breakeven Answer. And this is a breakeven fixed
costs variable costs chart. Now, what we have here are
units plotted along the x and dollar amounts are
plotted along the y. But the blue line is sales,
the red line is fixed cost, and the green line
is total cost. When we plot sales
and total cost, the intersection
of the two gives us our breakeven point in units. Now, this is a
very useful chart. Every class you take
in business school, from marketing to economics
to managerial accounting to finance to an Excel
class, will have you do it. And out there in
the working world, you have to do this before
you bring a product to market to see whether it's worthwhile. Is your demand past this
point or is it below it? And the beauty of doing
this in Excel, of course, is we set up our
assumption table. We're going to make
all of our formulas. And when we change one of
the inputs, what if this wasn't really $35, it was $33? When I hit Enter, all of the
formulas and our chart updates. Look at that. Instead of 400, it's 333 units. Ctrl-Z. All right. Let's go over to Breakeven
sheet and let's do this. Now we're going to have to
calculate units, sales based on the units,
variable cost based on the units, contribution
margin, fixed cost, total cost, and net income. All right. Units equals, and our assumed
start units will be 0. Enter. Equals, and this will
be a different formula than the first
formula in our column. So I'm going to say,
always look one cell above, plus the increment of 100. And I'm going to hit the F4 key. Ctrl-Enter, and copy it down. So we have from 0 to 1,000. Now, with our unit
price and variable cost, we can calculate our sales at
each unit level and variable cost at a unit level. Equals the units
times the unit price. And I'm going to lock
it with the F4 key. Ctrl-Enter. And now we can double
click and send it down because we have
something to the left. Equals the units times
our variable cost. I'm going to hit the F4 key. Ctrl-Enter. Double click and send it down. Now, contribution
margin is a term you hear in managerial
accounting or economics. Now, the calculation is going
to be sales minus variable cost. And you might have heard sales
minus variable cost referred to as gross profit
or gross margin. But for us, it's going to
be how much is left over to cover fixed costs
after we subtract all of our variable cost. So we're going to
calculate equals the sales, two sales to my left, minus
the variable cost, one cell to my left. Ctrl-Enter. Double click and send it down. Now, for our charting, we're
going to need fixed costs. And we need the same number
in every single cell. Now, this seems really weird. We don't normally do that. But if we're going to plot x
and then this one y here, sales, this other y, fixed, and then
total cost is our third y, well, we're going to need an
individual y value for each one of our x's. So equals, and there's the fixed
cost, with an F4 to lock it. Ctrl-Enter, double
click and send it down. So we can see if this is
the contribution margin, the amount left over to cover
fixed costs, right here at 400, we have exactly enough
to cover fixed costs. Anything past we have enough
left over to cover fixed costs and our profit. Now total cost is
going to be equals whatever the variable
cost plus our fixed costs. Ctrl-Enter, double
click and send it down. Net income, there's a few
ways we can calculate this. I'm going to say, hey,
sales minus total cost. Ctrl-Enter, double
click and send it down. Right there, we can see yes,
net income at 400 units is 0. We have exactly 4,000
in contribution margin to cover our fixed costs. Now we can plot it. We're going to highlight
our x and one of our y's. Notice we have our
labels at the top. Those show up as
our series name. Now I'm going to
hold the Control key, highlight the labels and all of
the second and third y values. Now I go up to Insert. Over to Charts. There's our xy. And this is where
you use the line. So I'm going to click,
actually, the second one so we have smooth lines. Now I'm going to click on
the chart title and Delete. We don't really need
a chart title here. This is part of our model. I'm going to click
on the legend, Ctrl-1 to open up the Task pane. And I'm going to
put this as the top, as if it was a label for us. Our sales, fixed
costs, and total costs. And now we need to come over
to our green plus, Axis Titles. This axis title, we
don't really need it. These are dollar amounts and
we can tell from these labels up here. So I'm going to delete it. But this one I'm going to
say, equal sign and click on Units over here and Enter. Now, the trick is, how do
we get the little marker to show our breakeven point? Well, the breakeven
point is going to be the total fixed cost
divided by the contribution margin for one product. So $45 minus $35 is $10. So for every one unit we
sell, we have $10 left over. So if we take 4,000 divided
by $10, that gives us 400. So our break even
formula here is going to be equals
the fixed cost divided by open parentheses,
and I'm going to say unit price,
minus our variable cost, close parentheses. Ctrl-Enter. Now, we're going to have a
problem with that formula in our chart later. And we will come
back and fix it. Our breakeven y,
well, if I'm going to plot a point on the chart,
not a line but a point, I still need an x and a y. Well, there's our x
at exactly 400 units. What's the y going to be? Well, the y is
the dollar amount. So I can choose to either do
total costs or total sales. It's easier to do total
sales, so I'm simply going to say, hey, 400 units
times my unit price, and Enter. So there it is. I have an x and y that I
can plot as a single marker on this chart. Now, I would like
to have a label so that I can have
a label that says breakeven units are equal to. And as the inputs change, the
breakeven unit will change, and that label will change. So I'm going to create
a text formula, equals, and in double quotes,
breakeven units, with a space, an equal sign,
a space, and double quotes. Join it to the 400. And when I hit Enter,
now I have my label. Now, notice we have an x,
a y, and not only that, I'm going to call
the series this. And so it's easy to access
this inside the chart to show up as a label. And it will show
up in our legend. All right. I'm not going to highlight
this and do something. I'm going to come over to
the chart, right-click, Select Data. This Select Data dialog box
is the real power to charting, because we can do
whatever we want. Like add a series. The series name, I'm going
to say, hey, there it is. x value, there's my x. The y value, be careful of
that little array there. You want to highlight
it and delete it before you put anything there. And now I click on the y. When I click OK, click OK, what? It didn't show up. We'll find in just a second,
but notice, no problem. It's right up in the legend. Now, one way to find it,
and this is a cool trick, you can click on
one of the lines and use your arrow keys
on your navigation pad. And look, I up
arrowed and found it. If I down arrow, notice
these arrow keys cycle through the series of number. And there I found it. Another way, if you
can't find it that way, is to go up to Format. And over in Current
Selection, there's a whole dropdown with all the
different elements in a chart. So now with this one
selected, I'm going to Ctrl-1. And I want to go over to the
Fill bucket, Marker, Marker Options. And you can choose whichever
built-in option you want. I'm going to choose a triangle,
Fill, how about Solid? And I'm going to pick a
very loud color like red. Now I'm going to close this
and come over to the plus. And now I want to
say, hey, data labels. And this triangle, I point and
I'm going to say More Options. And then over here
we have some options. I'm going to check Series
Name, and just like that, it shows up. Now I'm going to uncheck the
y, and the Show Leader Lines. That is going to be amazing. Look at that. There is a floating label. And now with everything
set in our chart, we can do our what if analysis. What if the price was $46? Instantly, our table of formulas
update, our chart updates also. Now, wait a second. Look at that unrounded number. Remember, that's
the series name that came from this formula that's
looking at this calculation. Now, we have number formatting
to show zero decimals, but formulas do not
see number formatting. So we need to
officially round this. And this is always
to the integer. Now, I could use Round
or I could use Round Up to be a little bit
more conservative. And we're always
going to the integer since this is units,
comma 0 number of digits, close parentheses, and Enter. And that solves the problem. Now we can do our
what if analysis. What if our variable
cost was actually $37? Then our breakeven
point in units is 445. If our fixed cost
were actually $5,000, I type $5,000 and Enter. Everything updates. Now I'm going to
Ctrl-Z-Z-Z-Z. And there we go. We have built a fixed cost
variable cost breakeven model with a bunch of
formulas and a dynamic chart. Now we have two more
charting examples. I want to go over to
the combo second axis. Now on this sheet, we
have revenue, expenses, all in dollars. And then here we have expenses
as a percent of revenue. Now, this is a completely
different scale, but no problem. We can add this as
a line on our chart and put on the second axis. Now in earlier versions,
this was harder to do. You actually had to manually
add the different series, and then add them
to the second axis. But in 2016, watch this. Here's our labels for
a horizontal axis. These will be columns,
this will be a line. I go up to Insert. Over to Charts. And there is our combo chart. Now watch this. When I highlight, look,
it gives us a preview. And that's the one we want. When I click OK, that
is pretty amazing. Now we want to click
on Chart Title, Delete. Now I'm going to click
on the legend, Ctrl-1, and I'm going to
add it to the top as if it were our chart title. And what's so amazing about
this secondary axis is, in the old days, the
line would be down here, and you have to click on it
and come over and change it from primary axis
to secondary axis. But that is done automatically. We simply highlight the labels,
column numbers, and our line numbers, and our chart is done. Now, we have one
last amazing chart. We want to go over
to the sheet Bubble. And on the Bubble sheet, we
have Project, the Expected Rate of Return, the Risk
Estimate for the project, and the Amount Invested. Now, this is going
to be our x value. This is our y value. But wait a second. We have a third variable. We'll actually plot this
third variable as the marker, but the size of the
marker or the bubble will indicate the size
of the third variable, the amount invested. We simply have to make sure that
the x is in the first column, just like our xy scatter charts. Highlight the three columns. The names of each one of the
number series are at the top. We go up to Insert, Chart. And in the xy scatter
group, there it is. There's the bubble. I click and we
have-- wait a second, some numbers without labels. Always the most important
thing for xy scatter is to immediately come
over and say, hey, give me the axis titles. I'm going to scoot
this over to the side. And Control and roll
to zoom out a bit. Click on the y-axis equals,
click on Risk Estimate, and Enter. Click on the x-axis, equal sign,
and click on Expected Return and Enter. Now notice, we can clearly see
this is our expected return. This is our risk estimate. But wait a second. We don't have a label for this. We'll add that in a second. Let's first click
on the chart title. And I don't have
anything in the cell, so I'm simply going to type
Investment Project, and Enter. Now, I'd like the dollar
amounts to actually show up, so here is a simple, hey, go
to the plus and Data Labels. And it actually does not
put in the right numbers. You can clearly see
these are risk estimates. So I'm going to go over to
the More button, More Options. Click up on the Series
option, Label Options. And check that out. There it is. Bubble size. So I'm going to check
that one and uncheck y. Now, we've seen how to
link chart titles and axis labels to the cells. Hey, we're going to have
to add our own label here. I'm going to go up to Insert. In the Illustrations
group, we click Shapes. And I'm simply going to
put, hey, a rectangle. I click once. I come over here and I see my
little very thin crosshair. I click and drag. Now I have added a text
box with the outside solid. I'm going to come up to
the Formula bar, type an equal sign, and then click on
the cell with Amount Invested, and Enter. And just like that,
what happened? That is formatting. I'm going to click on
the outside edge, come up to Solid Fill. I'm going to select
this color right here. I don't think I like
that black outline, so I'm going to come
down and say No Outline. Now, I see that this is white. I'm going to come over and
click on all of the text labels, go up to Home. Select White font so it matches. And look at that, it looks
like I misspelled the word. I'm going to come over here
put it in edit mode, F7 to do spell check. Invested, click Change,
click OK, and Enter. Now I'm going to change the
width of this just a little bit so it shows in millions. And there it is. There is our three
variable, x, y, and this third
variable, the size of the marker or the bubble. Wow, that was an epic video. We saw all sorts of charting. We saw a bubble chart. Back here we saw a combo
chart with columns and line. Back over on breakeven,
we saw an amazing model built with formulas for
fixed costs, variable costs, breakeven analysis, and
this amazing dynamic chart. We saw back on xy how to do xy
scatter plot with a trend line for our sample data. We saw how to do a line chart. We saw how to do a column
chart with no gap width when we had an upper and lower
limit called a histogram. We saw how to do stacked
column and clustered column. And then back on the
sheet, Column and Bar, we saw column, bar, and pie. All right. That was a lot about
visualizing data with charts. Next video, we'll get to
see another visualization method, conditional formatting. All right. We'll see you next video.
