馃攳
Depreciation Schedule | How to Calculate & Link into 3 Financial Statements - YouTube
Channel: Eric Andrews
[0]
hey everyone eric here in this video we're聽
gonna learn how to forecast a depreciation聽聽
[5]
schedule in excel using our capex plan then聽
i'll show you how to link depreciation back聽聽
[11]
into the balance sheet as accumulated聽
depreciation, the income statement as an expense,聽聽
[16]
and the cash flow statement as an adjustment to聽
net income in the operation cash flow section聽聽
[21]
okay let's get started so the first thing we need聽
to calculate a depreciation schedule is always the聽聽
[27]
capex plan which is the plan that shows any asset聽
purchases that we have coming over the next year聽聽
[33]
or whatever our budgeting period is so let's聽
say that we've been working with our teams and聽聽
[37]
we know that we need to buy a piece of machinery聽
that's worth 250 000 dollars in august of 2024聽聽
[45]
so we would put that in there and remember any聽
hardcoded inputs in excel when you're building聽聽
[51]
a financial model hard coded means just typed in聽
no formula put it in dark blue its a rule of thumb聽聽
[57]
for financial formatting and so make sure that you聽
do that so that other people can read your models聽聽
[63]
so 250 000 dollars and we've been working with聽
our engineering team and they need a piece of聽聽
[69]
software that's gonna cost 160 000 dollars聽
so over the next 12 months we're gonna spend聽聽
[76]
410 000 dollars on asset purchases so lets now聽
calculate the depreciation associated with these聽聽
[84]
asset purchases so let's say the machinery has聽
a useful life of 12 months and the software聽聽
[89]
has a useful life of 36 months here's a really聽
easy way to calculate the depreciation schedule聽聽
[96]
so i would just take the 250 000 dollars聽
tap f4 which locks the row and the column聽聽
[103]
and divide it by the useful life and then i would聽
tap f4 once again lock the row and the column and聽聽
[109]
hit enter then ctrl c shift right arrow control v聽
that's just copy paste so you can see that for the聽聽
[118]
last 5 months of the year we'll be depreciating聽
this asset and if we were gonna make uh extend聽聽
[123]
this forecast into the next year we would show the聽
full 12 months of the depreciation of the asset
[130]
software is exactly the same hit equals grab the聽
capex total lock the row and the column and divide聽聽
[138]
it by 36 and tap f4 again so we would again extend聽
this the full 36 month lifetime of the asset聽聽
[148]
were we to build a 3 or 4 year forecast right聽
now but for these next 12 months this is what聽聽
[154]
we can expect from these assets and so this shows聽
you your total new depreciation but don't forget聽聽
[159]
you will always have historical depreciation聽
that's rolling forward from past asset purchases聽聽
[166]
so the easiest way to get this is to ask your聽
accounting team they will know that you bought聽聽
[171]
say a piece of software 2 years ago and the聽
depreciation of that software might be 3 years聽聽
[176]
they'll have the exact dates and they'll have the聽
exact amount of depreciation that they're planning聽聽
[180]
to book so you can get this straight from them in聽
the case that they didn't have the information you聽聽
[187]
could go through the asset schedule yourself and聽
look at you know when did you purchase the asset聽聽
[191]
what's the useful life but you need to get this聽
information and add it to the new depreciation to聽聽
[196]
get your real total depreciation so this total聽
depreciation line is the depreciation schedule聽聽
[202]
so now we've calculated this by the way you can聽
download this excel template in the description聽聽
[207]
below for free and use it for yourself and if聽
you like this content please like this video and聽聽
[212]
subscribe to my channel right now it would really聽
help me out and i'll keep making content like this聽聽
[216]
okay so now you have total depreciation but聽
what do you do with it well you need to link聽聽
[221]
it back into the three financial statements聽
and i'm going to show you how to do that聽聽
[225]
right here so you have the balance sheet the聽
income statement and the cash flow statement聽聽
[230]
so let's say on the balance sheet you have聽
your net fixed asset section and this is聽聽
[234]
where the depreciation lives and we have 750聽
000 dollars of assets on the balance sheet聽聽
[240]
and then we have 425 000 dollars of accumulated聽
depreciation if you add these together you get the聽聽
[247]
net fixed asset value which is the assets minus聽
their accumulated depreciation so to forecast the聽聽
[256]
asset value going forward you'll just take the聽
historical assets and you add the capex to them聽聽
[262]
and so you'll see and i'll hit ctrl c shift right聽
arrow ctrl v this will show that the value of the聽聽
[272]
assets is increasing over time which makes sense聽
because we're purchasing assets and so the value聽聽
[278]
of the assets on our balance sheet should be聽
going up but to tie the depreciation back in聽聽
[283]
you need to take your depreciation which is always聽
shown as a negative number and actually subtract聽聽
[288]
your monthly depreciation so this will show how聽
you accumulate depreciation over time and how聽聽
[295]
that offsets the value of your assets and this is聽
really important because this will then show you聽聽
[303]
your net fixed asset value over time and you can聽
just feed that straight on to the balance sheet聽聽
[308]
and then you're done ok so the net fixed聽
the net fixed asset value is about 500聽聽
[314]
000 at the end of the year but what about the聽
income statement the income statement is easy聽聽
[320]
you just tie the depreciation straight in聽
to the income statement and you link it in聽聽
[324]
and its usually just linked into the operating聽
expenses but sometimes it's in the cost of sales聽聽
[329]
if it's related to the direct production of the聽
product but in general you just link it straight聽聽
[335]
in um it's not a negative number or anything you聽
just put it in as an expense and it flows through聽聽
[341]
and that helps you to calculate correctly聽
your operating expenses and your net income聽聽
[346]
so that you're showing the the cost of your asset聽
spread over the useful lifetime of the asset
[354]
and on the cash flow statement you link it聽
in identically to the income statement but聽聽
[358]
the purpose is actually different so this is聽
gonna be in the first section of your cash聽聽
[363]
flow statement which is the cash flows from聽
operations and the purpose of this section is聽聽
[367]
to start with net income and to make a bunch of聽
adjustments to net income to try to figure out聽聽
[372]
what was your actual operating cash flow so the聽
reason you link depreciation in this is because聽聽
[377]
depreciation is actually a non cash expense its聽
showing up on the income statement as 15 000聽聽
[383]
but its not like 15 000 dollars聽
of cash went out the door聽聽
[386]
so you need to add this back to net income聽
to try to figure out what is your actual聽聽
[390]
cash uh change in terms of the balance sheet聽
so you link it in identically but it represents聽聽
[396]
something different and it helps you figure out a聽
different metric which is your operating cash flow
[402]
so these are the three ways聽
that you link the depreciation聽聽
[405]
into your financial statements um again if聽
you want to download this file and use it聽聽
[410]
feel free to download it in the description for聽
free i also linked to my lesson on how to build a聽聽
[416]
full three statement financial model from scratch聽
i highly recommend that you check that out if you聽聽
[421]
don't know how to do that also if you found this聽
content valuable please subscribe and click the聽聽
[425]
little bell icon so you don't miss any of my new聽
videos hit the like button and also leave me a聽聽
[430]
comment below and finally check out my online聽
courses if you need to improve your excel and聽聽
[435]
finance skills okay that's all for today thanks聽
for watching and i'll see you in the next video
Most Recent Videos:
You can go back to the homepage right here: Homepage