Pay by the Hour and You Bought the Day. Pay by the Square and You Bought the Roof.

What it does

One worksheet, Crew Pay. It takes one roof you already closed and prices the labor on it twice — once paid by the hour with your burden on top, once paid by the square with the extras added — and then prints the line the totals hide: what one more day on that roof costs you under each deal.

It is a one-job sheet, not a log: thirteen numbers, one Y-or-N switch, and an answer block. Yellow cells (rows 5 to 10, 13 to 16, 19 to 22) are yours. Gray cells (rows 25 to 31) work themselves out. The green block at rows 34 to 37 is the drag line, and A40 is the headline sentence. Column D never computes anything — it says where each number comes from.

The yellow cells

B16 is percent-formatted, so type 30, not 0.30. In Excel and in LibreOffice, 30 typed into a percent cell is 30%. A literal 30 — pasted in, or typed after somebody reformats the cell as a plain number — is 3,000% and the guard catches it: anything above 1 (100%) or below 0 is refused. Your comp share comes off Module 11, Play 3's sheet; the tax half is your accountant's number.

B10 decides where burden lands, and it is the cell people get wrong. Y means the crew is on your payroll — then paying them by the square still costs you payroll taxes and comp, so B26 carries your burden onto the square side too and the comparison is honest. N means a sub crew: his price is his price, no burden rides on it, and the square side drops by that percentage. Flip the shipped example from Y to N and the square side falls from $3,094.00 to $2,380.00, which is the whole difference between "should I pay my own men by the square?" and "should I sub this roof out?" There is no dropdown on B10 — the guard is what holds it, and anything but Y or N stops every answer below.

B9 can be less than B8 only in your imagination, and the guard says so. A job that beat the schedule is real, but this sheet is built to price the day that ran long; a B9 under B8 is nearly always the two cells typed into each other's boxes.

The gray cells

Every one of them is wrapped in =IF($B$25>0,"Fix the yellow cells that don't line up",…), so while the guard reads anything but 0 the whole answer block says that instead of printing a number.

Row 28 is the same in both columns on purpose. That is the entire point of the sheet: by the square, the roof pays what it pays whether it takes two days or four. Row 27 moves and row 28 does not, and the difference between those two behaviors is who carries the slow day.

The extra-layer term is B5*(B6-1)*B20, not B5*B6*B20. The standard square rate in B19 already covers taking one layer off. A one-layer roof (B6 = 1) adds nothing; a two-layer roof adds the extra rate once across every square. B6 below 1 is refused — a roof with no layers is not a roof.

The example, worked by hand

The file ships filled in: a 28-square, two-layer roof with 4 sheets of decking, priced at 2 days, finished in 3, four men on payroll at 9 hours and $24 an hour with 30% burden, against a $65 square rate, $15 a square for the second layer and $35 a sheet.

• Hourly at planned days: 4 × 9 × 2 × $24 × 1.30 = $2,246.40. At actual days: 4 × 9 × 3 × $24 × 1.30 = $3,369.60.
• By the square: 28 × $65 = $1,820, plus 28 × 1 × $15 = $420, plus 4 × $35 = $140, = $2,380, and because the crew is on payroll, × 1.30 = $3,094.00 — in both columns.
• Cost per square: $80.23 hourly at planned days, $120.34 at actual, $110.50 by the square either way.
• The gap: −$847.60 at planned days (hourly would have been cheaper if the job had run on schedule), +$275.60 at actual days.
• The drag line: one more day costs $1,123.20 hourly and $0 by the square; the job ran 1 day long, so the slow day cost $1,123.20.
• A40 reads: By the square was cheaper on this job by $275.60. The slow days alone cost you $1,123.20 paid hourly.

That pair of numbers is the argument. On the day the job was priced, hourly wins by $847.60. On the day it actually ran, it loses — and one slow day was worth more than the whole gap either way. Delete the example before you use the sheet (select the yellow cells and press Delete), because an example left in the boxes is a decision made on somebody else's roof.

The guard, term by term

B25 is one cell and one formula, and it counts problems, not cells. It fires on:

1. A word or a blank where a number belongs — any of B5, B6, B7, B8, B9, B13, B14, B15, B16, B19, B20, B21, B22. One term each, thirteen in all.
2. A zero or a minus where only a positive can be true — squares (B5), days planned (B8), days actual (B9), men (B13), hours (B14), wage (B15), square rate (B19).
3. Layers below 1 (B6), and a minus in decking sheets (B7), extra-layer rate (B20), decking rate (B21) or other extras (B22). Zero is legal in all four — most roofs have no decking and no extras.
4. Burden outside 0% to 100% (B16) — the 3,000% typo above.
5. Days actual below days planned (B9 < B8).
6. Anything but Y or N in B10.

Each case above was fired on its own against an otherwise clean sheet and raised the count by exactly 1, and the clean sheet reads 0: blanking B5, setting B9 below B8, typing a word into B15, putting a literal 30 in B16, zeroing B6 and putting a minus in B22 each stopped the answer block with "Fix the yellow cells that don't line up," while Y→N and a job that finished on time both left the guard at 0 and simply changed the answer.

What the guard cannot catch: a wrong but believable number. A wage that left out the foreman, a square rate off last year's list, drive time you pay but didn't put in B14, or the days-planned cell holding what you wish you had priced instead of what you priced. It checks the shape of a number, not whether it is true — the timesheets and the signed price list are the truth.

What it does not do

It does not price the whole crew seat. Insurance, trucks, tools, callbacks and the office time a payroll crew eats belong to the crew-model sheet (Module 12, Play 1) — that one asks whose crew, this one asks which pay deal for the same men. It does not set your square rate; Play 2 does. It does not tell you whether a man is an employee or a sub, and it never will — that test is federal and state law and belongs to your attorney or accountant. And it prices one roof: run it again on a job that went badly before you change how you pay anybody.