hey everyone eric here in this video we're gonna learn how to forecast a depreciation
schedule in excel using our capex plan then i'll show you how to link depreciation back
into the balance sheet as accumulated depreciation, the income statement as an expense,
and the cash flow statement as an adjustment to net income in the operation cash flow section
okay let's get started so the first thing we need to calculate a depreciation schedule is always the
capex plan which is the plan that shows any asset purchases that we have coming over the next year
or whatever our budgeting period is so let's say that we've been working with our teams and
we know that we need to buy a piece of machinery that's worth 250 000 dollars in august of 2024
so we would put that in there and remember any hardcoded inputs in excel when you're building
a financial model hard coded means just typed in no formula put it in dark blue its a rule of thumb
for financial formatting and so make sure that you do that so that other people can read your models
so 250 000 dollars and we've been working with our engineering team and they need a piece of
software that's gonna cost 160 000 dollars so over the next 12 months we're gonna spend
410 000 dollars on asset purchases so lets now calculate the depreciation associated with these
asset purchases so let's say the machinery has a useful life of 12 months and the software
has a useful life of 36 months here's a really easy way to calculate the depreciation schedule
so i would just take the 250 000 dollars tap f4 which locks the row and the column
and divide it by the useful life and then i would tap f4 once again lock the row and the column and
hit enter then ctrl c shift right arrow control v that's just copy paste so you can see that for the
last 5 months of the year we'll be depreciating this asset and if we were gonna make uh extend
this forecast into the next year we would show the full 12 months of the depreciation of the asset
software is exactly the same hit equals grab the capex total lock the row and the column and divide
it by 36 and tap f4 again so we would again extend this the full 36 month lifetime of the asset
were we to build a 3 or 4 year forecast right now but for these next 12 months this is what
we can expect from these assets and so this shows you your total new depreciation but don't forget
you will always have historical depreciation that's rolling forward from past asset purchases
so the easiest way to get this is to ask your accounting team they will know that you bought
say a piece of software 2 years ago and the depreciation of that software might be 3 years
they'll have the exact dates and they'll have the exact amount of depreciation that they're planning
to book so you can get this straight from them in the case that they didn't have the information you
could go through the asset schedule yourself and look at you know when did you purchase the asset
what's the useful life but you need to get this information and add it to the new depreciation to
get your real total depreciation so this total depreciation line is the depreciation schedule
so now we've calculated this by the way you can download this excel template in the description
below for free and use it for yourself and if you like this content please like this video and
subscribe to my channel right now it would really help me out and i'll keep making content like this
okay so now you have total depreciation but what do you do with it well you need to link
it back into the three financial statements and i'm going to show you how to do that
right here so you have the balance sheet the income statement and the cash flow statement
so let's say on the balance sheet you have your net fixed asset section and this is
where the depreciation lives and we have 750 000 dollars of assets on the balance sheet
and then we have 425 000 dollars of accumulated depreciation if you add these together you get the
net fixed asset value which is the assets minus their accumulated depreciation so to forecast the
asset value going forward you'll just take the historical assets and you add the capex to them
and so you'll see and i'll hit ctrl c shift right arrow ctrl v this will show that the value of the
assets is increasing over time which makes sense because we're purchasing assets and so the value
of the assets on our balance sheet should be going up but to tie the depreciation back in
you need to take your depreciation which is always shown as a negative number and actually subtract
your monthly depreciation so this will show how you accumulate depreciation over time and how
that offsets the value of your assets and this is really important because this will then show you
your net fixed asset value over time and you can just feed that straight on to the balance sheet
and then you're done ok so the net fixed the net fixed asset value is about 500
000 at the end of the year but what about the income statement the income statement is easy
you just tie the depreciation straight in to the income statement and you link it in
and its usually just linked into the operating expenses but sometimes it's in the cost of sales
if it's related to the direct production of the product but in general you just link it straight
in um it's not a negative number or anything you just put it in as an expense and it flows through
and that helps you to calculate correctly your operating expenses and your net income
so that you're showing the the cost of your asset spread over the useful lifetime of the asset
and on the cash flow statement you link it in identically to the income statement but
the purpose is actually different so this is gonna be in the first section of your cash
flow statement which is the cash flows from operations and the purpose of this section is
to start with net income and to make a bunch of adjustments to net income to try to figure out
what was your actual operating cash flow so the reason you link depreciation in this is because
depreciation is actually a non cash expense its showing up on the income statement as 15 000
but its not like 15 000 dollars of cash went out the door
so you need to add this back to net income to try to figure out what is your actual
cash uh change in terms of the balance sheet so you link it in identically but it represents
something different and it helps you figure out a different metric which is your operating cash flow
so these are the three ways that you link the depreciation
into your financial statements um again if you want to download this file and use it
feel free to download it in the description for free i also linked to my lesson on how to build a
full three statement financial model from scratch i highly recommend that you check that out if you
don't know how to do that also if you found this content valuable please subscribe and click the
little bell icon so you don't miss any of my new videos hit the like button and also leave me a
comment below and finally check out my online courses if you need to improve your excel and
finance skills okay that's all for today thanks for watching and i'll see you in the next video