# Auto repair shop profit-margin worksheet

Use this worksheet for one closed period. Copy or print it and fill the last column. The amounts are an invented worked case, not targets. Accounting and tax treatment require your accountant’s review.

Shop: ____________________

Period start and end: ____________________

Prepared by and date: ____________________

Bookkeeper or accountant review: ____________________

Accounting basis and business structure: ____________________

Technician cost classification: ____________________

Owner compensation already in expenses and its location: ____________________

## Set the scope first

The case uses one month of accrual management reporting for a sole proprietor. It has no owner wages, no business income tax expense, and no other income. Personal income and self-employment taxes are outside this calculation. This is not a tax-return worksheet or a change to a tax accounting method.

All employee technician wages, paid nonbillable time, employer payroll taxes, and benefits are in cost of sales. Other employee payroll is in overhead. Do not deduct employee withholding a second time. Do not repeat payroll-related insurance in other insurance.

Parts use consistent inventory cost valuation. Purchases include assigned freight. Supplier credits reduce purchases once. There is no work in progress, shrinkage, personal inventory use, or separate inventory write-down in the case. Ask your accountant to extend the worksheet for those items.

Buyer-imposed sales tax is excluded from sales and customer receivables here. All tax charged in this case is collected in cash. Deposits remain liabilities until work is earned. The $500 applied deposit is already included in earned sales and not in receivables. No tax is charged on unearned deposits in this simplified case. Do not generalize that assumption into a tax rule.

If your records have seller-imposed tax, tax in receivables, unpaid equipment, new loans, capital contributions, other accruals, or asset disposals, have the cash bridge extended. Do not force those items into unrelated rows to make the result balance.

## 1. Build net sales

| Row | Amount or calculation | Worked case | Your amount |
| --- | --- | --- | --- |
| A | Parts sales before adjustments | $32,000 | |
| B | Parts discounts | $800 | |
| C | Parts refunds and allowances | $400 | |
| D | Net parts sales = A − B − C | $30,800 | |
| E | Labor sales before adjustments | $44,000 | |
| F | Labor discounts | $1,200 | |
| G | Labor refunds and allowances | $300 | |
| H | Net labor sales = E − F − G | $42,500 | |
| I | Net sublet sales after adjustments | $2,500 | |
| J | Net sales = D + H + I | **$75,800** | |

Enter discounts and refunds as positive reductions. If the source report is already net, do not subtract them twice. Flag credits for earlier periods. Do not assume a customer refund creates a supplier credit.

## 2. Deduct costs once

| Row | Amount or calculation | Worked case | Your amount |
| --- | --- | --- | --- |
| K | Beginning parts inventory | $10,000 | |
| L | Parts purchases before supplier credits | $19,000 | |
| M | Supplier credits | $1,000 | |
| N | Ending parts inventory | $11,000 | |
| O | Parts cost = K + L − M − N | $17,000 | |
| P | Employee technician wages | $20,000 | |
| Q | Technician employer taxes and benefits | $4,000 | |
| R | Sublet cost | $1,800 | |
| S | Cost of sales = O + P + Q + R | $42,800 | |
| T | Gross profit = J − S | **$33,000** | |
| U | Other employee payroll and employer costs | $6,500 | |
| V | Rent | $5,000 | |
| W | Utilities | $1,100 | |
| X | Insurance outside payroll burden | $1,300 | |
| Y | Software | $900 | |
| Z | Marketing | $1,000 | |
| AA | Other operating costs, including fees not elsewhere | $1,700 | |
| AB | Book depreciation | $1,200 | |
| AC | Operating expenses = U + V + W + X + Y + Z + AA + AB | $18,700 | |
| AD | Operating profit = T − AC | **$14,300** | |
| AE | Interest expense, excluding principal | $500 | |
| AF | Pretax profit = AD − AE | **$13,800** | |

There is no nonoperating income in the case. Do not omit it if it exists in your books. Entity income tax is not estimated here. The worksheet deliberately stops at pretax profit.

## 3. Calculate margins and the separate owner-pay adjustment

