How to use the model with Example

Master Depreciation Accounting with Advanced Excel Model Master Depreciation Model (how to use)
10 minutes
Share the link to this page
You need to have access to the item to view this lesson.
One-time Fee
List Price:  $99.99
You save:  $30
List Price:  €92.16
You save:  €27.65
List Price:  £78.95
You save:  £23.68
List Price:  CA$136.82
You save:  CA$41.05
List Price:  A$150.88
You save:  A$45.26
List Price:  S$134.99
You save:  S$40.50
List Price:  HK$780.84
You save:  HK$234.27
CHF 64.51
List Price:  CHF 92.16
You save:  CHF 27.65
NOK kr745.92
List Price:  NOK kr1,065.65
You save:  NOK kr319.72
DKK kr481.76
List Price:  DKK kr688.26
You save:  DKK kr206.50
List Price:  NZ$163.32
You save:  NZ$49
List Price:  د.إ367.26
You save:  د.إ110.19
List Price:  ৳11,714.86
You save:  ৳3,514.80
List Price:  ₹8,305.04
You save:  ₹2,491.76
List Price:  RM470.40
You save:  RM141.13
List Price:  ₦146,115.27
You save:  ₦43,838.96
List Price:  ₨27,783.57
You save:  ₨8,335.90
List Price:  ฿3,658.80
You save:  ฿1,097.75
List Price:  ₺3,219.48
You save:  ₺965.94
List Price:  B$516.66
You save:  B$155.01
List Price:  R1,841.76
You save:  R552.58
List Price:  Лв180.52
You save:  Лв54.16
List Price:  ₩136,656.33
You save:  ₩41,001
List Price:  ₪366.73
You save:  ₪110.03
List Price:  ₱5,819.86
You save:  ₱1,746.13
List Price:  ¥15,692.92
You save:  ¥4,708.34
List Price:  MX$1,669.81
You save:  MX$500.99
List Price:  QR364.18
You save:  QR109.26
List Price:  P1,356.93
You save:  P407.12
List Price:  KSh13,033.20
You save:  KSh3,910.35
List Price:  E£4,712.01
You save:  E£1,413.74
List Price:  ብር5,738.47
You save:  ብር1,721.71
List Price:  Kz84,827.41
You save:  Kz25,450.77
List Price:  CLP$90,869.43
You save:  CLP$27,263.55
List Price:  CN¥710.97
You save:  CN¥213.31
List Price:  RD$5,881.38
You save:  RD$1,764.59
List Price:  DA13,438.44
You save:  DA4,031.93
List Price:  FJ$226.74
You save:  FJ$68.02
List Price:  Q775.80
You save:  Q232.76
List Price:  GY$20,895.31
You save:  GY$6,269.22
ISK kr9,653.57
List Price:  ISK kr13,791.40
You save:  ISK kr4,137.83
List Price:  DH996.21
You save:  DH298.89
List Price:  L1,771.78
You save:  L531.58
List Price:  ден5,676.40
You save:  ден1,703.09
List Price:  MOP$803.71
You save:  MOP$241.13
List Price:  N$1,835.52
You save:  N$550.71
List Price:  C$3,675.86
You save:  C$1,102.87
List Price:  रु13,281.76
You save:  रु3,984.92
List Price:  S/373.37
You save:  S/112.02
List Price:  K388.13
You save:  K116.45
List Price:  SAR375.02
You save:  SAR112.51
List Price:  ZK2,665.60
You save:  ZK799.76
List Price:  L458.57
You save:  L137.58
List Price:  Kč2,271.01
You save:  Kč681.37
List Price:  Ft35,372.15
You save:  Ft10,612.70
SEK kr760.40
List Price:  SEK kr1,086.34
You save:  SEK kr325.93
List Price:  ARS$88,911.95
You save:  ARS$26,676.25
List Price:  Bs690.12
You save:  Bs207.05
List Price:  COP$386,435.76
You save:  COP$115,942.32
List Price:  ₡51,204.17
You save:  ₡15,362.78
List Price:  L2,468.08
You save:  L740.49
List Price:  ₲751,203.84
You save:  ₲225,383.69
List Price:  $U3,849.38
You save:  $U1,154.93
List Price:  zł392.63
You save:  zł117.80
Already have an account? Log In


Welcome back. In this section we'll be using this template to calculate depreciation in various scenarios purchase sale, what if we sell the asset which are purchased in the year? What if we sell asset before its useful life what will be the profit and loss and so on. So I'll be putting some dummy asset details so we have total seven assets I'll be importing useful life the assets I will put some random useful life of the assets. As you can see, this is data Metro depreciation. So depreciation percentage is calculated automatically, which is nothing but one divided by useful hope the asset so in case of first asset it is one divided by five second I said one you are at by six third I said one divided by three, focus at one divided by eight, and so on.

As input some purchase dates I'll be including some previous purchase date as well as current year purchase data. Last input Purchase costs of these assets. As you can see, delete depreciation is automatically calculated, which is nothing but purchase cost divided by this period that is ended minus purchases. As you can see in column y which is sale date rate is copying by default from End Date column which is column F, it is important to have this model to work properly. Let's go through other columns as well. For timing we can assume that we're not going to sell any asset in the year.

