accounting qep.
Instructions
| Plant Assets, Natural Resources, and Intangibles | ||
| Using Excel to prepare depreciation schedules | ||
| The Fraser River Corporation has purchased a new piece of factory equipment on January 1, 2018, and wishes to compare three depreciation methods: straight-line, double-declining-balance, and units-of-production. | ||
| The equipment costs $400,000 and has an estimated useful life of four years, or 8,000 machine hours. At the end of four years, the equipment is estimated to have a residual value of $20,000. | ||
| Use the blue shaded areas on the ENTER-ANSWERS tab for inputs. | ||
| ALWAYS use cell references and formulas where appropriate to receive full credit. Copy/pasting values will NOT earn full points. | ||
| Requirements | Possible Points | |
| 1 | Use Excel to prepare depreciation schedules for straight-line, double-declining-balance, and units-of-production methods. | 56 |
| 2 | Prepare a second depreciation schedule for double-declining-balance method, using the Excel function DDB. The DDB function cannot be used in the last year of the asset’s useful life. | 12 |
| 3 | At December 31, 2018, Fraser River is trying to determine if it should sell the factory equipment. Fraser River will only sell the factory equipment if the company earns a gain of at least $6,000. For each of the depreciation methods, what is the minimum amount that Fraser River will sell the factory equipment for in order to have a gain of $6,000? | 3 |
| *For all requirements, enter all amounts as positive values. Do not use a minus sign or parentheses for any values. | ||
| Excel Skills | ||
| 1 | Create formulas with absolute and relative cell references. | |
| 2 | Format the cells as accounting number format. | |
| 3 | Use the Excel function DDB to calculate double-declining-balance depreciation. | |
| Saving & Submitting Solution | ||
| 1 | Save file to desktop. | |
| a. | Create folder on desktop, and label COMPLETED EXCEL PROJECTS | |
| b. | Save your solution in the folder you just created; add -solution-date to end of file name | |
| 2 | Upload and submit your file to be graded. | |
| a. | Navigate back to the activity window – screen where you downloaded the initial spreadsheet | |
| b. | Click Choose button under step 3; locate the file you just saved and click Open | |
| c. | Click Upload button under step 3 | |
| d. | Click Submit button under step 4 | |
| Viewing Results | ||
| 1 | Click on Results tab in MyAccountingLab | |
| 2 | Click on the Assignment you were working on | |
| 3 | Click on Project link; this will bring up your Score Card | |
| 4 | Within Score Card window, click on Live Comments Report (lower right) to download spreadsheet with feedback |
ENTERANSWERS
| Requirement 1 | |||||||||
| Use Excel to prepare depreciation schedules for straight-line, double-declining-balance, and units-of-production methods. | |||||||||
| (Always use cell references and formulas where appropriate to receive full credit. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) | |||||||||
| Requirement 2 | |||||||||
| Prepare a second depreciation schedule for double-declining-balance method, using the Excel function DDB. The DDB function cannot be used in the last year of the asset’s useful life. | |||||||||
| (Always use cell references and formulas where appropriate to receive full credit. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) | |||||||||
| Requirement 3 | |||||||||
| At December 31, 2018, Fraser River is trying to determine if it should sell the factory equipment. Fraser River will only sell the factory equipment if the company earns a gain of at least $6,000. For each of the depreciation methods, what is the minimum amount that Fraser River will sell the factory equipment for in order to have a gain of $6,000? | |||||||||
| (Always use cell references and formulas where appropriate to receive full credit. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) | |||||||||
| **For calculations in each of the four tables below refer only to the values calculated in the same table or provided in the DATA table. | |||||||||
| DATA | |||||||||
| Cost | $ 400,000.00 | ||||||||
| Residual Value | $ 20,000.00 | ||||||||
| Useful Life (Years) | 4 | ||||||||
| Useful Life (Hours) | 8,000 | ||||||||
| Gain End of Year 1 | $ 6,000.00 | ||||||||
| Straight Line | |||||||||
| Year | Depreciable Cost | Rate | Depreciation Expense | Accumulated Depreciation | Book Value | ||||
| 1 | |||||||||
| 2 | |||||||||
| 3 | |||||||||
| 4 | |||||||||
| The minimum amount to sell the factory equipment in order to have a gain of $6,000 at the end of Year 1? | |||||||||
| Double Declining Balance | Double Declining Balance using DDB function | ||||||||
| Year | Book Value Beginning of Year | Rate | Depreciation Expense | Accumulated Depreciation | Book Value End of Year | Year | Depreciation Expense | Accumulated Depreciation | Book Value |
| 1 | 1 | ||||||||
| 2 | 2 | ||||||||
| 3 | 3 | ||||||||
| 4 | 4 | ||||||||
| The minimum amount to sell the factory equipment in order to have a gain of $6,000 at the end of Year 1? | |||||||||
| Units of Production | |||||||||
| Year | Depreciation Per Unit | Machine Hours | Depreciation Expense | Accumulated Depreciation | Book Value | ||||
| 1 | 2,400 | ||||||||
| 2 | 1,800 | ||||||||
| 3 | 2,480 | ||||||||
| 4 | 1,320 | ||||||||
| The minimum amount to sell the factory equipment in order to have a gain of $6,000 at the end of Year 1? | |||||||||
| HINTS | |||||||||
| Cell | Hint: | |||||||||
| C24:D27 | Use absolute cell references to the corresponding cells in the DATA table to calculate depreciable cost and depreciation rate values for periods 1-4. (Absolute cell reference uses $, example $A$1) | |||||||||
| ALL Book Value columns | Use absolute cell reference to the corresponding cell in the DATA table and the values obtained in the Accumulated Depreciation column to calculate the book values for periods 1-4. | |||||||||
| J34:J37 | Use the function =DDB() to calculate the depreciation expense. Do not enter any value for the FACTOR argument of the DDB function. Note that the DDB function cannot be used in the last year of the asset’s useful life (cell J37). | |||||||||
| D37 | Leave this cell blank. | |||||||||
| G39 | Do not use cell references to the values in the Double Declining Balance using DDB function table. | |||||||||
| C44:C47 | Use absolute cell references to the corresponding cells in the DATA table to calculate depreciation per unit values for periods 1-4. |
