Overtime & Premium Hours Tracker Spreadsheet
What the uplift on premium hours actually costs, and the point at which an extra hour stops paying for itself.
The overtime bill is the wrong number. Somebody was always going to work that hour, and the standard rate for it was not a decision anybody made this week — the only part that was ever extra is the uplift on top. This file totals the uplift and nothing else, then asks the question nobody asks: was that hour worth buying at that price?
What is in it
- Every row split into what the hours cost at standard rates and the uplift paid on top of them
- Ten kinds of premium hour, named and priced by you: weekday overtime, Saturday, Sunday and nights, bank holiday, call-out
- The Tipping Point: the highest multiple you can pay for an hour in each department and still be ahead
- Every hour marked on its own row as above or below that line, and the hours above it counted up
- Why It Happened: the hours you chose against the hours that happened to you — Planned, Reactive or Standard
- By Department, plus the whole year on one screen — eight linked tabs, and changing one rate reprices all of it
Who it is for
Anywhere some hours cost more than others: a laundry, a kitchen, a garage, a care home, a warehouse, a contractor, a print works. Hours go in by department and by kind of hour, never by person, so there is no personal data in the file at all.
The worked example
In the worked year 30,383 of 164,956 hours were premium — 18.4% of everything worked, the equivalent of 15.4 extra people. The uplift on them came to $388,426, which is 10.2% of a $3,802,622 wage bill.
14,371 of those hours, 47.3% of every premium hour in the year, were worked above the line for their department and lost $119,188 once everything they earned had been counted. Saturday overtime earned $239,808 and left $359 of it, and 68.5% of the premium bill was never a decision anybody made.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Buy it on Etsy — $9
Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the buyer protection are all handled by Etsy.
A look inside the file
Questions people actually ask
How do I work out what overtime really costs, and whether the hour was worth buying?
Count only the uplift — the third, the half, the double, the two and a quarter for a call-out — because somebody was always going to work that hour at standard rates. In the worked year 30,383 premium hours out of 164,956 carried a premium of $388,426, 10.2% of a $3,802,622 wage bill. Then the file divides what an hour produces by what a standard hour costs, which gives the highest multiple you can pay and still be ahead: 14,371 hours were worked above that line and lost $119,188.
We don't have departments — is this still for us?
Use whatever your business divides into: sites, shifts, crews, vehicles, wards, lines — and one works perfectly well if that is what you have. It is not only for shift businesses either; it works anywhere some hours cost more than others, such as a laundry, a kitchen, a garage, a care home, a warehouse, a contractor or a print works.
Where do the hours come from, and what if our overtime is time and a third rather than time and a half?
Five things per row off a payroll report — the week, the department, the kind of hour, why it happened and the hours — about thirty rows a week. Every rate lives in Setup as ten slots you name and price yourself, so time and a third is fine, and changing one rate reprices the whole year. The harder number is what an hour is worth to you: start with the revenue that hour carries, or what the same hour would cost from an agency.
Can I use it in Google Sheets, and is there a subscription?
Yes — the file is built as .xlsx, so it opens in Excel 2016 or newer on Windows or Mac, goes into Google Sheets through File → Import → Upload, and works in LibreOffice. No macros, no add-ons, no account to create, nothing to install and no subscription. You download a file and it is yours. Eight linked tabs, a filled-in worked year, a blank copy with the same formulas, and a plain-English PDF guide.
Does it hold staff names or any personal data?
No — hours go in by department and by kind of hour, never by person, so there is no personal data in the file at all. It is not a rota, a scheduler, a time clock or a payroll system, it does not know your overtime agreements, working-time rules or award rates, and it is not employment, tax or legal advice. It will not stop the machine breaking down; it tells you what the last hour of the week costs and which of your hours stopped paying for themselves.
Need it built around your business?
This file assumes hourly staff, a set of uplift multiples and departments you can name. If yours is different — annualised hours, a shift allowance paid as a flat sum rather than a multiple, agency staff charged at a margin instead of a rate, or a collective agreement that changes the rate by day and by hour — I can build the same logic around how you actually work.
All other templates are listed on the Excel & Google Sheets Templates page.