Template
Job Costing Spreadsheet:Cost and Rate Tab Columns, SUMIF Formulas, Loaded Labor Rate, Overhead Allocation and a Filled Exterior Repaint
Job costing spreadsheet columns and formulas: loaded labor rate, materials, equipment, overhead per billable hour, and a filled repaint costed with BLS wages.
A job costing spreadsheet sets one job's actual cost against its estimate, with a formula in every cost cell: labor hours at a loaded rate, materials, equipment, an overhead charge per billable hour, and the gross and net margin left on the price. The columns and formulas below are filled for an exterior repaint in Chesapeake, Virginia.
The layout is one of the free field service templates from FieldRanger, field service software that runs inside HubSpot. Three filled cost sheets, stopping at gross margin, are on the job costing examples page, and the method is defined in the glossary entry what is job costing. The page offers the spreadsheet as a free .xlsx download with no sign-up: a rate tab that builds the loaded labor rate from the wage, a cost tab with one row per line, and a summary that carries overhead through to net profit.
Job costing spreadsheet cost tab: columns and formulas
The cost tab holds one row per labor role, material, machine or subcontract, from row 6 down. The formulas work in Excel and Google Sheets alike.
| Column | Header | Contents | Formula |
|---|---|---|---|
| A | Category | Labor, Materials, Equipment or Subcontract | Typed from a fixed list |
| B | Line | The role, item or machine, specific enough to match a timesheet or receipt | Typed |
| C | Est. qty | Hours, gallons, days or miles, from the estimate | Typed |
| D | Unit | hr, gal, day, mi or lot | Typed |
| E | Unit cost | Loaded labor rate, supplier price, rental rate or mileage rate | =Rates!$B$12 for the painter, =Rates!$C$12 for the helper |
| F | Est. cost | Estimated quantity times unit cost | =C6*E6 |
| G | Actual qty | From timesheets, receipts, rental tickets and the mileage log | Typed |
| H | Actual cost | Actual quantity times unit cost, or a receipt total for a lot | =G6*E6 |
| I | Variance | Actual less estimate | =H6-F6 |
| J | Variance % | Variance as a share of the estimate | =IF(F6=0,"",I6/F6) |
| K | Source | Timesheet, receipt or ticket number behind the actual | Typed |
| Row | Line | Formula in column F |
|---|---|---|
| 32 | Labor | =SUMIF($A$6:$A$30,"Labor",F$6:F$30) |
| 33 | Materials | =SUMIF($A$6:$A$30,"Materials",F$6:F$30) |
| 34 | Equipment | =SUMIF($A$6:$A$30,"Equipment",F$6:F$30) |
| 35 | Subcontract | =SUMIF($A$6:$A$30,"Subcontract",F$6:F$30) |
| 36 | Direct cost | =SUM(F32:F35) |
| 37 | Billable labor hours | =SUMIF($A$6:$A$30,"Labor",C$6:C$30); in column H, the same over G |
| 38 | Overhead | =F37*Rates!$B$16 |
| 39 | Total cost | =F36+F38 |
| 40 | Price | The contract price |
| 41 | Gross profit and margin | =F40-F36, and =F41/F40 beside it |
| 42 | Net profit and margin | =F40-F39, and =F42/F40 beside it |
SUMIF adds the cells of a sum range where the matching cell of a criterion range meets a condition (Google Docs Editors Help, SUMIF, checked November 2026). The mixed references carry the design: $A$6:$A$30 stays fixed when the formula is copied from F to H, while F$6:F$30 becomes H$6:H$30, so one formula serves the estimate and the actual columns. A new category needs only a new summary row, not a new formula pattern.
Loaded labor rate tab: wage, payroll taxes, insurance and billable hours
The rate tab turns a wage into the cost of one billable hour. Each component row is rounded to the cent with ROUND, and the total sums the rounded rows, so the printed figures add.
| Row | Item | Source or formula | Painter (B) | Helper (C) |
|---|---|---|---|---|
| 3 | Base wage | BLS mean hourly wage, Virginia Beach-Chesapeake-Norfolk, May 2025 | $24.13 | $18.78 |
| 4 | Social Security and Medicare | =ROUND(B3*7.65%,2) | $1.85 | $1.44 |
| 5 | Federal unemployment | =ROUND(7000*0.6%/B10,2) | $0.02 | $0.02 |
| 6 | State unemployment | =ROUND(200/B10,2), from the state rate notice (sample $200 a worker) | $0.10 | $0.10 |
| 7 | Workers' compensation | =ROUND(B3*6.50/100,2), from the policy (sample $6.50 per $100 of payroll) | $1.57 | $1.22 |
| 8 | Benefits | Health stipend per paid hour (sample) | $2.40 | $2.40 |
| 9 | Cost per paid hour | =SUM(B3:B8) | $30.07 | $23.96 |
| 10 | Paid hours a year | Sample | 2,080 | 2,080 |
| 11 | Billable hours a year | Sample: paid hours less shop time, travel between jobs and paid leave | 1,700 | 1,700 |
| 12 | Loaded rate per billable hour | =ROUND(B9*B10/B11,2) | $36.79 | $29.32 |
| 14 | Annual overhead | Sample: office, insurance other than workers' compensation, software, owner's salary; vehicles excluded | $186,000 | |
| 15 | Company billable hours a year | Sample: all field roles | 10,200 | |
| 16 | Overhead per billable hour | =ROUND(B14/B15,2) | $18.24 |
The wages are means for the metro from the Bureau of Labor Statistics: $24.13 an hour for painters, construction and maintenance, and $18.78 for helpers to painters, paperhangers, plasterers and stucco masons (BLS, Occupational Employment and Wages in Virginia Beach-Chesapeake-Norfolk, May 2025, released July 10, 2026). A business with its own payroll replaces them with what it pays.
The tax rows come from the IRS. The employer pays 6.2% Social Security, on wages up to $184,500 in 2026, and 1.45% Medicare with no wage limit (IRS, Topic 751, updated November 9, 2026). The federal unemployment tax is 6.0% of the first $7,000 of each employee's wages, 0.6% after the maximum credit for state tax paid in full and on time (IRS, Topic 759, updated November 9, 2026). Spread over 2,080 paid hours, the $42.00 a year comes to two cents an hour.
Row 12 carries the unbilled hours. A painter paid for 2,080 hours who bills 1,700 costs $30.07 for each paid hour and $36.79 for each billable one; costed at $30.07, a job would leave the 380 unbilled hours unpaid by any job.
Overhead allocation per billable hour
Overhead is the cost of running the business that belongs to no single job: the office, insurance other than workers' compensation, software, and the owner's salary. The rate tab divides the year's overhead by the company's billable hours, and the cost tab charges each job its billable hours times that rate. A job that takes more hours absorbs more overhead.
Two exclusions keep the overhead pool from counting a cost twice. Vehicles are charged to jobs by the mile, so their cost stays out of row 14. Equipment rented for a job goes on the job's equipment lines, so only owned equipment the business does not charge by the hour belongs in overhead.
Filled job costing spreadsheet: exterior repaint in Chesapeake, Virginia
The job repainted the siding and trim of a two-story house at a fixed price of $9,850.00. The painter is the assigned lead and the helper works alongside; prices, quantities and the price of the job are sample entries, and the wages, payroll tax rates and mileage rate are published figures.
Two equipment rates need their basis stated. The owned pressure washer is costed at $6.00 an hour: $1,800 purchase less $300 resale over 500 hours of life, plus $3.00 an hour of fuel and maintenance (sample figures). The truck is costed at the IRS business standard mileage rate of 76 cents a mile for July 1 to December 31, 2026 (IRS, standard mileage rates, updated July 28, 2026), used here as a published cost per mile rather than a deduction.
| Category | Line | Est. qty | Unit cost | Est. cost | Actual qty | Actual cost | Variance | Var. % |
|---|---|---|---|---|---|---|---|---|
| Labor | Painter | 64 hr | $36.79 | $2,354.56 | 71 hr | $2,612.09 | $257.53 | 10.9% |
| Labor | Helper | 48 hr | $29.32 | $1,407.36 | 46 hr | $1,348.72 | −$58.64 | −4.2% |
| Materials | Exterior acrylic paint | 22 gal | $58.00 | $1,276.00 | 25 gal | $1,450.00 | $174.00 | 13.6% |
| Materials | Primer | 4 gal | $42.00 | $168.00 | 3 gal | $126.00 | −$42.00 | −25.0% |
| Materials | Caulk, tape, plastic and sundries | 1 lot | $185.00 | $185.00 | receipts | $212.00 | $27.00 | 14.6% |
| Equipment | Articulating boom lift, 40 ft, rental | 2 days | $395.00 | $790.00 | 3 days | $1,185.00 | $395.00 | 50.0% |
| Equipment | Pressure washer, owned | 6 hr | $6.00 | $36.00 | 7 hr | $42.00 | $6.00 | 16.7% |
| Equipment | Truck, 18 miles a round trip | 144 mi | $0.76 | $109.44 | 162 mi | $123.12 | $13.68 | 12.5% |
| Line | Estimate | Actual | Variance |
|---|---|---|---|
| Labor | $3,761.92 | $3,960.81 | $198.89 |
| Materials | $1,629.00 | $1,788.00 | $159.00 |
| Equipment | $935.44 | $1,350.12 | $414.68 |
| Direct cost | $6,326.36 | $7,098.93 | $772.57 |
| Billable labor hours | 112 | 117 | 5 |
| Overhead at $18.24 an hour | $2,042.88 | $2,134.08 | $91.20 |
| Total cost | $8,369.24 | $9,233.01 | $863.77 |
| Price | $9,850.00 | $9,850.00 | |
| Gross profit and margin | $3,523.64 · 35.8% | $2,751.07 · 27.9% | |
| Net profit and margin | $1,480.76 · 15.0% | $616.99 · 6.3% |
Variance lines on the repaint and the next estimate
The boom lift is the largest variance, $395.00, and its cause is in the source column: a rain day left the rented lift on site for a third day. The truck line moved with it, one more round trip. Neither line involves the painter's speed.
The painter's 7 extra hours and the 3 extra gallons share a cause recorded on the timesheet: the south wall's siding took a second full coat. The primer and the helper's hours came in under, so part of that wall's cost was offset elsewhere. Overhead rose by $91.20 because the job used 5 more billable hours.
The net column shows what the gross column hides. At 27.9 percent gross, the job looks sound; after $2,134.08 of overhead, it kept $616.99, or 6.3 percent. The next estimate for a house with that exposure prices the second coat on the south wall and a weather day for the lift.
Job costing spreadsheet instructions from rate tab to closeout
- Fill the rate tab once a year: wages, the payroll tax rates, the state unemployment and workers' compensation figures from their notices, benefits, and paid and billable hours.
- Recompute overhead per billable hour from the year's budget and the company's billable hours.
- For each job, copy the cost tab, fill the estimate columns from the accepted estimate, and enter the price in row 40.
- During the job, enter actual quantities from timesheets, receipts, rental tickets and the mileage log, with each document's number in the source column.
- At closeout, read the variance column top to bottom and write the cause of each line that moved.
- Check that the actual labor hours equal the timesheets, that each actual cost traces to a source document, and that rows 32 to 35 add to row 36 in both columns.
Job costing in FieldRanger reports and the columns it fills
A spreadsheet's actual columns are typed from paper: the timesheet, the receipts and the rental ticket each become a number by hand.
In FieldRanger, one job can carry several technicians, clock-ins and clock-outs are logged against the job, and geo-location time tracking records where each clock-in happened. Parts and materials used on each job are logged for job costing, and technicians check them out of a specific warehouse. Revenue reports show job revenue and margin by customer or service type, custom reports are built with a drag-and-drop report builder, and any report downloads as CSV or Excel, so the actual hours and materials can be pasted into the cost tab instead of retyped. Invoices and payments sync with QuickBooks. On the repaint, the painter and the helper are both on the job, so both sets of hours come from those clock records.
The rate tab, which builds the loaded labor rate from the wage, stays in the spreadsheet, and FieldRanger does not run payroll. Each day is its own job, so the repaint's working days are separate jobs to total. The janitorial job costing template applies the same columns to recurring cleaning contracts, where the cost period is a month.
Frequently asked questions
How to do job costing in Excel or Google Sheets?
Build a rate tab that turns each wage into a loaded rate per billable hour, and a cost tab with one row per labor role, material, machine and subcontract, holding estimated and actual quantities times unit cost. SUMIF totals each category, overhead is billable hours times an overhead rate, and the summary shows gross and net margin on the price.
What is a loaded labor rate?
The cost of one billable hour of a worker: the wage plus employer payroll taxes, unemployment taxes, workers' compensation and benefits, divided across billable rather than paid hours. A $24.13 wage becomes $36.79 in the example.
How is overhead allocated to a job?
Divide the year's overhead by the company's billable hours to get a rate, then charge each job its billable hours times that rate. Costs charged to jobs directly, such as mileage and rentals, stay out of the overhead pool.
Is there a free job costing spreadsheet template to download?
Yes. The page offers a free .xlsx download with no sign-up: the cost tab on a Template sheet, with lines from row 6 and the summary in rows 32 to 42, a Rates tab, and a Sample sheet with the filled exterior repaint.
What is the difference between gross profit and net profit on a job?
Gross profit is the price less the job's direct costs: labor, materials, equipment and subcontracts. Net profit also subtracts the job's share of overhead. The repaint's gross margin was 27.9 percent and its net margin 6.3 percent.
The repaint at 6.3 percent net
The Chesapeake job closed $863.77 over its estimated cost, $395.00 of it a lift that waited out one day of rain, and kept $616.99 of a $9,850.00 price. The next bid carries a weather-day line. Estimate and invoice forms that feed the cost tab are in the free field service templates hub, and FieldRanger's revenue and margin reports are described on the reporting page. Start free trial runs 14 days with no card, long enough to log one repaint's hours and materials and paste the export into the actual columns; plans are on pricing.
FieldRanger runs field service inside HubSpot
Estimates, scheduling and dispatch, a technician app and invoicing on the HubSpot records sales already keeps. Every plan starts with a free trial.
Book a demo