Welcome to Excel and business math video number 22.
In this video, we got to see how to use the rate
formula to create a common sized income statement.
Now we'll remind ourselves how to format an income statement.
How to create some formulas for an income statement, and then
we're going to see how to use the rate formula
part divided by base to create a common sized income statement.
Now we're going to start off by going to the sheet, CAT.
And what I did is I went online and found Caterpillar's income
statement.
We'll actually do CAT on this sheet,
and then we'll do Amazon income statement on the next sheet.
Now to remind you, we have revenues.
These are like the incomes for the business or the amounts
coming in for the business.
And then these are all the different expenses.
Each one of these is a different expense or cost
of running a business.
Now if we add up all of the costs or expense,
and subtract it from the revenue,
we get either profit or earnings.
Or in accounting, they call that net income.
Now the first thing is we want to do
a little bit of formatting.
Let's highlight all of the numbers including the two
cells that don't have formulas, but will have formulas
with our dollar amount.
Control one to open up Format Cells.
I'm going to go down to currency.
Select two decimal symbols.
OK, that's the minus.
If you like accounting better, by all means, select that.
Click OK.
Now when I come down to total expenses,
and I'm going to use the keyboard for the sum function,
Alt equals.
And it got it right.
It guessed the correct range, so Control Enter.
And I'm going to copy it over.
Now that's the total expenses.
We need to subtract that from the total revenue so down in.
Cell b18 equals total revenue minus total expenses.
Control Enter, and copy it to the side.
Now on an income statement or any report,
we can make these numbers less cluttered.
First off, we don't need the decimal and 00.
They're not going to show any pennies, so let's highlight.
And we can either use control one currency.
Or in this case, we can simply go home, number group,
decrease decimal, or I love my mini toolbar.
Right click, and there it is.
That's probably the closest, fastest way
to decrease decimals.
Another thing we can do to make it less cluttered,
and we talked about this back in video number seven,
is the important numbers like total revenue
and the first expense in the list of expense.
And then total expense and net income.
We can leave the unit, the dollar sign there.
But on all these remaining numbers
inside, we can highlight control one.
And under currency, we're going to say show symbol, none.
Click OK.
Now let's do some formatting.
I am going to follow the convention of adding some
green because these are formulas,
and these are numbers typed in.
If we were printing this out officially
as our report for some meeting or something,
I would not put green here.
But for the time being, I'm going
to select that green right there.
And then for the net income numbers, instead of go
back up to font group and home ribbon tab, right click
and on the mini toolbar.
I'm going to choose from recent colors that green.
If you don't have that one, you can put down to more colors.
So we've added our green to say, hey, those cells have formulas.
Now these numbers revenues, expenses, total expenses,
and net income, that's for the entire year ending 12/31/2015.
These numbers are for the year ending 12/31/2015.
Now I'm going to highlight both of those dates, control
b to add bold.
Over here to expenses, control b to add bold.
Now another convention for income statements
that we learned back in video number seven
is this cell is total expenses.
That's a calculation, and on an income statement,
you have a line above the calculation that
means that number is some calculation on the numbers
above.
Now that calculation is adding.
And this one is subtracting revenues and expenses.
So I'm going to highlight actually the label
two and the two numbers.
Control one to open up Format Cells.
On the border tab, I'm going to select the medium line,
and click over here to draw a line right at the top only.
Click OK.
If I click off to the side, that's looking good.
Now the mention for the final number
of an accounting report or many other reports,
that's the bottom line.
So we indicate it with a double line.
With the cell selected, control one.
I'm first going to draw my medium dark line at the top.
Then I'm going to select the double line,
and draw that at the bottom.
Click OK.
Click off to the side.
That's looking good.
Now let's format the title a little bit.
I'm going to select cell A6.
Right click, and in the font dropdown,
I'm going to select 16.
Now I'm going to select both cells, control b.
Now highlight one, two, three.
And the cells below, we have two labels,
and I want to center each label across the selection.
Control one, and on the Alignment tab, horizontal.
I'm going to select Center across selection.
While I'm here, I'm also going to go over
to fill, and use maybe this light blue right there.
Click OK.
Click off to the side, so that's looking pretty good.
We have added formulas for income statement
and formatted it.
Now we want to talk about something totally amazing
using our rate formula, the part divided by the base.
And this is super common in the financial world, accounting,
finance.
We're going to convert each item in each one of the year's
income statements to a percentage.
Now think about this.
We have revenue coming into the business.
What we'd like to do is compare every single number
using division.
So all of these numbers will be in the numerator,
and compare them all to revenue.
What that will do is here we're going
to get that number divided by that number, which is one.
That means for every $1.00 of revenue.
Well, of course, we have $1.00 of revenue.
But down here if I take total cost of goods sold,
and compare it with division to total revenue,
the percentage will tell us how many pennies for every $1.00
of revenue went to cost of goods sold.
Down here, we want to know for every $1.00 that came
into the cash register, how many pennies went to research
and development?
And finally, when we get down to the bottom, it's totally cool.
We'll have a percentage that says how many pennies of profit
for every $1.00 of revenue.
Now this is called common size income statement
because in essence, we're converting
all of the different numbers for each one of the years.
And when we go over to our next company,
we'll convert those too.
We'll convert all of the numbers to how many pennies for every
$1.00 of revenue.
So you ready?
Equals, now I'm going to click on total revenue.
That's the numerator divided by the same exact number,
but that's going to be f4 locked.
That's the denominator.
As I copy down, the relative cell reference will move,
but that one will remain locked.
Control, enter, and copy it down.
Now I'm going to delete this one right here.
Come down to the last one.
Hit f2, verifying that the cell references are
working correctly.
Enter and look at that.
Cost of goods sold.
Wow, about 74 pennies of every $1.00 for Caterpillar was spent
on the cost of building that Caterpillar, that excavator,
that bulldozer.
Down here, that means about four pennies
of every single dollar that comes in
as revenue goes to the owners of the company as profit.
Now let's do the same over here for the year 2015.
Equals revenue divided by revenue,
but now we hit f4 to lock it.
Control, enter, and copy it down.
I'm going to delete this one right here.
Come to the last cell, f2, verifying that the cell
references are correct.
Enter, now let's highlight, and add some number formatting.
I'm going to go to the dropdown.
Percentage, two decimals, click OK.
We don't need to worry about rounding.
All we're doing is looking at these numbers.
All right, so 74.48 pennies of every dollar
that comes into Caterpillar goes to cost of goods sold.
That means building the excavators, bulldozers,
and trucks.
Down here, every single dollar that comes in, 4.42 pennies
goes to the owners as profit.
Now let's go over to Amazon.
And these income statements, both Amazon,
and the CAT one, and the one for your homework, I
downloaded from Yahoo Finance.
And they had the same exact expenses
for each one of the companies.
Now if you look at the full income statements
from the SEC, the Securities and Exchange Commission,
the official accounting reports have
all sorts of different expenses for each company.
But what they do at Yahoo Finance
is they give the same expense categories to each company,
so we can compare.
But the thing is look at that.
That's exactly the same set up as over here, so watch this.
We're going to cheat.
We love learning fast, easy tricks in Excel.
I'm going to highlight every one of these cells.
Remember, we did a bunch of different formatting steps.
But because it's exactly the same size
here as it is over on Amazon, I can simply right click.
And on on the mini toolbar, click my format painter.
Format painter, what does it do?
It copies just the formatting.
So when I come over to Amazon, and very carefully click in a2,
just like that.
Now, of course, that wouldn't work
unless both of these templates were exactly
the same size with the same data, dates, numbers,
et cetera, in all the cells.
Now I already put the sum and the net income formulas here.
But now that we have our Amazon, we can come over here.
And I'm actually going to show you
a trick that usually don't show in business math class,
but I got to show you this trick.
It has to do with cell references.
Remember, we in this class have learned either relative cell
reference, or if I use the f4 key, that's absolute.
That means no matter where I copy
that, it's going to be locked.
But there are two dollar signs in that locked cell reference.
The b, that represents the b column.
The five, that represents row five.
What happens if I hit the f4 key not just one time
like we've done in all 21 videos so far in this class,
but what if I hit it a second time?
Oh, now it's just locking the row.
If I hit the f4 key a third time--
oh, just dollar sign in front of the column.
I'm locking just the column.
If I hit it a fourth time, it goes back
to relative cell reference.
So you could actually hit the f4 key as many times as you want.
This is actually called the merry go round key
because it just goes round and round
through all the different types of cell references.
Relative, f4, absolute, f4.
Just the row locked, not the column, f4.
Just the column locked, not the row.
Now if I hit f4 and stop there, watch what happens.
When I copy down, row five is locked.
But when I copy this to the since there
is no dollar in front of the b, the b
will move to c as if it were a relative cell reference.
Which happens to be exactly what we want here
for our denominator.
Copy to the side.
I want it to move there, so let's just try this.
Control, Enter.
If I copy down, remember this is the denominator.
But when I copy, the whole column of formulas over that
is amazing.
Now it's got the right denominator in this column.
You can come over and hit f2, enter, f2, enter, f2.
You see the five is locked, enter.
If I come to the top and hit f2, b$5 tab.
When I hit f2, oh, perfect.
The b over here was allowed to move to c, and that's
exactly what we want.
But again, as we copy down, enter, f2, enter, f2.
The five is locked, so that's a pretty cool trick.
Now watch this.
I'm going to delete all those, and here's
why that's so important.
Here we have two years, but you might have two, three, four,
five.
You might even have an income statement with 12 months.
That means all we have to do for our formula is relative cell
reference divided by that total revenue, f4 one, two times.
That formula will work.
Control, enter.
I'm going to apply general number formatting for the time
being.
I'm going to copy it down.
Then copy it to the side.
Then click the two cells with two zeros, delete.
Go to the diagonally furthest cell away.
Click f2, and look for that.
It got it exactly right.
Now I'm going to highlight all of these,
and come up to the dropdown and 0.2 percentage.
And just like that, I have all of the percentages for Amazon,
and they're very different than what
we saw over with Caterpillar.
Now what confuses me about what the numbers being reported
at Yahoo is I don't understand why Amazon's not
classifying any of their expenses
as research and development.
Because they're actually doing research and development
all the time.
But what we can see is, of course, Amazon is mostly
selling things online.
So their cost of goods sold, 66.96 pennies for every $1.00
of revenue.
That's like the actual cost of the books, and shoes,
and shirts that we buy.
But look at this.
Selling marketing administrator, that's
how much it is for marketing and selling.
That's 30.95 pennies per $1.00.
So every $1.00 that comes into Amazon's cash register,
we have about 67 pennies going to the cost of the good,
and about 31 pennies going to selling marketing expenses.
We go back over and look at CAT.
74 pennies went to the cost of the excavator and bulldozers.
And only 14.68 pennies went to selling marketing.
And we have four pennies going to research and development.
So that was a little bit about common sized income statements.
Income statements, very common.
All businesses create income statements
because they need to see what are the revenues.
What are the expenses, and most importantly,
what is the profit.
Common size allows us to see for every $1.00 of revenue,
how many pennies each item represents.
And then we can compare these percentages
with other companies.
In essence, we've converted these very specific numbers
to common percentages that we can use to compare.
Using what?
Our rate formula.
All right, in this video, we reminded ourselves
how to format an income statement.
We created two formulas, total expense net income.
We saw how to create a common size income statement.
And for the first time, we saw a new type of self-reference
called a mixed cell reference.
Now I'm not going to require a mixed cell reference on a test,
but I am going to show it to you in this video,
and, maybe a couple other videos also.
And what does it do?
It just helps us create all of the formulas more quickly.
All right, if you like that video,
be sure to click that thumbs up.
Leave a comment, and sub because there's always lots more videos
to come from Excel is fun.
Including next video, video number 23,
we'll talk about increase decrease problems.
All right, we'll see you next video.