📌 Stage 3 · Costs, CapEx, and the Loan · Monthly P&L · accrual assembly · 40–50 min

Revenue minus delivery cost minus operating cost, linked sheet by sheet

Learning goals

  • Assemble the accrual P&L from links to the driver sheets
  • Pull payroll into the P&L by start date with SUMIF and EOMONTH
  • Read gross profit, EBITDA, net profit, and the margin rows
  • Explain the July step-up and the summer loss months

Concepts

The accrual spine

The Monthly P&L tells the year's financial story in six steps: total revenue (row 8), less contractor cost to get gross profit (row 11), less operating expenses to get EBITDA (row 24), less depreciation and interest to get net profit (row 27). Every line is either a link to a driver sheet or a sum of other P&L rows — nothing on this sheet is typed.

That is the discipline that makes a budget maintainable: change a driver, and the P&L, the cash flow, and the covenant check all follow.

Payroll by start date

The P&L cannot link to Staff & Payroll row by row and still respect start dates, so it sums by date window:

=SUMIF('Staff & Payroll'!$D$5:$D$9,"<="&EOMONTH(C$4,0),'Staff & Payroll'!$F$5:$F$9)

Monthly P&L C14 (January salaries): sum the monthly salaries of everyone whose start date is on or before Jan 31, 2026 — the four current employees, $25,300.00. From July (column I) the criterion also passes the July hire and the line steps to $29,300.00.

💡 Tips:

  • The same SUMIF pattern with column G and H pulls employer taxes and health premiums into rows 15 and 16 — one formula idea, three payroll lines.
  • EOMONTH(C$4,0) turns the month header date into the month's last day, which is what makes the comparison a whole-month window.

Margins

Two ratio rows guard the story: gross margin (row 12) holds at 83.5% all year because both revenue and contractor cost scale with the same projects, and net margin (row 28) ends at 8.2% for the year. A budget whose margin row wobbles for no visible reason is telling you an input changed — margins are the model's smoke alarm.

Reading the year

FY2026 under Base: revenue $690,000.00, gross profit $576,000.00, operating expenses $510,462.00, EBITDA $65,538.00, net profit $56,865.97. The shape matters more than the totals: March carries the insurance renewal and SXSW travel, and July and August go cash-relevant-negative on EBITDA (−$5,733.50 and −$3,333.50) when the new hire's cost lands before the autumn busy season. That summer dip is the first thing the covenant check will test in Lesson 14.

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 C14 (January salaries) is blank: retype the SUMIF that pulls payroll by start date.

  1. Open Monthly P&L and see the spine — The exercise opens on Monthly P&L. Section bands mark REVENUE (row 5), COST OF DELIVERY (row 9), OPERATING EXPENSES (row 13), and RESULTS (row 23). Data rows carry pale-green formula styling; nothing here is typed.
  2. Retype the salary formula in C14 — Cell C14 (Salaries & wages, January) is empty. Type =SUMIF('Staff & Payroll'!$D$5:$D$9,"<="&EOMONTH(C$4,0),'Staff & Payroll'!$F$5:$F$9) and confirm: it should return $25,300.00.
  3. Find the July step — Scan row 14 across the year: $25,300.00 from January through June, $29,300.00 from July through December. Rows 15 and 16 (employer taxes and health) step at the same month.
  4. Check the totals — O8 (FY revenue) reads $690,000.00, O11 (gross profit) $576,000.00, O22 (operating expenses) $510,462.00, O24 (EBITDA) $65,538.00, O26 (interest) $997.03, and O27 (FY net profit) $56,865.97.
  5. Read the margin rows — Row 12 (gross margin) should read about 83.5% in every month; row 28 (net margin) ends at 8.2%. The July and August EBITDA cells (I24, J24) read −$5,733.50 and −$3,333.50 — the summer dip to watch.

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 SUMIF in C14 and it returned $25,300.00; row 14 steps to $29,300.00 in July
  • ☐ FY figures verified: revenue $690,000.00, gross profit $576,000.00, operating expenses $510,462.00
  • ☐ FY EBITDA reads $65,538.00 and FY net profit reads $56,865.97
  • ☐ Gross margin row reads about 83.5% and net margin ends at 8.2%