You run a car workshop and you want to know, at any time, where each car has got to, what every job order comes to in labor and parts, how much the customer has paid and still owes, and how much you earned selling parts. The free car workshop job orders tracker answers these from one book where you open one job order per car. The template is in Arabic.
The problem it solves
In many workshops the job order is on one slip, the parts on another, payments go into the cash notebook, and "who owes us" lives in someone's memory. The same slips repeat: a part fitted but not charged, a part-payment forgotten, a car ready for days while nobody phoned its owner. The sheet keeps all of it in one place and recalculates every time you open it.
How it works, step by step
- Write each customer's code, name and phone in Customers.
- Open one Job orders row per car: number, date, customer code, plate, model, then status, labor fee and any discount.
- Log each part in Parts: quantity, your cost per unit and the price you charge.
- Log each payment in Payments, with the order it was for.
- Change the status as the car moves along. The sheet works out each order's total and balance and the customer's balance.
- Read Debtors and Summary.
What each sheet does
- Customers: order count, total owed, total paid, balance and standing: owes, settled, paid ahead or no orders.
- Job orders: total, paid and remaining per order, with an alert for a ready or delivered car that still has a balance. Cancelled orders are greyed.
- Parts: sale total, cost and profit per part line.
- Payments and Debtors: the payments log, and who owes, largest first, with phone numbers.
- Summary: orders by status, revenue and collection, parts cost and profit.
A walk-through with the numbers in the sheet
The names and figures are sample data inside the sheet, not real people, in local currency.
- J001 (Ahmed Salem): labor 800, an oil filter at 130 and four litres of oil at 230 each, so parts are 1,050 and the total 1,850. He paid 1,850, so nothing is left.
- J002 (Mahmoud Adel): labor 1,200, parts 910, discount 100, total 2,010. He paid 1,500, leaving 510 on a delivered car, so the alert reads "delivered with a balance".
- J004 (Sayed Ramadan): labor 900 and a radiator at 1,900, total 2,800, paid 1,000. The car is "ready", so 1,800 is left and the alert reads "ready, not settled".
- J010 (Yasser Magdy): total 1,000, paid 1,200, so the customer shows "paid ahead" with 200 credit.
Debtors lists four customers: Karim Hassan 3,170, Sayed Ramadan 1,800, Hani Fouad 1,300, then Mahmoud Adel 510. The Summary shows ten orders (one cancelled, so with a zero total), four cars not yet delivered, labor of 7,200, parts sales of 10,830 against a cost of 8,490, so parts profit is 2,340. Order totals come to 17,880 and payments to 11,300.
What you get out of it
Each car's bill sits in one place, each customer's balance is clear instead of remembered, a ready car that is unpaid carries an alert, and parts profit comes from the gap between buying and selling price. It works in Excel 2016 and later, Google Sheets and LibreOffice, free with no sign-up, and holds 200 customers and 1,000 orders.
Using the online version
The template page opens straight on this example. Type in the white cells and the grey ones update at once, and add customers, orders, parts and payments from an entry form. You can import your own xlsx made from this sheet, export your work to xlsx, or download the original Excel file. Your data stays in your browser and is not sent to a server. Open the template page or the template list.
Who it suits, and when it is not enough
It suits a small or mid-size workshop run by the owner or one accountant. Parts profit here leaves out technicians' pay and rent, so do not read it as the workshop's profit. A customer code or order number that was never registered shows in red. If the technician, accountant and front desk each need their own access to the same data, that is what a custom program is for. See also the customer ledger, the installments tracker and the general inventory sheet.
FAQ
How is a job order's total worked out? Labor + parts sales - discount; a cancelled order counts as zero.
Order balance or customer balance? The first covers one order; the second all of the customer's orders and payments.
Is parts profit the workshop's profit? No, only sale price minus purchase price, before wages and expenses.