📌 Stage 3 · Capturing Actuals · Job Costing · Labor Cost · 30–40 min

Extend each row with one multiplication, then let SUMIFS do the totaling

Learning goals

  • Extend hours into dollars with a rounded multiplication
  • Aggregate labor by job and by job-plus-code with SUMIFS
  • Explain the difference between payroll and job-costed labor

Concepts

The extension formula

Column J is the entire bridge between time tracking and money. Hours times loaded rate, rounded to cents - nothing more. Once it exists, every labor question becomes an aggregation question.

=ROUND($H5*$I5,2)

H5 is the hours for the row (8) and I5 the loaded rate from Lesson 7 ($45.00). The product is $360.00; ROUND to two decimals keeps cents consistent so column totals tie to payroll exports exactly.

Labor by job and by code

Later sheets slice this column two ways. WIP and the Job P&L ask for labor by job: =SUMIFS(Timesheets!$J$5:$J$1004,Timesheets!$F$5:$F$1004,"J-2601") returns the kitchen's $2,880.00 of labor to date. The Estimates drill-down in Lesson 15 adds a second criterion - cost code - because its rows already carry both keys.

For the case period the numbers are: kitchen $2,880.00, deck $1,300.00, bath $1,380.00, office $960.00. Total labor costed: $6,520.00, of which $5,560.00 is direct (on jobs) - those two numbers drive utilization in Lesson 16 and the overhead base in Lesson 11.

💡 Tips:

  • SUMIFS takes the sum range first, then pairs of criteria range and criteria - the mirror image of SUMIF, where the criteria come first. Mixing them up is the most common SUMIFS bug.
  • The OH rows are excluded from job sums automatically because no job number matches OH except in the office totals - no filtering formulas needed.

Payroll versus job cost

Payroll will show Marcus earned $36.00 an hour; the job shows $45.00. Both are right: payroll reports the wage, job costing reports what the hour cost the company including burden. When you reconcile this workbook to payroll at month end, expect the difference to equal burden - if it does not, a burden percentage or a missing timesheet row is the culprit.

Notice also what this model deliberately simplifies: every hour worked is treated as costed to exactly one job and code. Overtime premiums, non-billable field time like truck maintenance, and paid time off are all outside the journals - a production workbook would add codes for them, but the arithmetic you are learning does not change.

Practice

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

  1. Retype the extension in J5 — Cell J5 is blank. Type =ROUND($H5*$I5,2) and press Enter. Eight of Marcus's hours at $45.00 return $360.00.
  2. Scan the column — Every row now shows dollars: Sofia's 8-hour days cost $320.00, the apprentice's $200.00, Dana's 6 supervision hours $360.00 at her $60.00 loaded rate.
  3. Find the office cost — The four OH rows at the bottom each show $240.00 (Priya, 8 hours at $30.00). That $960.00 will resurface inside the overhead pool in Lesson 11.
  4. Preview the job split — Open the WIP tab and read column F: kitchen $66,490.00, deck $12,990.00, bath $29,175.00 of total costs to date. Labor is $2,880.00 / $1,300.00 / $1,380.00 of those - the rest is materials and subs, which you capture next.

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 the extension into J5 and got $360.00
  • ☐ Confirmed the four OH rows each cost $240.00 (office labor of $960.00 total)
  • ☐ Noted direct labor to date is $5,560.00 across the three open jobs