Excel Trackers: Rates, Reconciliation, RA Bills

Module 6 taught you where money leaks on site: quiet rate creep, material walking off in small excesses, and bills that pay for the same work twice. Knowing the leaks is not enough — you need instruments that show them while there is still time to act. This lesson builds the three Excel trackers that do most of the work of a cost-control department: a rate and quotation tracker, a material reconciliation statement, and an RA bill tracker. All three run on the same database discipline as your takeoff sheet from the previous lesson, and downloadable formats for each are linked on this page.

Tracker 1: rates compared on landed cost, not sticker price

The most common quotation-comparison mistake is ranking suppliers by the quoted rate alone. What matters is the landed cost — what one unit costs delivered to your site, with freight, loading, and applicable taxes on a like-for-like basis.

Worked example — three cement quotations (indicative mid-2026 figures; always use your city's current quotations):

Supplier B has the lowest sticker price and the highest landed cost; supplier C has the highest sticker price and wins. On a 4,000-bag project the spread between B and C is 4,000 x 6 = Rs. 24,000 — from one line item. Your tracker should hold: material, supplier, quoted rate, basis (ex-godown or delivered), freight, GST treatment (confirm whether quotes are inclusive or exclusive — compare on the same basis), credit period, and the date. Keep old quotes in the sheet; the history is your negotiation leverage, and a month-on-month rate chart tells you when to pre-buy before an announced price increase.

Two India-specific traps your tracker must handle. First, units change by trade and region: sand and aggregate are quoted per cum in some cities, per cft in others, and per brass (100 cft) across much of Maharashtra and nearby regions. Add a landed-cost-per-standard-unit column and convert everything before comparing: 1 brass = 100 cft = 2.83 cum, so a sand quote of Rs. 4,800 per brass (indicative mid-2026) is 4,800 / 2.83 = Rs. 1,696 per cum. Compare that figure, never the sticker. Second, material dealers price the credit cycle: a 30-day udhaar rate commonly runs 2-3 percent above the cash rate. Record both rates and the period, so you are comparing money — not favours you will repay later.

Tracker 2: material reconciliation — theoretical vs actual

Reconciliation answers one question: did the material issued from store actually turn into built work? The logic, for any material:

1. Theoretical consumption = executed quantity x consumption coefficient (from your rate analysis, Module 4, or the BBS for steel).
2. Allowable consumption = theoretical + wastage allowance. Wastage norms are a matter of company policy and contract — a common working figure for reinforcement steel is around 3 percent (cutting losses and unusable offcuts); confirm the norm your contract specifies.
3. Variance = actual issued - allowable. Positive variance is money to investigate.

Worked example — steel reconciliation at slab casting stage:

That Rs. 50,701 has exactly four places to hide: unrecorded stock still on site, extra laps and chairs not captured in the BBS, genuine excess wastage, or theft. The reconciliation does not tell you which — it tells you to go and look, this month instead of at project closeout when the trail is cold. Run the same structure for cement using the theoretical coefficients from your rate analysis. Frequency matters more than precision: a monthly reconciliation with a rough coefficient beats a perfect one done once at the end.

Tracker 3: the RA bill tracker — cumulative, always

Running Account (RA) bills, from Module 7, follow one iron rule: each bill measures cumulative work up to date, and the amount payable is cumulative minus previously billed. The moment someone prepares "this month's work" as a fresh measurement, double payment becomes possible.

Worked example — one item across an RA bill:

Before the bill is prepared, get the up-to-date quantity recorded as a joint measurement — measured together with the thekedar or contractor's representative and signed on the sheet (the joint measurement format from the previous lesson exists for exactly this). In the RERA era, disputes are decided on documentation: a signed cumulative record protects the contractor from under-certification and the owner from over-billing at the same time, while an unsigned Excel total protects nobody. Months later, when someone claims work was billed twice or never paid, the signed sheets plus this tracker settle it in minutes.

Your tracker holds one row per BOQ item with columns: agreement quantity, up-to-date quantity, previous quantity, this-bill quantity (formula), rate, this-bill amount (formula), and cumulative amount. Two more columns protect you:

• Percent of agreement quantity executed = up-to-date / agreement. Anything approaching or crossing 100 percent must trigger the deviation/extra-item process from Module 7 before the work is done, not after.
• Retention (security deposit) deducted — commonly around 5 percent of each bill in Indian private works, but the percentage, cap, and release terms are whatever your contract says; track deducted and released amounts so the money is not forgotten at final bill. The retention tracker linked below does exactly this.

Wire the three trackers together

The real power appears when the trackers share item codes and talk to each other:

• Reconciliation reads executed quantities from the RA bill tracker — one source of truth for progress.
• The RA bill tracker reads rates from the same rate library your estimate used, so billed rates can never silently drift from agreed rates.
• A one-page monthly summary: this month's certified amount, retention held, material variance value, and any item past 90 percent of agreement quantity. That single sheet is a project cost review.

Common mistakes

1. Comparing quotations on sticker price and ignoring freight, taxes, or credit terms.
2. Reconciling only at project end, when nothing can be recovered and nobody remembers.
3. Fresh-measurement RA bills instead of cumulative-minus-previous — the classic double-billing door.
4. Forgetting retention at final bill because it lived in someone's head instead of a tracker.
5. Different item codes in different trackers, so nothing can be cross-checked without a day of manual matching.

These three trackers, plus the takeoff workbook, are a complete single-project QS system in Excel. The next lesson is about the day that system starts to crack — and how to recognise that day before it costs you money.