| Row | Amount or calculation | Worked case | Your amount |
| --- | --- | --- | --- |
| AG | Owner compensation already included in expenses above | $0 | |
| AH | Estimated replacement cost of owner duties, with employer costs | $6,400 | |
| AI | Owner-adjusted pretax profit = AF + AG − AH | **$7,400** | |
| AJ | Gross margin = T ÷ J × 100 | 43.54% | |
| AK | Operating margin = AD ÷ J × 100 | 18.87% | |
| AL | Pretax margin = AF ÷ J × 100 | 18.21% | |
| AM | Owner-adjusted pretax margin = AI ÷ J × 100 | 9.76% | |
| AN | Parts margin = (D − O) ÷ D × 100 | 44.81% | |
| AO | Billed hours corresponding to net labor sales | 340 | |
| AP | Effective labor rate = H ÷ AO | $125 | |

If net sales, parts sales, or billed hours are zero or negative, leave the related ratio undefined and investigate. A negative profit with positive sales is a valid loss margin. Do not average category percentages to get the shop margin.

AG is a memo entry, not another expense. In the case it is zero because the owner takes draws. For other structures, confirm the compensation classification with your accountant. Never put a draw or distribution in AG. The replacement estimate is planning advice, not a payroll recommendation or deductible expense.

Replacement-cost evidence and duties covered: ____________________

## 4. Reconcile profit to cash, optional

Complete this section only if you need to explain the change in cash and have every listed input. It is separate from the profit-margin calculation. This bridge starts with AF, not AI. The owner-pay adjustment is not a cash transaction.

| Row | Amount or calculation | Worked case | Your amount |
| --- | --- | --- | --- |
| AQ | Beginning customer receivables | $8,000 | |
| AR | Ending customer receivables | $12,500 | |
| AS | Beginning operating supplier payables | $6,000 | |
| AT | Ending operating supplier payables | $8,000 | |
| AU | Beginning payroll liabilities | $2,000 | |
| AV | Ending payroll liabilities | $3,000 | |
| AW | Buyer sales tax collected in cash | $2,000 | |
| AX | Buyer sales tax remitted | $1,700 | |
| AY | New unearned customer deposits | $2,000 | |
| AZ | Earlier deposits applied to earned sales | $500 | |
| BA | Operating cash = AF + AB − (AR − AQ) − (N − K) + (AT − AS) + (AV − AU) + AW − AX + AY − AZ | **$14,300** | |
| BB | Equipment bought for cash | $9,000 | |
| BC | Loan principal paid | $1,500 | |
| BD | Owner draw | $6,000 | |
| BE | Cash change = BA − BB − BC − BD | **−$2,200** | |
| BF | Beginning cash | $20,000 | |
| BG | Calculated ending cash = BF + BE | **$17,800** | |
| BH | Actual ending cash from reconciled records | $17,800 | |
| BI | Difference = BG − BH | **$0** | |

The case assumes all other operating expenses and interest are paid in the month, with no other balance changes. Beginning sales tax payable is $500, ending $800. Beginning unearned deposits are $1,000, ending $2,500. Loan balance falls from $20,000 to $18,500.

Equipment net book value rises from $30,000 to $37,800 after the $9,000 purchase and $1,200 depreciation. Beginning equity is $38,500. Ending equity is $46,300 after $13,800 profit and $6,000 drawings. Assets equal liabilities plus equity at both dates.

Investigate any nonzero difference. A zero difference checks the entered amounts, not whether every accounting choice is right.

## Use the CSV and formula checker

Open `worksheet.csv` in a spreadsheet or a text editor. It contains manual input fields, worked amounts, a blank `your_amount` column, and formula descriptions in plain text. It is not a self-calculating spreadsheet. The formulas use row IDs, not spreadsheet cell references, so opening the file does not calculate a result.

For manual use, fill the blank working column above or calculate from the CSV’s named formulas. For automatic calculation, Python 3 is required. No packages or network access are needed.

Run the supplied case and independent tests from this directory:

```sh
python3 check_worksheet.py
```

To calculate your own period, copy the CSV to a private location. Fill `your_amount` for every input row, including explicit zeros. Leave derived rows blank. Run:

```sh
python3 check_worksheet.py --input /private/path/your-month.csv
```

The command reports derived values and the cash difference. It refuses missing inputs or negative sales denominators. It reports an undefined ratio for a zero denominator. It does not certify your books or suggest a margin target. Keep real shop financial records private.

## Review before using the result

- All inputs belong to the same period and accounting basis.
- Discounts, credits, payroll, and tax are counted once.
- Inventory, receivables, payables, and cash agree with reconciled records.
- Owner compensation and personal tax exclusions are labeled.
- The cash difference is zero or has a documented explanation.
- An accountant has reviewed any departures from the case assumptions.

Accounting questions still open: ____________________

One issue to investigate next period: ____________________