📌 Stage 5 · Job Profitability · Job Costing · Cost Code Detail · 35–45 min

The Estimates sheet turns itself into estimate-versus-actual detail

Learning goals

  • Pull actual labor and non-labor onto each estimate line with SUMIFS
  • Read variance and percent-of-estimate-used at code level
  • Turn three typical variance patterns into three different actions

Concepts

The drill-down was free

Lesson 14 said the kitchen is roughly on estimate. Roughly is not where money is made or lost - code 2100 being $250 over while code 2200 runs $7,360 under is the actual news. Because every journal row already carries a job and a cost code, the detail view costs two more formulas per estimate line.

=SUMIFS(Timesheets!$J$5:$J$1004,Timesheets!$F$5:$F$1004,$B5,Timesheets!$G$5:$G$1004,$C5)

Actual labor for this exact job and code: sum Timesheets column J where the job equals B5 AND the cost code equals C5. The kitchen's 1000 General Conditions line collects Dana's and Jamal's supervision hours - $640.00.

=SUMIFS('Cost Log'!$I$5:$I$1004,'Cost Log'!$D$5:$D$1004,$B5,'Cost Log'!$E$5:$E$1004,$C5)

Actual non-labor: the same two-criteria sum over the Cost Log's amount column. General Conditions has no purchases, so this line returns $0.00 - demo's $4,150.00 lands one row below on code 2100.

=$G5+$H5

Actual total: labor plus non-labor for the line. $640.00 + $0.00 = $640.00 on the kitchen's supervision line. Variance (column J) then subtracts the estimate, and column K divides actual by estimate to express burn as a percentage.

Three variance patterns, three actions

Pattern one - small overrun on a finished scope. Kitchen code 2100 Demolition: estimated $3,900.00, actual $4,150.00, 106.4% used. The work is done and the variance is $250.00: note it in the next bid (demo has been running hot) and move on.

Pattern two - under-run on an open scope. Kitchen code 2200 Framing labor: estimated $9,600.00, only $2,240.00 booked, 23.3% used. Alarming - until you remember the job is 86.4% complete overall. Framing should be finished; if it is, the estimate was padded or the crew was faster, and the next kitchen bid carries a leaner number. If framing is not actually finished, the percent-complete math from Lesson 13 is quietly wrong. Always check the field before booking an under-run as savings.

Pattern three - big-ticket line near budget. Code 6000 Cabinetry: $18,900.00 estimated, $19,450.00 actual, delivered in June. A $550.00 over on the largest line of the job - worth a conversation with Foxglove Cabinet Works, not a crisis.

💡 Tips:

  • Read variance next to % of Est Used: +$250.00 at 106.4% is closed; -$7,360.00 at 23.3% is either savings or a missing timesheet. The percentage is the discriminator.

Where the rest of the money went

Scan the kitchen's remaining lines: electrical $8,275.00 against $8,400.00 estimated, plumbing $7,120.00 against $6,700.00 (a $420.00 over - the second plumbing over in the case, a pattern worth watching across jobs), drywall sub $5,940.00 against $5,600.00, cabinets and counters as above, tile labor $4,265.00 against $4,300.00, paint $3,690.00 against $3,800.00.

None of it is dramatic - that is the finding. This job's thin margin is not a field failure; it is the pricing-versus-overhead structure Lesson 14 exposed. The drill-down is how you prove that instead of guessing, and its rows on the deck and bath tell the same story job by job.

💡 Tips:

  • Excel Tables or a pivot table over this journal would give you the same grouping with drag-and-drop - worth doing in your own copy with desktop Excel. The formulas stay so the web exercise and every Excel version agree.

Practice

Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell I5 on Estimates has been cleared - you will retype it.

  1. Retype the actual total in I5 — Cell I5 on Estimates is blank. Type =$G5+$H5 and press Enter. The kitchen's General Conditions line returns $640.00 - supervision labor only, no purchases.
  2. Read the variance pattern — J5 shows -$4,160.00 and K5 13.3% used: the line is far under estimate because supervision is logged weekly and the job is not closed. Compare row 6 (code 2100): J6 +$250.00, K6 106.4% - a closed scope, slightly over.
  3. Check the framing line — Row 7 (code 2200): I7 $2,240.00 against a $9,600.00 estimate, K7 at 23.3%. This is the under-run that needs a field check before anyone celebrates.
  4. Skim the deck and bath — Rows 17 through 21 (deck) and 22 through 33 (bath) repeat the pattern per job; the four garage bid rows at the bottom sit at $0.00 actuals - no work has started until the bid signs.

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 =G5+H5 into I5 and got $640.00 of actual cost on General Conditions
  • ☐ Confirmed the demo line shows a $250.00 over at 106.4% used and framing 23.3% used
  • ☐ Traced at least one overrun (plumbing, $420.00 over) to its vendor and month