This case study is based on a real client engagement. All client identifying details, commercial terms, and specific project characteristics have been desensitised to protect confidentiality. The dollar values, hour allocations, and model outputs are illustrative — they reflect the methodology and structure used in the engagement but have been adjusted with hypothetical numbers. The labour tracking methodology and allocation framework are presented as they were applied.

Introduction

In early 2025, the operations manager of a mid-sized mechanical and pipe subcontracting firm reached out with a frustration many construction business owners will recognise.

The company had 20–30 staff working across 5–10 active projects at any given time. Labour was their largest cost (60–70% of revenue), but they had no structured way to track which hours were being spent on which project. Job costs were estimated at tender stage based on the project manager's gut feel, and actual costs were only visible at project completion — when both the labour was spent and the money was gone.

The client wanted a system that answered one question in real time: are we on budget for labour on each project, right now?

The Brief

The engagement had two phases:

1. Labour tracking workflow — design a process for collecting daily hour allocations from site supervisors
2. Project hour allocation model — build an Excel model that consumes the timesheet data and produces project-level labour cost reports

Phase 1: The Workflow

The first challenge was data collection. The existing system was a whiteboard in the site office where supervisors wrote down who was on which job. This data was never entered into any system.

We designed a simple three-step workflow:

1. Daily timesheet (5 minutes) — Each supervisor submits a Google Form at end of shift listing:

   • Employee name
   • Project code (from a dropdown)
   • Hours worked
   • Labour category (skilled, unskilled, apprentice)
   • Notes (overtime, special conditions)

2. Weekly data import (15 minutes) — An admin exports the Google Sheet to CSV and imports into the Excel labour tracker

3. Weekly review (30 minutes) — Operations manager reviews labour cost vs budget by project, flags overruns

The key design principle: the workflow had to work on a phone from a construction site. The Google Form was mobile-optimised, had dropdowns for project codes (no typing), and could be submitted in under 5 minutes.

Phase 2: The Model

The Excel model had four sheets:

Sheet 1: Data Import

Raw timesheet data cleaned and structured with columns: date, employee, project code, hours, category, rate.

Sheet 2: Rate Matrix

Employee-level pay rates, including overtime multipliers (1.5×, 2×), allowances, and on-costs (superannuation, workers comp, payroll tax). The model calculated a loaded labour rate for each employee — the true cost to the business per hour.

Sheet 3: Project Budgets

Tender-stage budgets showing:

• Budgeted hours per project (by labour category)
• Budgeted labour cost
• Budgeted completion timeline

Sheet 4: Actual vs Budget Dashboard

The core output — a project-by-project view:

A conditional formatting rule flagged any project where actuals exceeded budget by more than 10%. An "estimate-to-complete" column projected final cost based on current burn rate.

The Overtime Insight

The model revealed a pattern that the whiteboard system had been hiding. Job A was not just over budget — it was experiencing a significant overtime premium. The project manager was scheduling weekend work to meet deadlines, but the 2× overtime rate meant each weekend hour actually cost more than an additional weekday hire would have.

We modelled the overtime vs additional hire trade-off and found that hiring one additional casual employee for 8 weeks would save approximately $3,200 in overtime premium on Job A alone. The client implemented this change and the Job A forecast variance narrowed from −$32K to −$11K.

Outcome

The client received:

• A Google Form–based daily timesheet system (digital, phone-friendly)
• An Excel labour allocation model with four integrated sheets
• A weekly dashboard showing actual vs budget labour cost per project
• A rate matrix with loaded labour costs (including all on-costs)
• A recommendation on the overtime vs additional hire trade-off

The system has been in weekly use since implementation. The operations manager now catches labour overruns in week 2 or 3 of a project rather than at completion. The estimating team uses the accumulated data to refine their tender-stage labour budgets.

Key Takeaways

1. Start with the data collection problem. A sophisticated model is useless if the data never reaches it. Design for the person entering the data, not the person reading the report.
2. Loaded labour rates change everything. The gap between an employee's hourly wage and the true cost to the business (including on-costs, overtime, and downtime) is often 40–60%. Budgeting on base wage alone guarantees margin erosion.
3. Project the overshoot. A simple "estimate-to-complete" column — current spend + (remaining hours × average burn rate) — is more useful than a historical variance report. It tells you what the outturn will be, not where you've been.
4. Overtime is an expensive habit. Weekend and after-hours work at penalty rates should be the exception, not the plan. Model the trade-off before scheduling it.