Apply Your Skils 183 Apply Your Skills EA6-A1 Complete a Depreciation Schedule for JCW Supplies her e , you will create a depreciation schedule for JCW Supplies as of 10/31/2017 leatYou will then sort, filter, and analyze the data in the table. These fixed assets with sociated data as of 10/31/2016, were acquired prior to the current wear Fixed Asset Cost Useful Life (years) Date of Acquisition 11/1/2014 11/1/2012 Salvage Value 5400 $700 Furniture Office Equipment Machinery $3,400 $7,000 Accumulated Depreciation $1,000 $2,800 11/1/2015 $6,500 $100 $1,600 JCW Supplies also acquired office furniture on 11/1/2016. The office furniture had a cost of $1,200, an estimated salvage value of $100, and an estimated useful life of eleven years. 1. Open a Blank Workbook and save the file in your Chapter 06 folder as EA6-A1-Depreciation-[Your Name] 2. Enter appropriate bank reconciliation headers in the range A1:A2: apply bold formatting 3. Enter the fixed asset data in the range A4:57, for the accumulated depreciation, enter the title but not the dollar amounts 4. Format the range A4:57 as a table using Light Orange, Table Style Light 17 and add the table name Depreciation Schedule 5. Add a total row that shows sums in columns CD and an average in column E 6. Turn on the First Column table style option. Adjust Table Rows and Columns 7. From within cell F7, extend the table to add one additional row and then enter all fixed asset information for the newly acquired asset. 8. In order, add these two columns to the far right of the table: Depreciation Expense and Year-End Book Value 9. Wrap the text in the headers 10. Apply appropriate column widths and, if necessary, autofit the header row height. 11. Change the worksheet orientation to Landscape and set the option so only the table will print. Use Depreciation Functions 12. Use an Excel function to calculate depreciation expense for each fixed asset under the double-declining balance depreciation method. Hint: Turn off calculated columns when entering the formulas for each fixed asset Excel for Accounting Chapter 6: Depreciation Schedule 13. Replace the formula with one that uses an Excel function to calculate depreciation expense for each fixed asset under the straight-line depreciation method. Use calculated columns so you can enter the formula in a single row and have it automatically extend to all other fixed assets. 14. Use structured references to calculate the accumulated depreciation for each fixed asset. 15. Use structured references with calculated columns to calculate the year-end book value for each fixed asset 16. Use the Sum function on columns F:H. Sort and Filter a Table 17. Sort the table data based on salvage value from largest to smallest. 18. Filter the table to hide fixed assets with a cost of $7,000. 19. Clear all filters; apply a new filter that displays only fixed assets with a cos $2,000. Create a Chart Using Quick Analysis 20. Use Quick Analysis to create a column chart displaying the a each fixed asset presently displayed in the table. Position the chart 21. Apply the Accounting number format with zero decimal place 22. Save and close your file. chart displaying the accumulated depreciation for table. Position the chart below the table. with zero decimal places to all dollar amounts. EA6-A2 Complete a Depreciation Schedule for Furnit r Furniture
