📌 Stage 6 · Monthly Review · Job Costing · Utilization · 35–45 min

Who was billable, what is over-billed, and the close routine that keeps it honest

Learning goals

  • Compute each person's job-versus-office utilization from the timesheet journal
  • Close the month with a repeatable review of WIP, margin, and aging
  • Take the workbook into your own business - and know its limits

Concepts

Utilization is a labor P&L

Utilization answers a question labor cost alone cannot: of the hours we paid for, how many landed on work that earns revenue? The formula splits each person's logged hours into job hours and office hours and divides.

=SUMIFS(Timesheets!$H$5:$H$1004,Timesheets!$D$5:$D$1004,$B5,Timesheets!$F$5:$F$1004,"<>OH")

Job hours for one employee: sum the Hours column where the employee code matches B5 AND the job is anything except OH. The <> operator inside SUMIFS criteria means not equal to - the cleanest exclusion in the classic function set.

=IFERROR($E5/$G5,0)

Percent on jobs: job hours over total hours logged. Dana shows 11 of 11 hours on jobs - 100.0% - and Priya 0.0%, both exactly as designed: one supervises, one runs the office. IFERROR keeps people with no logged hours at zero instead of #DIV/0!.

What the case shows

For Mar 1 through Jun 30, 2026: Marcus 44 job hours, Sofia 37, Tyler 24, Jamal 24, Dana 11, Priya 0 on 32 office hours. The crew totals 140 job hours against 32 office hours - 81.4% on jobs, with $6,520.00 of loaded labor cost behind it ($5,560.00 direct, $960.00 office).

Careful with the denominator: these are logged hours, not paid hours. Nobody logged 480 hours a month here because the case captures selected weekly entries - real utilization runs against a capacity number like weeks times 40. The pattern is what transfers: field staff near 100%, office staff near 0%, and the gap priced into overhead rather than hidden in it.

💡 Tips:

  • Agencies live on this exact number: billable staff should sit in the 65-75% band and anything lower is a sales problem, not a people problem.
  • Add a Capacity column (weeks x 40) to compare logged hours against paid hours - the difference is where supervision, training, and rework hide.

The monthly close in ten minutes

Run the routine in order. One - roll the period: set Settings period start and end to the new month and the report date to month end. Two - journals: every timesheet, purchase, and invoice entered and dated inside the month. Three - WIP: read the flags (the deck still says Review billing at -$3,863.35 until earned catches up or the bill is corrected) and the net position (-$282.10 company-wide). Four - Job P&L: check the Margin Check column and drill any flagged job by cost code. Five - Billings aging: $64,000.00 was past due at the June close; chase it. Six - Overhead: update the month's pool column and glance at the rate.

Then download the two workbooks from the final page and keep your own copy: the case file to study, the blank template to run. Both are macro-free - plain formulas that open anywhere - and the Read Me sheet travels with them.

💡 Tips:

  • This workbook intentionally contains no macros. If a future version of your own file needs them, save as .xlsm and accept the security prompts that follow.
  • Boundaries: single-user, no audit trail, no backups, not accounting software and not tax advice. When the business grows past it, graduate to real job-costing accounting software - and take these reports with you as the spec.

Practice

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

  1. Retype the utilization formula in H5 — Cell H5 on Utilization is blank. Type =IFERROR($E5/$G5,0) and press Enter. Dana Whitfield returns 100.0% - all 11 of her logged hours were on jobs.
  2. Read the crew — Rows 6 through 10: Marcus through Jamal all show 100.0% (the case logs no field downtime), and Priya shows 0.0% on 32 office hours - by design, she lives in the overhead pool.
  3. Check the totals — Row 11: 140 job hours, 32 office hours, 81.4% on jobs, and $6,520.00 of labor cost in column I.
  4. Finish the review — Open WIP and confirm the deck still reads Review billing at -$3,863.35 with the company net at -$282.10 - then open Job P&L row 8 for the headline: $8,952.90 of gross profit after $18,110.00 of overhead.
  5. Take it with you — Use the download buttons to save job-costing-case.xlsx and job-costing-template.xlsx, and open them in desktop Excel - every formula you typed in this course is live in the case file.

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.

表格加载中…

Practice files: job-costing-case.xlsx, job-costing-template.xlsx (see the attachments section at the bottom of this page).

Checklist

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

  • ☐ Typed the utilization formula into H5 and got 100.0% for Dana Whitfield
  • ☐ Verified the crew totals: 140 job hours, 32 office hours, 81.4% on jobs, $6,520.00 labor cost
  • ☐ Re-read the close: WIP net -$282.10 with the deck flagged, $64,000.00 past due, and $8,952.90 gross profit after overhead
  • ☐ Downloaded both macro-free workbooks for your own use