Proforma Income Statement

Balance Sheet and Income Statement https://www.youtube.com/watch?v=DLTelkJ2qDA
For more videos http://facpub.stjohns.edu/~moyr/videoonyoutube.htm

---

Closed Caption:

when you first learn about capital
budgeting techniques such as net present
value or $PERCENT internal rate of
return your generally given the cash
flows and we do this to make it easier
so you can concentrate on learning the
technique net present value or $PERCENT
internal rate of return the next step in
the process is learning how to estimate
those cash flows
now you may recall that you can get
different cash flows like operating cash
flow by looking at the income statement
of a company now one thing we like to do
is create a pro forma income statement
or forecasted income statement and this
income statement could be for the
company as a whole but in most cases
were interested in producing a proform
income statement for the project
so if we undertake a new project what
are the cash flow is going to be or
whatever
what are the revenues going to be what
are the costs going to be one of the
prophets going to be so and we call this
a proform income statement and here's
how we can construct it there's a lot of
ways to construct it but one method is
what we refer to as the percentage of
sales method that we're going to going
to assume is that many of the important
very such as cost of good soul are going
to be a percentage of sales that is that
they don't change very much so in fact
let's take a look at here I've gone to
morningstar.com and I've typed in dell
and if you scroll down if you look at
key ratios and hit profitability they
have things that are percentage of sales
ok revenues of course our sales revenue
so that's a hundred percent everything's
/ sales notice that cost of goods sold
Coggs is fairly consistent in terms of
its percentage of sales eighty-two
percent
one . 7081 . succeed etc okay it does
fall off in 2012
now there's two possibilities here
perhaps their cost of goods sold went
down because they found a more efficient
way or less expensive way to produce
their computers or perhaps Del is moving
into a different line of business
some of you may be familiar with the
computer company IBM IBM built its
reputation on building large mainframe
computers in our computer company and
later on they had a very successful
personal computer the IBM PC but lately
they've moved into the consulting
business
ok that's they've actually reinvented
themselves as the computer business the
especially the pc business was not
becoming very profitable it was becoming
quite quite a commodity a lot of
companies make these computers they're
all basically the same so they got out
of that line of business they don't even
make personal computers anymore they
sold their their personal computer
business to lenovo which is a chinese
company you may have noticed if you
watch TV at all
they run a lot of commercials where they
talk about being IBM offers where they
talk about building a smarter planet
that you know food is being wasted and
you know our logistics we can figure out
how to stop that from happening or how
we can help with with the traffic
congestion and what are they doing
they're selling consulting business and
the consulting business is going to have
a lower cost of goods sold
you don't need raw materials to do
consulting so it may be here that dell
has shifted its focus away from building
personal computers to more of a
consulting business so that may be one
reason that their cost of goods sold his
azz
on down but for a company that has not
changed what they're doing cost of goods
sold
okay most of these percentages are
pretty consistent sales in general
administrative expenses
ok 8.61 8.55 8.33 ok you see it going up
here and again i'm not sure why but
that's something that could be that
would be interesting to investigate if
you were going to analyze dell is a
possible investment
perhaps these are going up because they
are shifting lines of business and
they're spending more on advertising to
make consumers aware of the new lines of
business there in if you look at their
R&D hovers around one percent ok went
down a little bit here
ok perhaps because of the recession k
back around one percent up a little bit
here so you know again pretty consistent
so we go back to our spreadsheet
what do we do we try and create this
poor proform income statement by putting
the relevant variables the things we're
going to change in the I put them in the
left-hand column here these two
left-hand columns and the nice
essentially set up an income statement
down here
sales revenue minus cost of goods sold
minus sales and general administrative
expenses depreciation cetera so that i
can create so that i can find out what
net profit or net income is and so how
do we create that well the reason we
like the sales percentage of sales
method is sales are much more
straightforward to forecast you know if
you think about a company like Ford they
probably have a pretty good idea of how
many cars they will sell any year now a
recession could hit in the middle of the
year or something something could happen
that could change that forecast but they
probably do a pretty good job they've
hired economist to forecast these things
these economist would look at things
like you know how many cars were sold
last year people haven't bought a car
and then
many years are likely to need to replace
their car as i'm doing this this video
the east coast was hit by Hurricane
sandy many people's automobiles were
damaged so that would bump up the demand
but in normal years you can probably
project automobile sales based on how
many cars were sold last year and the
year before that year before that
because people will replace their cars
you can look at how the economy is doing
if the economy is growing well then
people will be buying and replacing cars
of the economy's sluggish car sales will
probably lag but still it's probably
much more straightforward to forecast
can also look at interest rates for
financing cars so you try and come up
with some sort of forecasts of sales and
you may want to put in a growth rate so
that sales will be growing over a number
of periods so you know if we happen to
be doing this for dell since I looked at
Dells financial statement
perhaps are introducing a new computer
they believe that they're going to sell
a million units in the first year we'll
put in a formula for their sales growth
rate i have incorporated in yet because
i put in a 0 they're going to sell this
computer for four hundred dollars we
have cost of goods sold here as
eighty-one percent sales and general
administrative expenses at nine percent
in a a fixed appreciation straight-line
depreciation method of 10 million
dollars now we can modify that to use
some of the more complicated methods
such as the macrs modified accelerated
capital recovery system it's a
depreciation method that the government
allows that allows companies to
accelerate their appreciation to to
write off more the cost early in the
life of that capital expenditure day to
get some of their money back faster and
we'll we'll talk about that more in a
different tutorial but here let's just
set this thing up what our sales revenue
revenues are going to be sales times
price price times quantity that equals
revenue we can also incorporate a growth
rate and that's what I've done here so
few if I just wanted price times
quantity I'd have b6 which is quantity x
price which is b8 but I want to
incorporate a growth rate and so i have
1 plus b 7 1 plus the growth rate and
I'm gonna raise it to a power I want to
raise it to the 0 power year because in
the first year it's not going to grow
I want to raise it to the second power
I'm sorry to the first power here to the
3rd 2nd power here 3rd power for power
notice what I've done i'm raising it to
a power that's one year to the what year
it is in the project minus one that's
what I've done here I said let's look at
what's in c12 that's the number one and
i subtracted 1 and I raised it to i'm
sorry i don't raise the tank that's the
power raising two and then i multiply it
all of this times the price so I've got
me do that again
I've got quantity sold x 1 plus the
growth rate raised to the whatever year
it is minus 1 you can see the way I put
it in if I moved to this cell here now
it's d12 so it's going to take two and
subtract one and there are other ways
you could create this spreadsheet as
well this is the way I did it and you'll
notice that if i change the growth rate
of sales zero growth rates of sales is
the same every year which means that
cost of goods sold is the same every
year because that's eighty-one percent
of sales and SGA sales in general
administrative expenses of the same
suppose I change this to five percent so
the growth we get a five percent growth
rate
what do you see you see now revenues
have gone up by five percent cost of
goods sold are now higher than they were
before
hey in your three they're going to be
five percent higher than they were near
to you can see that net profit is
changing right it's going up because
every year were selling 5% more so if
you use something like this
proforma income statement and you do it
on the spreadsheet it's a very powerful
way to calculate net profits
it's also a great way to do some
sensitivity analysis we can look and we
can see well how does net profit or net
income change if cost of goods sold
change the post cost of goods sold go up
to eighty four percent that doesn't
sound like a big deal but when that
changes net profit quite a bit
ok let's go back to the original
eighty-one percent is all that was 19
million five hundred thousand when we
went up to $OPERAND eighty-four percent
it came down quite a bit came down to 11
million seven hundred thousand so you
can see how sensitive profits are to
cost of goods sold that's why companies
work so hard to try and keep costs down
so this is one of the the the tools that
will need in order to evaluate a project
and incoming tutorials will take this a
step further will calculate cut cash
flows and then we'll calculate net
present value internal rate of turn etc