Learning goals

Assemble each job's P&L from lookups you have already built
Read direct variance, gross profit to date, and margin to date together
Diagnose why a 20% priced margin became a 6.7% realized margin

Concepts

A P&L that is all lookups

The Job P&L sheet types almost nothing. Job name, status, contract price, and estimated cost come from Jobs by VLOOKUP; actual direct cost and earned revenue come from WIP; allocated overhead comes from the rate cell built in Lesson 12. If a number on this sheet is wrong, the bug lives upstream - this sheet is where upstream bugs become visible.

One row per won job, plus an All jobs total row. The garage bid is deliberately absent: profit statements about unsigned work are fiction.

=$K5-$I5

Gross profit to date: earned revenue (K5, from WIP) minus total cost to date (I5, actual direct plus allocated overhead). For the kitchen: $83,112.50 - $77,572.18 = $5,540.32.

=IFERROR($L5/$K5,0)

Gross margin to date: profit over earned revenue, not over contract price - you can only measure margin on work you have actually performed. The kitchen runs $5,540.32 / $83,112.50 = 6.7%.

=IF($E5<=0,"",IF($M5<Settings!$C$10,"Below target","On target"))

The margin check compares column M against the target in Settings!C10 (15%). Every won job in the case reads Below target - which is the point of the column: it turns a quiet problem into text you cannot scroll past.

The story in one table

Read the Jun 30, 2026 row set. Kitchen: $96,250.00 contract, $66,490.00 actual direct against a $77,000.00 estimate, $5,540.32 gross profit, 6.7% margin. Deck: $981.55 profit on $16,136.65 earned - 6.1%. Bath: $2,431.03 on $36,468.75 - 6.7%. All jobs: $8,952.90 of gross profit on $135,717.90 earned, 6.6%.

Now set that against Stage 2: every job was priced at 19.5% to 20.0% gross margin, and the field is roughly on estimate - direct variance is favorable on all three jobs (the kitchen is $10,510.00 under its estimate with 13.6% of work remaining). Nothing went wrong in the field. Overhead did what overhead does: 16.7 points of every direct dollar went to the office, and the realized margin settled near 6.7%.

💡 Tips:

The fix is arithmetic, not effort: price at estimate divided by (1 - margin - overhead rate), or trim the pool. The workbook has now measured both halves of that sentence.

Direct variance is favorable-but-incomplete, not a win: the kitchen still owes its last $10,510.00 of estimated work. Read variance next to percent complete, never alone.

What this sheet is not

Gross profit to date is not cash (billings and collections tell that story - Lesson 10 flagged $64,000.00 past due), and it is not a tax number. It is the operating truth of the portfolio: which jobs earn their keep after carrying the shop.

A monthly habit of five minutes here beats a quarterly surprise: read the margin check column, then drill the flagged jobs by cost code - which is exactly the next lesson.

Practice

Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell L5 on Job P&L has been cleared - you will retype it.

Retype the gross profit formula in L5 — Cell L5 on Job P&L is blank. Type =$K5-$I5 and press Enter. The kitchen's gross profit to date returns $5,540.32.
Read the margin and the check — M5 should show 6.7% and N5 Below target - the 15% target from Settings is not being met by a wide margin.
Compare all three jobs — Rows 6 and 7: the deck earns $981.55 at 6.1%, the bath $2,431.03 at 6.7%. Column J shows every direct variance still favorable: -$10,510.00, -$3,110.00, -$10,025.00 - the field is on estimate, and the margin story is an overhead story.
Total the portfolio — Row 8: $8,952.90 of gross profit on $135,717.90 earned after $18,110.00 of allocated overhead - the two numbers this course has been building toward.

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:

☐ Typed =K5-I5 into L5 and got $5,540.32 for the kitchen
☐ Confirmed M5 reads 6.7% and N5 reads Below target on all three jobs
☐ Verified the portfolio: $8,952.90 gross profit after overhead on $135,717.90 earned

🏗️ Lesson 14 · The job P&L: estimate versus actual