馃攳
Excel & Business Math 22: Rate Formula for Common Size Income Statement using Mixed Cell Reference - YouTube
Channel: unknown
[0]
welcome to excel and business math video
[3]
number 22 and this video we got to see
[7]
how to use the rate formula to create a
[10]
common sized income statement
[13]
now we'll remind ourselves how to format
[16]
an income statement how to create some
[18]
formulas for an income statement and
[20]
then we're gonna see how to use the rate
[22]
formula part divided by base to create a
[25]
common sized income statement now we're
[28]
gonna start off by going to the sheet
[30]
cat and what I did is I went online and
[34]
found catepillars income statement will
[38]
actually do cat on this sheet and then
[40]
we'll do Amazon income statement on the
[43]
next sheet now to remind you we have
[45]
revenues these are like the incomes for
[49]
the business or the amounts coming in
[52]
for the business and then these are all
[54]
the different expenses each one of these
[56]
is a different expense or cost of
[60]
running the business now if we add up
[62]
all of the costs or expense and subtract
[66]
it from the revenue we get either profit
[69]
or earnings or an accounting they call
[72]
that net income now the first thing is
[75]
we want to do a little bit of formatting
[76]
let's highlight all of the numbers
[78]
including the two cells that don't have
[81]
formulas both will have formulas with
[84]
our dollar amounts control want to open
[87]
up format cells I'm gonna go down to
[89]
currency select two decimal symbols okay
[93]
that's the - if you like a county better
[96]
by all means select that click OK now I
[100]
want to come down to total expenses and
[102]
I'm going to use the keyboard for the
[104]
sum function - alt equals and it got it
[108]
right it gets the correct range so ctrl
[110]
enter and I'm gonna copy it over now
[113]
that's the total expenses we need to
[116]
subtract that from the total revenue so
[119]
down in cell B 18 equals total revenue
[124]
minus total expenses control-enter and
[128]
copy it to the side now on an income
[132]
statement or
[133]
any report we can make these numbers
[136]
less cluttered first off we don't need
[139]
the decimal and zero zero they're not
[142]
going to show any pennies so let's
[144]
highlight and we can either use control
[147]
one currency or in this case we can
[149]
simply go home number group decrease
[153]
decimal or I love my mini toolbar right
[156]
click and there it is that's probably
[158]
the closest fastest way to decrease
[161]
decimals another thing we can do to make
[164]
it less cluttered and we talked about
[165]
this back in video number seven is the
[168]
important numbers like total revenue and
[171]
the first expense in the list of expense
[174]
and then total expense and net income we
[177]
can leave the unit the dollar sign there
[180]
but on all these remaining numbers
[182]
inside we can highlight control one and
[186]
under currency we're going to say show
[188]
symbol none click OK now let's do some
[192]
formatting I am gonna follow the
[194]
convention of adding some green because
[197]
these are formulas and these are numbers
[199]
typed in if we were printing this out
[202]
officially as our report for some
[204]
meeting or something I would not put
[205]
green here but for the time being I'm
[208]
gonna select that green right there and
[210]
then for the net income numbers instead
[213]
of going back up to font group and home
[215]
ribbon tab right click and on the mini
[218]
toolbar I'm going to choose from recent
[221]
colors that green if you don't have that
[223]
one you can go down to more colors so
[226]
we've added our green to say hey those
[228]
cells have formulas now these numbers
[232]
revenues expenses total expenses and net
[234]
income that's for the entire year ending
[237]
1231 2014 these numbers are for the year
[241]
ending 1231 2015 now i'm gonna highlight
[245]
both of those dates ctrl be to add bold
[249]
over here to expenses control be to add
[252]
bowl now another convention for income
[256]
statements that we learned back in video
[258]
number seven is this cell is total
[260]
expenses that's a calculation and on an
[263]
income statement you have a line above
[266]
calculation that mean that number is
[268]
some calculation on the numbers above
[271]
now that calculation is adding and this
[274]
one is subtracting revenues and expenses
[277]
so I'm gonna highlight actually the
[278]
label two and the two numbers control
[281]
log to open up format cells on the
[284]
border tab
[286]
I'm gonna select the medium line and
[289]
click over here to draw a line right at
[292]
the top only click OK
[295]
if I click off to the side that's
[297]
looking good now the convention for the
[301]
final number of an accounting report or
[304]
many other reports that's the bottom
[306]
line
[306]
so we indicate it with a double line
[308]
with the cell selected control one I'm
[312]
first going to draw my medium-dark line
[315]
at the top then I'm going to select the
[318]
double line and draw that at the bottom
[321]
click it ok click off to the side that's
[325]
looking good now let's format the title
[329]
a little bit I'm gonna select cell a6
[333]
right click and in the font drop-down
[336]
I'm gonna select 16 now I'm gonna select
[340]
both cells control B now highlight one
[344]
two three and the cells below we have
[347]
two labels and I want to Center each
[349]
label across the selection control one
[353]
and on the alignment tab horizontal I'm
[357]
going to select Center across selection
[359]
while I'm here I'm also going to go over
[362]
to fill and use maybe this light blue
[365]
right there click OK click off to the
[369]
side so that's looking pretty good we
[371]
have added formulas for our income
[373]
statement and formatted it now we want
[377]
to talk about something totally amazing
[379]
using our rate formula the part divided
[382]
by the base and this is super common in
[385]
the financial world accounting finance
[388]
we're going to convert each item in each
[392]
one of the years income statements to a
[395]
percentage now think about this we have
[398]
revenue coming
[399]
into the business what we'd like to do
[402]
is compare every single number using
[405]
division so all of these numbers will be
[407]
in the numerator and compare them all to
[411]
revenue what that will do is here we're
[414]
going to get that number divided by that
[415]
number which is one that means for every
[418]
one dollar revenue well of course we
[420]
have one dollar of revenue but down here
[422]
if I take total cost of goods sold and
[425]
compare with division to total revenue
[427]
the percentage will tell us how many
[430]
pennies for every one dollar of revenue
[434]
went to cost of goods sold down here we
[438]
want to know for every one dollar that
[440]
came into the cash register how many
[443]
pennies went to research and development
[445]
and finally when we get down to the
[448]
bottom it's totally cool we'll have a
[450]
percentage that says how many pennies of
[455]
profit for every one dollar of revenue
[458]
now this is called common size income
[462]
statement because in essence we're
[464]
converting all of the different numbers
[466]
for each one of the years and when we go
[469]
over to our next company we'll convert
[471]
those to we'll convert all of the
[473]
numbers to how many pennies for every
[476]
one dollar of revenue so you're ready
[479]
equals now I'm going to click on total
[482]
revenue that's the numerator divided by
[485]
the same exact number but that's going
[489]
to be f4 locked that's the denominator
[492]
as I copy down the relative cell
[495]
reference we'll move but that one will
[497]
remain locked control enter and copy it
[501]
down now I'm going to delete this one
[504]
right here come down to the last one hit
[506]
f2 verifying that the cell references
[509]
are working correctly enter and look at
[513]
that cost of goods sold Wow
[516]
about 74 pennies of every one dollar for
[521]
Caterpillar was spent on the cost of
[524]
building that caterpillar that excavator
[528]
that bulldozer down here that means
[531]
about four
[533]
or pennies of every single dollar that
[537]
comes in as revenue goes to the owners
[540]
of the company as profit now let's do
[544]
the same over here for the year 2015
[547]
equals revenue divided by a revenue but
[551]
now we hit f4 to lock and ctrl enter and
[554]
copy it down I'm going to delete this
[557]
one right here come to the last cell f2
[560]
verifying that the cell references are
[563]
correct
[563]
enter now let's highlight and add some
[567]
number formatting I'm going to go to the
[569]
drop down percentage two decimals click
[574]
OK we don't need to worry about rounding
[576]
all we're doing is looking at these
[578]
numbers
[579]
all right so seventy four point four
[581]
eight pennies of every dollar that comes
[585]
into caterpillar goes to cost a good
[588]
soul that means building the excavators
[591]
bulldozers and trucks down here every
[594]
single dollar that comes in four point
[597]
four two pennies goes to the owners as
[599]
profit now let's go over to Amazon and
[603]
these income statements both Amazon and
[607]
the cat one and the one for your
[608]
homework I downloaded from Yahoo Finance
[612]
and they had the same exact expenses for
[615]
each one of the companies now if you
[617]
look at the full income statements from
[620]
the SCC the Securities and Exchange
[623]
Commission the official accounting
[626]
reports have all sorts of different
[628]
expenses for each company but what they
[631]
do at Yahoo Finance's they give the same
[635]
expense categories to each company so we
[638]
can compare but the thing is look at
[640]
that Boop that's exactly the same setup
[644]
as over here so watch this we're gonna
[647]
achieve we love learning fast easy
[650]
tricks in Excel I'm gonna highlight
[652]
every one of these cells remember we did
[655]
a bunch of different formatting steps
[657]
but because it's exactly the same size
[661]
here as it is over on Amazon I can
[664]
simply right click and on the mini to
[666]
bar click my format painter format
[670]
painter what does it do it copies just
[674]
the formatting so when I come over to
[676]
Amazon and very carefully clicking a to
[679]
whoop just like that now of course that
[682]
wouldn't work unless both of these
[684]
templates were exactly the same size
[686]
with the same data right dates numbers
[689]
etc and all the cell's now I already put
[693]
the sum and the net income formulas here
[696]
but now that we have our Amazon we can
[699]
come over here
[699]
and I'm actually going to show you a
[701]
trick that I usually don't show in
[703]
business math class but I got to show
[706]
you this trick it's it has to do with
[708]
cell references remember we in this
[710]
class have learned either relative cell
[713]
reference or if I use the f4 key that's
[715]
absolute that means no matter where I
[718]
copy that it's gonna be locked but there
[722]
are two dollar signs in that locked cell
[726]
reference the B that represents the B
[730]
column the five that represents row five
[735]
what happens if I hit the f4 key not
[740]
just one time like we've done in all 21
[744]
videos so far in this class but what if
[746]
I hit it a second time oh now it's just
[750]
locking the row if I hit the f4 key a
[753]
third time oh just a dollar sign in
[756]
front of the column I'm locking just the
[758]
column if I hit it a four time it goes
[762]
back to relative cell reference so you
[764]
could actually hit the f4 key as many
[767]
times as you want this is actually
[769]
called the merry-go-round key because it
[771]
just goes round and round through all
[774]
the different types of cell references
[776]
relative f4 absolute f-14 just the row
[781]
locked not the column f4 just the column
[786]
lock not the row now if I hit f4 and
[790]
stop there watch what happens when I
[793]
copy down row five is locked but when I
[798]
copy this to the side
[800]
since there's no dollar in front of the
[803]
B the B will move to C as if it were
[807]
relative cell reference which happens to
[810]
be exactly what we want here for our
[813]
denominator copy to the side I want it
[816]
to move there so let's just try this
[818]
control enter by copy down remember this
[821]
is the denominator but when I copy the
[824]
whole column of formulas over that is
[828]
amazing
[829]
now it's got the right denominator in
[831]
this column you can come over and hit f2
[833]
enter f2 int or f2 you see the 5 is
[837]
locked enter if I come over here or no
[841]
if I come to the top and hit f2 B dollar
[844]
sign 5 tab when I hit f2 Oh perfect
[850]
the B over here was allowed to move to C
[853]
and that's exactly what we want but
[855]
again as we copy down enter F to enter F
[858]
to the 5 has locked so that's a pretty
[861]
cool trick now watch this I'm going to
[865]
delete all those and here's why that's
[868]
so important here we have two years but
[871]
you might have 2 3 4 or 5 you might even
[874]
have an income statement with 12 months
[877]
that means all we have to do for our
[879]
formula it is relative cell reference
[882]
divided by that total revenue f4 1/2
[888]
times that formula will work
[891]
control-enter I'm going to apply general
[895]
number formatting for the time being I'm
[898]
gonna copy it down then copy it to the
[901]
side then click the two cells with the
[904]
zeros delete go to the diagonally
[906]
furthest cell away
[908]
click f2 and look at that it got it
[911]
exactly right now I'm gonna highlight
[914]
all of these and come up to the drop
[917]
down and point to percentage and just
[920]
like that I have all of the percentages
[923]
for Amazon and they're very different
[926]
than what we saw over with caterpillar
[929]
now what confuses me about what the
[932]
numbers being reported
[933]
yahooo is I don't understand why
[936]
Amazon's not classifying any of their
[939]
expenses as research and development
[941]
because they're actually doing research
[943]
and development all the time but what we
[945]
can see is of course Amazon is mostly
[948]
selling things online so their cost of
[952]
goods sold sixty six point nine six
[954]
pennies for every one dollar of revenue
[957]
that's like the actual cost of the books
[960]
and shoes and shirts that we buy but
[963]
look at this selling marketing
[965]
administrator that's how much it is like
[967]
from marketing and selling that's thirty
[969]
point nine five pennies per one dollar
[973]
so every one dollar that comes in to
[976]
Amazon's cash register we have about
[979]
sixty seven pennies going to the cost of
[982]
the good and about thirty one pennies
[984]
going to selling marketing expenses if
[987]
we go back over and look at cap seventy
[991]
four pennies went to the cost of the
[993]
excavator and bulldozers and only
[996]
fourteen point six eight pennies went to
[999]
selling marketing and we have four
[1001]
pennies going to research and
[1002]
development so that was a little bit
[1005]
about common sized income statements
[1009]
income statements very common all
[1012]
businesses create income statements
[1014]
because they need to see what are the
[1017]
revenues what are the expenses and most
[1019]
importantly what is the profit common
[1021]
size allows us to see for every one
[1024]
dollar of revenue how many pennies each
[1026]
item represents and then we can compare
[1030]
these percentages with other companies
[1034]
in essence we've converted these very
[1036]
specific numbers to common percentages
[1039]
that we can use to compare using what
[1042]
our rate formula alright in this video
[1046]
we reminded ourselves how to format an
[1049]
income statement we created two formulas
[1051]
total expense net income we saw how to
[1054]
create a common size income statement
[1057]
and for the first time we saw a new type
[1060]
of cell reference called a mixed cell
[1063]
reference now I'm not going to require
[1065]
mixed cell reference on
[1067]
test but I am gonna show it to you in
[1068]
this video and maybe a couple other
[1070]
videos also and what does it do it just
[1074]
helps us create all of the formulas more
[1077]
quickly alright if you like that video
[1080]
be sure to click that thumbs up leave a
[1083]
comment and sub because there's always
[1085]
lots more videos to come from excel is
[1087]
fun including next video video number 23
[1091]
we'll talk about increase/decrease
[1094]
problems alright we'll see you next
[1097]
video
Most Recent Videos:
You can go back to the homepage right here: Homepage