Retainage tracking spreadsheet: balance, readiness and next action

Separate the money question from the readiness question

A spreadsheet can accurately total retained amounts while leaving the office unable to act. One job may need an internal closeout record, another may have a release request awaiting acknowledgment and a third may have money released into billing but not yet collected. A single column labeled “retainage due” hides those distinctions.

Give the tracker three jobs: reconcile the financial control amounts, identify the reviewed basis for the current status and assign the next specific action. The appropriate project and finance representatives determine the actual requirements and treatment. The sheet should make their sources and decisions visible rather than manufacture a release condition from a project name or completion percentage.

Download the free blank CSV near the top of this page. It requires no signup and provides starting columns for the control record. The populated examples are fictional teaching data, not a customer ledger, legal determination or substitute for the financial system your team designates as authoritative. Adapt the fields and formulas with the responsible reviewer before using them on a real project.

Start from reconciled records and reviewed requirements

Identify the project, contract reference, billing period, currency, prior held amount, current withholding and verified changes. Keep links to the relevant billing records and review source. If two exports disagree, resolve the starting difference before using the sheet to forecast collection. Otherwise, a neat total can conceal inconsistent inputs from the beginning.

AIA's G702 instructions describe a specific payment-application process that includes retainage, prior payments and the current request, with a related breakdown of the work. That illustrates the need to reconcile a management tracker to its actual billing source. This worksheet is an original control structure; it does not reproduce the licensed AIA form or establish a universal billing method.

Record the source for the project's actual release requirements and who reviewed it. Keep unresolved interpretation as a question for the appropriate person. Do not insert a default percentage, milestone or statutory deadline just because another job used it. A tracker can hold the reviewed rule and response without deciding what a contract or jurisdiction requires.

Sources: AIA Contract Documents: G702–1992 instructions and payment-application context

Read the three-job example without treating readiness as cash

Our fictional register contains Cedar Building, Harbor Works and North Yard. Their closing held balances are $20,000, $35,000 and $25,000, totaling $80,000. Each has a different next action. The amounts and project names are invented for instruction; none establishes that the corresponding money is currently due or collectible on a particular date.

Cedar also has $3,000 released into billing but not yet collected under the example's verified control records. That amount is tracked separately from its $20,000 still held. Harbor has a submitted release request whose receipt needs confirmation. North has not reached a reviewed readiness decision because the requirement source remains incomplete. A common age or balance column cannot explain all three situations.

The team should see $80,000 still held and a separate $3,000 released-uncollected amount in this example, rather than count the release as cash. Under this teaching model, the combined associated uncollected amount is $83,000. Use the definitions approved for your own control process and have the finance reviewer confirm the classification and reconciliation.

Reconcile the held and released-uncollected balances

For Cedar, assume the opening held balance is $23,000, new verified withholding is $3,000 and $6,000 has been released from the held category in the verified billing record. The closing held amount is $23,000 plus $3,000 minus $6,000, or $20,000. A request or unposted expectation is not substituted for that verified release event in this example.

The separate released-uncollected control starts at $2,000. Add the same $6,000 release and subtract $5,000 of verified cash specifically applied to that category, leaving $3,000. The release moves value between the two control categories; it does not itself reduce their combined amount. Only the appropriately verified collection in this simplified example reduces the combined uncollected total.

These formulas teach a reconciliation distinction, not a universal chart of accounts or accounting policy. The responsible finance reviewer must establish which actual records support each input, how adjustments are represented and when a change belongs in each category. Preserve the source references and avoid entering a general project payment as retainage collection without the applicable allocation evidence.

Track readiness as a reviewed decision with dependencies

Create a separate readiness field and link the actual requirement source. Identify each unresolved dependency, its responsible person and the evidence needed to resolve it. A closeout document, inspection result, commercial question or receiving acknowledgment may each require a different action. Do not mark readiness solely because a folder contains a plausible file.

For Cedar, the project manager needs to confirm which remaining packet requirements apply to the next request. Keep that question distinct from the finance team's collection follow-up on the already released $3,000. One action concerns future readiness; the other concerns an existing billing state. Combining them can send the same unhelpful reminder to two different recipients.

