📌 Stage 3 · Costs, CapEx, and the Loan · CapEx & Loan · depreciation · 35–45 min

Straight-line depreciation starting the month in service

Learning goals

  • Read the asset plan: description, in-service date, cost, life
  • Compute monthly straight-line depreciation with a guarded formula
  • Separate the 2026 cash outlay from prior-year assets
  • Trace the depreciation step-ups into the Monthly P&L

Concepts

The asset plan

Four assets sit on the CapEx & Loan sheet, rows 5 to 8: workstations and displays ($8,400.00, Jan 5, 2026, 4-year life), the studio build-out ($21,600.00, Feb 2, 2026, 8-year life), cameras and production kit ($6,300.00, Sep 3, 2026, 3-year life), and furniture and fixtures bought back in March 2024 ($14,400.00, 6-year life — it keeps depreciating in 2026 but spent its cash long ago).

Two different questions follow from this table: what hits the bank this year (cash), and what hits the P&L this year (depreciation).

Monthly straight-line depreciation

Straight-line spreads cost evenly over the useful life, in whole months, starting the month the asset is placed in service:

=IF($E5="",0,ROUND($D5/($E5*12),2))

CapEx & Loan F5: $8,400.00 divided by (4 years x 12) = $175.00 per month. The guard returns 0 while the life cell is blank, so the blank planning template recalculates cleanly instead of showing #DIV/0!. The build-out is $225.00, the cameras $175.00, and the 2024 furniture $200.00 per month. F9 totals the monthly column at $775.00.

💡 Tips:

  • Real depreciation rules (Section 179, bonus depreciation, listed property) can differ sharply from straight-line — this is a planning simplification, not a tax computation. Confirm the treatment with your CPA.

2026 cash outlay

The cash question answers itself from the in-service date:

=IF($C5="",0,IF(YEAR($C5)=2026,$D5,0))

CapEx & Loan G5: workstations were placed in service in 2026, so the full $8,400.00 is a 2026 cash outlay. G8 returns $0.00 for the 2024 furniture — it cost cash in 2024 but still depreciates. G9 totals the 2026 outlays at $36,300.00.

The P&L reads depreciation by date

The Monthly P&L does not retype depreciation; it sums the monthly amounts for assets whose in-service date has arrived:

=SUMIF('CapEx & Loan'!$C$5:$C$8,"<="&EOMONTH(C$4,0),'CapEx & Loan'!$F$5:$F$8)

Monthly P&L C25 (January): assets in service by Jan 31, 2026 are the workstations ($175.00) and the 2024 furniture ($200.00) = $375.00. February adds the build-out ($225.00) and steps the row to $600.00; September adds the cameras and steps it to $775.00. The FY total is $7,675.00.

Practice

Fixed case: Harbor Creative Studio LLC, a four-person design studio in Austin, Texas, planning calendar year 2026. Cached numbers are the Base scenario. Pale-green cells are formulas; white cells are typed inputs. The exercise opens on the sheet this lesson teaches. Cell G5 (2026 cash outlay for the workstations) is blank: retype the year-guarded formula.

  1. Open CapEx & Loan and inspect the asset plan — The exercise opens on CapEx & Loan. Rows 5 to 8 are the four assets; row 9 is the total. Column C holds real in-service dates, D the cost, E the life in years.
  2. Retype the outlay formula in G5 — Cell G5 (2026 Cash Outlay) is empty. Type =IF($C5="",0,IF(YEAR($C5)=2026,$D5,0)) and confirm: it should return $8,400.00. G8 should return $0.00 for the 2024 furniture.
  3. Check the depreciation column — F5 through F8 should read $175.00, $225.00, $175.00, and $200.00 per month, with F9 totaling $775.00. G9 totals the 2026 outlays at $36,300.00.
  4. Trace the step-ups into the P&L — Open Monthly P&L row 25 (Depreciation): C25 reads $375.00 in January, steps to $600.00 from February, and to $775.00 from September. O25 (FY depreciation) reads $7,675.00.
  5. Confirm the cash timing — Open Cash Flow row 16 (Capital purchases paid): $8,400.00 in January, $21,600.00 in February, $6,300.00 in September — the same dates, read with SUMIFS from the asset plan.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Retyped the outlay formula in G5 and it returned $8,400.00; G8 returns $0.00
  • ☐ Monthly depreciation row totals $775.00 and the 2026 outlay total is $36,300.00
  • ☐ Monthly P&L depreciation steps $375.00 (Jan), $600.00 (Feb), $775.00 (Sep) with FY $7,675.00
  • ☐ Can explain why the 2024 furniture shows $0.00 cash but $200.00 monthly depreciation