01 May EA6-R1 Complete a Depreciation Schedule for Linen Wholesalers

EA6-R1 Complete a Depreciation Schedule for Linen Wholesalers

In this exercise, you will create a depreciation schedule for Linen Wholesalers using an Excel table. You will then sort, filter, and analyze the data. These fixed assets, with associated data as of 12/31/2025, were acquired prior to the current year:

 

Fixed Asset Date of

Acquisition Cost Salvage

Value Useful Life

(years) Accumulated

Depreciation

Office Tables 1/1/2025 $1,400 $200 6 $200

Furniture 1/1/2020 $6,000 $0 12 $3,000

Warehouse

Machinery 1/1/2022 $17,500 $1,500 16 $4,000

Linen Wholesalers also acquired office equipment on 1/1/2026. The office equipment had a cost of $4,200, an estimated salvage value of $700, and an estimated useful life of seven years.

 

Open a Blank Workbook and save the file in your Chapter 06 folder as: EA6-R1-Depreciation

Type Linen Wholesalers in cell A1 and Depreciation Schedule in cell A2.

Enter the fixed asset data in the range A4:F7. Include the Accumulated Depreciation header but exclude its amounts. Also exclude the newly acquired asset for now.

Select cell A4, choose Home→Styles→Format as Table→Blue, Table Style Light 13, and click OK.

Choose Table Design→Properties, enter Depreciation_Schedule for the table name, and tap Enter.

Choose Table Design→Table Style Options→Total Row.

One by one, select the cells listed and choose the indicated formula from the drop-down menu:

Cell C8: Sum

Cell D8: Sum

Cell E8: Average

Choose Table Design→Table Style Options→First Column.

CHECK FIGURE Cell C8 = $24,900

 

Adjust Table Rows and Columns

Select cell F7, tap Tab, and enter the information for the newly acquired fixed asset into the range A8:E8.

Click in cell F8 and choose Home→Cells→Insert →Insert Table Columns to the Right two times.

Type Depreciation Expense in cell G4 and Year-End Book Value in cell H4.

Wrap the text in row 4.

Use the Ribbon to set the width of columns G:H to 12 and the right-click method to set the width of column F to: 14

Use the Ctrl key to select the columns B and E headers then use the Ribbon to set the width of both to: 12

Set the width of column D to 4 using any method and then choose Home→Cells→Format→AutoFit Column Width.

Double-click between the columns A–B headers to autofit column A.

Autofit the height of row 4.

Select cell A5 and choose File→Print.

In the Settings area, choose Portrait Orientation→Landscape Orientation and then choose Print Active Sheets→Print Selected Table.

CHECK FIGURE Cell C9 = $29,100

 

Use Depreciation Functions

Click the Back button to exit Backstage view and then navigate to the Home tab, if necessary.

Select cell G5, type =DDB( and select cell C5, type a comma, select cell D5, type a comma, select cell E5, and then type ,2) and tap Enter.

With cell G6 selected, click in the Formula Bar, replace 2 with 7 and tap Enter, and then click Undo on the Quick Access toolbar.

Edit the formulas in the Formula Bar for the cells as indicated:

In cell G7, change 2 to: 5

In cell G8, change 2 to: 1

For each cell in the range G5:G8, edit the formulas in the Formula Bar to replace DDB with: SYD

Delete the contents in the range G5:G8.

Select cell G5, type =SLN( and select cell C5, type a comma, select cell D5, type a comma, select cell E5, and then type a closed parenthesis and tap Enter.

Select cell F5, type =200+ and select cell G5, and then tap Enter.

Press Ctrl+Z.

Repeat step 26 for each cell in the range F6:F8. Ensure that the correct accumulated depreciation amount is entered at the beginning of each formula (based on the information provided in the instructions).

Select cell H5 and type an equals sign, select cell C5 and type a minus sign, select cell F5, and tap Enter.

Select cell F9, click the drop-down button, and choose Sum. Repeat for cells G9 and H9.

CHECK FIGURE Cell F9 = $9,400

 

Sort and Filter a Table

Select cell C7 and choose Data→Sort & Filter→Sort Smallest to Largest.

Click the drop-down arrow in cell E4 and then uncheck the box next to 12 and click OK.

Click the drop-down arrow in cell H4 and choose Number Filters→Custom Filter.

In the dialog box, click in the Equals box and choose is greater than, type 1500 in the field to the right, and click OK.

CHECK FIGURE Cell G9 = $1,500

 

Create a Chart Using Quick Analysis

Highlight the range A4:H9 and click the Quick Analysis button in the bottom-right corner of the range.

Choose the Charts category and click More Charts.

Click the All Charts tab, choose Pie from the menu, and click OK.

Drag the chart so its top-left corner is in cell A11.

Choose Chart Design→Data→Select Data, highlight the ranges A4:A8 and H4:H8, and click OK.

Click the filter button in cell H4 and choose Clear Filter From “Year-End Book Value.”

Apply the Accounting number format and zero decimal places to the ranges C5:D9 and F5:H9.

Save and close your file.