# Job Close-Out Variance Worksheet > Source: https://netray.co/tools/job-shop-job-closeout-variance-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Editable worksheet comparing quoted versus actual hours and material to show variance and margin per closed job. ## Category cost-finance ## Industry Machine Shops ## Audience - Estimator - Shop owner - Controller ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Job number **Key:** job **Type:** text #### Part **Key:** part **Type:** text #### Quoted hours **Key:** est_hours **Type:** number **Unit:** hours #### Actual hours **Key:** act_hours **Type:** number **Unit:** hours #### Shop rate **Key:** rate **Type:** number **Format:** currency **Unit:** per hour #### Quoted material and outside **Key:** est_mat **Type:** number **Format:** currency #### Actual material and outside **Key:** act_mat **Type:** number **Format:** currency #### Invoiced revenue **Key:** revenue **Type:** number **Format:** currency #### Hours variance **Key:** var_hours **Type:** number **Expr:** act_hours - est_hours **Format:** decimal #### Actual cost **Key:** cost **Type:** number **Expr:** act_hours * rate + act_mat **Format:** currency #### Profit **Key:** profit **Type:** number **Expr:** revenue - act_hours * rate - act_mat **Format:** currency #### Margin **Key:** margin **Type:** number **Expr:** if(gt(revenue, 0), (revenue - act_hours * rate - act_mat) / revenue * 100, 0) **Format:** percent **Rows:** **Job:** J-24790 **Part:** Hydraulic manifold **Est hours:** 42 **Act hours:** 38 **Rate:** 85 **Est mat:** 1800 **Act mat:** 1760 **Revenue:** 7200 **Job:** J-24802 **Part:** Aero bracket **Est hours:** 60 **Act hours:** 78 **Rate:** 85 **Est mat:** 2400 **Act mat:** 2650 **Revenue:** 9800 **Job:** J-24815 **Part:** Pump shaft **Est hours:** 25 **Act hours:** 24 **Rate:** 85 **Est mat:** 900 **Act mat:** 900 **Revenue:** 4100 **Job:** J-24833 **Part:** Valve body **Est hours:** 80 **Act hours:** 95 **Rate:** 85 **Est mat:** 3100 **Act mat:** 3300 **Revenue:** 12600 **Totals:** #### Total hours variance **Key:** var_hours **Agg:** sum **Format:** decimal #### Total profit **Key:** profit **Agg:** sum **Format:** currency #### Total revenue **Key:** revenue **Agg:** sum **Format:** currency **Summary:** #### Blended margin **Key:** blended **Expr:** sum_profit / sum_revenue * 100 **Format:** percent **Primary:** true #### Average hours variance per job **Key:** avg_var **Expr:** sum_var_hours / count **Format:** decimal **Notes:** Use actual hours from your ERP labor transactions. Include outside processing in material and outside. Review jobs where hours variance is positive and repeat parts first. ## Content **Intro:** You find out whether a quote was right only after the job ships. This worksheet puts quoted and actual hours and cost side by side for each closed job, so you can find the parts you consistently underprice and fix the estimate next time. **Sections:** #### How to use the worksheet **Body:** After a job is invoiced, add a row with the quoted hours, actual hours, shop rate, quoted and actual material and outside processing, and invoiced revenue. The sheet calculates hours variance, actual cost, profit and margin. Review the biggest positive hours variances first. Export to Excel monthly and keep the file as a record of estimating accuracy. #### The calculations **Body:** Hours variance equals actual hours minus quoted hours. Actual cost equals actual hours times shop rate plus actual material and outside cost. Profit equals revenue minus actual cost, and margin is profit divided by revenue. Because the rate is a blended number, treat margin as a comparison across jobs rather than as accounting profit. **Bullets:** - Positive hours variance means the job ran long - Compare repeat parts across multiple runs - Separate setup variance from run variance when you can - Look at material variance for scrap or price changes #### Reading the result **Body:** One bad job is noise. A pattern is information: if the same operation type runs long on every quote, the standard times are wrong. If material cost runs over on every job, review how you price bar stock and scrap allowance. Update the routing standards in your ERP when the pattern is clear. #### ERP notes **Body:** JobBOSS, E2 Shop System, Global Shop Solutions and ProShop all report estimated versus actual per job. The value of this sheet is the discipline of a short review meeting, plus the freedom to add your own notes. If your ERP numbers disagree, check whether labor entries were captured and whether outside costs were posted to the job. **Cta:** If you want estimated-versus-actual reviews generated automatically from your ERP every week, Netray can help build the report. **Faq:** #### Where do I get actual hours? **Answer:** From labor or shop floor data collection in your ERP, or from timecards if you do not have one. Make sure setup, run and rework time are all attached to the correct job number. #### Should rework hours be included? **Answer:** Yes, include them in actual hours, but consider tracking them separately as well so you can see how much margin is lost to rework rather than estimating error. #### Is the margin an accounting profit? **Answer:** No. It uses one blended shop rate, so it is a management comparison. Your accountant's job cost reports may differ because of overhead allocation methods. #### How often should I review it? **Answer:** Weekly or monthly for jobs closed in the period. Review immediately for any job that lost money, while the details are still fresh. ## Search terms job costing variance spreadsheet, estimated vs actual hours machine shop, job close out review template, job profitability worksheet, quote vs actual job shop, post job review excel --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json