JobCostKit › Guides
How to set up a job costing spreadsheet
Most contractors find out whether a job made money weeks after it's finished, when the books are done. A simple job costing spreadsheet tells you while there's still time to do something about it: raise a change order, tighten up a crew, or price the next job better. Here's how to set one up, with the exact formulas.
What the sheet needs to answer
Three questions, every week:
- How much have we spent so far, by cost code?
- How far along is each part of the job?
- If things carry on like this, what will the job cost in the end, and what's left as profit?
Everything below is in service of those three answers. Resist adding more until they work.
Step 1: Pick your cost codes
Cost codes are the buckets you track money in. Use the same ones you estimate with, otherwise you can't compare budget to actual. For a remodeler that might be demo, framing, plumbing, electrical, drywall, tile and fixtures. An electrician might use service, rough-in, trim, fixtures and permits. Keep it to 6–15 codes per job. Too few and problems hide; too many and nobody codes receipts properly.
Step 2: Build the budget sheet
One row per cost code, with these columns:
| Column | What goes in it |
|---|---|
| A: Code | The cost code (input) |
| B: Budget | Cost from your estimate, not the selling price (input) |
| C: Actual to date | Formula, pulled from the costs log |
| D: % complete | Your honest estimate of progress (input) |
| E: Projected final | Formula: what this code will cost by the end |
| F: Over / (under) | Formula: projected final minus budget |
Step 3: Build the actual costs log
On a separate sheet called Costs, log every bill, receipt and payroll entry as one row: date, vendor, cost code, description, amount and invoice number. The cost code column should be a dropdown (Data → Data validation, list from your codes) so typos don't break the totals. This is the only sheet anyone updates day to day.
Step 4: The formulas
Actual to date for the code in A5, where the Costs sheet has codes in column C and amounts in column E:
Projected final cost. If the line has started, scale the actual spend up by progress; if it hasn't, assume the budget (or the actual, if bills arrived early):
Over / (under) budget:
Then total columns B, C, E and F at the bottom, and below that put the contract price, projected gross profit (contract price − total projected final) and projected margin (gross profit ÷ contract price). These formulas work the same in Excel and Google Sheets.
Finally, add conditional formatting to column F so anything over budget turns red. That one rule does most of the work: you open the sheet and your eye goes straight to the problem.
Worked example: a bathroom remodel, week 4
Contract price $54,000, estimated cost $43,000, so the planned gross profit is $11,000 (a 20.4% margin). Four weeks in, the sheet looks like this:
| Code | Budget | Actual | % done | Projected | Over / (under) |
|---|---|---|---|---|---|
| Demo | $3,200 | $3,450 | 100% | $3,450 | $250 |
| Framing | $4,800 | $4,600 | 100% | $4,600 | ($200) |
| Plumbing | $9,500 | $7,900 | 70% | $11,286 | $1,786 |
| Electrical | $6,200 | $2,500 | 40% | $6,250 | $50 |
| Drywall | $3,800 | $0 | 0% | $3,800 | $0 |
| Tile | $7,400 | $0 | 0% | $7,400 | $0 |
| Fixtures | $8,100 | $0 | 0% | $8,100 | $0 |
| Total | $43,000 | $18,450 | $44,886 | $1,886 |
Projected gross profit is now $54,000 − $44,886 = $9,114, a 16.9% margin, down from 20.4%. And it's almost all plumbing: $7,900 spent at 70% done projects to $11,286 against a $9,500 budget.
Because you caught it at week 4 rather than at the year-end books, you have options. If the overrun came from the client moving the shower drain, that's a change order: write it up now while everyone remembers. If it was a bad estimate, fix the plumbing line in your estimating template so the next job is priced right. Either way, the sheet turned a vague feeling of "plumbing's running long" into a number you can act on.
Making it stick
- Same day each week. Fifteen minutes on a Friday: log the week's bills, update % complete, look at the red cells.
- Code at the source. Write the cost code on the receipt or in the supplier account's job reference. Coding a shoebox of receipts at month end is where accuracy dies.
- Include your own labour. Hours × a loaded labour rate (wage plus payroll taxes, insurance and benefits). Leaving out your own crew's time is the most common reason a job "made money" but the bank account disagrees.
- Be honest with % complete. The projection is only as good as this number. When in doubt, go lower.
- Close the loop. When the job's done, compare final cost by code to the estimate and update your estimating rates. That's where job costing pays for itself.
Before any of this, make sure the budget is inside a price that covers your overhead. The free markup calculator below checks that in a couple of minutes.