Lesson ProgressPhase 4 of 6
Phase 4Independent Practice
Independent Practice: Build Asset Register and Depreciation Schedule
Build the real asset register and depreciation schedule workbook with verification checkpoints
Workbook Sprint: Build the Asset Register
Two Sheets, Linked by FormulaThis is where you build the real artifact. Open the starter workbook and complete both sheets. Your finished workbook should auto-calculate depreciation for every asset and verify that book value is correct.
Build Sequence
- Open the starter workbook. It has two blank sheets named “Asset Register” and “Depreciation Schedule.”
- Build the Asset Register sheet. Enter the column headers: Asset ID, Description, Cost, Useful Life, Salvage Value, Method, Purchase Date. Enter at least 3 assets (use the data from Phase 2 or your own).
- Build the Depreciation Schedule for Asset A-001. Create rows for each year of the asset's life. Calculate annual expense using the straight-line formula:
=(Cost - SalvageValue) / UsefulLife. Reference the register cells by formula, not by typing numbers. - Calculate accumulated depreciation and book value. Year 1 accumulated = Year 1 expense. Year 2 accumulated = Year 1 accumulated + Year 2 expense. Book value = Cost − Accumulated Depreciation.
- Add a verification check column. Use an IF formula:
=IF(BookValue = Cost - AccumDep, "OK", "ERROR"). Every row should show “OK.” - Verify the final year. The last year's book value should equal the salvage value. If not, check your formulas.
Definition of Done
- Asset Register sheet has at least 3 assets with all 7 required fields
- Depreciation Schedule calculates annual expense by formula (not hard-coded)
- Accumulated depreciation is a running total
- Book value = Cost − Accumulated Depreciation for every row
- Check column shows “OK” for every row
- Final year book value equals salvage value
Verification Checkpoints
After each build step, verify your workbook against these checks.
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Verification Checkpoint | ||||
| 2 | Check | Formula | Expected Result | Status | |
| 3 | Book Value Check | =Cost - AccumDep | Matches register cost | OK | |
| 4 | Final BV = Salvage | Last year BV | Equals salvage value | OK | |
| 5 | AccumDep Sum | Sum of annual expenses | Equals final accum dep | OK | |
| 6 | Total Depreciation | Sum all assets | Matches income statement | OK | |
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Self-Assessment: Workbook Complete?