Balance Sheet Using Excel

Basic Instructions on How to Create a Declining-balance Depreciation Schedule

Love Thy Pet Inc., Depreciation Schedule - Declining-balance For 5 Year Asset 20,000 5,000 5 Years D F C H Book value at Annual G Accumulated beginning of Depreciation Book value at end E depreciation year (Cost (Book value at Accumulated of year (Cost at beginning Accumulated Depreciation beginning of depreciation at Accumulated of year Depreciation) rate year X Rate) end of year depreciation) 0 $20,000.00 40% $8,000 $8,000 $12,000 8,000 $12,000.00 40% $4,800 $12,800 $7,200 12,800 $7,200.00 40% $2,200 $15,000 $5,000 15,000 $5,000.00 40% $0 $15,000 $5,000 15,000 $5,000.00 40% $0 $15,000 $5,000

1 2 3 4 Cost of Asset 5 Residual Value 6 Projected units per useful life

7 8 9 10 11 12

A End of year 1 2 3 4 5

B Cost of Asset $20,000 $20,000 $20,000 $20,000 $20,000

Rate is 100%/ life of asset X 2 = 40%

1

2

Step-by-Step Instructions Always start with the name of the company, the title of the statement, and the period for which the statement is being prepared. Step 1. On line 4 place the title “Cost of Asset” and the amount of the asset when purchased. Step 2. On line 5 place the title “Residual Value” and the amount of the residual value or the value of the asset once it has ended it useful life to the company. Step 3. On line 6 place the title “Useful life” and the projected number of years the asset will be useful to the company. Step 4. On line 7 place the titles:

A End of year

B Cost of Asset

C Accumulated depreciation at beginning of year

D Book value at beginning of year (Cost Accumulated Depreciation)

E Depreciation rate

F Annual Depreciation (Book value at beginning of year X Rate)

G Accumulated depreciation at end of year

H Book value at end of year (Cost Accumulated depreciation)

Step 5. On line 8 column A through line 12 column A place the years the asset will be used or number the lines for the beginning to the end of the assets number of years of useful life. Step 6. On line 8 column B through line 12 column B place the cost of the asset.

Step 7. On line 8 column C through line 12 column C place the beginning of the year accumulated depreciation. The beginning of the first year is always 0 and the next year comes from the accumulated depreciation at the end of the prior year. Step 8. On line 8 column D through line 12 column D place the beginning of the year book value.

3

The formula is: Column B minus column C equals column D. Step 9. On line 8 column F through line 12 column F place the annual depreciation. Use the following formulas to calculate the amounts: D8 times E8 equals F8 D9 times E9 equals F9 F10 is equal to H9 minus line 5 (because the depreciable cost can not go above 15,000 and the Residual value can not go below 5,000.) F11 and 12 are 0 because all of the depreciation was taken in the first 3 years of the asset. Step 10. On line 8 column F through line 12 column F place the accumulated depreciation. Use the following formulas to calculate the amounts: G8 is equal to F8. G9 is equal to F9 plus G8. G10 is equal to F10 plus G9. G11 is equal to F 11 plus G10. G12 is equal to F12 plus G11. Step 11. On line 8 column H through line 12 column H place the Book value of the asset. Use the following formulas to calculate the amounts: H8 is equal to the cost of the asset (line 4) minus the Accumulated depreciation on line G8. H9 is equal to the cost of the asset (line 4) minus the Accumulated depreciation on line G9. H10 is equal to the cost of the asset (line 4) minus the Accumulated depreciation on line G10. H11 is equal to the cost of the asset (line 4) minus the Accumulated depreciation on line G11. H12 is equal to the cost of the asset (line 4) minus the Accumulated depreciation on line G12. Note: The number on line 12 column H should be the same as the residual value (line 5). The number on line 12 column G should be the same as the cost of the asset minus the residual value (depreciable cost).

4