The Material Line Missed — Which of Three Things Did It?

What it does

One worksheet, Material. It takes a closed job's field shingle — squares and price per square, what you bid and what you actually bought — and splits the difference into three things that feel identical on a bank statement and have three different fixes: price, the roof, and dead stock.

The log is rows 7 through 26, twenty jobs. Columns A through H are yellow: that is where you type. Columns I through O are gray: they work themselves out, and you never type in them. The answer block sits at rows 30 through 38 — C30 is the guard, and C31 through C38 are the answers, green except for the two gray checking cells C30 and C36. Row 3 is the banner that says the same thing in one line. Rows 40 through 48 are the closing prose, each one merged across A:O.

The eight yellow columns:

Column D is the trap. D and F have to measure the same material or the price miss is fiction. F is field shingle off the invoice, so D is the field shingle price per square you bid at — the one on the written quote behind that bid. Do not lift it off the takeoff sheet's field material cell (M10-01, B9): that cell is shingle and underlayment, so it reads high, and every job would print a large, believable price miss that never happened. Squares (column C) do come straight off the takeoff; the price does not.

Column E is gross — every square the invoices billed you for, before any return. Squares that rode back to the branch stay in E. The leftover goes in G and the money that came back goes in H, and that pair is how the sheet tells a square that went on the roof from one that went home on the truck. Net a return out of E instead and nothing objects: take the 1.5 returned squares off the 30 in row 7 of the shipped example and the guard still reads 0, C36 still says the three add up, the whole miss drops from $936.00 to $744.00 and the roof's leg falls from $244.00 to $56.50 — less than a quarter of the real number — while dead stock does not move at all. A return gets counted once, as the credit in H; never a second time by shrinking E.

Column G is in squares, and the debrief counts bundles. M10-02 has the foreman write down bundles left over at the job debrief. Divide by your bundles per square (M10-01, B8 — check the wrapper, products differ) before the number goes in column G. Carried across as bundles it is roughly three times too big, it inflates dead stock, and the guard cannot see it: 4.5 typed as squares is still a legal-looking row.

The seven gray columns, all in dollars and cents, all wrapped in =IF($A7="","",…) so an empty row stays empty:

L + M = K on every row, to the penny. That is plain price/quantity variance and it is exact by construction, not by luck: E(F−D) + (E−C)D = EF − CD. M then splits again — the leftover squares priced at what you BID (G×D) is the dead stock share, and O is what is left, which is roof the bid did not carry. So price + roof + dead stock = the whole miss, exactly, and C36 subtracts the three from C32 and prints "Yes - price + roof + dead stock = the whole miss" when they land on zero.

Column N is deliberately not one of the three, and it is the second dead-stock number. The split's dead-stock leg (C35) prices the leftover squares at what you BID, because that is what makes the three add back to the whole miss. N prices the same squares at what you actually PAID and takes off the credit the branch actually gave back — what the leftover took out of your pocket. C37 totals it, and it is labelled "What the leftover cost you $" so the two never read as the same number. On the shipped example they are $614 and $314.50, and both are right.

How to use it

1. One row per closed job, in rows 7 to 26. Type A and B, then C off the takeoff sheet you bid the job on (Module 10, Play 1) and D off the written quote behind that bid — see the column D note above; the takeoff's own material cell carries underlayment and cannot be used here. Then E and F off the invoices for that job — E is every square they billed you for before any return, sent-back squares included. Field shingle only, in squares — that is most of the money and all of the argument.
2. G is the leftover in squares, converted from the debrief's bundles. H is the credit that actually landed, not the credit you were promised. If the pallet is sitting in your yard until the color is dead, H is 0.
3. Watch C30 first. It counts every problem the sheet can see — problems, not rows, so one half-typed job can raise it by more than 1 — and it only ever adds. While it reads anything but 0, C31 through C38 all print "Fix the rows that don't line up" instead of a number — guard first, then empty-form, then the number.
4. Read C32 to C35: the whole miss, then how much of it was price, the roof and dead stock. C36 is the arithmetic check on those three. C37 is what that leftover actually cost you after the credit.
5. C38 is the headline — it names the biggest of the three by size and points at the fix: price goes to Play 3, the roof goes to Module 10, Play 2, dead stock goes to Play 1 and Play 6. It compares sizes and ignores the sign, so a large minus number can be the headline too, and a tie goes to price. When all three come out at zero it says so instead, and sends you nowhere.
6. Run it on closed jobs at the monthly sitting the job report card already uses (Module 6, Play 19). Read ten jobs before you change anything.

