Material Reconciliation Format in Excel for Construction

Materials & purchase7 min read

Every month, someone on a construction project has to answer a simple question: where did the material go? Material reconciliation is how you answer it with numbers instead of guesses. It compares what came in, what went out and what is left, then checks what the work should have used against what it actually used. This guide explains the formula, walks through a worked example for cement and steel, and gives you a free Excel format to start with.

What is material reconciliation in construction?

Material reconciliation is a periodic statement that accounts for every unit of a material on a project. It is usually prepared monthly, or at each running bill, by the store keeper and site engineer together, and checked by the project manager or QS.

It answers two separate questions, and it helps to keep them apart:

  • Stock check: does the material physically in the store match what the records say should be there?
  • Consumption check: did the work done use roughly the quantity of material it should have, according to the estimate?

The first catches store losses, wrong entries and missing receipts. The second catches wastage, theft at the work front, poor workmanship and estimating errors. On contracts where the client supplies cement or steel, the reconciliation statement is often a contract requirement, and excess consumption may be recovered from the contractor’s bill.

The material reconciliation formula

The stock check uses one line of arithmetic for every material:

Opening stock + Received − Issued = Book closing stock

Then count what is actually in the store. Stock difference = Physical stock − Book closing stock. A negative number means material is missing from the store; a positive one usually means a receipt or return was recorded wrongly.

The consumption check compares two figures for the same period:

  • Theoretical consumption = quantity of work done × consumption per unit of work. The consumption per unit comes from your estimate, rate analysis, approved mix design or bar bending schedule.
  • Actual consumption = quantity issued to the work − quantity returned to store − quantity still lying unused at the work front.
  • Variance = Actual consumption − Theoretical consumption.
  • Variance % = Variance ÷ Theoretical consumption × 100.

Run your material & inventory on CivilPilot

A material catalogue, requests from site, and stock for every project with every movement recorded.

Material reconciliation format: the columns

A practical format has two parts. Keep one row per material in each, and use the same unit throughout (bags or tonnes for cement, kg or MT for steel, never a mix).

Columns for the stock check and the consumption check.
PartColumnHow to fill it
Stock checkMaterial, code, unitFrom your material catalogue
Stock checkOpening stockLast period’s physical closing stock
Stock checkReceivedTotal of goods receipts in the period
Stock checkIssuedTotal issued from store to the work
Stock checkBook closingFormula: opening + received − issued
Stock checkPhysical stockCounted or weighed on the last day
Stock checkStock differenceFormula: physical − book closing
Consumption checkTheoretical consumptionWork done × consumption per unit
Consumption checkActual consumptionIssued − returned − unused at work front
Consumption checkVariance and variance %Formula, as above
Consumption checkAllowed variance %From your contract or estimator
Consumption checkRemarksReason for any variance above the allowance
Free material reconciliation format (Excel)Stock check, consumption check and a theoretical-quantity working sheet, with the formulas already in place.Free download · Excel · no sign-up

Worked example: cement reconciliation

The figures below are for illustration only. The consumption per unit of work is an assumed figure for this example; take yours from your own mix design or rate analysis.

Cement stock check for one month (example figures, in bags).
OpeningReceivedIssuedBook closingPhysicalDifference
1501,2001,180170166−4

Four bags are missing from the store. That is small, but it still needs a reason: a torn bag written off without an entry, a short receipt, or an issue that was never recorded.

Theoretical cement consumption for the same month (assumed consumption figures).
ActivityWork doneCement per unit (assumed)Theoretical bags
RCC, columns and slab120 cum8.0 bags per cum960
Internal plastering1,500 sqm0.13 bags per sqm195
Total1,155

All 1,180 bags issued were used, with none returned or lying at the work front. So actual consumption is 1,180 bags, the variance is 1,180 − 1,155 = 25 bags, and the variance % is 25 ÷ 1,155 × 100 = 2.2%.

Worked example: steel reconciliation

Steel is reconciled by weight. Again, these are example figures.

Steel reconciliation for one month (example figures, in kg).
LineQuantity (kg)
Opening stock4,200
Received18,000
Issued to work16,950
Book closing (4,200 + 18,000 − 16,950)5,250
Physical stock (weighed)5,250
Theoretical consumption, from the bar bending schedule for work done16,400
Actual consumption (issued, nothing returned)16,950
Variance550
Variance % (550 ÷ 16,400 × 100)3.4%

Before treating 550 kg as waste, check how the bar bending schedule was prepared. Whether laps, chairs, binding wire and cutting offcuts are included in the theoretical figure varies from one estimate to another. Usable offcuts that are returned to store should be entered as a return, which reduces actual consumption.

How much variance is acceptable?

There is no single wastage figure that applies to every project. Allowances vary by contract, specification, type of work and site conditions, and on client-supplied material the contract usually states the limit. Take your allowed variance for each material from your contract or from your estimator, and write it into the format so every site uses the same figure.

Whatever the allowance, investigate when:

  • Variance is above the allowance for two periods in a row.
  • One site shows a much higher variance than others doing similar work.
  • The physical stock is short and nobody can explain why.
  • Variance is negative, which usually means work was over-measured or issues were not recorded, not that the site is unusually efficient.

Where the numbers come from

Reconciliation fails when the inputs are scattered. The received column should come from goods receipt notes (GRNs) against purchase orders, not from supplier invoices that arrive weeks later. The issued column should come from issue slips recorded the day material leaves the store. The work quantities should come from the DPR or measurement book.

  1. 1Record every receipt against a purchase order on the day it arrives, with the quantity actually received.
  2. 2Record every issue from store to the work, with the activity it was for.
  3. 3Count physical stock on the same day each month, before the next receipt.
  4. 4Fill the consumption check from measured work, then explain every variance above the allowance in the remarks.

Do this in CivilPilot

CivilPilot keeps the stock records that a reconciliation is built from. It does not calculate theoretical consumption for you, but it means the opening, received and issued columns no longer have to be pieced together from registers and chat messages.

  • Material catalogue: every material with a code, category, unit and rate, so every site uses the same names and units.
  • Site requests: the site raises a request with priority and required date, and the office approves it before anything is ordered.
  • Purchase orders and goods receipts: in procurement, POs go through approval, and goods are received in part or in full against the PO, with no over-receipt.
  • Stock by project and stock movements: in material and inventory, every receipt and every issue to site is recorded against the project, so the balance for each material at each site is always current.

At month-end, the opening, received and issued figures come from those records. Add your physical count and your theoretical quantities in the Excel format above, and the reconciliation is done in an hour, not a day. All of this works in a phone browser, so the store keeper can record receipts and issues on site.

Frequently asked questions

Start counting what your sites really use.

Set up your first project today. Your site engineers can send their first daily report this afternoon.

  • 7-day free trial
  • No credit card
  • Cancel anytime
  • Made in India