Question

deprecaion ror partial periods. Save the worbook as ha-OS student name (replacing student name with your name). Print the newly created worksheet Chapter 4 Case Problem 1: KELLY'S BOUTIQUE Kelly's Boutique owned the following fixed assets as of December 31, 2018: Description 102 103 101 Asset # Building Computer System Phone System Asset Date acquired Cost $625,000 $95,000 $25,000 $4,000 25 years $84,000 $5,000 5 years Salvage value Estimated useful life 4 years Excel for Part 1 08 You are to create a fixed asset depreciation summary and individual depreci- ation worksheets for Kelly's Boutique using the straight-line depreciation method based on the information tabulated above. Be sure to pay close attention to the date of purchase for each asset so that your summary sheet is correct The summary sheet need only include depreciation from 2015 through 2018. Individual assets must show depreciation over their entire useful life. Follow the text examples for formatting. Label your worksheets as follows: Summary SL Asset 101 SL, Asset 102 SL, and Asset 103 SL. In the same workbook, create Kelly's Boutique's fixed asset depreciation summary and individual depreciation worksheets using the double declining bal- method and based on the tabulated information. Again, pay to the date of purchase for each asset so that your summary sheet is co Label your worksheets: Summary DDB, Asset 101 DDB, Asset 102 DDB, and Asset 103 DDB In the same workbook, next create Kelly's Boutique's fixed asset deprecia tion summary and individual depreciation worksheets using the sum-of-the close attention rrect. ance method and based on the tabulated information. Pay close atte year's digits to the date of purchase for each asset so that your summary sheet is attention s correct. worksheets: Summary SYD, Asset 101 SYD, Asset 102 SYD. Label your Asset 103 SYD the same workbook, you should now create a chart of Asset 101's deprec ation (over its 25-year estimated useful life) that compares the straight-line, dou nds halance, and sum-of the-year's digits methods of cachat ble declining balance, and ociation. Label this worksheet: Chart. Choose any 2D line chart an layout that you like. Save the workbook as ch4-03 student _name your name). Print all worksheets in Value printed in the lower left footer and the file of calculating view, with your name with name in the lower right footer Excel for Accounting rt 1 18 Fill this new formula down from cell G13 to G16 19 Type -G12 in cell H12, and then type -H12+G13 into cell H13, umns G 20 Fill down the formula in H13 to cell H16 and then format col 4.8. and H as currency. Your screen should look like Figure 1 What SUP, Inc. Depreciation Schedule Depreciation2 le with New Asset on Amounts 3 Date aquired 1001 Display Racks 1/1/2017 . 7Depreciation 25,0000 7 Depreciation method DDB 5 Salvage vaue 9 Estimated useful lifeS 10 Depreciation Accumulated Straight-Line Expense Depreciation $10,000.00 Depreciation Accumslae Test Switch $5,000.00 $10000 $ TeSwitch Expense Deiete 12 10,000.00 $10.000 56,000.00 $16,00. 3,600.00 $19,600.0 2,700.00$22 300.0 2,700.00$25,00 56,000.00 516,000.00 4 15 $21,760.00 $3.-000,00 . $23,05600 $3,240.00 $2,700.00 216000$19 600.00$3 750.00 1,296.00 $1,296 23,056.00$2,700 ow the same steps to modify Asset 1002 for double declin on. Your completed screen for Asset 1002 should l like Figure 4.9. (Observe that a switch was also necessar re 4.9 1 What SUP, Inc. Depreciation Schedale eciation2 e1002A 3 Asset 4 Asset Date acquired 1002 Chain 1/1/2017 Co $ 8,000.00 DDB 7Depreciation method 8 Salvage value 9Estimated useful life 10 Depreciation Accumalated Straight-Line 13 3,200.00 1,920.00 2018 200$3,120.0 $1,540 o Swilich $1.15200 3,120 00$1125.0 691.20 2019 15 16 i4 93 Chapter 4 To create a sum-of-the-year's digits depreciation schedule: 1 Open the straight-line depreciation workbook you created earlier in the chapter named ch4-01_SLN student name.xls. 2 Save this workbook with a new name: ch4-01 SYD student name.xls (replacing student name with your name) 3 Activate the Asset 1001 worksheet. 4 Type SYD in cell B7 5 Change cell B12 to read #8YDSBss,SBS,SBS9,1). Once again, the depreciation function SYD takes arguments located within the paren- theses. The first three arguments are the same as the SLN function: cost, salvage value, and useful life. All three of these arguments have been set for absolute referencing so that later, when you copy them down the worksheet, they will continue to reference the appropriate cells. The fourth argument is the period for which the asset is being depreciated: the first, second, third, etc. 6 Change cell B13 to read -SYDISBS6,SBS8,sBs9,2). Note that the only difference between B13 and B12 is the fourth argument. 7 Continue to place the SYD formula in cells B14, B15, and B16, changing only the last argument. This is best accomplished by filling down the formula and then going back to edit each cell individuall Upon completion, your worksheet should look like Figure 4.10 Figure 4.10 Schedule for Asset 1001 Dale argaired 1/1/2017 466 $15000. 2,000.00 ,333.38 23,338 3 040 9 save your work. Follow th

