Deep Dive into Depreciation Model

Master Depreciation Accounting with Advanced Excel Model Master Depreciation Model (how to use)
7 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$69.99
List Price:  $99.99
You save:  $30
€64.33
List Price:  €91.91
You save:  €27.57
£54.76
List Price:  £78.24
You save:  £23.47
CA$95.38
List Price:  CA$136.26
You save:  CA$40.88
A$105.05
List Price:  A$150.08
You save:  A$45.03
S$94.33
List Price:  S$134.76
You save:  S$40.43
HK$546.67
List Price:  HK$781
You save:  HK$234.32
CHF 63.74
List Price:  CHF 91.06
You save:  CHF 27.32
NOK kr733.71
List Price:  NOK kr1,048.21
You save:  NOK kr314.49
DKK kr480.07
List Price:  DKK kr685.84
You save:  DKK kr205.77
NZ$113.63
List Price:  NZ$162.33
You save:  NZ$48.70
د.إ257.07
List Price:  د.إ367.26
You save:  د.إ110.18
৳8,211.53
List Price:  ৳11,731.26
You save:  ৳3,519.73
₹5,822.15
List Price:  ₹8,317.71
You save:  ₹2,495.56
RM328.51
List Price:  RM469.33
You save:  RM140.81
₦103,757.37
List Price:  ₦148,231.17
You save:  ₦44,473.80
₨19,474.72
List Price:  ₨27,822.22
You save:  ₨8,347.50
฿2,564.11
List Price:  ฿3,663.18
You save:  ฿1,099.06
₺2,252.35
List Price:  ₺3,217.78
You save:  ₺965.43
B$362.10
List Price:  B$517.30
You save:  B$155.20
R1,286.74
List Price:  R1,838.27
You save:  R551.53
Лв125.83
List Price:  Лв179.77
You save:  Лв53.93
₩95,104.15
List Price:  ₩135,868.90
You save:  ₩40,764.74
₪257.31
List Price:  ₪367.61
You save:  ₪110.29
₱4,058.68
List Price:  ₱5,798.37
You save:  ₱1,739.68
¥10,979.06
List Price:  ¥15,685.04
You save:  ¥4,705.98
MX$1,167.55
List Price:  MX$1,668
You save:  MX$500.45
QR255.10
List Price:  QR364.44
You save:  QR109.34
P950.14
List Price:  P1,357.41
You save:  P407.26
KSh9,273.67
List Price:  KSh13,248.67
You save:  KSh3,975
E£3,325.22
List Price:  E£4,750.52
You save:  E£1,425.30
ብር3,990.85
List Price:  ብር5,701.46
You save:  ብር1,710.61
Kz59,620.28
List Price:  Kz85,175.48
You save:  Kz25,555.20
CLP$63,105.46
List Price:  CLP$90,154.52
You save:  CLP$27,049.06
CN¥497.57
List Price:  CN¥710.85
You save:  CN¥213.27
RD$4,120.99
List Price:  RD$5,887.39
You save:  RD$1,766.39
DA9,415.45
List Price:  DA13,451.22
You save:  DA4,035.77
FJ$155.95
List Price:  FJ$222.79
You save:  FJ$66.84
Q543.57
List Price:  Q776.56
You save:  Q232.99
GY$14,648.31
List Price:  GY$20,927.06
You save:  GY$6,278.74
ISK kr9,619.42
List Price:  ISK kr13,742.62
You save:  ISK kr4,123.20
DH694.87
List Price:  DH992.71
You save:  DH297.84
L1,239.53
List Price:  L1,770.84
You save:  L531.30
ден3,964.07
List Price:  ден5,663.20
You save:  ден1,699.13
MOP$562.84
List Price:  MOP$804.09
You save:  MOP$241.25
N$1,285.31
List Price:  N$1,836.24
You save:  N$550.92
C$2,575.71
List Price:  C$3,679.74
You save:  C$1,104.03
रु9,302.62
List Price:  रु13,290.03
You save:  रु3,987.40
S/261.94
List Price:  S/374.22
You save:  S/112.27
K272.11
List Price:  K388.75
You save:  K116.63
SAR262.50
List Price:  SAR375.02
You save:  SAR112.51
ZK1,873.65
List Price:  ZK2,676.76
You save:  ZK803.10
L320.13
List Price:  L457.35
You save:  L137.22
Kč1,586.33
List Price:  Kč2,266.28
You save:  Kč679.95
Ft24,682.62
List Price:  Ft35,262.40
You save:  Ft10,579.78
SEK kr737.43
List Price:  SEK kr1,053.51
You save:  SEK kr316.08
ARS$62,536.06
List Price:  ARS$89,341.06
You save:  ARS$26,805
Bs483.53
List Price:  Bs690.78
You save:  Bs207.25
COP$270,803.02
List Price:  COP$386,878.04
You save:  COP$116,075.02
₡35,985.46
List Price:  ₡51,410
You save:  ₡15,424.54
L1,729.24
List Price:  L2,470.46
You save:  L741.21
₲526,430.33
List Price:  ₲752,075.57
You save:  ₲225,645.23
$U2,694.45
List Price:  $U3,849.38
You save:  $U1,154.93
zł273.60
List Price:  zł390.87
You save:  zł117.27
Already have an account? Log In

Transcript