The example

The four yellow rows shipped in the file are an EXAMPLE — select A7:H10, press Delete, then type your own jobs in. An example row you leave behind keeps feeding every answer below it. 14 Oak St (bid 28 squares at $125, bought 30 at $128, 1.5 left over, $90 credited), 9 Mill Rd, 301 Bridge St and 7 Larch Ct. Row 7 works out by hand: bid $3,500, bought $3,840, whole miss $340; price miss 30 × $3 = $90; quantity miss 2 × $125 = $250; and $90 + $250 = $340. The dead stock share is 1.5 × $125 = $187.50 and the roof is $250 − $187.50 = $62.50, so $90 + $62.50 + $187.50 = $340 again. Across the four rows C32 reads $936, made of $78 of price, $244 of roof and $614 of dead stock — which is why C38 says dead stock is the biggest. C37 reads $314.50: what that leftover actually cost after credits.

The guard, term by term

C30 is one cell and one formula. It counts problems, not rows — a job name with nothing else typed next to it raises it by four, one for each missing number:

1. A job name with a missing number anywhere in C, D, E or F.
2. Anything typed in C through H on a row with no job name.
3. Squares left over greater than squares bought (SUMPRODUCT on G > E), on a row where squares bought is a positive number.
4. A zero or a minus number where only a positive one can be true: squares bid (C), squares bought (E), or either price per square (D and F), on a named row.
5. A minus number in G or H on a named row.
6. Credit in H on a row where G is blank or exactly zero.
7. A credit bigger than the leftover was worth — column N goes minus on a row that has leftover and a real price paid in F.
8. A word typed where a number belongs, in C, D, E, F or H.
9. A named row whose gray formulas were cleared or deleted — any one of the seven gray cells I through O coming back empty on a row that has a job name. A cleared gray cell does not blank an answer; it quietly moves one. While this term watched column K alone, clearing N7 on the shipped example left the guard at 0 and slid C37 from $314.50 to $212.50 with every other cell still looking right. All seven are watched now.
10. Anything at all on rows 27 and 28, the two rows past the block.

Three terms are gated so one typo can only be counted once. Term 3 only runs where E is a positive number, because a blank or a zero in E is already term 1's or term 4's — before that gate, blanking E on a row with leftover raised the count by 2. Term 7 only runs where F is a positive number, for the same reason: a blank or zero F makes N minus by itself. And term 6 fires on a G of exactly zero, not on any G of zero or less, so a minus leftover is only term 5's. There is no separate term for a word in G: term 3 has it, because a word sorts above every number. Every case above was fired on its own against an otherwise clean sheet and raised the count by exactly 1, and every legal row that must be left alone — a zero leftover, a zero credit, a credit worth exactly what the leftover was — was fired too and kept it at 0: no term is dead, and no single typo is counted twice.

What the guard cannot catch, and it is worth knowing: a wrong but believable price in column F. Type $128 where the invoice said $182 and every cell recalculates happily and lies to you. Same with squares off by one, a delivery you forgot to add into E, a return netted out of E instead of taken as a credit in H, bundles typed into G as if they were squares, and a bid price in D lifted off a sheet that priced more than the shingle. The guard checks the shape of a row, not whether it is true — the invoice is the truth.

Adding rows

Type into the empty yellow rows between row 7 and row 26. The gray columns I through O are already sitting on every one of those rows and start working the moment you type a job name, so there is nothing to drag down — and never clear them: clear any one of the seven on a named row and the guard counts it, because that row is still a job and part of its money has stopped reaching the totals. Do not type on rows 27 or 28; the guard counts that as an error on purpose, because anything down there is invisible to the totals.

What it does not do

It does not set your waste percentage — Module 10, Play 2 owns that, and this sheet never prints one. It does not price the job (Module 10, Play 1) and it does not hand a fourth number to the job-cost sheet. It does not replace the job report card's monthly look at the material line (Module 6, Play 19); it tells you which of three things moved that line. Its material dollars are field shingle only, so on the same job they read lower than the report card's material line, which takes everything the supplier invoiced. That is on purpose — do not try to reconcile the two.