Job Costing Worksheet: Estimate vs Actual by Job
Job costing only helps if you compare what you thought a job would cost with what it did cost. This worksheet puts estimated and actual hours and material side by side per job and calculates the variance and the actual margin.
| Job number | Customer | Estimated hours | Actual hours | Labor rate | Estimated material | Actual material | Sell price | Estimated cost | Actual cost | Cost variance | Variance percent | Actual margin | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
$6,376 | $7,367 | $991 | 15.5% | $1,833 | |||||||||
$3,634 | $3,478 | -$156 | -4.3% | $2,122 | |||||||||
$10,270 | $12,739 | $2,469 | 24% | $1,061 | |||||||||
$1,712 | $1,585 | -$127 | -7.4% | $815 | |||||||||
$5,608 | $5,676 | $68 | 1.2% | $2,224 |
Positive variance means the job cost more than estimated. Labor rate here is a single burdened rate per row; if you cost by work center, enter the weighted average rate for the job from your ERP job cost report.
Automate estimate vs actual reporting
Optional: ask us how to pull this comparison straight from your ERP data without manual exports.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Pull completed jobs from your ERP's job cost or job profitability report. Global Shop Solutions, JobBOSS, E2 Shop System, ProShop and Fulcrum all report estimated versus actual hours and material per job. Enter each job's estimated and actual hours, the labor rate, estimated and actual material, and the sell price. The sheet computes estimated cost, actual cost, variance in dollars and percent, and margin. Sort in Excel by variance percent to find the jobs that hurt most.
- Review closed jobs monthly, not annually
- Group by customer and by part family
- Flag any job over a set variance threshold, such as 15%
- Feed the findings back into estimating run times
The formulas
Estimated cost is estimated hours times rate plus estimated material. Actual cost uses actual hours and actual material. Variance is actual minus estimated, so a positive number is an overrun. Variance percent divides variance by estimated cost. Actual margin is sell price minus actual cost. This keeps the math transparent so you can check it against the ERP report and spot where burden or overhead is handled differently.
Reading the sample
In the sample, job 24-1204 ran about a third over on hours and its margin shrank sharply, while 24-1190 came in under. One bad job does not prove a pattern, but if the same part family or the same customer repeats an overrun, the estimate standard is wrong. Check whether the overrun came from setup, a scrap event, a tooling issue or an out of date run time before you adjust prices.
Common mistakes
Comparing jobs with different quantities, mixing in rework hours without tagging them, and using a rate that excludes overhead are the usual errors. Also watch for jobs closed before all labor was posted, which makes variance look better than reality.
Frequently Asked Questions
Does this replace the job costing module in my ERP?
No. Your ERP is the system of record. This worksheet is a quick analysis and review tool, useful when you want to sort, filter and share results in Excel or when your ERP report format is hard to read.
What variance percent is acceptable?
It depends on job complexity and lot size. Many shops investigate anything beyond about 10 to 15 percent. Repeat parts should land much closer than one-off prototypes. Track the trend across many jobs rather than reacting to one.
Should rework hours be included?
Include them in actual hours because they are real cost, but tag them separately in your ERP so you can see whether the overrun was an estimating problem or a quality problem. The fix for each is different.
Can I use this for Global Shop, JobBOSS and E2 data?
Yes. It is system neutral. Copy the estimated and actual figures from whichever job cost report your system produces, and the worksheet does the comparison the same way.
If you would like estimate-versus-actual analysis surfaced automatically from your ERP data, we can set up read-only reporting or a natural-language question layer on your own servers.
Related Tools
Job Shop Quote Worksheet
Quote a machined part operation by operation: setup hours, run minutes per piece, shop rate and material. Totals the cost and shows the price at a target margin.
Costing & FinanceJob Shop WIP Report Template
A month-end work in process report layout for open jobs: costs to date, percent complete, billings and over or under billing, with sign-off lines for the controller.
MES & Shop FloorShop Traveler Template
Generate a printable job traveler with job header, material, routing steps, inspection points and sign-off lines. Export to PDF, Word or Excel.
ERP Migration & SelectionJob Shop ERP Implementation Cost Calculator
Estimate the first year and multi-year cost of a job shop ERP: licenses, implementation hours, data migration, training, hardware and annual support. Includes a chart of cost against user count.
More Costing & Finance tools
See all- SAP QM Inspection Effort and Cost Calculator
- SAP Standard Cost Estimate BOM Rollup Calculator
- Legacy ERP Retirement Cost and Payback Calculator
- JD Edwards World Month-End Close Procedure Template
- Distributor Customer Credit Application Template
- Distributor Customer Profitability Worksheet
- Distributor GMROI and Inventory Turns Calculator
- Vendor Early Pay Discount Worksheet
- SyteLine Cost Rollup Variance Calculator