Welcome back guys in this section we'll be deep diving into depreciation model I will explain you each and every section each and every column we can what each column does columns with light yellow color our data input columns, there are four data input columns in the first section which is asset data column as you can see on the screen and one more light yellow column is in sale of asset section has trouble the columns are locked to protect calculation security of the entire workbook on production possible product to you. While I would highly recommend to keep model password protected in column one you can put the name of your company organization column A two you can put the name of the sheet as you want currently have name FA and a potential but if you like to name something else you can do that. Since this model will be our base model when you start using it.

So after this year, you will be adding more years which I'll explain you how to do it. Suppose this year is 2016. So you have to add here 2016 manually or when you add another year as a next year, this cell B three will be automatically updated based on the year input. I will exactly explain you how you can do that start here and here is automatically calculated based on the figure importing sell v3 which is obviously here of depreciation generally all efficiency modules are maintained in blocks which are for the each fixed asset in class this model also does say this block is for plant and machinery first section in the plant and machinery block is for fixed asset details, this section is most important details key details for the purpose of all calculation in the entire model column is for serial number which will give automatic serial numbers as you add more assets I will discuss with you how to add more rows in each block in later sections.

I'll be assuming you will be adding each asset one by one even if your two units of same asset The reason for doing Same is true accurate profit or loss in case of sale or disposal of fixed assets column B asset Name column is for description of the asset. I have currently mentioned dummy names has parted mentioned the 123 till seven column C's for depreciation rate values in this column are auto generating the formula in the column is one divided by useful life of the asset which is the very next column column the useful life is a column of data entry column as it is highlighted in yellow. of Finland method of repetition you must estimate useful life of the asset which is again depends on the nature of the asset and kind of business your operating column the purchase date is again data entry column as it is highlighted in yellow you have to input purchase date or date when the asset is put to use in your business.

This date is important as the efficient calculation is based on this data only column F is ended column values in these columns are auto generated it is date when assets are expected to end its utility from the operation. It is calculated based on values you have entered in purchase column date, and useful if you entered in column D column g purchase cost column this column is data entry column it is highlighted in yellow Vario to include actual costs of the asset. This will also include all direct costs which are incurred to bring the asset with operating condition column matches daily efficient and the most important column in entire model the reason why daily appreciation is calculated to have accurate depreciation expenditure calculation where assets are purchased within the month on any order event. For example, the very first asset in the example here in row 11 Have a nice piano one it is purchased on August 2013.

So, in order to calculate 19 days depreciation which is remaining days in month of August in a depression value is used to work out the same amount similarly if the asset is retiring during the month on any order even daily recreation helping the model to determine exit depreciation expense for the period we will see this columns utility and purpose in action in later section of this course. next section from columns I took a is for sale of asset sale date is colored in bright yellow and also conditionally formatted by default sale is equal to end date which is column F. This is important to have accurate profit or loss calculation on sale of asset you must totally override this default value, which is the end date when you have either sold or disposed of the asset with actual date of sale or disposal. Once you're at these values in this column, it will be highlighted in bright red as reminder that sale of asset has taken place for this particular asset in the period, column j sale value of the asset when you sell or dispose of an asset you must have realized some value due for the same same failure to input here remember this is data input column eight it is highlighted in bright yellow if sale or disposal of the asset has taken place you must enter its value here even if your disposal of asset for nil value you must enter zero value here for the purpose of accurate calculation of profit or loss on sale of fixed asset in case there is no sale this column value must be nothing that means, you have to select this column and press Delete key This is an important step in calculating profit or loss on sale of fixed asset column K is for the actual profit or loss on sale of asset This is automatically calculated based on input in column i and j column l two o represents cross block of the asset these values are actual purchase cost of the asset as you're provided in column g opening balances carried forward of the actual cost of the asset addition in your column is for any addition which you have in there.

Similarly deletion in your column is for any sale or dispose of the asset in the closing balance represents and value of the assets which is after netting of purchase or sale in here. Once again these values are actual gross cost of the Assets another most important section in the model is the patient calculation section which spreads across column q to AB. As I mentioned before, I've assumed that financial year starts with Jan and ends with December. You can always change these values to any fiscal year for example Apple to march or July to June calculation is performed by using very complex nested function. It also uses date function also division monthly is calculated accurately thanks to delete a patient's column as I explained earlier, if you have asset additional relation on odd days during the month, the appreciation is worked out accordingly for the precise days of use, we will see this columns in action later section of the video columns AC to a represents accumulated depreciation of the section.

If you are using this asset model for the first time you will be inputting your existing asset details in this section. In that case, column AC will calculate wt automatically that is for the first time only when you start using this model. Column eight is nothing but total life For the period, so, in this case it is summation of Jan to December in deposition section which is column q to a b column A is deletion of akula deposition in case of zero disposal of asset column EF is closing balance or accumulate depreciation, which I suppose is self explanatory at this stage, last column is net book value column, which is also called web column or written down value column at end of the period. This is derived by subtracting values in column F, which is closing balance of the accurate depreciation from column o which is closing balance of gross book values of assets.

Now, since you understood how each and every column works and what are each and every section supposed to do in this calculation model, we will going through practical example for different different asset classes Well, I will also explain you how to add next year tab. How to add additional rows How to delete rows. This model uses Excel tables for each blog as you can see table has a structured references which are most useful in accounting calculation. Thank you for watching. I will see you the next section of the course.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.