Construction Historical Cost Database in Excel: Store Hours, Not Dollars

Redline Concrete and Masonry does $6.8 million a year and self-performs everything it can: footings, slab on grade, edge and curb form, CMU wall. Thirty-four jobs closed in the last three years. Every one of them was bid off published unit cost data with a city cost index applied, because that is what the estimator learned in 2009 and it has never obviously failed. A construction historical cost database in Excel is the alternative, and the reason to build one is not that published data is wrong. It is that published data describes an average crew in an average metro, and Redline has never once sent an average crew to a job site.

Here is what Redline's own numbers say about one cost code, 03 30 00, slab on grade, placing and finishing. Eleven jobs where somebody actually wrote down how many square feet got poured and how many labor hours were coded to it.

The published figure Redline has been bidding, after the city labor index of 0.94 is applied, is 1.83 hours per 100 square feet. Look down the right column. Redline hits that number on exactly three jobs, and all three are over 14,000 square feet.

Two averages of the same eleven jobs, 26 percent apart

The instinct at this point is to average the column and be done. That is where most self-built cost history dies, because there are two defensible averages and they disagree badly.

Average the eleven ratios: =AVERAGE(E2:E12) gives 2.89 hours per 100 square feet. Divide total hours by total quantity instead: =SUM(D2:D12)/SUM(C2:C12)*100 gives 2.29. Same eleven jobs, same time cards, 26 percent apart. The simple average weights a 2,850 square foot pad the same as a 22,400 square foot dock. The weighted average lets the three big jobs, which are 57 percent of the square footage, drown out the eight small ones.

Both numbers are wrong, and they are wrong in a specific way that matters more than the gap between them. Unit cost is not a constant. It is a curve against quantity, because mobilization, layout, edge to area ratio, and the crew standing around waiting for the truck are roughly fixed per pour and get spread over whatever you place that day. Store one number per cost code and you have averaged away the only structure in the data.

Bracket by size, then take percentiles

Split the same eleven jobs into three bands and the picture stops being noise.

Use the median, not the mean, and keep the 75th percentile next to it. The median is your estimate. The spread between p50 and p75 is your contingency, priced instead of guessed. And carry the sample count in a visible cell, because three jobs is not a database and the sheet should say so out loud rather than returning a confident number built on three rows.

The coefficient of variation, =STDEV.S(range)/AVERAGE(range), is the honesty check. Under about 15 percent means your crews are repeatable and the scope definition is holding. Over 30 percent almost never means your crews are erratic. It means two of those jobs are not the same scope wearing the same code, and you need to go read the notes before you trust the median.

Store hours, not dollars

Nearly every unit cost history built in a spreadsheet stores dollars per unit, and every one of them is obsolete within eighteen months. A labor dollar from 2023 is not a labor dollar from 2026. Your composite burdened rate moved, the comp mod moved, health insurance moved. Bake all of that into a stored $/sf figure and you have made your own history depreciate.

Hours per unit does not depreciate. How long it takes your finishers to pull 100 square feet of 4,000 psi slab is a fact about your crew and your means and methods, and it is just as true in 2026 as it was when they did it. Store the hours. Price them at today's rate at the moment you look them up.

That single decision splits the database into two mechanisms that behave differently, which is exactly right, because labor and material behave differently.

• Labor is stored as hours per unit and repriced with the current burdened crew rate: =Hours_Per_Unit*XLOOKUP(Crew,Rates!$A:$A,Rates!$B:$B). No index, no escalation guess. Redline's concrete crew composite is $52.80 fully burdened today, so 3.71 hours per 100 square feet becomes $1.96 per square foot, and it becomes something else automatically the day the rate cell changes.
• Material and sub dollars are stored as spent and escalated forward with an index, because you cannot reprice a 2023 concrete delivery from first principles: =Mat_Per_Unit*Idx_Today/XLOOKUP(JobID,Jobs!$A:$A,Jobs!$H:$H).
• Equipment follows whichever convention you actually use. Owned equipment on an internal hourly rate behaves like labor. Rented pumps and lasers behave like material.

The material side is where the money hides. Redline's ready-mix ran $148 per cubic yard in the second quarter of 2023 and runs $181 today, up 22.3 percent. A 6 inch slab at 5 percent over-pour is 0.01944 cubic yards per square foot. On the Kemper job the as-spent material read $2.88 per square foot. Average that raw against 2026 jobs and you produce a bid number that is 65 cents per square foot light on concrete alone. Normalize it, =2.88*122.3/100, and it reads $3.52, which is what the truck will actually cost you next month.

