# Job Costing Worksheet: Estimate vs Actual by Job

> Source: https://netray.co/tools/global-shop-solutions-job-costing-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Compare estimated and actual labor and material for each job and see the cost variance and variance percent. Good for monthly job reviews and quote feedback.

## Category

cost-finance

## Erp

Global Shop Solutions

## Industry

Job Shop Machining

## Audience

- Controller
- Estimator
- Shop owner

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Job number

**Key:** job

**Type:** text

#### Customer

**Key:** customer

**Type:** text

#### Estimated hours

**Key:** est_hrs

**Type:** number

**Format:** decimal

#### Actual hours

**Key:** act_hrs

**Type:** number

**Format:** decimal

#### Labor rate

**Key:** rate

**Type:** number

**Format:** currency

#### Estimated material

**Key:** est_mat

**Type:** number

**Format:** currency

#### Actual material

**Key:** act_mat

**Type:** number

**Format:** currency

#### Sell price

**Key:** price

**Type:** number

**Format:** currency

#### Estimated cost

**Key:** est_cost

**Type:** number

**Expr:** est_hrs * rate + est_mat

**Format:** currency

#### Actual cost

**Key:** act_cost

**Type:** number

**Expr:** act_hrs * rate + act_mat

**Format:** currency

#### Cost variance

**Key:** variance

**Type:** number

**Expr:** act_hrs * rate + act_mat - est_hrs * rate - est_mat

**Format:** currency

#### Variance percent

**Key:** var_pct

**Type:** number

**Expr:** if(gt(est_hrs * rate + est_mat, 0), (act_hrs * rate + act_mat - est_hrs * rate - est_mat) / (est_hrs * rate + est_mat) * 100, 0)

**Format:** percent

#### Actual margin

**Key:** margin

**Type:** number

**Expr:** price - act_hrs * rate - act_mat

**Format:** currency

**Rows:**

**Job:** 24-1182

**Customer:** Hartwell Aero

**Est hrs:** 42

**Act hrs:** 51.5

**Rate:** 78

**Est mat:** 3100

**Act mat:** 3350

**Price:** 9200

**Job:** 24-1190

**Customer:** Pinecrest Medical

**Est hrs:** 28

**Act hrs:** 26

**Rate:** 78

**Est mat:** 1450

**Act mat:** 1450

**Price:** 5600

**Job:** 24-1204

**Customer:** Delta Pump Co

**Est hrs:** 65

**Act hrs:** 88

**Rate:** 78

**Est mat:** 5200

**Act mat:** 5875

**Price:** 13800

**Job:** 24-1211

**Customer:** Ridgeline Tools

**Est hrs:** 14

**Act hrs:** 12.5

**Rate:** 78

**Est mat:** 620

**Act mat:** 610

**Price:** 2400

**Job:** 24-1223

**Customer:** Hartwell Aero

**Est hrs:** 36

**Act hrs:** 37

**Rate:** 78

**Est mat:** 2800

**Act mat:** 2790

**Price:** 7900

**Totals:**

#### Total estimated cost

**Key:** est_cost

**Agg:** sum

**Format:** currency

#### Total actual cost

**Key:** act_cost

**Agg:** sum

**Format:** currency

#### Total cost variance

**Key:** variance

**Agg:** sum

**Format:** currency

#### Total sell price

**Key:** price

**Agg:** sum

**Format:** currency

#### Total actual margin

**Key:** margin

**Agg:** sum

**Format:** currency

**Summary:**

#### Overall variance percent

**Key:** overall_var_pct

**Expr:** sum_variance / sum_est_cost * 100

**Format:** percent

**Primary:** true

#### Actual margin percent

**Key:** margin_pct

**Expr:** sum_margin / sum_price * 100

**Format:** percent

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Does this replace the job costing module in my ERP?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

global shop solutions job costing, job costing worksheet excel, job shop estimate vs actual, job cost variance template, machine shop job costing spreadsheet, job shop profitability by job, actual vs estimated job cost, job costing report manufacturing

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json