Job Quotes & Margin Tracker
Made for builders, electricians, plumbers and other trades.
A workbook for a tradesman: record every quote with its customer, labour hours and rate, materials and quoted price; see the margin made on each job and the win rate on quotes. The summary carries a chart that redraws itself as you type.
Of the 36 columns, you fill in 26 and the other 10 work themselves out, and all 18 figures on the summary do too. There is room for 1,450 rows of your own.
£4.99one payment, yours to keep
- Opens in Excel, Google Sheets and Numbers
- Delivered straight away
- No macros, nothing to install
- Change it free before you buy
Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.
Already have a spreadsheet of your own? Check it for mistakes, free.
- A new row is typed into the Quotes tab, one cell at a time, in the white cells a person fills in.
- The shaded cells in that row fill themselves in.
- The figures on the Summary tab change to include the new row.
- The chart, Value quoted by job type, redraws with it.
Everything inside, before you pay
This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 1,450 rows. Open any tab to see its columns.
Customers
10 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| Customer | Type any text. | |
| Contact | Type any text. | |
| Phone | Type any text. | |
| Type any text. | ||
| Type | Pick one: Domestic, Landlord, Letting Agent, Commercial or Builder / Trade. | |
| Town / Area | Type any text. | |
| Quotes Sent | Fills itself in. | The number of rows on Quotes where Customer matches this row's Customer. The formulaIF(ISBLANK([customer_name]), "", COUNTIF([Quotes!customer], [customer_name])) |
| Jobs Won | Fills itself in. | The number of rows on Quotes where Customer matches this row's Customer and Status is “Won”. The formulaIF(ISBLANK([customer_name]), "", COUNTIFS([Quotes!customer], [customer_name], [Quotes!status], "Won")) |
| Value Won | Fills itself in. | The total of Price Quoted on Quotes where Customer matches this row's Customer and Status is “Won”. The formulaIF(ISBLANK([customer_name]), "", ROUND(SUMIFS([Quotes!quoted_price], [Quotes!customer], [customer_name], [Quotes!status], "Won"), 2)) |
| Notes | Type any text. |
Quotes
18 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| Quote No | Your own reference, e.g. Q-1041. Materials lines are matched to this.Type any text. | |
| Date Quoted | Type a date. | |
| Customer | Pick one of the Customer entries on Customers. | |
| Job | Type any text. | |
| Job Type | Pick one: Plumbing, Electrical, Carpentry, Tiling, Painting, Roofing, Bathroom Fit, Kitchen Fit, General Maintenance or Other. | |
| Status | Pick one: Quoted, Won, Lost or Cancelled. | |
| Labour Hours | Type a number. | |
| Hourly Rate | Type an amount in £. | |
| Labour Cost | Fills itself in. | Worked out from this row's Quote No, Labour Hours and Hourly Rate. The formulaIF(ISBLANK([quote_ref]), "", ROUND([labour_hours] * [hourly_rate], 2)) |
| Materials Cost | Fills itself in. | The total of Line Cost on Materials where Quote No matches this row's Quote No. The formulaIF(ISBLANK([quote_ref]), "", ROUND(SUMIF([Materials!quote_ref], [quote_ref], [Materials!line_cost]), 2)) |
| Other Costs | Type an amount in £. | |
| Total Cost | Fills itself in. | Worked out from this row's Quote No, Labour Cost, Materials Cost and Other Costs. The formulaIF(ISBLANK([quote_ref]), "", ROUND([labour_cost] + [materials_cost] + [other_costs], 2)) |
| Price Quoted | Type an amount in £. | |
| Margin | Fills itself in. | Worked out from this row's Quote No, Price Quoted and Total Cost. The formulaIF(ISBLANK([quote_ref]), "", ROUND([quoted_price] - [total_cost], 2)) |
| Margin % | Fills itself in. | Worked out from this row's Quote No, Margin and Price Quoted. The formulaIF(ISBLANK([quote_ref]), "", IFERROR([margin_value] / [quoted_price], 0)) |
| Effective £/Hour | Fills itself in. | Worked out from this row's Quote No, Price Quoted, Materials Cost, Other Costs and Labour Hours. The formulaIF(ISBLANK([quote_ref]), "", IFERROR(([quoted_price] - [materials_cost] - [other_costs]) / [labour_hours], 0)) |
| Decided On | The day the customer said yes or no.Type a date. | |
| Notes | Type any text. |
Materials
8 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| Quote No | Pick one of the Quote No entries on Quotes. | |
| Date | Type a date. | |
| Item | Type any text. | |
| Supplier | Type any text. | |
| Qty | Type a number. | |
| Unit Cost | Type an amount in £. | |
| Line Cost | Fills itself in. | Worked out from this row's Item, Qty and Unit Cost. The formulaIF(ISBLANK([item]), "", ROUND([quantity] * [unit_cost], 2)) |
| Notes | Type any text. |
Summary
18 figures
| Figure | Worked out as |
|---|---|
| Quotes recorded | The number of rows on Quotes with Quote No filled in. The formulaCOUNTA([Quotes!quote_ref]) |
| Quotes still awaiting a decision | The number of rows on Quotes where Status is “Quoted”. The formulaCOUNTIF([Quotes!status], "Quoted") |
| Value out with customers | Price quoted on jobs not yet won or lost. The total of Price Quoted on Quotes where Status is “Quoted”. The formulaROUND(SUMIF([Quotes!status], "Quoted", [Quotes!quoted_price]), 2) |
| Jobs won | The number of rows on Quotes where Status is “Won”. The formulaCOUNTIF([Quotes!status], "Won") |
| Jobs lost | The number of rows on Quotes where Status is “Lost”. The formulaCOUNTIF([Quotes!status], "Lost") |
| Win rate | Won as a share of everything decided; cancelled and pending quotes are left out. Worked out from Status on Quotes. The formulaIFERROR(COUNTIF([Quotes!status], "Won") / (COUNTIF([Quotes!status], "Won") + COUNTIF([Quotes!status], "Lost")), 0) |
| Total value quoted (all quotes) | The total of Price Quoted on Quotes. The formulaROUND(SUM([Quotes!quoted_price]), 2) |
| Value of work won | The total of Price Quoted on Quotes where Status is “Won”. The formulaROUND(SUMIF([Quotes!status], "Won", [Quotes!quoted_price]), 2) |
| Cost of work won | The total of Total Cost on Quotes where Status is “Won”. The formulaROUND(SUMIF([Quotes!status], "Won", [Quotes!total_cost]), 2) |
| Profit on work won | Worked out from Status, Price Quoted and Total Cost on Quotes. The formulaROUND(SUMIF([Quotes!status], "Won", [Quotes!quoted_price]) - SUMIF([Quotes!status], "Won", [Quotes!total_cost]), 2) |
| Average margin on work won | Worked out from Status, Price Quoted and Total Cost on Quotes. The formulaIFERROR((SUMIF([Quotes!status], "Won", [Quotes!quoted_price]) - SUMIF([Quotes!status], "Won", [Quotes!total_cost])) / SUMIF([Quotes!status], "Won", [Quotes!quoted_price]), 0) |
| Average job value won | Worked out from Status and Price Quoted on Quotes. The formulaIFERROR(SUMIF([Quotes!status], "Won", [Quotes!quoted_price]) / COUNTIF([Quotes!status], "Won"), 0) |
| Labour hours on work won | The total of Labour Hours on Quotes where Status is “Won”. The formulaSUMIF([Quotes!status], "Won", [Quotes!labour_hours]) |
| Profit per labour hour on work won | What each hour on site actually earned you after materials. Worked out from Status, Price Quoted, Total Cost and Labour Hours on Quotes. The formulaIFERROR((SUMIF([Quotes!status], "Won", [Quotes!quoted_price]) - SUMIF([Quotes!status], "Won", [Quotes!total_cost])) / SUMIF([Quotes!status], "Won", [Quotes!labour_hours]), 0) |
| Jobs won at under 10% margin | Priced too keenly, worth a look before you quote the next one. The number of rows on Quotes where Status is “Won” and Margin % is below 0.1. The formulaCOUNTIFS([Quotes!status], "Won", [Quotes!margin_pct], "<0.1") |
| Quotes sent this year | Worked out from Date Quoted on Quotes. The formulaCOUNTIFS([Quotes!date_quoted], ">=" & DATE(YEAR(TODAY()), 1, 1)) |
| Quotes sent in the last 30 days | Worked out from Date Quoted on Quotes. The formulaCOUNTIFS([Quotes!date_quoted], ">=" & TODAY() - 30) |
| Materials spend recorded | The total of Line Cost on Materials. The formulaROUND(SUM([Materials!line_cost]), 2) |
More in Business
-
Freelance Invoice & VAT Tracker
Tracks every invoice sent, what is outstanding, what is overdue, and how much VAT is due this quarter, with a reusable client list.
22 figures work themselves out£4.99
-
Small Business Bookkeeping
A simple set of books for a one-person business: record every payment received and every business cost, tag each one with a category, and see totals by category, totals by month and profit so far this year ready for the tax return.
26 figures work themselves out£4.99
-
Etsy Seller Profit Tracker
Record every Etsy sale with fees, postage and materials, and see what each order and each item actually earns.
35 figures work themselves out£4.99
-
Client & Project Tracker
A workbook for tracking clients, the projects running for each one, what is due soon and the fees still to come in.
23 figures work themselves out£4.99