Subcontractor progress payment tracking table Excel template

Actual file previews

The images below are the actual data entry and executive summary pages of the workbook to be downloaded.

Step by step usage

1. Examine the example and prepare the blank file

   Each contract work item is one line. Use the same unit in quantity fields. Prior progress payment is the total gross amount approved through previous periods; It is not a bank payment. Make a file copy in the new reporting period, move the previous cumulative amount to the previous progress payment, and update the completed amount. Use the yellow input fields on the tracking page; Do not change the blue formula fields. The sample sheet is filled with fictitious records and does not count towards the actual work total.

2. Write actual records in the yellow fields

   The cumulative gross amount is the total quantity completed × unit price. Period difference is the subtraction of the previous progress payment from this amount. Completion is the amount realized / contract quantity. If the previous progress payment exceeds the new total or the actual amount exceeds the contract amount, a control warning appears. Update the revised contract scope with approved information; Do not change the amount to hide the difference. An empty mandatory field is distinguished from a real zero. Correct missing or incorrect records in the control column; Since these lines are excluded from the summary, do not use the total as a final report until the problem is resolved.

3. Fix control alerts and review summary

   Match period difference with field approval. Reconcile previous progress total.

What does this Excel file solve?

Track contract items by cumulative completed quantity, unit price, and previous progress payment. Free Excel preview file showing the period difference. Each contract work item is one line. Use the same unit in quantity fields. Prior progress payment is the total gross amount approved through previous periods; It is not a bank payment. Make a file copy in the new reporting period, move the previous cumulative amount to the previous progress payment, and update the completed amount. Use the yellow input fields on the tracking page; Do not change the blue formula fields. The sample sheet is filled with fictitious records and does not count towards the actual work total.

What's inside the file?

Cumulative gross amount, Gross difference for this period, Quantity completion rate. The file contains sample rows, canned formulas, data validation fields, risk colorings, and executive summary.

Calculation and control logic

The cumulative gross amount is the total quantity completed × unit price. Period difference is the subtraction of the previous progress payment from this amount. Completion is the amount realized / contract quantity. If the previous progress payment exceeds the new total or the actual amount exceeds the contract amount, a control warning appears. Update the revised contract scope with approved information; Do not change the amount to hide the difference. An empty mandatory field is distinguished from a real zero. Correct missing or incorrect records in the control column; Since these lines are excluded from the summary, do not use the total as a final report until the problem is resolved.

Worked example: Subcontractor progress payment advance tracking table: calculation example

The contract amount is 100 m². Completed 40 m², price 500 TL/m². Previous gross progress payment is 15,000 TL. Result: Cumulative 20,000 TL, this period difference is 5,000 TL and completion is 40%.

Decisions that can be taken as a result of this

Match period difference with field approval. Reconcile previous progress total

Common usage mistakes

Using the bank payment instead of the previous progress payment. Re-adding the same work item in the same file every period. Using the summary total as the final result without correcting the lines with control warnings

Limits and proper use

It is not an official progress payment or net payable amount calculation. There is no VAT, withholding tax, guarantee, advance deduction, price difference and contract deductions. Complete the report with contract and expert control. The file is prepared for 150 record lines. The parent summary includes all valid rows, regardless of filters. When capacity is full, open a new period copy; additional rows require the summary and validation ranges to be updated as well. There are no macros, external data connections or automatic notifications.

Compatibility and quality control

Two pages of this file were visually inspected; Formulas were recalculated and checked in empty, sample and incorrect entry scenarios. There was no separate device testing of the Excel desktop application. .xlsx without macro; formulas and drop-down lists are included in the file. The tracking page includes 150 preset record lines and a standalone Sample page. Prepared for desktop Excel; Check views and validations in other spreadsheet applications.

Scope of this file

This independent file has been prepared for the subcontractor progress payment preliminary tracking table. It's not that Shiftmate automates this industry workflow; The application focuses on team, shift and timesheet management.

Relevant official and technical resources

• Microsoft: SUMIFS function

Frequently asked questions