--- title: How do I roll each monthly pay application forward from the last certified one and catch carry-forward and retainage errors with AI? | AEC Agents url: https://aecagents.si/use-cases/subcontractor-pay-app-roll-forward description: Small trade contractors often bill on a schedule of values in a workbook that gets copied month to month, and the errors come from the carry-forward: last month's submitted numbers rolled forward instead of what the general contractor actually certified, a change order billed before it was approved, retainage figured on the wrong base. Have Copilot in Excel or Gemini in Google Sheets build the workbook so every application starts from a Certified tab, with approved change orders as their own lines and retainage per line at the rate you enter from your subcontract, and have Claude or ChatGPT compare what you submitted with the certified copy that comes back. Your contract terms, disputes and notices stay with your PM, accountant and attorney. last_modified: 2026-11-11 source: AEC Agents (free, open workflows; not engineering advice) --- [AI assists](https://aecagents.si/use-cases/subcontractor-pay-app-roll-forward#honesty-labels)Workflow guide · not independently tested # How do I roll each monthly pay application forward from the last certified one and catch carry-forward and retainage errors with AI? Small trade contractors often bill on a schedule of values in a workbook that gets copied month to month, and the errors come from the carry-forward: last month's submitted numbers rolled forward instead of what the general contractor actually certified, a change order billed before it was approved, retainage figured on the wrong base. Have Copilot in Excel or Gemini in Google Sheets build the workbook so every application starts from a Certified tab, with approved change orders as their own lines and retainage per line at the rate you enter from your subcontract, and have Claude or ChatGPT compare what you submitted with the certified copy that comes back. Your contract terms, disputes and notices stay with your PM, accountant and attorney. [Subcontractor](https://aecagents.si/roles/subcontractor) · Trade contracting · Microsoft Excel · Microsoft Copilot · Google Sheets · Claude · ChatGPT Content updated 11 November 2026 · Tool links listed below; vendor features and pricing change, so confirm with the vendor ## Short answer Use Copilot in Excel or Gemini in Google Sheets, then Claude or ChatGPT: Copilot or Gemini builds the monthly billing workbook so each application starts from the last certified amounts, with approved change orders as their own lines and retainage tracked by period; Claude or ChatGPT reads the GC's certified copy and lists every line where it differs from what you submitted. Small trade contractors often bill on a schedule of values in a workbook that gets copied month to month, and the errors come from the carry-forward: last month's submitted numbers rolled forward instead of what the general contractor actually certified, a change order billed before it was approved, retainage figured on the wrong base. Have Copilot in Excel or Gemini in Google Sheets build the workbook so every application starts from a Certified tab, with approved change orders as their own lines and retainage per line at the rate you enter from your subcontract, and have Claude or ChatGPT compare what you submitted with the certified copy that comes back. Your contract terms, disputes and notices stay with your PM, accountant and attorney. A person still checks: every amount against your subcontract, approved change orders and the certified copy; the billing form's arithmetic before it is signed; and any dispute, retainage release or notice question with your project manager, accountant or attorney. ## AI tie-in **Tool:** Copilot in Excel or Gemini in Google Sheets, then Claude or ChatGPT Copilot or Gemini builds the monthly billing workbook so each application starts from the last certified amounts, with approved change orders as their own lines and retainage tracked by period; Claude or ChatGPT reads the GC's certified copy and lists every line where it differs from what you submitted. ### Prompt 1 ``` I bill monthly on a schedule of values. Here are my column headers [paste: line number, description, scheduled value, previous certified to date, this period work, stored materials, total completed, percent complete, balance to finish, retainage held]. Write Excel formulas (or a step-by-step Copilot plan) that: carry 'previous' from the CERTIFIED tab of last month, not from what I submitted; add approved change orders from a CO tab as separate lines with their own numbers; compute totals, percent complete and balance to finish per line; compute retainage per line from the rate I type in Settings (I will enter the rate my subcontract states); and add CHECK flags for any line over 100 percent complete, any negative balance, and any change order not marked approved. Use only my data. ``` ### Prompt 2 ``` Here is the application I submitted [paste or upload] and the certified copy the general contractor returned [paste or upload]. Compare them line by line and list every line where the scheduled value, previous amount, this period amount, stored materials, retainage or totals differ, with both values side by side and the difference. Then list any change order on my application that is missing from the certified copy. Do not judge whether a reduction is justified and do not draft anything about withholding or lien rights. ``` **Human check:** Every amount against your subcontract, approved change orders and the certified copy; the billing form's arithmetic before it is signed; and any dispute, retainage release or notice question with your project manager, accountant or attorney. ## The problem Roll each monthly pay application forward from the last certified one and catch carry-forward and retainage errors with AI. This is a common, time-consuming task for a subcontractor. ## What this workflow does Small trade contractors often bill on a schedule of values in a workbook that gets copied month to month, and the errors come from the carry-forward: last month's submitted numbers rolled forward instead of what the general contractor actually certified, a change order billed before it was approved, retainage figured on the wrong base. Have Copilot in Excel or Gemini in Google Sheets build the workbook so every application starts from a Certified tab, with approved change orders as their own lines and retainage per line at the rate you enter from your subcontract, and have Claude or ChatGPT compare what you submitted with the certified copy that comes back. Your contract terms, disputes and notices stay with your PM, accountant and attorney. **How it works:** AI drafts or analyzes. A person operates the tools, checks the work, and takes action. **Your checkpoint:** The workbook does the arithmetic and the comparison; it does not decide what you are owed, whether a GC reduction is justified or when retainage must be released. Check every amount against your subcontract, approved change orders and the certified copy, and take disputes, notices and lien questions to your project manager, accountant or attorney. Not legal or accounting advice. ## Before you start - Access to the named tool; confirm license, version and cost with the vendor. - A small, non-confidential sample to try the workflow on first. - A qualified person to review the output before it is used. ### Tools and access - [Microsoft Excel](https://www.microsoft.com/microsoft-365/excel) - [Microsoft Copilot](https://www.microsoft.com/microsoft-365/copilot) - [Google Sheets](https://workspace.google.com/products/sheets/) - [Claude (Anthropic)](https://www.anthropic.com/claude) - [ChatGPT](https://chatgpt.com/) ## Follow the steps 1. Set up one workbook per job with tabs for the schedule of values (as agreed with the GC), Settings (retainage rate and any rate change, typed by you from your subcontract), Change Orders (number, description, amount, approved yes or no, date), one Submitted tab per month and one Certified tab per month. 2. Have Copilot in Excel or Gemini in Google Sheets write the formulas: previous to date comes from last month's Certified tab, this period is what you enter, approved change orders appear as their own lines, and totals, percent complete, balance to finish and retainage are computed per line. 3. Add CHECK flags for any line over 100 percent, any negative balance, any change order billed while not marked approved, and any line where previous to date does not equal last month's certified total to date. 4. Fill in the GC's required billing form (often a continuation sheet in the format the GC specifies) from the workbook totals, and check the form's own arithmetic against the workbook before it is signed. 5. When the certified copy comes back, upload both versions to Claude or ChatGPT and have it list every line that changed, with both values and the difference, plus any change order that was dropped. Type the certified numbers into the Certified tab so next month starts from them. 6. Keep a running retainage ledger by period from the Certified tabs, so the amount held to date, and what is due at release, is one number you can defend with the GC's own certified copies. ## Try a small example Replace the bracketed fields with your own project details. No tested sample run is published for this workflow yet. ### Sample input ``` Fill in the bracketed [fields] in the text below with your project data. ``` ### Starter prompt ``` I bill monthly on a schedule of values. Here are my column headers [paste: line number, description, scheduled value, previous certified to date, this period work, stored materials, total completed, percent complete, balance to finish, retainage held]. Write Excel formulas (or a step-by-step Copilot plan) that: carry 'previous' from the CERTIFIED tab of last month, not from what I submitted; add approved change orders from a CO tab as separate lines with their own numbers; compute totals, percent complete and balance to finish per line; compute retainage per line from the rate I type in Settings (I will enter the rate my subcontract states); and add CHECK flags for any line over 100 percent complete, any negative balance, and any change order not marked approved. Use only my data. ``` ### Expected output ``` Not published yet. Treat any output as a draft and check it against the source documents. ``` ## Check the result - Every value, citation or quantity traces back to a source document. - Missing information is marked as missing, not filled in. ## Limits and recovery - The workbook does the arithmetic and the comparison; it does not decide what you are owed, whether a GC reduction is justified or when retainage must be released. Check every amount against your subcontract, approved change orders and the certified copy, and take disputes, notices and lien questions to your project manager, accountant or attorney. Not legal or accounting advice. - Check your organization’s data policy before sharing project files with third-party tools. **Stop or switch when:** The source cannot be verified, permissions are missing, or the result contains unsupported facts. ## Frequently asked questions How does AI help with this? Copilot in Excel or Gemini in Google Sheets, then Claude or ChatGPT: Copilot or Gemini builds the monthly billing workbook so each application starts from the last certified amounts, with approved change orders as their own lines and retainage tracked by period; Claude or ChatGPT reads the GC's certified copy and lists every line where it differs from what you submitted. A person still checks: Every amount against your subcontract, approved change orders and the certified copy; the billing form's arithmetic before it is signed; and any dispute, retainage release or notice question with your project manager, accountant or attorney. How do I how do I roll each monthly pay application forward from the last certified one and catch carry-forward and retainage errors with AI?? Set up one workbook per job with tabs for the schedule of values (as agreed with the GC), Settings (retainage rate and any rate change, typed by you from your subcontract), Change Orders (number, description, amount, approved yes or no, date), one Submitted tab per month and one Certified tab per month. Have Copilot in Excel or Gemini in Google Sheets write the formulas: previous to date comes from last month's Certified tab, this period is what you enter, approved change orders appear as their own lines, and totals, percent complete, balance to finish and retainage are computed per line. Add CHECK flags for any line over 100 percent, any negative balance, any change order billed while not marked approved, and any line where previous to date does not equal last month's certified total to date. The full step list is above. Does this use an AI agent or regular software? It is labeled AI assists: AI drafts or analyzes. A person operates the tools, checks the work, and takes action. What tools do I need? Microsoft Excel, Microsoft Copilot, Google Sheets, Claude, ChatGPT. Confirm license, version and cost with the vendor, and try it on a small non-confidential sample first. Has this workflow been tested? Workflow guide · not independently tested. Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written. ## Where to go next - [How do I turn signed T&M tags into a priced change order request with AI?](https://aecagents.si/use-cases/tm-tags-to-change-order-request) - [How do I log backcharges from the general contractor against notice dates and backup, and draft responses with AI?](https://aecagents.si/use-cases/subcontractor-backcharge-notice-log) - [How do I document material price escalation against my bid quotes with AI?](https://aecagents.si/use-cases/material-price-escalation-vs-bid-quotes) - [All Subcontractor workflows](https://aecagents.si/roles/subcontractor) - [All Schedules, submittals and project controls workflows](https://aecagents.si/tasks/project-controls) ## Evidence and maintenance Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written. - [Microsoft Excel (vendor site)](https://www.microsoft.com/microsoft-365/excel) - [Microsoft Copilot (vendor site)](https://www.microsoft.com/microsoft-365/copilot) - [Google Sheets (vendor site)](https://workspace.google.com/products/sheets/) - [Claude (Anthropic) (vendor site)](https://www.anthropic.com/claude) - [ChatGPT (vendor site)](https://chatgpt.com/) ## Put it to work Start with a small sample and keep the original record open while you verify the result. ## Know what does the work. Agent executes : An AI agent calls connected tools to carry out bounded actions. You set permissions and review the result. AI assists : AI drafts or analyzes. A person operates the tools, checks the work, and takes action. Conventional automation : Rules or specialist software perform the task. No AI agent is required.