Question

Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template should be a SL depreciation schedule and the other template should be a DDB depreciation schedule. You should construct the spreadsheets using the formulas and cell referencing so that when the value of input variables are altered the calculations which automatically adjust. The spreadsheet columns should include depreciation expense, accumulated depreciation, and book value end of year (see textbook 575-576 for examples). Please note, you can not depreciate assets below any salvage value, therefore you will need to use IF function (see IF function guidance below). You must construct the worksheet with the appropriate numeric format and professional layout. Round number values to the nearest whole dollar. The fixed variables for the assignment are as follows:

Life of asset = 10 years

Depreciation methods = straight-line or double-declining balance method

Purchase date of asset = January 1, 2018

Answer 1

Given Information,

Life of asset= 10 years

Date of Purchase= January 1, 2018

Cost of Asset= $100,000( Not given in Question hence assumed $100,000)

Salvage Value=$10,000 ( Not given in Question hence assumed $10,000)

Calculation of Depreciation as per Straight Line Method

Calculation of Depreciation rate as per Straight Line Method

Formula:

Rate of Depreciation= Annual Depreciation / Cost of Asset- Salvage Value

=$9,000 / ($100,000-$10,000)

=10%

Depreciation per year = ( Cost of Asset - Salvage Value ) /Life of asset

= ($100,000-$10,000) / 10 years

=$9,000

Depreciation Schedule as per Straight Line Method

Period	Opening Value	Depreciation Expnese	Accumulated Depreciation	Book value
2018	100,000	9000	9000	91,000
2019	91,000	9000	18000	82,000
2020	82,000	9000	27000	73,000
2021	73,000	9000	36000	64,000
2022	64,000	9000	45000	55,000
2023	55,000	9000	54000	46,000
2024	46,000	9000	63000	37,000
2025	37,000	9000	72000	28,000
2026	28,000	9000	81000	19,000
2027	19,000	9000	90000	10,000

Calculation of Depreciation as per Double declining Balance Method

Calculation of Depreciation rate as per Double declining Balance Method

Formula:

Rate of Depreciation= 2 X Rate of Depreciation as per Straight Line Method

= 2 X 10%

=20%

Period	Opening Book Value	Depreciation Expnese	Accumulated Depreciation	Closing Book Value
2018	100,000	20000	20000	80,000
2019	80,000	16000	36000	64,000
2020	64,000	12800	48800	51,200
2021	51,200	10240	59040	40,960
2022	40,960	8192	67232	32,768
2023	32,768	6554	73786	26,214
2024	26,214	5243	79028	20,972
2025	20,972	4194	83223	16,777
2026	16,777	3355	86578	13,422
2027	13,422	2684	89263	10,737

Please feel free to reach or comment back if you need further clarity or somethng is missing.