sheetsmith

Templates › Business and freelance

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.

Tabs
4
Columns
36
Fill themselves in
28

£7one 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.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

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.

Customers

Everyone you quote for. Add a customer here first so it appears in the dropdown on the Quotes tab. Room for 150 rows.

ColumnTypeWorked out as
Customertext
Contacttext
Phonetext
Emailtext
Typechoice (list)
Town / Areatext
Quotes SentnumberIF(ISBLANK([customer_name]), "", COUNTIF([Quotes!customer], [customer_name]))
Jobs WonnumberIF(ISBLANK([customer_name]), "", COUNTIFS([Quotes!customer], [customer_name], [Quotes!status], "Won"))
Value WoncurrencyIF(ISBLANK([customer_name]), "", ROUND(SUMIFS([Quotes!quoted_price], [Quotes!customer], [customer_name], [Quotes!status], "Won"), 2))
Notestext

Quotes

One row per quote. Fill in the hours, your rate and the price you quoted. Materials pull through from the Materials tab, and the margin works itself out. Room for 300 rows.

ColumnTypeWorked out as
Quote Notext
Date Quoteddate
Customerchoice (from Customers › customer_name)
Jobtext
Job Typechoice (list)
Statuschoice (list)
Labour Hoursnumber
Hourly Ratecurrency
Labour CostcurrencyIF(ISBLANK([quote_ref]), "", ROUND([labour_hours] * [hourly_rate], 2))
Materials CostcurrencyIF(ISBLANK([quote_ref]), "", ROUND(SUMIF([Materials!quote_ref], [quote_ref], [Materials!line_cost]), 2))
Other Costscurrency
Total CostcurrencyIF(ISBLANK([quote_ref]), "", ROUND([labour_cost] + [materials_cost] + [other_costs], 2))
Price Quotedcurrency
MargincurrencyIF(ISBLANK([quote_ref]), "", ROUND([quoted_price] - [total_cost], 2))
Margin %percentIF(ISBLANK([quote_ref]), "", IFERROR([margin_value] / [quoted_price], 0))
Effective £/HourcurrencyIF(ISBLANK([quote_ref]), "", IFERROR(([quoted_price] - [materials_cost] - [other_costs]) / [labour_hours], 0))
Decided Ondate
Notestext

Materials

Materials bought or priced in for each quote. Each line adds itself to that quote's materials cost. Room for 1,000 rows.

ColumnTypeWorked out as
Quote Nochoice (from Quotes › quote_ref)
Datedate
Itemtext
Suppliertext
Qtynumber
Unit Costcurrency
Line CostcurrencyIF(ISBLANK([item]), "", ROUND([quantity] * [unit_cost], 2))
Notestext

Summary

Win rate and the money made, updating as you fill the Quotes tab in.

MeasureWorked out as
Quotes recordedCOUNTA([Quotes!quote_ref])
Quotes still awaiting a decisionCOUNTIF([Quotes!status], "Quoted")
Value out with customersROUND(SUMIF([Quotes!status], "Quoted", [Quotes!quoted_price]), 2)
Jobs wonCOUNTIF([Quotes!status], "Won")
Jobs lostCOUNTIF([Quotes!status], "Lost")
Win rateIFERROR(COUNTIF([Quotes!status], "Won") / (COUNTIF([Quotes!status], "Won") + COUNTIF([Quotes!status], "Lost")), 0)
Total value quoted (all quotes)ROUND(SUM([Quotes!quoted_price]), 2)
Value of work wonROUND(SUMIF([Quotes!status], "Won", [Quotes!quoted_price]), 2)
Cost of work wonROUND(SUMIF([Quotes!status], "Won", [Quotes!total_cost]), 2)
Profit on work wonROUND(SUMIF([Quotes!status], "Won", [Quotes!quoted_price]) - SUMIF([Quotes!status], "Won", [Quotes!total_cost]), 2)
Average margin on work wonIFERROR((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 wonIFERROR(SUMIF([Quotes!status], "Won", [Quotes!quoted_price]) / COUNTIF([Quotes!status], "Won"), 0)
Labour hours on work wonSUMIF([Quotes!status], "Won", [Quotes!labour_hours])
Profit per labour hour on work wonIFERROR((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% marginCOUNTIFS([Quotes!status], "Won", [Quotes!margin_pct], "<0.1")
Quotes sent this yearCOUNTIFS([Quotes!date_quoted], ">=" & DATE(YEAR(TODAY()), 1, 1))
Quotes sent in the last 30 daysCOUNTIFS([Quotes!date_quoted], ">=" & TODAY() - 30)
Materials spend recordedROUND(SUM([Materials!line_cost]), 2)