A spreadsheet is a claim like any other. Row arithmetic, where they book things, columns that aren’t theirs and the afternoon 2 errors cancelled exactly while every check read OK.
On a contract of any size the monthly payment claim arrives as a workbook. Hundreds of rows, a summary tab, a cover sheet with a payment figure on it. The temptation is to import the contractor’s numbers into your own assessment workbook and start assessing. Don’t. A spreadsheet is a claim like any other and it gets the same treatment. These are the checks I run before I trust a single cell, in the order I run them.
The contractor’s claim to date should be the previously certified figure plus this month’s claim. Test that identity on every row before you import anything. On one claim it failed on 4 drainage rows because the claim to date had been calculated as the claim amount multiplied by the tender quantity, which put $112 million on the drainage subtotal of a contract worth a fraction of that. Nobody had noticed, because nobody reads the subtotal of a section they aren’t assessing. Stage the corrected figure, record both numbers and put the query to the contractor in writing.
The contractor’s previously certified figure and yours will often differ on one section. Before treating it as an error, find out where each side books variations and cost fluctuation. A contractor who carries both under the dayworks section will disagree with an assessment workbook that carries them on their own tabs, line by line and agree in total. Reconcile in total first. Then chase only the differences that survive.
A contractor’s claim template often has “Engineer certified this month” columns and a cover sheet payment block built off them. Whatever sits there is left over from an earlier cycle, sometimes last month’s certification, sometimes nothing recognisable. Ignore those columns and everything computed from them, which includes the contractor’s own variance column and the whole cover sheet. None of it is a defect to raise. The claim is the claimed quantity and the claimed amount. The certification is yours.
This one cost me an afternoon and it is the reason I check components rather than totals. Rolling a claim from one month to the next in an assessment workbook, a month’s certified quantities were left in the “this month” column after being archived, so the month was counted twice in the total certified to date. The same rollover activated a cost fluctuation row whose formula, with its multipliers blank, returned the negative of the same figure. Plus $1.67 million in one place, minus $1.67 million in another. Every check on the checks tab read OK, because both sides of every check moved together.
The rule that came out of it. After any rollover, total certified to date must equal what it was last month plus what was certified last month, checked from the components, not from a check cell. And the escalation row has to reconcile against the contractor’s own cost fluctuation sheet to the cent before the month is trusted.
Where an escalation month can’t be certified yet, because the indices are provisional or the contractor hasn’t submitted the month, it is deferred. It is not waived and it is not zero. The record for that month says which month it is, that it is deferred, which multipliers were used for the interim figure and what triggers the true up. Never certify escalation for a held month. Never let a held month silently disappear from the cumulative. The contractor is entitled to it when the indices land. The Principal is entitled to the negative months the same way. More on payment claim traps →
File names drift. “Claim 11 July” gets copied to start Claim 12 and nobody renames it. The claim number cell inside the workbook and the month header it drives are the truth. Before archiving anything, check the target columns are empty. If they aren’t, stop, because someone has already rolled this month and you are about to do it twice.
A spreadsheet opened by a tool that doesn’t recalculate on load shows you the cached values from the last save, which look exactly like live numbers. Every check will read whatever it read last time. Force the recalculation, then read. If your tool can’t evaluate a function the workbook uses, it will pass the stale value through without telling you. Know what your tool can’t do before you rely on what it shows.
The contractor’s workbook is evidence of the contractor’s position. It is imported as claimed quantities and claimed amounts only, after the arithmetic test. Nothing in the assessment rests on a number I haven’t recomputed. The assessment workbook has a checks tab. The checks tab is checked too, because the one time it mattered most it read OK. Claim assessment →
If you want a claim workbook checked before the schedule goes out, send me the contractor’s file, the schedule of prices and last month’s certification. Claim assessment →
Common questions
Only the claimed quantities and claimed amounts, after testing on every row that claim to date equals previously certified plus this claim. Any column the contractor has filled in on the Engineer’s behalf is ignored.
Not necessarily. Find out where each side books variations and cost fluctuation first. Two workbooks that carry them in different places disagree line by line and agree in total. Reconcile in total, then chase what survives.
A cost fluctuation month that cannot be certified yet, usually because the indices are provisional or the month has not been submitted. It is held, not waived. The record states the month, the interim multipliers and what triggers the true up. The cumulative still carries it.
Yes. If 2 errors move both sides of a check by the same amount, the check reads OK. Check the components, not just the check cell. Reconcile anything that matters against an independent source to the cent.
It doesn’t. That is the point. File names drift when a workbook is copied to start the next claim. The claim number recorded inside the workbook is the truth. Confirm it before archiving or importing anything.
Have a specific question
This page is general guidance. For a question about your own contract, use the free tool or get in touch directly.