Free Excel templates

A free auto body shop model in Excel, with a full worked case

Flagged hours and repair orders, program, insurance and customer-pay work, labor rates and the effective labor rate, paint and materials, parts and sublet, flat-rate and hourly pay, a P&L with adjusted EBITDA and SDE, contribution per body hour and break-even, cash and two offers for the shop.

The file follows Callowhill Collision, a fictional independent shop with 6 body technicians, 3 painters and one booth. It delivers 1,323 repair orders for $4,528,516 of revenue and adjusted EBITDA of $544,313 (12.0%), and breaks even at 885 repair orders a year. Every figure in the book comes from the full model; this simplified version keeps the core so you can put your own shop in it.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other free models.

Auto_Body_Shop_Model_Callowhill.xlsx (16 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is the worked case of The Auto Body Shop Owner. The book explains every assumption and every tab: why body hours set the shop’s output, what a supplement left unwritten costs, how flat-rate pay sits inside the overtime rules, what a program gives and takes, how much cash waits with insurers, and how two buyers would value the shop.

DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide

What is in the file

Eight tabs. Blue cells are inputs; every other cell is a formula.

Inputs
Technicians, painters and hourly staff, flagged hours, burden, flat rates and wages, costs and overhead, marketing, owner adjustments, cash and the two offers; and by source of work: mix, hours per repair order, rates, paint and materials, parts, parts margin and sublet.
Repair Orders and Revenue
Repair orders from flagged hours (1,323 in the case), the average repair order by source, revenue by line and the effective labor rate ($73.94).
Direct Costs and Gross Profit
Flat-rate and hourly pay, paint, parts and sublet at cost, shop supplies and equipment; gross margin (31.3%) and gross profit by revenue line.
Overhead and EBITDA
Overhead with the owner at market pay, estimate requests and marketing, adjusted EBITDA, reported EBITDA and SDE ($674,313).
Contribution and Break-Even
Contribution per repair order by source and in total ($1,244.87), per body hour ($133.86), fixed costs, break-even and margin of safety.
Cash
Insurer receivables, cars in the shop and the cost tied up in them, stock, payables, the weekly production payroll and the cash floor ($81,000).
Value
A multi-shop operator’s offer on adjusted EBITDA with a holdback, an individual buyer’s offer on SDE with an SBA loan and a seller note, the loan payment and its coverage, and the equipment value.
Checks
A comparison with the figures printed in the book: with the case’s inputs every line reads OK.

Three things to try

Flag more hours. Change flagged hours per body technician from 2,050 to 2,200: repair orders, contribution and EBITDA move together, because body hours are the constraint in the case.

Change the mix. Move a share of repair orders from program claims to other insurance claims and watch the effective labor rate, the parts margin and the marketing line.

Shorten the cycle time. Enter 8.5 days instead of 11.0 in the cash inputs: the cost tied up in unfinished cars falls.

Questions

What does a repair order leave in the case?

On average $1,244.87 of contribution after parts, paint and materials, sublet, flat-rate pay, shop supplies, card fees and new-customer marketing: $1,290.17 on a program claim, $1,481.30 on another insurance claim and $605.93 on customer pay.

Does it use macros?

No macros and no circular reference. It recalculates in Excel, LibreOffice or Google Sheets.

Can I use it for my own shop?

Yes: replace every blue input, starting with your technicians, flagged hours, mix of work and posted rates. Callowhill and every figure in it are fictional case assumptions, not market data.

Related reading