Math for Business Operations

Applied Accounting with Excel

UNIT08 - Lesson 5

Build Asset Register and Depreciation Schedule in Excel

45 minutes
Lesson Overview

Lesson Focus

Learn asset register workbook anatomy and build with linked depreciation schedule

Key Unit Objectives

Enduring Understandings:

  • Long-term assets are tracked differently from everyday expenses because they provide value over multiple periods
  • Depreciation method choice affects reported profits, tax obligations, and asset book values over time
  • Professional asset tracking requires organized registers with cost, useful life, salvage value, and method documentation
  • The depreciation decision has real consequences for financial statement presentation and business decision-making

Lesson Activities

Activity 1: Asset Register Workbook Anatomy
10 minutes

Understand the structure of a professional asset register

Details:

  • Asset Register sheet: Asset ID, description, cost, acquisition date, useful life, salvage value, method
  • Depreciation Schedule sheet: Year-by-year expense, accumulated depreciation, book value for each asset
  • Summary sheet: Totals by method, annual depreciation expense summary
  • Checks sheet: Validation formulas for book value accuracy and accumulated depreciation limits
Activity 2: Excel Build: Asset Register
15 minutes

Build the asset register with proper formatting and data validation

Details:

  • Set up Excel Table with asset register columns
  • Add data validation for method dropdown (SLN, DDB)
  • Enter sample asset data from provided dataset
  • Calculate depreciable base for each asset
Excel Functions for Depreciation

Excel has built-in functions for common depreciation methods

  • SLN(cost, salvage, life): Straight-line depreciation per period
  • DDB(cost, salvage, life, period, [factor]): Declining balance depreciation
  • DB(cost, salvage, life, period, [month]): Fixed-declining balance
  • SYD(cost, salvage, life, period): Sum-of-years'-digits depreciation
Activity 3: Excel Build: Depreciation Schedule
15 minutes

Build linked depreciation schedule using SLN and DDB functions

Details:

  • Use SLN function for straight-line assets
  • Use DDB function for double-declining balance assets
  • Link accumulated depreciation and book value columns
  • Verify calculations match hand-calculated schedules from Days 3-4
Activity 4: Build Verification
5 minutes

Run verification checks on the completed workbook

Details:

  • Check: All assets have complete information?
  • Check: Depreciation calculations match hand calculations?
  • Check: Book value = Cost − Accumulated Depreciation for every row?
  • Milestone: Asset register and schedule complete
Required Materials
  • Asset register starter workbook
  • Depreciation schedule template
  • Build verification checklist
  • Sample asset dataset
Differentiation Strategies

For Struggling Students

  • • Scaffolded asset register with pre-filled asset descriptions
  • • Formula templates for depreciation calculations
  • • Step-by-step build guide with screenshots
  • • Peer mentoring partnerships with advanced students
  • • Reduced asset count while maintaining authentic business context

For Advanced Students

  • • Extension challenge: Add partial-year depreciation logic
  • • Compare three or more depreciation methods
  • • Leadership roles: Mentor teammates and facilitate peer critique
  • • Deep dive: Tax vs. book depreciation differences
  • • Research MACRS depreciation and its real-world application

For English Language Learners

  • • Depreciation terminology glossary with visual aids
  • • Key formulas explained in plain language with examples
  • • Recommendation templates with language scaffolding
  • • Visual depreciation schedule examples
  • • Partner with fluent English speakers for presentation preparation