As you can see in column l two oh cross costs are automatically populated asset one to 4pm or 2pm for our purchase before 2016. So therefore, they are opening balance in column l as you can see, while asset five to seven are purchased in 2016, so they will be showing as addition in the year in 2016. closing balance is nothing but some of opening balance addition minus donation scissor assume there is no religion For time being there would be nothing in Division column. Let's move forward. As you can see, depreciation is automatically calculated here, all the asset which are purchased before 2016. They have a full year impact in 2016. But for asset which are purchased in 2016 has impacted on 16 as well.

So, for example, this asset PNM five has been purchased in January. So, impact of 19 discard appreciation you call them all 15 which is $65. It's exactly 19 days of depreciation beyond that depreciation calculate based on the days in the month. Since this is a leap year there will be additional in February So, depreciation would be higher or for others deputies to calculate according to the days 3031 and so on. The asset which has been brought on 30th April, depletion definition for the same starts from first maybe just for the full month. Similarly acid which has been bought on 31st May, that application will start from first June as you can see.

Since this is the first year of the model when you start using it, opening balance depreciation is calculated with a formula. As you can see all the four SS PNM 124 which has in purchase before 2016 has opening balance accordingly. additional expenses I think would appreciation for the year which is some of Jan to December. Since we are no disposal or sale in the year, there is no deletion here. closing balance is nothing but summation of opening balance in addition minus division. net book wells are nothing but difference between column or column as well let's try to sell some assets in this year.

We'll be selling one asset which is a Before 2016 and we'll be selling two assets which is according to those 16. So let's take second asset which is PNM two, it has purchase costs of 65,000 to one to its end date is 30 those are 19. Now, this column is taking value from and date, but in case of sale we are to or at this value when tearing sale date manually. So, we will be assuming that we are going to sell this asset on 31st may 2016. As you can see it has been highlighted in red. This will inform the user that there is a sale of asset in the year now wants to add date manually here you are expected to put value here.

If the asset has been sold for nothing you have to put zero you cannot keep this column blank like this. So, let's assume we have sell this asset for $25,000. As you can see, the loss has been calculated $6,683 this is calculated comparing salary with a wt As expected, the total cost of this asset has been credited as a deletion in gross cost block. Stefanos Antoine to is been credited 65,200 to hear in closing balances net. Since we sell this asset on 31st may depreciation has been calculated for a full month in 31st day after that, from June onwards there is no depreciation. This is how it's supposed to be.

In column A C to F as you can see, we had opening balance of WWE then we had efficient for five months which is nothing but Jen to me, which is for five to six. Now we have to delete this depreciation as it has been sold. So it will be nothing but some of these 229003 plus 452-630-3529. And that closes the account for this asset as that book value is zero. So what would be the accounting entry for this transaction? First of all, you will be debiting cash balance which $25,000 you'd be debiting your profit and loss account with the last 6683 you'd be debiting your accumulated depreciation with 33529.

And at last you'll be crediting 65212. Unless you let's do another sale transaction where asset has been acquired in the year which is 2016 in sold in the air, what happens then? So this asset PNM five, which is bought on credit January 2016, has been sold on 30th June 2016. You're going to sell this asset for five thousand dollars as you can see, it has a loss of $7,000 similarly, in column l two oh asset was bought in this year, so, it has addition but also sold in this year so, it has deletion. So, this column is already calculated is adjustment similarly, depreciation was started from 12th January and since the asset is toll on 30th June that position is automatically calculated. Note that even if I put the date randomly for within the month suppose 15 June 2016 accordingly the depreciation will be calculated for 15 days well there has to clear that should not be any deposition that's why model is not calculating anymore.

As expected, there is no opening balance has this asset was bought in the year. But what about appreciation we charge 534 has been credited because that was only accumulate deficient in the asset account is close to net book value is zero. Accounting entry would be similar as I explained before, let's try to sell an asset with a profit. This asset has very long useful life, but even though we'd be able to sell it profit, let's try to do that. Let's try to sell this asset on 31st July 2016 or $15,000. Now, we have a profit of 4006.

As expected in grace blog asset has been closed with the same cost value. Similar deposition was only accurate for three months so the asset period was with us for three months only. efficient for three months was 250 $8 and it has been closed as it is the only accurate patient we have. Similarly, asset account is closed with the nail book quality net book value column. The accounting entry for this profit on sale of asset would be bank or cash account debited with $15,000. Equity accumulated depreciation account debited with 258.

Profit to be credited with 4006 and asset cost account cross value will be credited 11 to 15. As you can see with this model, it's very easy to determine profit or loss on sale of assets with some few entries. In this model, you can determine accurate profit or loss and also adjustment entries. In the next section I will walk you through how to add one more year in this block what happens if we add next year and how this model will behave in next year. We'll keep this same examples in displace and we'll work accordingly.

Sign Up


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.