BMIS 211 Depreciation Schedule and Asset Book-Value Summary Create a both a depreciation schedule and a summary of the book value of assets for the Bozeman City Golf course. Your workbook should, at a minimum, have the following characteristics: Depreciation Schedule 1. Allows for the entry of Purchase Price, Salvage Value, Year Purchased, Useful Life, Depreciation Method, and Asset Description for each asset. 2. Allows the tracking of 25 assets simultaneously. 3. Spans the years 2015 to 2099. 4. Includes today's date 5. Ensures that the Salvage Value is less than the Purchase Price. 6. Ensures that the Useful Life is a whole number. 7. Ensures that the Asset Year + the Useful Life is less than the last year of the table 8. Allows the choice of Straight Line (SL), Sum of the Years Digit (SYD), or Double Declining Balance (DDB) depreciation for each asset. 9. Ensures that Asset Description and Year are always visible. 10. Calculates Total Depreciation for each year this amount should always be visible. 11. Ensures that depreciation for each asset begins in the Year Purchased and ends at the appropriate time period. 12. Is formatted to professional standards. 13. Has the depreciation schedule Cell Width set to at least 17 units. Asset Book Value 1. Maintains the formatting of the depreciation schedule with different coloring so that it is clear to the user exactly what s/he is seeing 2. Draws the purchase price through asset description data from the depreciation schedule. Calculates Total Asset Value on an annual basis. 3. Hints Depreciation Schedule 1. Whenever possible, use named cells and ranges for the fields that involve calculations 2. Simplify your formula creation: 1. First, figure out how to put something, anything (maybe yes, no) in the correct cells based on year purchased and useful life. b. Second, figure out how to calculate the correct period for DDB and SYD based on the year and the year the equipment was purchased c. Third, figure out how to calculate depreciation for each method. d. Once you have a, b, c working individually, put them together and create one big honking formula that you can copy across and down the schedule! 1. Use hints 1 and 2 above, but note you may not be able to use named cells and references for every item in your formulas. 2. Insert a column immediately to the left of 2015, then leave it blank. You may need it to do your calculations. Once the calculations are working properly, you may hide that column for aesthetic reasons. If you end up not needing the column, you may delete it, Asset Value BMIS 211 Depreciation Schedule and Asset Book-Value Summary Create a both a depreciation schedule and a summary of the book value of assets for the Bozeman City Golf course. Your workbook should, at a minimum, have the following characteristics: Depreciation Schedule 1. Allows for the entry of Purchase Price, Salvage Value, Year Purchased, Useful Life, Depreciation Method, and Asset Description for each asset. 2. Allows the tracking of 25 assets simultaneously. 3. Spans the years 2015 to 2099. 4. Includes today's date 5. Ensures that the Salvage Value is less than the Purchase Price. 6. Ensures that the Useful Life is a whole number. 7. Ensures that the Asset Year + the Useful Life is less than the last year of the table 8. Allows the choice of Straight Line (SL), Sum of the Years Digit (SYD), or Double Declining Balance (DDB) depreciation for each asset. 9. Ensures that Asset Description and Year are always visible. 10. Calculates Total Depreciation for each year this amount should always be visible. 11. Ensures that depreciation for each asset begins in the Year Purchased and ends at the appropriate time period. 12. Is formatted to professional standards. 13. Has the depreciation schedule Cell Width set to at least 17 units. Asset Book Value 1. Maintains the formatting of the depreciation schedule with different coloring so that it is clear to the user exactly what s/he is seeing 2. Draws the purchase price through asset description data from the depreciation schedule. Calculates Total Asset Value on an annual basis. 3. Hints Depreciation Schedule 1. Whenever possible, use named cells and ranges for the fields that involve calculations 2. Simplify your formula creation: 1. First, figure out how to put something, anything (maybe yes, no) in the correct cells based on year purchased and useful life. b. Second, figure out how to calculate the correct period for DDB and SYD based on the year and the year the equipment was purchased c. Third, figure out how to calculate depreciation for each method. d. Once you have a, b, c working individually, put them together and create one big honking formula that you can copy across and down the schedule! 1. Use hints 1 and 2 above, but note you may not be able to use named cells and references for every item in your formulas. 2. Insert a column immediately to the left of 2015, then leave it blank. You may need it to do your calculations. Once the calculations are working properly, you may hide that column for aesthetic reasons. If you end up not needing the column, you may delete it, Asset Value