The index does not have to be sophisticated

Keep an Index tab with one row per quarter and one column per input class: ready-mix, rebar, block, lumber, and a blended column for subs. Populate it from your own purchase records, not from a national series, because your supplier's price list is the thing your job will actually pay. Twelve rows a year, updated in ten minutes each quarter, and every historical dollar in the file becomes comparable to today.

You are not building a cost database, you are building a quantity database

This is the part that decides whether the project takes a week or dies in month two.

You already have the cost half. It is in your accounting system, coded to jobs and cost codes, reconciled to the penny because a bookkeeper closes it every month. What you do not have, anywhere, in any system, is the denominator. Nobody wrote down that Southgate Church was 6,200 square feet of slab. The cost of a cost code without a quantity is accounting. Cost divided by quantity is estimating. The entire build is about manufacturing that one missing column.

Run the coverage test before you build anything. For each cost code, count how many closed jobs have cost, then how many of those also have a defensible installed quantity.

Thirty-four closed jobs produce three trustworthy numbers. That is not a failure of the method, it is the honest yield, and it is still three numbers more than the estimator had on Monday. The bottleneck column is the fourth one, and it is the only column worth an afternoon of backfill.

Two quantities, not one, and they answer different questions

Store the bid quantity and the actual installed quantity in separate columns. Divide by the wrong one and you corrupt the number you are trying to build.

Southgate Church was taken off at 5,900 square feet and poured at 6,200. Divide 186 hours by the bid quantity and you get 3.15 hours per 100 square feet. Divide by the actual and you get 3.00. That 5 percent gap is not your crew being slow. It is your takeoff missing 300 square feet, and if you file it under productivity you have permanently taxed the crew for the estimator's error and lost the signal that your takeoffs run light on churches.

• Hours divided by actual quantity is productivity. It is what goes in the database.
• Actual quantity divided by bid quantity is takeoff accuracy. Track it by job type and it becomes its own useful number.
• Cost divided by bid quantity is bid performance. Useful for a postmortem, poisonous inside a unit cost history, because it silently blends two unrelated failures.

Build the sheet: six tabs and one hard rule

The hard rule first, because no formula survives without it. Your accounting cost code list and your estimating assembly list must be the same list, with the same numbers, and each code carries a written inclusion boundary. Most contractors run two lists that were never reconciled, and the result is a database where 03 30 00 means slab plus pump plus vapor barrier on one job and slab alone on another.

Hollis Industrial had the pump truck and the vapor barrier install buried in 03 30 00. As coded, its material reads $3.34 per square foot. Pull the pump and the barrier out and it reads $3.05. On a large band with three data points, one miscoded job moves the median by nine percent and nobody can see why. Write the boundary in a cell, one line, in plain English: includes place, screed, float, trowel, cure and control joints. Excludes pump, vapor barrier, subgrade prep, reinforcing.

Jobs tab

One row per job. Columns A through J: Job ID, name, type, actual start, substantial completion, cost midpoint, index period, material index, conditions note, include flag.

The cost midpoint is what you escalate from, not the start or the finish: =D2+(E2-D2)/2. Turn it into an index key with =YEAR(F2)&"-Q"&ROUNDUP(MONTH(F2)/3,0) and pull the factor with =XLOOKUP(G2,Index!$A:$A,Index!$B:$B).

The conditions note is not decoration. Ridgeline Retail Pad is the 4.28 hour outlier in the small band, and the note says winter pour with blankets and a heated enclosure. That job stays in the database with the note attached, because you will bid another winter pour. What it does not do is silently drag the median for a job you are pouring in June.

CostLines tab, the grain of the whole file

One row per job per cost code. Job ID, cost code, description, bid quantity, actual quantity, UOM, labor hours, material dollars as spent, sub dollars as spent, equipment dollars as spent, include flag, exclusion note. Nothing computed lives here. This tab is a record of what happened and it never changes after a job closes.

UnitHistory tab, everything computed

Same row count as CostLines, all formulas.

• Hours per unit: =IF(E2=0,"",G2/E2). The guard matters, because a job that got coded before the quantity was captured will otherwise fill your file with divide errors that break every downstream FILTER.
• Quantity variance: =IF(D2=0,"",E2/D2-1).
• Material per unit at today: =IF(E2=0,"",H2/E2*Idx_Today/XLOOKUP(A2,Jobs!$A:$A,Jobs!$H:$H)).
• Size band, with the breaks stored per code rather than hard coded, because 5,000 means nothing to a code measured in linear feet: =IFS(E2<XLOOKUP(B2,Codes!$A:$A,Codes!$D:$D),"S",E2<XLOOKUP(B2,Codes!$A:$A,Codes!$E:$E),"M",TRUE,"L").
• Labor dollars per unit at today's rate: =M2*XLOOKUP(XLOOKUP(B2,Codes!$A:$A,Codes!$G:$G),Rates!$A:$A,Rates!$B:$B).