For North, keep the requirement source explicitly unresolved. Assign the task of obtaining the reviewed basis before presenting a release date as confirmed. A blank or unknown readiness state can be useful when it has a clear owner and next action. Replacing it with an unsupported green status makes the report easier to scan but less reliable.

Keep submitted, acknowledged and accepted distinct

Record the request reference, submitted version, destination and submission date. Preserve the relevant transmittal or receiving evidence. Harbor's fictional request is submitted, but its receipt is not yet confirmed. The next task is to locate the receiving reference or obtain an acknowledgment, not automatically to describe the balance as accepted for payment.

When a response asks for a correction, identify the exact missing item and preparer. Link the revised submission to the original request and retain the earlier version. A later acknowledgment may resolve the receiving question while leaving review or approval open. Give each status a defined meaning that the project and finance users understand consistently.

Use accepted or approved labels only for the decision actually documented under your process. Identify what the decision covers, who issued it and whether another financial or administrative step remains. A general reply saying “looks okay” should not silently become a verified release, billing entry and payment promise at the same time.

Use the blank worksheet with consistent inputs

The blank CSV includes project and currency, contract reference, period, opening held amount, new withholding, the verified release from held and the closing held amount. It also provides a separate opening released-unpaid amount, cash applied and closing released-unpaid amount. The remaining fields hold the condition source, readiness question, next action, owner and last source update.

Use one currency per control total or create a clearly defined separate reporting view. Enter numeric amounts as numbers in the spreadsheet and keep source references in their designated cells. Do not paste a narrative such as “around twenty thousand” into a formula input and let the sheet imply exact precision. Resolve or label the uncertainty with the responsible reviewer.

The CSV contains blank response cells rather than embedded financial assumptions. If you implement formulas, use the example only after confirming your own definitions and adjustment process. Preserve an opening snapshot and a change history. A useful spreadsheet should let another authorized person reproduce the total and explain why a row changed.

Give every row a next action someone can complete

Write the action as a concrete request. “Follow up retainage” is too broad. “Billing administrator to obtain the receiving reference for Harbor's current release packet” identifies the missing evidence and the person who can pursue it. Keep the requested response and follow-up date separate from any confirmed contractual or payment date.

Route document questions to the preparer and financial-record questions to the appropriate finance user. The project manager may help establish readiness without being the person who posts or allocates a payment. A shared tracker should connect those handoffs rather than force one user to guess at another department's records.

When an action is completed, record the answer and its source, then identify what remains. A received document may still need review; a corrected request may still need submission; an accepted request may still need a verified financial event. This keeps the next reminder aligned with the current blocker instead of repeating an already answered question.

Review changes without turning the ledger into a forecast promise

At a regular review, reconcile the opening amounts, new withholding, verified releases, collections and explained adjustments. Keep the still-held and released-uncollected categories separate in the summary. Ask why a large balance changed and require the relevant source before treating a movement as resolved.

Discuss readiness and next actions alongside the amounts, but label expected dates according to their basis. A team's planned request date differs from a recipient's confirmed review commitment or a verified payment event. Do not represent all three as one forecast. Record the source and any condition so another reader can interpret the date appropriately.

Measure your own operational progress: unresolved requirement sources, missing receiving confirmations, corrections returned to the preparer and financial movements reconciled to evidence. A tidier spreadsheet does not prove faster collection or establish legal entitlement. Use it to make the work visible and test whether the team can complete the next handoff with less reconstruction.

Bring the balance and the missing response together

Start a workflow pilot with a small, reconciled set of jobs. Confirm who maintains the financial inputs and who reviews the requirement and response fields. Keep a snapshot before introducing follow-up automation so a data cleanup does not appear to be a product-driven collection result.

Buildalytic's payment-follow-up offering describes checking submitted invoices and pay applications and giving the office a record of missing documents and expected dates. Use that as a concrete demo path around the ledger. Ask the demonstration to preserve the source response and route an ambiguous answer to a person, rather than turn every reply into a confirmed payment state.

Bring a redacted row, its current billing or request reference and the unresolved response. Map the path from reviewed requirement to submitted packet, receiving decision and verified receipt, identifying which decisions remain with your team. Confirm the proposed implementation scope and measure the work it actually helps complete before expanding to more projects.

Sources: Buildalytic payment follow-up