I did the first 3 methods (not sure if correctly). Can you help with the last two things and tell me if I did the first 3 right? THANK YOU!

2 Excel Project #3 3 Due Date: Submit in Canvas: July 8, 2021 11:59pm 4 5 You are working in the accounting department of a manufacturing firm that has purchased a new high-tech piece of machinery. 6 The purchase price of the machinery (not including taxes) was $175,000, sales tax was $15,750, and installation and setup costs totalled $2,250. 7 The CFO of the company would like to understand how various methods of depreciating the asset would affect the company's income in the current and future years. 8 9 10 Additional Information: 11 The company's fiscal year = calendar year. 12 The machine was purchased on January 1, 2021. 13 Due to its modular design, the machine can be easily repaired and have its components upgraded/replaced as necessary. Thus, management expects its useful life to be 27 years. 14 Management expects to sell the machine after 27 years for $17,500. 15 The depreciation methods that are being considered are straight line, double declining balance (*with a switch to straight-line when straight-line would result in higher depreciation*), sum-of-the-years-digits, and units of production. 16 The machine is expected to produce 15,000 units of output in each of the first 18 years it is in service, and 7,500 units of output in each of the last 9 years of service. 17 18 Required: 19 1 On each of the respective tabs, prepare the machinery depreciation schedule for the respective depreciation method. 20 At a minimum, each schedule should have columns for year, depreciation expense, accumulated depreciation, and net book value. 21 These columns would show each of these amounts for each respect|ve year (depreciation expense for the year, and A/D and net book value at the end of a given year). 22 23 2 On the "Summary and Graph" tab, prepare a summary schedule that has a "Year" column, and a column for each depreciation method showing the depreciation in the given year under the respective method of depreciation. 24 25 3 On the "Summary and Graph" tab, prepare a line graph that visually presents the depreciation amounts under each method. Include years on the x-axis, $/depreciation amounts on the y-axis, and a line for each method. 26 27 4 You will need to hard-code information related to the asset on each tab so that you can reference this information in your tables. Do this on each tab to the side or above the table, not in the table itsself. 28 Do not hard-code amounts in any tables - use formulas and cell referencing. Full credit will not be awarded if amounts are hard-coded in tables. 29 initial cost- useful life sell after 27- produce units produce units 9 1 purchase sales tax install set costs (years) salvage val in 18 yrs years 2 175000 15750 2250 27 17500 15000 7500 3 4 book val sell after 27-salvage accum. Deprc. useful life (years straight line de annual depr ex 5 175000 17500 157500 27 3.70% 5833.333333 6 depreciation accumulated 7 Year Book val (beg) Deprec. Rate expense depr. book val end 8 1 $ 175,000.00 3.709 $ 5,833.33 $ 5,833.33 169,166.67 9 2 $ 169,166.67 3.70% $ 5,833.33 11,666.67$ 163,333.33 10 3 $ 163,333.33 3.70% $ 5,833.33 $ 17,500.00 $ 157,500.00 11 4 $ 157,500.00 3.70% $ 5,833.33 $ 23,333.33 $ 151,666.67 12 5 $ 151,666.67 3.709 $ 5,833.33 $ 29,166.67$ 145,833.33 13 6 $ 145,833.33 3.70% $ 5,833.33 $ 35,000.00 $ 140,000.00 14 7 $ 140,000.00 3.70% $ 5,833.33 $ 40,833.33 $ 134,166.67 15 8 $ 134,166.67 3.7096 $ 5,833.33 $ 46,666.67$ 128,333.33 16 9 $ 128,333.33 3.70% $ 5,833.33 $ 52,500.00 $ 122,500.00 17 10 $ 122,500.00 3.70% $ 5,833.33 $ 58,333.33 $ 116,666.67 18 11 S 116,666.67 3.70% $ 5,833.33 $ 64,166.67$ 110,833.33 19 12$ 110,833.33 3.70% $ 5,833.33 $ 70,000.00 $ 105,000.00 20 131 $ 105,000.00 3.70%$ 5,833.33S 75,833.33 $ 99,166.67 21 14 S 99,166.67 3.70% $ 5,833.33 $ 81,666.67$ 93,333.33 22 15 $ 93,333.33 3.70% $5,833.33 $ 87,500.00 $ 87,500.00 23 16 S 87,500.00 3.70% $ 5,833.33 $ 93,333.33% 81,666.67 24 171 $ 81,666.67 3.70%) s 5,833.33 $ 99,166.67$ 75,833.33 25 18 $ 75,833.33 3.70% S 5,833.33 $ 105,000.00 $ 70,000.00 26 19 S 70,000.00 3.7096 $ 5,833.33 $ 110,833.33 $ 64,166.67 27 201 $ 64,166.67 3.70% S 5,833.33 $ 116,666.67$ 58,333.33 28 21 $ 58,333.33 3.70% $ 5,833.33 $ 122,500.00 $ 52,500.00 29 22 $ 52,500.00 3.70% $ 5,833.33 $ 128,333.33 $ 46,666.67 30 23 $ 46,666.67 3.70% $ 5,833.33 $ 134,166.67$ 40,833.33 31 241 S 40,833.33 3.70%) $ 5,833.33 $ 140,000.00 $ 35,000.00 32 25 s 35,000.00 3.709 $ 5,833.33 $ 145,833.33 $ 29,166.67 33 26 $ 29,166.67 3.70% $ 5,833.33 $ 151,666.67 23,333.33 34 271 $ 23,333.33 3.70% $ 5,833.33 $ 157,500.00 $ 17,500.00 35 Instructions Straight line DNB with switch to SL Sum-of-the-years' digits Units of production Summary and E50 fic H K M A B C D E F G initial cost- useful life sell after 27- produce units produce units 1 purchase sales tax install set costs (years) salvage val in 18 yrs 9 years 2 175000 15750 2250 27 17500 15000 7500 3 4 book val salvage val accum. Deprc. useful life (years straight line de annual depr ex 5 175000 17500 157500 27 3.70% 5833.333333 6 7 Year book val (beg) deprec. % depreciation e accum depr book val (end) 8 1 s 175,000.00 7.41% $ 12,962.96 $ 12,962.96 $ 162,037.04 9 2 $ 162,037.04 7.41% $ 12,002.74 $ 24,965.71 $ 150,034.29 10 3 S 150,034.29 7.41% $ 11,113.65 $ 36,079.36 $ 138,920.64 11 4 $ 138,920.64 7.41% $ 10,290.42 $ 46,369.78 $ 128,630.22 12 5 $ 128,630.22 7.41% $ 9,528.16 $ 55,897.94 $ 119,102.06 13 61 S 119,102.06 7.41% S 8,822.37$ 64,720.32 $ 110,279.68 14 7 S 110,279.68 7.41% $ 8,168.87 $ 72,889.18 $ 102,110.82 15 8 $ 102,110.82 7.41% $ 7,563.76 $ 80,452.95 $ 94,547.05 16 9 $ 94,547.05 7.41% $ 7,003.49 $ 87,456.43 $ 87,543.57 17 10 $ 87,543.57 7.41% S 6,484.71 $ 93,941.14 $ 81,058.86 18 11 s 81,058.86 7.41% $ 6,004.36 $ 99,945.50 $ 75,054.50 121 $ 75,054.50 7.41% $ 5,559.59 $ 105,505.09$ 69,494.91 20 13 S 69,494.91 7.41% S 5,147.77 $ 110,652.86$ 64,347.14 21 141 $ 64,347.14 7.41% s 4,766.45 $ 115,419.32 $ 59,580.68 22 15$ 59,580.68 7.41% $ 4,413.38 $ 119,832.70 $ 55,167.30 161 $ 55,167.30 7.41% $ 4,086.47 $ 123,919.17$ 51,080.83 171 $ 51,080.83 7.41% 3,783.77 $ 127,702.93 $ 47,297.07 25 18 $ 47,297.07 7.41% $ 3,503.49 $ 131,206.42 S 43,793.58 19 $ 43,793.58 7.41% $ 3,243.97 $ 134,450.39 $ 40,549.61 201 S 40,549.61 7.41%) s 3,003.67$ 137,454.06 $ 37,545.94 28 21 $ 37,545.94 7.41% $ 2,781.18 $ 140,235.24 $ 34,764.76 29 22 $ 34,764.76 7.41% S 2,575.17$ 142,810.41 $ 32,189.59 30 23 $ 32,189.59 7.41% $ 2,384.41 $ 145,194.83 $ 29,805.17 24 $ 29,805.17 7.41% $ 2,207.79 $ 147,402.62 $ 27,597.38 32 250 $ 27,597.38 7.41% $ 2,044.25 $ 149,446.87 S 25,553.13 261 $ 25,553.13 7.41% $ 1,892.82 $ 151,339.69 $ 23,660.31 34 27 S 23,660.31 7.41% $ 1,752.62 $ 153,092.31 $ 21,907.69 35 38 39 Instructions Straight-line DDB with switch to SL Sum-of-the-years' digits Units of production Summary and + Sell D37 fc A B D E F G H J L M N o P Q 1 2 initial cost useful life -purchase sales tax install set costs (years) 175000 15750 2250 sell after 27- produce units produce units salvage val in 18 yrs 9 years 27 17500 15000 7500 4 6 book val sell after 2 accum. Deprc. useful life (years) 175000 17500 157500 27 sum up to 27 378 7 8 Year 9 10 11 12 13 14 15 16 17 18 19 20 21 book val beg tot cost deprc. depr. % 1 $ 175,000 $ 157,500 2 $ 163,750 $ 157,500 3 $ 152,917 $ 157,500 4 $ 142,500 $ 157,500 5 $ 132,500 $ 157,500 6 $ 122,917 $ 157,500 71 $ 113,750 $ 157,500 8 $ 105,000 $ 157,500 9 $ 96,667 $ 157,500 10 $ 88,750 $ 157,500 11 $ 81,250 $ 157,500 12 $ 74,167 $ 157,500 13 $ 67,500 $ 157,500 14 $ 61,250 $ 157,500 150 $ 55,417 $ 157,500 16 $ 50,000 $ 157,500 17 $ 45,000 $ 157,500 18 S 40,417 $ 157,500 19 $ 36,250 $ 157,500 20 S 32,500 $ 157,500 21 $ 29,167 $ 157,500 22 $ 26,250 $ 157,500 23 $ 23,750 $ 157,500 24 $ 21,667 $ 157,500 25 $ 20,000 $ 157,500 26 $ 18,750 $ 157,500 27 $ 17,917$ 157,500 tot % = 22 23 24 25 26 27 28 29 30 31 32 33 34 deprec. Expense accum. Depr. book val end 79 $ 11,250 $ 11,250 $ 163,750 79 $ 10,833$ 22,083 $ 152,917 796 $ 10,417 S 32,500 $ 142,500 69 $ 10,000 $ 42,500 $ 132,500 6% $ 9,583 $ 52,083 $ 122,917 6% $ 9,167 $ 61,250 $ 113,750 696 $ 8,750 $ 70,000 $ 105,000 5% $ 8,333$ 78,333$ 96,667 59 $ 7,917 S 86,250 $ 88,750 596 $ 7,500 $ 93,750 $ 81,250 496 $ 7,083 S 100,833$ 74,167 49 $ 6,667 $ 107,500 $ 67,500 49 $ 6,250 $ 113,750 $ 61,250 496 $ 5,833$ 119,583 $ 55,417 39 $ 5,417 125,000 $ 50,000 39 $ 5,000 $ 130,000 $ 45,000 396 $ 4,583$ 134,583 $ 40,417 396 $ 4,167 $ 138,750 $ 36,250 3,750 S 142,500 $ 32,500 29 $ 3,333 $ 145,833 $ 29,167 29 $ 2,917 S 148,750 $ 26,250 296 $ 2,500 $ 151,250 $ 23,750 1% $ 2,083 S 153,333$ 21,667 19 $ 1,667S 155,000 $ 20,000 196 $ 1,250 $ 156,250 $ 18,750 19 $ 833$ 157,083 $ 17,917 0% $ 417 S 157,500 $ 17,500 1 29 $ 35 36 37 38 Instructions Straight-line DDB with switch to SL Sum-of-the-years' digits Units of production Summary and 2 Excel Project #3 3 Due Date: Submit in Canvas: July 8, 2021 11:59pm 4 5 You are working in the accounting department of a manufacturing firm that has purchased a new high-tech piece of machinery. 6 The purchase price of the machinery (not including taxes) was $175,000, sales tax was $15,750, and installation and setup costs totalled $2,250. 7 The CFO of the company would like to understand how various methods of depreciating the asset would affect the company's income in the current and future years. 8 9 10 Additional Information: 11 The company's fiscal year = calendar year. 12 The machine was purchased on January 1, 2021. 13 Due to its modular design, the machine can be easily repaired and have its components upgraded/replaced as necessary. Thus, management expects its useful life to be 27 years. 14 Management expects to sell the machine after 27 years for $17,500. 15 The depreciation methods that are being considered are straight line, double declining balance (*with a switch to straight-line when straight-line would result in higher depreciation*), sum-of-the-years-digits, and units of production. 16 The machine is expected to produce 15,000 units of output in each of the first 18 years it is in service, and 7,500 units of output in each of the last 9 years of service. 17 18 Required: 19 1 On each of the respective tabs, prepare the machinery depreciation schedule for the respective depreciation method. 20 At a minimum, each schedule should have columns for year, depreciation expense, accumulated depreciation, and net book value. 21 These columns would show each of these amounts for each respect|ve year (depreciation expense for the year, and A/D and net book value at the end of a given year). 22 23 2 On the "Summary and Graph" tab, prepare a summary schedule that has a "Year" column, and a column for each depreciation method showing the depreciation in the given year under the respective method of depreciation. 24 25 3 On the "Summary and Graph" tab, prepare a line graph that visually presents the depreciation amounts under each method. Include years on the x-axis, $/depreciation amounts on the y-axis, and a line for each method. 26 27 4 You will need to hard-code information related to the asset on each tab so that you can reference this information in your tables. Do this on each tab to the side or above the table, not in the table itsself. 28 Do not hard-code amounts in any tables - use formulas and cell referencing. Full credit will not be awarded if amounts are hard-coded in tables. 29 initial cost- useful life sell after 27- produce units produce units 9 1 purchase sales tax install set costs (years) salvage val in 18 yrs years 2 175000 15750 2250 27 17500 15000 7500 3 4 book val sell after 27-salvage accum. Deprc. useful life (years straight line de annual depr ex 5 175000 17500 157500 27 3.70% 5833.333333 6 depreciation accumulated 7 Year Book val (beg) Deprec. Rate expense depr. book val end 8 1 $ 175,000.00 3.709 $ 5,833.33 $ 5,833.33 169,166.67 9 2 $ 169,166.67 3.70% $ 5,833.33 11,666.67$ 163,333.33 10 3 $ 163,333.33 3.70% $ 5,833.33 $ 17,500.00 $ 157,500.00 11 4 $ 157,500.00 3.70% $ 5,833.33 $ 23,333.33 $ 151,666.67 12 5 $ 151,666.67 3.709 $ 5,833.33 $ 29,166.67$ 145,833.33 13 6 $ 145,833.33 3.70% $ 5,833.33 $ 35,000.00 $ 140,000.00 14 7 $ 140,000.00 3.70% $ 5,833.33 $ 40,833.33 $ 134,166.67 15 8 $ 134,166.67 3.7096 $ 5,833.33 $ 46,666.67$ 128,333.33 16 9 $ 128,333.33 3.70% $ 5,833.33 $ 52,500.00 $ 122,500.00 17 10 $ 122,500.00 3.70% $ 5,833.33 $ 58,333.33 $ 116,666.67 18 11 S 116,666.67 3.70% $ 5,833.33 $ 64,166.67$ 110,833.33 19 12$ 110,833.33 3.70% $ 5,833.33 $ 70,000.00 $ 105,000.00 20 131 $ 105,000.00 3.70%$ 5,833.33S 75,833.33 $ 99,166.67 21 14 S 99,166.67 3.70% $ 5,833.33 $ 81,666.67$ 93,333.33 22 15 $ 93,333.33 3.70% $5,833.33 $ 87,500.00 $ 87,500.00 23 16 S 87,500.00 3.70% $ 5,833.33 $ 93,333.33% 81,666.67 24 171 $ 81,666.67 3.70%) s 5,833.33 $ 99,166.67$ 75,833.33 25 18 $ 75,833.33 3.70% S 5,833.33 $ 105,000.00 $ 70,000.00 26 19 S 70,000.00 3.7096 $ 5,833.33 $ 110,833.33 $ 64,166.67 27 201 $ 64,166.67 3.70% S 5,833.33 $ 116,666.67$ 58,333.33 28 21 $ 58,333.33 3.70% $ 5,833.33 $ 122,500.00 $ 52,500.00 29 22 $ 52,500.00 3.70% $ 5,833.33 $ 128,333.33 $ 46,666.67 30 23 $ 46,666.67 3.70% $ 5,833.33 $ 134,166.67$ 40,833.33 31 241 S 40,833.33 3.70%) $ 5,833.33 $ 140,000.00 $ 35,000.00 32 25 s 35,000.00 3.709 $ 5,833.33 $ 145,833.33 $ 29,166.67 33 26 $ 29,166.67 3.70% $ 5,833.33 $ 151,666.67 23,333.33 34 271 $ 23,333.33 3.70% $ 5,833.33 $ 157,500.00 $ 17,500.00 35 Instructions Straight line DNB with switch to SL Sum-of-the-years' digits Units of production Summary and E50 fic H K M A B C D E F G initial cost- useful life sell after 27- produce units produce units 1 purchase sales tax install set costs (years) salvage val in 18 yrs 9 years 2 175000 15750 2250 27 17500 15000 7500 3 4 book val salvage val accum. Deprc. useful life (years straight line de annual depr ex 5 175000 17500 157500 27 3.70% 5833.333333 6 7 Year book val (beg) deprec. % depreciation e accum depr book val (end) 8 1 s 175,000.00 7.41% $ 12,962.96 $ 12,962.96 $ 162,037.04 9 2 $ 162,037.04 7.41% $ 12,002.74 $ 24,965.71 $ 150,034.29 10 3 S 150,034.29 7.41% $ 11,113.65 $ 36,079.36 $ 138,920.64 11 4 $ 138,920.64 7.41% $ 10,290.42 $ 46,369.78 $ 128,630.22 12 5 $ 128,630.22 7.41% $ 9,528.16 $ 55,897.94 $ 119,102.06 13 61 S 119,102.06 7.41% S 8,822.37$ 64,720.32 $ 110,279.68 14 7 S 110,279.68 7.41% $ 8,168.87 $ 72,889.18 $ 102,110.82 15 8 $ 102,110.82 7.41% $ 7,563.76 $ 80,452.95 $ 94,547.05 16 9 $ 94,547.05 7.41% $ 7,003.49 $ 87,456.43 $ 87,543.57 17 10 $ 87,543.57 7.41% S 6,484.71 $ 93,941.14 $ 81,058.86 18 11 s 81,058.86 7.41% $ 6,004.36 $ 99,945.50 $ 75,054.50 121 $ 75,054.50 7.41% $ 5,559.59 $ 105,505.09$ 69,494.91 20 13 S 69,494.91 7.41% S 5,147.77 $ 110,652.86$ 64,347.14 21 141 $ 64,347.14 7.41% s 4,766.45 $ 115,419.32 $ 59,580.68 22 15$ 59,580.68 7.41% $ 4,413.38 $ 119,832.70 $ 55,167.30 161 $ 55,167.30 7.41% $ 4,086.47 $ 123,919.17$ 51,080.83 171 $ 51,080.83 7.41% 3,783.77 $ 127,702.93 $ 47,297.07 25 18 $ 47,297.07 7.41% $ 3,503.49 $ 131,206.42 S 43,793.58 19 $ 43,793.58 7.41% $ 3,243.97 $ 134,450.39 $ 40,549.61 201 S 40,549.61 7.41%) s 3,003.67$ 137,454.06 $ 37,545.94 28 21 $ 37,545.94 7.41% $ 2,781.18 $ 140,235.24 $ 34,764.76 29 22 $ 34,764.76 7.41% S 2,575.17$ 142,810.41 $ 32,189.59 30 23 $ 32,189.59 7.41% $ 2,384.41 $ 145,194.83 $ 29,805.17 24 $ 29,805.17 7.41% $ 2,207.79 $ 147,402.62 $ 27,597.38 32 250 $ 27,597.38 7.41% $ 2,044.25 $ 149,446.87 S 25,553.13 261 $ 25,553.13 7.41% $ 1,892.82 $ 151,339.69 $ 23,660.31 34 27 S 23,660.31 7.41% $ 1,752.62 $ 153,092.31 $ 21,907.69 35 38 39 Instructions Straight-line DDB with switch to SL Sum-of-the-years' digits Units of production Summary and + Sell D37 fc A B D E F G H J L M N o P Q 1 2 initial cost useful life -purchase sales tax install set costs (years) 175000 15750 2250 sell after 27- produce units produce units salvage val in 18 yrs 9 years 27 17500 15000 7500 4 6 book val sell after 2 accum. Deprc. useful life (years) 175000 17500 157500 27 sum up to 27 378 7 8 Year 9 10 11 12 13 14 15 16 17 18 19 20 21 book val beg tot cost deprc. depr. % 1 $ 175,000 $ 157,500 2 $ 163,750 $ 157,500 3 $ 152,917 $ 157,500 4 $ 142,500 $ 157,500 5 $ 132,500 $ 157,500 6 $ 122,917 $ 157,500 71 $ 113,750 $ 157,500 8 $ 105,000 $ 157,500 9 $ 96,667 $ 157,500 10 $ 88,750 $ 157,500 11 $ 81,250 $ 157,500 12 $ 74,167 $ 157,500 13 $ 67,500 $ 157,500 14 $ 61,250 $ 157,500 150 $ 55,417 $ 157,500 16 $ 50,000 $ 157,500 17 $ 45,000 $ 157,500 18 S 40,417 $ 157,500 19 $ 36,250 $ 157,500 20 S 32,500 $ 157,500 21 $ 29,167 $ 157,500 22 $ 26,250 $ 157,500 23 $ 23,750 $ 157,500 24 $ 21,667 $ 157,500 25 $ 20,000 $ 157,500 26 $ 18,750 $ 157,500 27 $ 17,917$ 157,500 tot % = 22 23 24 25 26 27 28 29 30 31 32 33 34 deprec. Expense accum. Depr. book val end 79 $ 11,250 $ 11,250 $ 163,750 79 $ 10,833$ 22,083 $ 152,917 796 $ 10,417 S 32,500 $ 142,500 69 $ 10,000 $ 42,500 $ 132,500 6% $ 9,583 $ 52,083 $ 122,917 6% $ 9,167 $ 61,250 $ 113,750 696 $ 8,750 $ 70,000 $ 105,000 5% $ 8,333$ 78,333$ 96,667 59 $ 7,917 S 86,250 $ 88,750 596 $ 7,500 $ 93,750 $ 81,250 496 $ 7,083 S 100,833$ 74,167 49 $ 6,667 $ 107,500 $ 67,500 49 $ 6,250 $ 113,750 $ 61,250 496 $ 5,833$ 119,583 $ 55,417 39 $ 5,417 125,000 $ 50,000 39 $ 5,000 $ 130,000 $ 45,000 396 $ 4,583$ 134,583 $ 40,417 396 $ 4,167 $ 138,750 $ 36,250 3,750 S 142,500 $ 32,500 29 $ 3,333 $ 145,833 $ 29,167 29 $ 2,917 S 148,750 $ 26,250 296 $ 2,500 $ 151,250 $ 23,750 1% $ 2,083 S 153,333$ 21,667 19 $ 1,667S 155,000 $ 20,000 196 $ 1,250 $ 156,250 $ 18,750 19 $ 833$ 157,083 $ 17,917 0% $ 417 S 157,500 $ 17,500 1 29 $ 35 36 37 38 Instructions Straight-line DDB with switch to SL Sum-of-the-years' digits Units of production Summary and