A pay application draws on three kinds of records: field tickets, change orders, and the schedule of values. Each kind is usually kept on its own, so a ticket can appear twice, a change order can lack backup, or a billed amount can differ from an approved amount. Before the application goes out, those records must agree with each other.
I built Construction Control, an Excel workbook (v1.1.2) for subcontractor pay-application review. It is macro-free and requires desktop Excel. Its 528 formulas use nine functions only: IF, ROUND, ABS, AND, OR, COUNTIF, SUMIF, ISBLANK, and SUM. Users paste data into four CSV templates for tickets, change orders, SOV main, and SOV retainage, running seven checks.
This article explains the design of those seven checks and the formula approach behind them, including why no lookup function is used. It also states the limits plainly: there is no importer, the workbook runs the checks, and the human reconciles. A separate free one-page checklist PDF exists, but it runs no checks.
Pasting records in
Records enter the workbook only by pasting, not by file import. The workbook ships with four CSV paste templates: template_tickets.csv, template_change_orders.csv, template_sov_main.csv, and template_sov_retainage.csv. The buyer fills a template from their existing spreadsheet or accounting export, then pastes the values into the workbook at marked starting cells. A paste guide (PDF) shows the exact cells, the formats, and a count check to run before trusting a paste. The workbook does not import files by itself and does not connect to any accounting system. Records that exist only on paper, in texts, or in PDFs still have to be typed once, into the templates.
About this article
This article explains how Construction Control — Subcontractor Pay Application Reconciliation Workbook runs its seven exception checks on records pasted into it, gathers flagged items in an Exception Report, and how records move through them; after this first mention, it is called "the workbook".
The formula approach
The workbook's formulas use exactly nine Excel functions: IF, ROUND, ABS, AND, OR, COUNTIF, SUMIF, ISBLANK, and SUM. There is no lookup function in the workbook. Across the seven exception checks, the formulas count occurrences, sum values by key over the pasted ranges, and test for blanks.
A worked example from the demo sheet
The workbook's Metro Tower Demo sheet includes COR-03, an approved change order for a credit of $390 (signed value −$390). Nothing is billed for it on the pay application: EX-06 compares the approved value against the billed total accumulated by SUMIF — $0 here — and flags the gap. The Exception Report is a summary table (Check ID, Review category, Count, Signed amount $, Status, Interpretation); it never lists individual change orders. COR-03 appears only inside the EX-06 row: count 1, signed amount −$390. The report's own notes state that amounts across categories overlap and must never be summed, that a missing credit is not unclaimed revenue, and that a flag means a comparison needs human attention — the workbook runs the checks, the human reconciles.
The seven checks, one by one
All seven checks run on pasted records. EX-01 Duplicate Tickets uses COUNTIF to count rows with the same Ticket ID; an ID on multiple rows is flagged. EX-02 Missing Fields blank-tests each ticket's Date, Scope, Labor hours, Loaded $/hr; a ticket missing any is flagged. EX-03 Unlinked Tickets flags a ticket with no COR ID in its Linked COR column.
EX-04 COR Without Backup uses SUMIF to total linked tickets for each change-order request; a $0 total is flagged for human review, since some legitimate credits have no field ticket, so it is not an error verdict. EX-05 Pending COR Mismatch flags a pending request whose proposed amount differs from its linked tickets' SUMIF total. EX-06 Missing from Pay App uses SUMIF to accumulate an approved request's billed total from pay-application records; the gap between approved and billed totals is flagged. EX-07 Billed ≠ Approved flags an approved request on the pay app whose billed-to-date total differs from its approved amount, over-billed included. Flagged items form one Exception Report, a summary table never listing individual change orders.
Build Notes: Four Constraints That Shaped the Design
Desktop Excel is required, and the workbook contains no VBA and no macros of any kind. That constraint keeps every calculation visible as a cell formula rather than hidden code. A reviewer can inspect a result, trace its precedents, and see exactly which cells produced it, without trusting a script.
Data enters only by pasting into one of four CSV templates: tickets, change orders, SOV main, and SOV retainage. There is no importer, no file-open dialog, and no external data connection. That limit puts the pasted ranges in fixed places, so the formulas can address the same ranges on every review.
The workbook uses 528 formulas from nine functions only: IF, ROUND, ABS, AND, OR, COUNTIF, SUMIF, ISBLANK, and SUM. It deliberately uses no lookup function: no VLOOKUP, no INDEX/MATCH, no XLOOKUP. Cross-list comparisons count and sum by key over the pasted ranges, matching by shared keys instead of pulling values from another list.
The seven checks, EX-01 through EX-07, produce flags for human review, and the Exception Report gathers them in a summary table. The workbook runs the checks and stops there. It does not decide which flag is correct, change a pasted value, or close an exception. That decision stays with the reviewer.
What the workbook does not do
The workbook does not import files by itself and does not connect to any accounting system. It compares only the records the buyer enters; it does not verify those records against the contract, owner documents, or signed tickets. It does not flag billing over 100% of scheduled value. It does not check retainage rates or G702/G703 totals. It does not flag change orders billed before approval. A pending change-order request that has already been billed may not be identified by any of the seven checks. The "Prior completed $" column on the pay-app sheet is typed in by hand. It does not file or submit the pay application. It does not approve anything, and it does not guarantee a pay application will be accepted or paid.
The workbook runs seven exception checks on pasted records, gathering flagged items in the Exception Report, a summary table never listing individual change orders. A flag needs human attention; the workbook checks, the human reconciles. The checks compare only entered records, not the contract, owner documents, or signed tickets, and do not check G702/G703 totals, retainage rates, or billing beyond the full scheduled value. It is a desktop Excel workbook that does not file or submit the pay application.
Resource mentioned in this article
The workbook described above is Construction Control — Subcontractor Pay Application Reconciliation Workbook (v1.1.2), a paid Excel workbook from Construction Control. The free guide this article accompanies is published in full at the canonical link for this post, and the one-page checklist PDF mentioned above is free and runs no checks.
Disclosure: AI tools assisted in drafting this article. The publisher reviewed and verified its contents before publication.
Top comments (0)