Answer 1

First Method = Straight Line Method of Depreciation

Depreciation on Asset 101 SL i.e. Building

Calculation of Depreciation = 625000 - 25000/25 = 24000 each year depreciation for 25 Years.

Depreciation on Asset 102 SL i.e. Computer System

Calculation of Depreciation = 95000 - 4000/4 = 22750 each year depreciation for 4 Years.

Depreciation on Asset 103 SL i.e. Phone System

Calculation of Depreciation = 84000 - 5000/5 = 15800 each year depreciation for 5 Years.

Summary SL from 2015 to 2018

Particulars 2015 2016 2017 2018 TOTAL

Depreciation on Building 24000 24000 24000   24000 96000

Depreciation on Computer 22750 22750 45500

Depreciation on Phone 15800 15800 31600

Second Method = Sum of Years Digit Method

Asset 101 SYD i.e. Building

Date of purchase = 1/1/15

Years = 25 years

Cost = 625000, Salvage = 25000

Calculation of Depreciation =>

Years Total = 25+24+23+22+21+20+19+18+17+16+15+14+13+12+11+10+09+08+07+06+05+04+03+02+01 = 325

Calculation formula = Original Cost - Scrap Value *No. of years remaining/Total of years

2015 depreciation = 625000-25000 * 25/325 = 46154 amt.

Depreciation Sheet =

Years Depreciation Amt. Years Depreciation Years Depreciation   

2015 46154 2023 31385 2031 16615

2016 44308 2024 29538 2032 14769

2017 44231 2025 27692 2033 12923

2018 40615 2026 25846 2034 11077

2019 38769 2027 24000 2035 9230

2020 36923 2028 22153 2036 7385

2021 35077 2029 20308 2037 5538

2022 33231 2030 18462 2038 3692

2039 1846

Asset 102 SYD = Computer System

Date of purchase = 1/1/2017

Sum of Years = 4+3+2+1 = 10     

Years Depreciation Amt.

2017 36400

2018 27300

2019 18200

2020 9100

Asset 103 SYD = Phone System

Date of purchase = 1/1/2017

Sum of Years = 5+4+3+2+1 = 15

Years Depreciation Amt.

2017 26333

2018 21067

2019 15800

2020 10533

2021 5267

Summary SYD from 2015 to 2018

Particulars 2015 2016 2017 2018 TOTAL

Depreciation on Building 46154 44308 44231 40615 175308

Depreciation on Computer 36400 27300 63700

Depreciation on Phone 26333 21067 47400

  

Third Method = Double Declining Balance Method

Asset 101 DDB - Building

Depreciation rate = 100%/25 = 4*2 = 8%

Depreciation amt. for 2015 year = 625000-25000= 600000 * 8% = 48000

Depreciation amt. for 2016 year = 600000-48000 = 552000*8%= 44160

Depreciation amt for 2017 year = 600000 - 48000-44160 = 507840 *8% = 40627

and the above calculation goes on for remainig years

Asset 102 DDB - Computer System

Depreciation rate = 100%/4 = 25*2 = 50%

Depreciation amt. for 2017 year = 95000-4000= 91000 * 50% = 45500   Depreciation amt. for 2018 year = 91000-45500= 45500 * 50% = 22750  

Depreciation amt. for 2019 year = 91000-45500-22750 = 22750 * 50% = 11375 Depreciation amt. for 2020 year = 91000-45500-22750-11375= 11375 * 50% = 5688  

  Asset 103 DDB - Phone System

  Depreciation rate = 100%/5 = 20*2 = 40%

Depreciation amt. for 2017 year = 84000-5000= 79000 * 40% = 31600

Depreciation amt. for 2018 year = 79000-31600= 47400 * 40% = 18960

Depreciation amt. for 2019 year = 79000-31600-18960= 28440 * 40% = 11376

Depreciation amt. for 2020 year = 79000-31600-18960-11376= 17064 * 40% = 6826

Depreciation amt. for 2021 year = 79000-31600-18960-11376-6826= 10238 * 40% = 4095

Summary DDB from 2015 to 2018

Particulars 2015 2016 2017 2018 TOTAL

Depreciation on Building 48000 44160 40627 37377 170164

Depreciation on Computer 45500 22750 68250

Depreciation on Phone 31600 18960 50560