# Job Costing Variance Worksheet: Estimate vs Actual > Source: https://netray.co/tools/job-costing-variance-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Compare the estimated and actual quantity and rate on a job by cost element and split the difference into quantity and rate variance. ## Category cost-finance ## Audience - Estimator - Cost accountant - Job shop owner ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Cost element **Key:** element **Type:** select **Options:** - Material - Labor - Machine - Subcontract - Freight - Other #### Detail **Key:** detail **Type:** text #### Estimated qty or hrs **Key:** est_qty **Type:** number #### Estimated rate **Key:** est_rate **Type:** number **Format:** currency #### Actual qty or hrs **Key:** act_qty **Type:** number #### Actual rate **Key:** act_rate **Type:** number **Format:** currency #### Estimated cost **Key:** est_cost **Type:** number **Expr:** est_qty * est_rate **Format:** currency #### Actual cost **Key:** act_cost **Type:** number **Expr:** act_qty * act_rate **Format:** currency #### Quantity variance **Key:** qty_var **Type:** number **Expr:** (act_qty - est_qty) * est_rate **Format:** currency #### Rate variance **Key:** rate_var **Type:** number **Expr:** (act_rate - est_rate) * act_qty **Format:** currency #### Total variance **Key:** total_var **Type:** number **Expr:** act_cost - est_cost **Format:** currency **Rows:** **Element:** Material **Detail:** 316L plate, kg **Est qty:** 420 **Est rate:** 7.9 **Act qty:** 465 **Act rate:** 8.35 **Element:** Labor **Detail:** Welding hours **Est qty:** 96 **Est rate:** 34 **Act qty:** 118 **Act rate:** 34.5 **Element:** Machine **Detail:** CNC mill hours **Est qty:** 40 **Est rate:** 62 **Act qty:** 36 **Act rate:** 62 **Element:** Subcontract **Detail:** Heat treat, lot **Est qty:** 1 **Est rate:** 1400 **Act qty:** 1 **Act rate:** 1520 **Element:** Freight **Detail:** Inbound and outbound **Est qty:** 1 **Est rate:** 650 **Act qty:** 1 **Act rate:** 590 **Totals:** #### Total estimated cost **Key:** est_cost **Agg:** sum **Format:** currency #### Total actual cost **Key:** act_cost **Agg:** sum **Format:** currency #### Total quantity variance **Key:** qty_var **Agg:** sum **Format:** currency #### Total rate variance **Key:** rate_var **Agg:** sum **Format:** currency #### Total variance **Key:** total_var **Agg:** sum **Format:** currency **Summary:** #### Variance as % of estimate **Key:** var_pct **Expr:** if(gt(sum_est_cost,0), sum_total_var / sum_est_cost * 100, 0) **Format:** percent **Primary:** true **Description:** Positive means actual cost exceeded the estimate. **Notes:** A positive variance is unfavorable (actual above estimate). Quantity variance uses the estimated rate; rate variance uses the actual quantity, so the two always add up to the total variance. ## Content **Intro:** When a job closes, the useful question is not just how far over or under the estimate it was, but why. This worksheet takes the estimated and actual quantity and rate for each cost element and splits the difference into quantity variance and rate variance. **Sections:** #### How to use it **Body:** Enter one row per cost element on the job: material, labor, machine time, outside processing and freight. For each, put in the estimated quantity or hours and rate from the quote, then the actual quantity or hours and rate from the job cost report. The sheet calculates estimated cost, actual cost, quantity variance, rate variance and total variance. Export it to Excel to attach to the job closeout file or the post-mortem. #### The variance formulas **Body:** Quantity variance is actual quantity minus estimated quantity, multiplied by the estimated rate. It answers whether you used more or fewer hours or units than planned. Rate variance is actual rate minus estimated rate, multiplied by the actual quantity. It answers whether each unit cost more or less than planned. The two add up exactly to actual cost minus estimated cost. This is the same split used for material usage and price variances and for labor efficiency and rate variances in standard costing. **Bullets:** - Quantity variance points to routing, scrap, rework or estimating accuracy. - Rate variance points to purchase price, overtime, wage mix or outdated rates. - Positive numbers are unfavorable, negative numbers are favorable. #### Reading the result **Body:** Start with the largest absolute variance. A large labor quantity variance on a welding job suggests the estimate undercounted fit-up or rework, which should change how the next similar job is quoted. A rate variance on material may be a market price move, and might justify a surcharge clause. Offsetting variances can hide each other, so scan both columns before you conclude the job was on budget. Compare several jobs of the same type to separate one-off problems from systematic estimating bias. #### Common mistakes **Body:** Comparing estimates that included overhead and markup with actuals that did not will create false variances, so compare like with like. Mixing cost elements in one row blurs the cause. Closing the job before all receipts and time are posted understates actual cost. Finally, do not forget to update the estimating database after the review; variance analysis only pays off when the next quote uses what you learned. **Cta:** If job closeout variances live in your ERP, we can build a read-only agent that produces this analysis for every closed job and highlights recurring estimating bias. **Faq:** #### What is job costing variance? **Answer:** It is the difference between the estimated cost of a job and its actual cost. Breaking it down by cost element, and into quantity and rate parts, shows whether the gap came from using more resources or paying more for them. #### What is the difference between quantity and rate variance? **Answer:** Quantity variance measures the effect of using more or fewer units or hours than estimated, valued at the estimated rate. Rate variance measures the effect of a different price or wage per unit, valued at the actual quantity. #### Is a positive variance good or bad? **Answer:** In this sheet, positive means actual cost is above the estimate, which is unfavorable for a cost. A negative variance means the job came in under the estimate. Always check that savings were real and not unposted costs. #### When should I run the variance review? **Answer:** At job closeout, once all material issues, labor tickets, subcontract invoices and freight are posted. For long jobs, run it at milestones so you can correct course before the final cost is locked in. ## Search terms job costing variance worksheet, estimate vs actual job cost, job cost variance analysis excel, job cost report template, quantity and rate variance, actual vs estimated cost worksheet, job closeout cost review, job profitability worksheet --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json