Overtime & Premium Hours Tracker Spreadsheet | Shift Premium Cost Excel Google Sheets Template | Labour Cost Analysis

⏱ OVERTIME & PREMIUM HOURS TRACKER — EXCEL & GOOGLE SHEETS

The overtime bill is the wrong number.

Somebody was always going to work that hour. The standard rate for it was not a
decision anybody made this week. The only part that was extra is the uplift — a
third on top, a half on top, double, two and a quarter for a call-out.

This file totals the uplift and nothing else, and then asks the question nobody
asks: was that hour worth buying at that price?

---

EIGHT LINKED TABS

· Start Here — what each tab expects and where to type.
· Setup — two numbers, and three short lists you name yourself.
· Hours — one row per week, per department, per kind of hour.
· By Premium Type — what each kind of hour makes, and where it stops making anything.
· Why It Happened — the hours you chose against the hours that happened to you.
· By Department — which parts of the business live on premium hours.
· The Tipping Point — the tab this file exists for.
· Overview — the whole year on one screen.

---

FROM THE WORKED YEAR INSIDE THE FILE

Hours worked 164,956
Premium hours 30,383
Share of everything 18.4%
Which is this many extra people 15.4

The wage bill $3,802,622
At standard rates $3,414,196
THE PREMIUM $388,426
Share of the wage bill 10.2%

Hours worked above the line 14,371
Share of your premium hours 47.3%
WHAT THOSE HOURS LOST $119,188

---

+34.5%. +13.2%. +0.1%. −20.0%. −37.0%. −54.2%.

Standard hours, weekday overtime, Saturday, Sunday and nights, bank holiday,
called out. The same people doing the same work, in the same building, and six
completely different answers to whether it was worth doing.

Saturday overtime in the worked year earned $239,808 and left $359 of it.
Break-even, to within a rounding error, and nobody in the building knew.

---

THE TIPPING POINT

An hour is worth what it produces. Divide that by what a standard hour costs you
and you have the highest multiple you can pay for it and still be ahead.

In the worked laundry that line runs from 1.36x in maintenance to 1.60x in
finishing. Time and a half sits almost exactly on it — which is why the same
Saturday hour makes 6.0% in one department and loses 10.1% in another.

The file works out every combination of department and premium type before you
have logged a single hour, and then counts how many hours you actually worked
above the line.

14,371 of them. 47.3% of every premium hour in the year, $119,188 down after
everything they earned had been counted.

---

68.5% OF IT WAS NOT A DECISION ANYBODY MADE

Against each reason in Setup there is one word: Planned, Reactive or Standard.
It is the only judgement call in the file and it does more work than anything
else in it.

Sickness. A vacancy nobody filled. A machine down for a fortnight. A job that
overran. Two thirds of the premium bill happened to the business rather than
being chosen by it — and an hour nobody chose cost 1.19x an hour somebody did,
because emergencies happen at the times of the week you pay the most for.

That is worth knowing before anybody promises to cut the overtime bill.

---

ONE DEPARTMENT, TEN PER CENT OF THE HOURS, HALF OF THE LOSS

By Department shows what share of each part of the business runs on premium
hours. In the worked year it goes from 13.3% in one department to 35.3% in
another — and the one on 35.3% has 7.4% left after wages against 31.9% in the
department next to it.

Same trade, same building, same year.

---

WHAT YOU ACTUALLY TYPE

Five things per row: the week, the department, the kind of hour, why it
happened, and the hours. Thirty rows a week off a payroll report.

Every rate comes from Setup, so changing one of them reprices the whole year.
Every row splits itself into what the hours cost at standard rates and the
uplift, and says on the row whether that hour was above or below the line for
that department.

---

WHAT YOU GET

Two workbooks and a guide. One workbook has the year above already filled in, so
you can see what every column expects before you touch anything. The second is
the same file completely blank — same eight tabs, same formulas — ready for your
own payroll from the first row. The guide is a plain-English PDF.

---

EXCEL AND GOOGLE SHEETS

Built as .xlsx. Excel 2016 or newer on Windows or Mac, and Google Sheets through
File → Import → Upload. LibreOffice too. No macros, no add-ons, no account to
create, nothing to install, no subscription. You download a file and it is yours.

---

QUESTIONS PEOPLE ACTUALLY ASK

We do not have departments.
Use whatever your business divides into — sites, shifts, crews, vehicles, wards,
lines. One works perfectly well if that is what you have.

I do not know what an hour is worth to us.
Start with the revenue that hour carries, or what it would cost you to buy the
same hour from an agency. It is the harder of the two numbers and it is the one
worth arguing about internally.

Our overtime is time and a third, not time and a half.
Type whatever your multiples are. Ten slots, named and priced by you.

Is this only for shift businesses?
No. It works anywhere some hours cost more than others — a laundry, a kitchen, a
garage, a care home, a warehouse, a contractor, a print works.

Does it hold staff names?
No. Hours go in by department and by kind of hour, never by person. There is no
personal data in the file at all.

---

WHAT THIS IS NOT

Not a rota, a scheduler, a time clock or a payroll system. Not accounting,
bookkeeping or tax software. Not connected to anything — nothing syncs, nothing
logs in. It does not know your overtime agreements, your working-time rules or
your award rates. Not employment, tax or legal advice.

It will not stop the machine breaking down. It will tell you what the last hour
of the week costs, which of your hours stopped paying for themselves, and in
which part of the business.

---

Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the
buyer protection are all handled by Etsy. I never ask for access to anything —
no password, no payroll login, no bank feed. You download a file; that is the
whole transaction.

The example business, its departments and its rates are invented. The arithmetic
behind every figure is not.