Material Reconciliation in Excel: Theoretical Quantity, Allowed Waste, Received, Issued and Physical Stock

Materials are among the biggest cost items, and waste, theft and poor storage quietly drain profit. The only way to learn where materials go is regular reconciliation: how much should be consumed for the work done, how much was actually issued, and how much is left in the store.

This sheet performs that reconciliation for each material. You enter the theoretical quantity from the take-off and the allowed waste percentage, the quantities received and issued to site and the physical stock after counting, and it computes expected stock, compares issues with the allowance, and gives each material a status: OK, over-consumption, stock shortage or both.

Who is it for?

• Storekeepers and site engineers
• Quantity and purchasing engineers
• Project managers controlling cost
• Contractors who want to spot waste early

What is inside the sheet?

• A table for 40 materials: unit, theoretical quantity, allowed waste %
• Received, issued and physical stock
• Allowed quantity (theoretical + waste) and excess consumption
• Expected stock and stock-count difference
• A stock-count tolerance and a status per material
• A summary count of materials in each status
• A guide sheet

How to use it

1. Enter each material with its unit and theoretical quantity from the approved take-off.
2. Enter the allowed waste percentage from experience or contract.
3. Record cumulative received and issued up to the reconciliation date.
4. Count the store and enter the physical quantity.
5. Read the status and differences and start with the materials flagged red.

Formulas used

• Allowed quantity = theoretical × (1 + allowed waste %)
• Excess consumption = issued − allowed (if positive)
• Expected stock = received − issued
• Count difference = physical stock − expected stock
• Shortage alert if the negative difference exceeds the tolerance

Worked example from the sheet itself

Reinforcing steel: theoretical 100 tonnes with allowed waste of 3%, so allowed 103 tonnes.

• Received 110 and issued 108 give expected stock of 2 tonnes.
• Excess consumption = 108 − 103 = 5 tonnes (over-consumption).
• Physical stock of 1.5 tonnes gives a difference of −0.5 tonne.

How the reconciliation works

It rests on the material equation: what you received − what you issued = what should be in the store. And what you issued should be matched by executed work equal to its theoretical quantity plus acceptable waste. Any difference from these two balances deserves a question.

The first balance (issues against theoretical) reveals over-consumption. The second (count against expected) reveals shortage, theft or recording errors. The sheet computes both and combines them into one status per material.

Typical waste allowances

Allowances vary by material and quality of work, and these are common approximate values for first estimates only: reinforcing steel 2 to 5 percent, ready-mix concrete 1 to 3, bricks and blocks 3 to 7, tiles and ceramics 5 to 10 (more with complex cutting), and paint 5 to 10. Do not treat these numbers as contractual; set your own from your previous projects or from the contract.

A proper stock count

A stock count is a systematic job: a fixed time (end of each month, say), a team of at least two, actual measurement by counting, weighing or geometry, and avoiding eyeball estimates of piled materials. For sand and aggregate use the stockpile volume method and estimate the density. Record what is on site outside the store (on floors) so a false shortage does not appear.

Interpreting the statuses

• OK: issues are within the allowance and the count within tolerance.
• Over-consumption: more was issued than theoretical plus waste. Check the take-off, rework or actual waste.
• Stock shortage: physical stock is below expected. Check recording, unrecorded transfers and theft.
• Both: needs urgent investigation.

Sources of difference other than waste

Design changes during execution raise actual quantities above theoretical, so update the theoretical quantity with variation orders. Rejected work and its redoing uses extra material and should be recorded as a reason. Short or excess deliveries from the supplier show in the received column, so compare with invoices. The actual weight of steel differs slightly from the theoretical by the mill tolerance.

Common mistakes

• Comparing a period quantity with the total theoretical.
• Not updating the theoretical after variation orders.
• Mixing units (tonnes and kg, m³ and m²).
• Counting without including materials outside the store.
• Recording issues without a signed issue document.

Tips for best results

• Reconcile at least monthly for expensive materials.
• Tie every issue to a work order or a specific item.
• Set waste percentages from your actual experience.

Frequently asked questions

Do I enter quantities cumulatively?

Yes, received and issued up to the reconciliation date.

What stock-count tolerance should I use?

A value you set, commonly 1 to 2 percent of received for accurately counted materials.

Does it compute cost?

It focuses on quantities; multiply the difference by the unit rate for cost.

How many materials?

Up to 40 materials.

Download the sheet

Press the download button at the top of the page to get the Excel file. It includes a full guide sheet, yellow input cells and formulas protected from accidental edits. The sheet is also available in Arabic from the language switcher.

The sheet is a monitoring aid. The waste figures quoted are approximate and should be set from your project.