Analysis of Rates Format in Excel — Free Rate Analysis Template
An analysis of rates is the working that sits behind one line of a bill. Before a rate per cubic metre can be written against concrete, something has to account for the cement, sand, aggregate and water it consumes, the mason-days and mazdoor-days it absorbs, the tools and sundries, and the overheads and profit carried on top — and show how they add up. That working is the analysis. It is what you produce when a consultant queries a rate, and what you go back to when material prices move mid-project.
Most rate analysis formats available to download are a fixed table of coefficients: someone else's cement factor, typed in, with nothing to say where it came from. This one is parametric. The mix ratio, the dry-volume factor and the wastage allowance are inputs at the top of each sheet, and the material quantities recompute from them. Every quantity carries a Basis column spelling out the arithmetic that produced it, so a reviewer can check a coefficient rather than take it on trust.
What is in the workbook
Eight tabs. A Rate Library holding every material and labour rate in one place — change cement once and all four analyses follow. Four worked analyses: PCC 1:2:4, RCC M20, brickwork in CM 1:6, and 12 mm cement plaster. A blank analysis sheet with the totals block already wired, to copy for items of your own. And a rate summary abstracting every derived rate onto one page.
About the rates in this file
The Rate Library ships with illustrative placeholder figures, so the workbook computes something the moment you open it. They are not certified, not quoted, and not tied to any published schedule. Replace every one of them with your own supplier quotations or your own schedule edition before the output leaves your desk. The workbook says so in three places, and there is a Source column beside each rate for recording where your replacement came from — an analysis is only as defensible as that column.
Each analysis also carries a Rate Basis Declaration: basis, schedule edition, effective date, city, and who compiled it. Filling it in is the difference between a rate someone can audit and a number in a spreadsheet. There is a blank "Schedule ref." field on every sheet rather than a pre-filled item code, because the code depends on which schedule you are working to and a wrong one is worse than none.
Common mistakes
Forgetting the dry-volume conversion — or applying it twice. Ingredients are bought dry and concrete is measured wet. Miss the factor and you under-procure by roughly a third; apply it once in the parameters and again in a hand-typed quantity and you over-procure by half again as much. In this workbook it is applied in exactly one place, and the Basis column shows where.
Mixing rate bases inside one analysis. Cement priced from a supplier quotation and labour lifted from a published schedule produce a total that belongs to neither basis. Declare one basis per analysis and stay on it.
Dropping the wastage allowance. It is small per line and systematic across a project, which is the worst combination — it never looks wrong on any single sheet.
Carrying overheads and profit inconsistently. If one analysis adds 15% and the next adds none, the rates are not comparable with each other, let alone with a tender.
Leaving the analysis undated. A rate build-up with no effective date cannot be defended six months later, when the cement figure it rests on has moved.
What each column means
| Column | What goes in it |
|---|---|
| Sr | Line number within the analysis. Materials, labour and sundries are numbered in their own blocks. |
| Code | The Rate Library reference for this material or trade, e.g. M01 for cement, L03 for a mazdoor. It is a lookup key into this workbook only — not a schedule-of-rates item number. |
| Description | Pulled from the Rate Library, not typed. Rename a material once there and every analysis follows. |
| Unit | Also pulled from the Rate Library. Bag, cum, MT, day. The unit the rate is quoted in has to be the unit the quantity is measured in. |
| Quantity | Computed from the design parameters at the top of the sheet — mix ratio, dry-volume factor, wastage — not typed in. Change a parameter and every material quantity recomputes. |
| Rate (₹) | Looked up from the Rate Library. Edit it there, never here, so one change propagates to every analysis. |
| Amount (₹) | Quantity × rate. Stays blank rather than showing zero if either side is missing. |
| Basis of quantity | The arithmetic that produced the quantity, written out. This is the column a reviewer reads when they want to check a coefficient instead of trusting it. |
Everything in this band is priced on a bill of quantities. The BOQ format in Excel is that bill — pre-sectioned by trade, with an abstract that totals it — and every rate you settle here is applied against its measured quantities.
Frequently asked questions
What is analysis of rates in construction?
It is the itemised working behind a single rate. To justify a rate per cubic metre of concrete you account for the cement, sand, aggregate and water it consumes, the mason and mazdoor days it takes, tools and sundries, and the overheads and profit you intend to carry — then divide by the quantity analysed. The analysis is what you produce when a client's consultant queries a rate.
Does this template come with rates filled in?
It ships with illustrative placeholder rates so the workbook computes something the moment you open it, and they are flagged inside the file as uncertified. They are not quoted figures and not tied to any published schedule. Replace every one of them with your own supplier quotations or your own schedule edition before the output leaves your desk.
Can I change the mix ratio?
Yes — that is the point of the workbook. Mix proportions, the dry-volume factor and the wastage allowance are inputs at the top of each analysis, and the material quantities below recompute from them. Changing 1:2:4 to 1:3:6 updates cement, sand and aggregate without retyping a coefficient.
What is the dry volume factor?
Concrete and mortar are measured wet but their ingredients are bought dry, and dry material occupies more volume than the mix it produces. A factor is applied to convert wet volume to the dry quantity to procure — commonly taken as 1.52 for concrete and 1.30 to 1.35 for mortar. It is an input in this workbook rather than a constant, because conventions differ and published schedules apply their own.
Can I use this analysis for a tender submission?
The format is standard and the arithmetic is shown in full, which is what makes an analysis reviewable. Whether the figures are acceptable depends entirely on the rates you put in and the basis you declare — fill in the Rate Basis Declaration block, and where a tender mandates a specific schedule edition, reconcile against that edition.