Forecasting Financial Statements Part 2

This is a video showing how to forecast financial statements using Excel

---

Closed Caption:

so this is the second in the 24 casting
lectures that I'm putting out online for
you in this lecture what I'm going to do
is work through a spreadsheet and this
spreadsheet is available for you out on
d2l so you can work along with me on
this
we're gonna work through this
spreadsheet and create through forecast
for three years of of this company that
that I've made up
there's if you open up the work that sat
on d2l you'll see that there's different
tabs down here at the bottom the first
of the balance sheet and so we have
three existing years 2010 2011 2012 and
then we've got 2013e that just stands
for expected you might see if they're
sometimes for forecasted or pro forma
but those are the years we that that
we're going to forecast for over on this
side our assumptions that we're going to
use for the forecast leave a sales
growth rate for each year for sixty-six
percent sales growth factor of 1.6 which
is just 1 plus the sales growth rate the
long-term sales growth rate of
three-and-a-half percent we're not going
to worry about that today but when we
get into free cash flow modeling and
determine the value of the firm
we're going to use that long-term sales
growth rate to determine the horizon
value for the firm and interest rate on
short-term investments of to it
two-point-three percent and interest
rate on notes payable of 4.2 percent and
straight a long-term debt of 6.3 percent
we have a depreciation expense as a
percent of net fixed assets of seven
percent so we'll use that to calculate
the depreciation expense of the income
statement the effective income tax rate
of twenty six percent in a payout ratio
44-percent for the firm three other
assumptions we're going to use to kind
of make things a little simpler are that
we're going to maintain marketable
securities and let me actually change
that to short-term investments they mean
the same thing I just be consistent with
what's on the sheet we're going to
maintain our short-term investments
right here at nine hundred and five
dollars for the forecasted years
we're going to name maintain our notes
payable
at 1,000 185 . 75 for the forecasted
years and then we're going to use
long-term debt for to fulfill any
additional funds needed as you can see
on our existing balance sheets on the
balance sheet balances which is a good
thing we have total assets 7180 2570 in
liabilities plus owner's equity of 7 20
25 inches as intended balance for 2011
and for 2012
moving on to the income statement we see
here we also have three years to
forecast and we calculated our sales for
the existing years the gross profit all
the way down to dividends paid in the
addition to retained earnings and then
finally our ratios i calculated most of
the ratios we went over in class with
the exception of the market value ratios
so we don't have a price to use later on
well actually calculate the value per
share for this firm
we're not going to do that yet as you
can see we've got the years up here 2010
2011 2012 we also have the industry
average for this firm for each of the
ratios the profitability ratios and then
also the dividend payout ratio as you
can see here they've increased their
dividend payout ratio or the last three
years and they exceed that of the
industry what we're going to do after
we've calculated are forecasted income
statement and balance sheet is come back
here then and compare well see what's
happened to the ratios are forecasted
years to make sure it's consistent to
what they've done in the past or what
plans they have for the future and also
to compare this with the industry
averages to see where it if they're
moving in a positive direction or
negative direction and how well they
compared to their peers so to start off
with the first step if you look back on
your notes from last time from the last
lecture that i put online
the first thing we need to do is
identify those accounts that should
change with sales so those would be our
operating accounts kind of the obvious
ones i'll start here with the income
statement sales should increase with
sales so will
start there what I'm going to do is
actually highlight each of them so you
can see when we come back we're going to
use some calculations that allow us to
very quickly forecast financial
statement our cost of goods sold should
also increase with sales so as we sell
more items those will increase sales
there's an implicit assumption here that
the cost of goods sold as a percentage
of state sales will stay the same if
that's not the case will need to come
back and recalculate these gross profit
is just the difference between sales and
cost of goods sold sold our general
selling general and administrative
expenses will also grow with sales
because the more we sell the more
administrators we need managers we're
going to have to market more we're going
to have to have more overhead of going
to have to pay for again we can go back
and change the percent as a person
percent of this excuse me change this
line item as a percentage of sales but
for now we're assuming that this stays
the same as a percentage of sales
I just growing it with sales on the
income statement that's really
everything that's going to directly grow
with sales make some other assumptions
that will come back to about
depreciation expense interesting coming
interest expense and then also the
income tax expense in the dividends paid
you remember those from the the
assumptions that i showed you on the
balance sheet for the balance sheet
we've seen the cash increases with sales
and cash on the balance sheet really
reflects or should hopefully reflect the
cash needed for day-to-day operations as
i mentioned in class companies don't
just like to keep a lot of cash around
and that includes catch and checking
accounts because it really doesn't earn
any interest if they have excess
liquidity or excess cash they tend to
put that in short-term investments we
don't grow short term investments as a
percentage of sales because it is excess
cash and really not necessary for the
day-to-day operations of the front we
expect accounts receivable to grow
with sales so as you know we sell things
on credit if we sell more those accounts
receivable shit increase and also
inventory we would expect to increase
with sales again we can go back and
calculate inventory turnover ratios and
accounts receivable these cells
outstanding and see if we want to manage
those differently in the future and we
can make changes to our forecast based
on that for now though we're just going
to focus on assuming those ratios stay
the same over time I'm gross fixed
assets we're not going to assume that
the those gross sales will make a
calculation later to fill in that blank
but we do assume that knit fixed assets
go with sales i mentioned kmart before
and Choji Martin class
this isn't true for kmart their well
their sales are declining but their net
fixed assets are declining at a faster
rate so it's not a positive sign
what we really want to see from the
company is that they're making the
investments necessary and really the
reason why we forecast the financial
statements is to make sure the resources
are placed these net fixed assets
accounts receivable and inventory are in
place so that they'll have the ability
to grow the the sales of the company in
the future intangible assets that these
are typically things like patents
copyrights investments they made or the
expected value of relationships with
customers and we're assuming these don't
change over the life of the of the firm
or at least in the forecast . this if
you want to use an example this would be
like the trademark the coca-cola has and
it's and it's recipe for coca-cola so
it's not something we're going to really
change the value of during the
forecasted . so those that's not an
operating a set the operating
liabilities are the accounts payable and
accrued expenses we consider those we
call those spontaneously generated
liabilities accounts payable should
increase as be increased sales because
we're buying more supplies and more
inventory on credit and so
as we increase our sales and so that's
going to increase accrued expenses think
of accrued wages as I sell more products
i need more employees as a result of my
crew wages will increase and so we would
call that an operating account and
increase that with sales the long-term
debt is $MONEY per our assumptions are
going to use long-term debt for any
additional funds needed and the rest is
common stock we're going to students
some states the same as well as paid-in
capital and excessive are the only
reason these two accounts which changes
if we issue new equity so those would
increase or if we retire stock and we're
not going to get into this today but
later on we'll talk about retiring stock
if we retire stock means we've
repurchased it which can show up as a
line item is treasury stock but returned
stock actually takes it off the books
entirely we make it disappear and so we
would actually see a decrease in common
stock and painting capital excessive par
if we actually retired to stop for the
firm
so the next step we're going to do is
actually increase these accounts will go
out one year to start with so I'm going
to forecast one year to just go step by
step show you how it's done and put in
the correct formulas and then what
you'll see is that we can easily pull
that out across several years once we
have the correct formulas in place so
what I've done to make life a little
easier is I've named these cells and let
me show you how to do that so you select
the cell and here you already CS growth
for sales growth so all you would do is
put in SG r th for sales growth and
if you're making up your spreadsheet
from scratch you would actually have to
go and type this so this would actually
say k3 up here and you would change it
to whatever you want to do the nice
thing about naming these cells i can put
these names in my formulas so this is Lt
growth so long term growth rate of
three-point-five percent i find this
stood for short-term interest i named
this
NP it for notes payable interest i need
this LT in for long-term interest for
long so long term interest rate on
long-term debt
I mean this de PR for depreciation this
tax rate because the effective tax rate
in this PR for payout ratio so i can put
these the way you do this again is that
you would actually go up here and you
would put the name in for whatever you
want to be in that cell that that way i
can go back now and create formulas
referencing the name rather than the
cells so if i move the the formula over
if i drag the formula over the name
stays the same itself stays the same so
that's what i'll do i'll start with the
balance sheet we have to adjust both the
balance sheet and the income statement
doesn't matter that much the order that
you do this in so for cash i'm going to
it fresh put in equals last year's cash
x and then i'm going to use this sales
growth factor 1 plus the growth rate so
SG factor and you should see the name
come up here and you could actually just
select that and so it gives me next
year's cash based on my growth rate of
six percent i'll do the same here with
accounts receivable and actually i can
just drag this down
pull it across my assumption says that
we're going to maintain short-term
investments and 905 so i can go and put
that in here as well and that gives me
current assets of 10,000 for 12.4 I
gross fixed assets I'm going to come
back to i'll show you that here in a few
minutes but we want to start with net
fixed assets we need to maintain a
certain level of net fixed assets to
generate sales growth in the future so
that's what I'm going to do there and I
so I increase my new fixed assets by the
six percent growth rate intangible
assets
I don't have it over here and put this
in here we're assuming that we're not
going to to eliminate or sell off any
patents we're not going to take any more
intangible assets on during the
forecasted years so we're just going to
leave those the same so from this i can
calculate my total assets because we
don't include gross fixed assets or
accumulated depreciation in this number
so it would be equal to our current
assets plus our netflix assets plus are
intangible assets so we have total
assets of eighty-six thousand nine 6864
our notes payable will be equal to last
year's x this sales growth factor our
Creed expenses will do the same thing
and then our notes payable on our
assumptions that says our notes payable
will remain at eleven 85-75 so my work
total current liabilities are equal to
four thousand 8697 my long-term debt i'm
going to leave that alone for now
because i'm going to use long-term debt
to adjust my balance sheet for any
imbalance my total liabilities will be
equal to our long-term debt plus my
current liabilities so 408 6.97 for now
we'll change that our common stock will
say the same we're not planning on
issuing any additional common stock or
retiring any stock because we're going
to adjust our additional funds needed
through the long-term debt are paid in
capital and excessive par will say the
same
alright and then retained earnings are
going to be equal to last year's retain
earnings plus here on the income
statement i have addition to retain
earnings and so that's net income minus
the dividends paid and then we calculate
our total equity as the sum of these
amounts are total liabilities plus
owner's equity is equal to those two now
make sure that you're going through this
that you use formulas and not type in
numbers because these numbers are all
going to change as we go through here
and we calculate the income statement of
retained earnings you're going to change
later on what you're going to see is
when we have to adjust long-term debt we
put a number in here that's going to
change our income statement because
we're going to have to include interest
expense and so all these numbers will
change so you need to use formulas just
as I've shown you here rather than using
actual numbers in those cells so let's
move on to the income statement now and
we expect sales to grow by the rate of
sales growth
so and then cost of goods sold us again
we're assuming that these state remain
constant as a percentage of sales and
then our gross profit is equal to the
difference between the two we expect
SG&A expenses to grow with sales and
depreciation expense if you remember
from the assumptions we say that
depreciation expense is equal to a
percentage of the net fixed assets so
i'll select that equals depr x our
netflix assets for the year that we've
already projected out so we have an
expensive 3748 96 is our operating
income is equal to a gross profit minus
SG&A expense minus depreciation expense
so we have an operating income or
operating profit so far of 83 1915 and
just so you know this can be referred to
as operating profit as well you'll see
that sometimes it's also the earnings
before interest in taxes
ok also eat it you'll need to know that
not yet but in the future when we
calculate the free cash flows are
interesting can we put it as a negative
number and that's because if you look
all of our expenses are positive so our
expenses are our income are other income
will decrease our expenses so we'll put
this in as a negative number is equal to
negative interest rate on short-term
investments so st int star int x the
amount of short-term investments for the
year and then the interest expense we
have two different interest expenses
the first one is the interest expense on
notes payable so if you remember I put
this in as NP int four notes payable
interest expense
and that we're going to multiply that by
so it's right here is 4.2 percent and
i'm going to multiply that by my notes
payable for the year and then I'm going
to add to that a long-term interest
expense of 6.3 percent so it's el-tee
int x our long-term debt which we don't
have a number for yet but we will in a
minute
so our earnings before taxes so far are
going to be equal to operating income
minus the sum of these two numbers so
it's 80 to 90 . 16 our income tax
expense would be equal to our earnings
before taxes x our tax rate and here we
have a tax rate of $PERCENT effective in
context a 26-percent you'll see the word
tax rates there what's nice about this
is once we get the 2013 balance
we're gonna pull is over and it's just
going to keep referencing that tax rate
sell our net income then is equal to
earnings before taxes minus article tax
expense
are dividends paid are equal to the
payout ratio and our payout ratio we
have set at forty-four percent x our net
income and then our addition to retain
earnings would be the difference between
our net income and the dividends paid so
let's go back to our balance sheet
that's our income statement for now one
thing that's going to change on here is
our interest expense we don't have any
long-term debt on our balance sheet yet
so are additional funds needed at this
point and you'll notice i'm calculating
it a little bit different than what we
did in the previous lecture the previous
lecture for that we're just have very
basic numbers and we're calculating are
additional funds either based on that we
could actually go back and do this here
I find it easier to be a little Mormon
article going to the balance sheet going
to the income statement rather than just
looking at the operating accounts
individually but you could do that you
could go back and calculate it that way
couple things I can do here i can finish
up the this part of it doesn't have any
effect on the actual amounts for the
total liabilities and owner's equity or
the total assets but it fills out the
the balance sheet so what I'm going to
do here is going to set the accumulated
depreciation equal to last year's plus
depreciation expense we do make the
assumption here that we haven't sold off
any net fixed assets if we sell off
netflix or excuse me
gross fixed assets that we're not
selling off any of our assets if we do
sell off an asset then our accumulated
depreciation would fall by the amount of
depreciation that had been expense to
get that asset
ok so we have gross fixed assets of 96 a
20 accumulated depreciation 43 260 3.96
and the netflix asus and 53 550 650 and
the way to calculate this again I'm the
depreciation cumulated depreciation is
last year's depreciation accumulated
depreciation plus this year's
depreciation expense so that's our
accumulated depreciation on netflix
assets is a function of the sales growth
rate in the existing fixed assets of the
firm and to get the gross fixed assets
and we just add together the accumulated
depreciation and the netflix asses are
tangible assets are changing so what
kind of done with this part of the
balance sheet for now are additional
funds needed would be equal to the total
assets minus the total liabilities plus
owner's equity and we set up here that
we're using our long-term dead for any
additional funds needed so here's a
little trick here going to go ahead and
put in my long-term debt my additional
funds needed as my long-term gets me 23
444
85 but then it shows me that I have
additional funds needed of six 1207 so
what happened
so whenever we put that number in we
were automatically increasing our
interest expense to cover the interest
on that debt i could go through and just
increases slowly so i could go to 24,000
and it still there's additional funds
needed so i'll go 24-7 1.12 other still
additional funds needed this is referred
to as an iterative process so we would
if we wanted to we could keep typing
numbers until we got really really close
or are able to actually eliminate our
additional funds needed but there's an
easier way i'm in Excel i'm going to
highlight this cell additional funds
needed to go to data what-if analysis
goal see because of all I want to do is
get rid of this number so i'm going to
set that sell 20 by changing my
long-term debt and then press ok and it
does that so it finds the amount of
long-term debt balance the balance sheet
given that changing the long-term debt
affects the interest expense for the
firm so I've done that my balance sheet
balances my retained earnings call from
one year to the next
so I'm happy and we can see that 449
71-68 + 280 6.96 is equal to 50
excuse me 43 770 8.64 so the two main
things I'm looking for their occur so
what do I do now
and now what i can do is actually pull
all my accounts across because I how I
set them up and you have to be careful
about this and I have to make this 90 57
of six
five but if you have your formulas in
there correctly you can just pull these
across so you see it's referencing my
sales growth factor for each year so our
sales growth our sales are increasing
our counselor increase our inventories
increasing I can do the same here just
by . this over i'll have to watch out
here and change that back to 23,000
again i can do the same thing here I
just pull everything over long-term debt
we're going to leave that alone for now
because we're going to need to fill in
those amounts my captain my common stock
my ad capital and excessive partner
that's changing the retained earnings
will grow hopefully with my
profitability
oh I'm going to show anything yet
because I haven't forecasted out my I
haven't moved out my inconsistent
so now our balance sheet doesn't balance
but it will come closer once we finish
our income statements so i'm going to
select all of these pulling out over the
two years so you see we have included
our debt yet so for this year
it shows that i have i need to pull this
over to there we go
I'm it shows that i have additional
funds needed of 20 40 40 48 and for this
year's 2405 what I'm going to do now is
just use that will see to find out the
amount of get ineed goal seek i'm going
to set this additional funds needed sell
equal to zero by changing cell long-term
debt and it comes up with my answer now
I just do the same thing here let's set
the cell equal to zero by changing my
long-term dad and my balance balance
balance she balances 310 during its flow
properly from one year to the next
so unhappy this is my forecast financial
statement is my forecast Sunni balance
sheet I forecasted income statement and
then finally my ratios I've already
calculated the ratios for your homework
assignment you're going to have to go
through and calculate the ratios but
I've already done that here so now i can
just pull these across and take a look
at what i have what I'm most interested
in is are there big change the ratios
and are the ratios training in a way
which I would want as a stockholder
anything that we want to change we're
not going to go back and change anything
yet but we just want to identify
anything that looks strange
anything extremely out of the ordinary
or a trend that we want to reverse the
future start with the current ratio as
we can see historically and the current
ratio is decreasing the quick ratio is
decreasing in 2010 these are both quite
a bit higher than the industry average
as we talked about in class during
normal times we actually like to see
these ad average or a little bit below
average because liquid assets don't give
us a very good return so we were
actually doing really well here
the company was but then now we're
starting to increase again so we we
might want to take a look at we might
want to take a look at why this is
happening if i look at the balance sheet
i can see that the accounts receivable
are increasing with sales and inventory
is increasing with sales and also our
caches so we might want to make it
decision there about what we would want
to change in the future
quick ratio has the same trend was
decreasing and a slight uptick over the
forecasted . our accounts receivable
sales outstanding we see a decrease from
2011-2012 and then it remaining fixed
after that time . and inventory days on
hand we see a decrease from 2008-11
slight increase and then we're holding
those steady during the forecasted years
these are pretty consistent accounts
receivable days sales outstanding is a
little lower in the industry which is a
good thing
inventory days on hand is a little lower
which is also a good thing our total
asset turnover ratio is increasing and
continues to increase during the
forecast period they're ever so slightly
every year are fixed asset turnover
ratio decreases and then hold steady
throughout the forecast years
ideally we want to see these higher
higher is usually better with these
they're not really up to the industry
average yet so one thing the company may
look at during this forecast period is
finding a way to increase their total
asset turnover the debt ratio for the
firm started off slightly under that of
the industry has increased and then
we're showing a decrease in the debt
ratio over the excuse me over the period
of the forecast . this is because we're
funding a greater percentage of the
assets of the company with retained
earnings as time goes on long-term debt
to equity ratio shows the same trend and
increase between 2010 2012 and then
decreasing over the forecast . the times
interest earned ratio and I i said in
class at six is a good minimum number
for the industry is below that so maybe
this is an industry that has a lot of
fixed assets or is a good candidate for
dead where they're going to be able to
take a little more debt i like this
trend as a stockholder these are fairly
reasonable numbers if not a little low
so it see it increase to me is kind of a
healthy sign again we'd want to know a
little bit more about the industry to
make a good determination there the
gross profit margin is a big deal for
the company
so what this means is that approximately
$OPERAND sixty-three percent of the
revenues are eaten up by our cost of
goods sold so we really want to see this
remains stable are actually go down a
little bit over time we see is very very
slight decrease between 2010-11 and the
forecast shows that were expecting this
to remain stable for the for the
forecast period net profit margin slight
increase over time which is a good trend
not really up to the industry average so
maybe we can look at ways to improve
that clearly the difference between
the firm in the industry is it due to
the cost of goods sold
they're about the same so it's pretty
likely that the firm has a higher SG&A
expense than that of the industry
because they have a lower we would
assume they have a lower interest
expense because they have a little less
debt the industry doesn't a little
higher times interest earned return on
assets are improving over the time . not
really up to the industry average at
that is a positive sign return on equity
again it's increasing not really up to
the industry average but improving over
time if we look at our coupon analysis
can compare that to the industry our net
profit margins lower so that means our
early that's probably driving the
difference in our way they're total
asset turnover is lower than the
industry so again that's driving the the
lower are we and our equity multiplier
so are the amount of debt we rely on is
is also lower so the first click on more
debt and increase our Alisa the dividend
payout ratio for the firm is higher than
the industry and we want to look at
there is possibly that sustainable
growth rate that i showed you before if
the company's paying out this much in
dividends this percent of dividend of
income is dividends they may not be able
to grow in the long term as quickly as
their industry competitors so that's
something we want to look at maybe
changing selected fun more growth
through the retained earnings so this is
just an overview of how to calculate a
forecasted financial statements and
things we look at the pretty ratios some
techniques with using naming cells to
make things a little faster and then
using goal seek to make our balance
sheet balance i hope this has been
useful for you i'm like i said all i'll
be putting out a an assignment that's
similar to this where you'll be asked to
forecast the financial statement i'll
give you some assumptions
then you need to make sure you balance
she balances your retained earnings flow
and then analyzed the ratios for the
firm for the forecasted . if you have
any questions please let me know and i
look forward to see you next week