# Effective labor rate worksheet

This worksheet calculates net labor sales per sold hour. It does not recommend prices or calculate profit. Use USD and decimal hours. Thirty minutes is 0.5 hours, not 0.30.

## Quick start on paper

You do not need a spreadsheet or Python for the basic calculation. For each job, write its net labor sales after labor-only reductions and its sold hours.

Use the checked four-job case:

| Job | Net labor sales | Sold hours |
| --- | --- | --- |
| A | $50 | 0.5 |
| B | $210 | 1.5 |
| C | $600 | 4.0 |
| D | $260 | 2.0 |
| Total | $1,120 | 8.0 |

1. Add net labor sales: $50 + $210 + $600 + $260 = $1,120.
2. Add sold hours: 0.5 + 1.5 + 4.0 + 2.0 = 8.0.
3. Divide once: $1,120 ÷ 8.0 = $140.00 per sold hour.

For your own jobs, copy the two amount columns onto paper. Keep invoice IDs and dates beside the rows so you can trace them. Do not average each job's rate. Do not guess a missing sold-hours amount.

## Record the basis before entering rows

| Setting | Fill in |
| --- | --- |
| First and last posted dates, inclusive | ____________________ |
| Shop and job categories included | ____________________ |
| Source invoice list or report and saved filters | ____________________ |
| Labor share of package prices and invoice discounts | ____________________ |
| Price-credit and prior-period adjustment policy | ____________________ |
| Excluded records, dollars, hours, and reasons | ____________________ |
| Checked by and date | ____________________ |

Use posted invoices, including unpaid and partially paid invoices. Here, posted date means the consistent finalized or issued invoice date in your source system. It is not the payment date or job-completion date. Exclude open estimates. Use labor selling amounts only. Do not enter parts, tax, fees, sublets, deposits, wages, or actual job hours. Reconcile to the same scope in your labor-sales report.

For example, a job completed September 30 with an invoice finalized October 1 belongs in the October set under this method. Payment on October 5 does not move it. This date boundary does not replace the shop's accounting policy.

## Use the CSV in a spreadsheet

Open `worksheet.csv` in a spreadsheet. It contains four arithmetic practice rows matching the article, not real customer records. Save a copy before replacing them with your records. Each row represents one unique labor line, not a whole invoice with repeated totals.

Columns A through H are inputs:

| Column | Meaning |
| --- | --- |
| A, record_id | Unique labor-line or adjustment ID. Include invoice ID in the key if your line numbers repeat. |
| B, invoice_id | Original invoice reference, also used for a linked price adjustment |
| C, posted_date | Consistent finalized or issued invoice date from the source system. For adjustments, use the date assigned under your documented review policy. |
| D, kind | `line` for labor sold, `adjustment` for a linked price-only credit or increase |
| E, sold_hours | Decimal hours sold on that line, not technician clock hours |
| F, labor_before_adjustments | Labor selling dollars before reductions. For already-net source data, enter the net amount and zero in G. |
| G, labor_reductions | Positive labor-only discounts or credits. Do not deduct amounts already removed from F. |
| H, note | Allocation method, adjustment reference, or reason for a zero amount |

Add I1 as `Net labor` and J1 as `Row ELR`. In I2 enter `=F2-G2`. In J2 enter `=IF(E2=0,"Undefined",I2/E2)`. Fill both formulas down through your last row. Round by cell display formatting, not by changing the inputs.

For the supplied four rows, place totals below the data:

| Cell | Formula | Expected result |
| --- | --- | --- |
| E6 | `=SUM(E2:E5)` | 8 |
| F6 | `=SUM(F2:F5)` | 1200 |
| G6 | `=SUM(G2:G5)` | 80 |
| I6 | `=SUM(I2:I5)` | 1120 |
| J6 | `=IF(E6=0,"Undefined",I6/E6)` | 140 |

Extend the ranges when adding rows. Never include the total row in its own range. `AVERAGE(J2:J5)` is not the total ELR. It produces 130 for these unequal jobs and fails the check.

The spreadsheet formulas do not filter dates or validate inputs. First remove out-of-period rows from your calculation copy and record them on your exclusions list. Do not rely on hiding or filtering rows, because ordinary SUM still includes them. Resolve blank, duplicate, negative, or unexplained zero-hour inputs before calculating. Spreadsheet formula instructions have been checked algebraically, not executed in Excel or Google Sheets.

For paper use, subtract G from F on each row. Add column E and column I separately. Divide total I by total E. Round only the final answer to two decimal places.

## Review price adjustments

To enter a $30 price credit against RO-D, add a unique record ID, RO-D in column B, zero hours, zero in F, and 30 in G. Set kind to `adjustment`, supply a review date under your recorded policy, and explain the original sale in the note. Match the credit to its source invoice before including it.

The 136.25 result below assumes the credit is included with those invoices under the documented policy. An October-dated credit is outside a September calculation. If the original invoice is outside your selected period, resolve the treatment with your accountant before calculating. Keep the actual credit date and reference in the note if an accountant-approved revised analysis assigns it to an earlier period. Label that analysis as revised. Never change the original invoice's posted date to make it fit.

If an invoice has net labor below zero, check for duplicate credits, already-net inputs, and a credit assigned to the wrong sales. Resolve the difference before using the result.

A price increase uses F for the increase and zero in G. Do not use this price-only row for voids or hour reversals. Resolve those against the source records and rebuild the matched dataset with professional guidance.

## Checks that expose common mistakes

| Change from the four-row file | Correct result |
| --- | --- |
| None | 1120 ÷ 8 = 140.00 |
| Add a linked $30 price credit, with no hour reversal | 1090 ÷ 8 = 136.25 |
| Add 0.5 valid sold hours with $75 labor fully discounted by $75 | 1120 ÷ 8.5 = 131.76 |
| Replace C with one sold hour and $150 labor | 670 ÷ 5 = 134.00 |
| Use only A | 50 ÷ 0.5 = 100.00 |
| Select a period containing no rows | Undefined, not 0.00 |

For a package with $120 total sales, $70 parts sales, and $50 labor sales, enter only the $50 labor share. With 0.5 sold hours its ELR is 100.00. Do not deduct parts cost to create a labor selling amount.

Keep this operational worksheet separate from bookkeeping entries. Ask your accountant to review allocations, credits, bad debts, and period treatment before relying on it for financial decisions.