01 May Complete a Depreciation Schedule for JCW Supplies

EA6-A1 Complete a Depreciation Schedule for JCW Supplies 

In this exercise, you will create a depreciation schedule for JCW Supplies as of 10/31/2027 using an Excel table. You will then sort, filter, and analyze the data in the table.

 

Access the proper file.

 

Using Project Grader: Download EA6-A1-Depreciation-eStart.

Not using Project Grader: Open EA6-A1-Depreciation.

This table shows the fixed assets as of 10/31/2026, prior to the calculation of the depreciation expense for the most recent year:

 

Fixed Asset      Acquisition

Date     Cost     Salvage

Value   Useful Life

(years) Accumulated

Depreciation

Furniture          11/1/2024        $3,400 $400     6              $1,000

Office Equipment        11/1/2022        $7,000 $700     9              $2,800

Machinery        11/1/2025        $6,500 $100     4              $1,600

Your starting file is populated with raw data. You will begin by turning the data into a table and making some initial settings.

 

Table Settings:

 

Setup: (PG-1)

Table Range: A4:F7

Table Name: Depreciation_Schedule (use the Ribbon to enter the name)

Style: (PG-2)

​​​​​​​Table Style: Light Orange, Table Style Light 17

Table Style Options: Total Row, First Column (add these to any existing options)

zoom image

Format:

Column widths: Set column A to 21 and columns B–F to 15. (PG-3a)

Headers: Wrap Text (PG-3b)

Total row (use drop-down list summary functions with structured references):

Column C: Use the Sum function for the Cost column. (PG-4a)

Column D: Use the Sum function for the Salvage Value column. (PG-4b)

Column E: Use the Average function for the Useful Life (years) column. (PG-4c)

Print options: (PG-5)

Orientation: Landscape

Set the option to print the table only.

In addition to the fixed assets in the table, JCW Supplies acquired office furniture on 11/1/2026 totaling $1,200 and with an estimated salvage value of $100 and an estimated useful life of eleven years. From cell F7, extend the table by adding one table row below and enter all provided data for the Office Furniture asset. (PG-6)

 

Also add two columns to the far right, in the order shown, setting their widths to match columns B–F: Depreciation Expense and Year-End Book Value (PG-7)

 

Autofit the header row height. (PG-8)

 

Use Depreciation Functions

In the appropriate column for each:

 

Use a function and structured references to calculate depreciation expense for each fixed asset under the straight-line depreciation method. (PG-9a)

Use structured references to calculate the accumulated depreciation for each fixed asset. (PG-9b)

Use structured references with calculated columns to calculate the year-end book value for each fixed asset. (PG-9c)

In the Total row, use the drop-down list Sum function with structured references for columns F–H. (PG-9d)

Sort and Filter

Sort: By salvage value, from largest to smallest (PG-10a)

Filter: Display only fixed assets with a cost greater than $2,000. (PG-10b)

Quick Analysis

Use Quick Analysis to create a clustered column chart displaying the accumulated depreciation for each fixed asset. Position the chart below the table and title it: Accumulated Depreciation (PG-11)

 

In the table, ensure all dollar amounts are in Accounting number format with zero decimal places and all other numbers are in Comma style with two decimal places. (PG-12)

 

Save and close the file.

 

Using Project Grader: Save the file as EA6-A1-Depreciation-eSubmission and attach it to the assignment you submit for grading.

 

Not using Project Grader: Submit the file per your instructor’s guidelines.