Lookup tab, the face the estimator actually uses

Two inputs, cost code in B2 and the quantity you are about to bid in B3. Everything else computes.

Wrap every FILTER in =IFERROR(...,"NO DATA"), because FILTER on an empty match returns a hard error that will cascade through the tab. On Excel 2019 and earlier, swap FILTER for =MEDIAN(IF((CostLines!$B$2:$B$400=$B$2)*(UnitHistory!$Q$2:$Q$400=$B$4)*(CostLines!$K$2:$K$400=1),UnitHistory!$M$2:$M$400)) entered with Ctrl+Shift+Enter, and use SUMPRODUCT for the count.

Cell B10 is the most valuable cell in the file. It is the one that stops the sheet from lying with confidence. When it says THIN, the estimator uses published data for that line and the sheet has still done its job by telling him which line to distrust.

What three years of history is actually worth

Here is the Novak Machine Shop II bid, 4,200 square feet of slab, priced four ways at the same $52.80 burdened rate.

The bid Redline was about to send carries $4,058 of labor for work that will cost $8,227. On a slab package that prices out around $38,000, that is eleven percent of the package missing before anyone talks about profit. And note the last row: the entire risk premium between the median and the 75th percentile is $311. Redline has been carrying a five percent blanket contingency, roughly $1,900 on this package, to cover an uncertainty the data says is worth $311.

Run the same test across the three codes Redline self-performs, on the trailing twelve months of small jobs only.

Redline nets about 6.5 percent on $6.8 million, roughly $442,000. The $71,336 is sixteen percent of net income, performed and never billed, and it does not show up anywhere in the monthly financials as a problem because the jobs all finished and the checks all cleared. It shows up as a company that works hard and has a thin year.

The other half of the story is the half nobody looks for. On pours over 12,000 square feet Redline runs 1.64 hours per 100 square feet against a published 1.83. They are 10 percent faster than the book on exactly the work they have been pricing at book plus a safety factor. In 2025 they bid eleven large slab packages and won two. Assume a $185,000 average package and a 16 percent gross margin, and winning two more of those, which a 4 to 6 percent price correction plausibly does, is $59,200 of gross profit. That number is an estimate and it depends on a bid spread you would need to pull from your own results, but the direction is not in doubt: the same wrong number is losing you the jobs you should win and winning you the jobs you should lose.

The two week version

1. Pick the three cost codes that carry the most self-performed labor dollars. Not the most codes, three. Everything else stays on published data and that is fine.
2. Write the inclusion boundary for each of those three codes in one sentence. Get the field superintendent to agree with it before you key a single row, because he is the one whose time cards have to obey it.
3. Pull actual installed quantities for the last eight to twelve closed jobs on those codes. Pay applications, final takeoffs, and delivery tickets get you most of the way. This is the whole project and it is an afternoon per code.
4. Export cost by job and cost code from accounting. It is a report you already have. Do not retype it.
5. Build the Index tab from your own supplier invoices, one row per quarter, four material classes. Ten minutes.
6. Split into three size bands per code and refuse to publish any band with fewer than four jobs. Let the flag say THIN. Resist the urge to widen the bands to manufacture a sample.
7. Put the Lookup tab in front of the estimator with the sample count and the flag visible on the same screen as the number. A unit cost with no n next to it will get trusted, and that is how the file starts causing damage instead of preventing it.
8. At every job closeout, add one row. Fifteen minutes. The database is worthless as a project and valuable as a habit.

The recommendation, plainly: build it, but do not build it as a standalone file. The reason isolated cost history spreadsheets always die is structural, not motivational. Costs live in accounting, quantities live in the takeoff and the pay application, cost codes live in a list somebody maintains in a different program, and the person keeping the history file is rekeying all three by hand. By the fourth job they stop, and what is left is a file that looks authoritative and stopped being true in March.

The SheetCraft Construction Budget Tracker already holds the pieces this sits on: a single cost code list shared by the budget and the actuals, job level cost capture by code, and committed versus actual by line. The column it needs is actual installed quantity next to the cost you are already recording, and once that column exists, the unit cost history is a lookup against data you maintain anyway rather than a second set of books. That is the difference between knowing your real numbers once, during a slow week, and knowing them on every bid you send for the next ten years.