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.
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.
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
Eight tabs. Blue cells are inputs; every other cell is a formula.
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.
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.
No macros and no circular reference. It recalculates in Excel, LibreOffice or Google Sheets.
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.