Data Files samples / Builder
Kilnside Builders
Enquiries, site visits, quotes, contracts, stage payments, variations, retentions, job costs and the site diary for a small building firm.
Queries
The questions this database answers, saved under a name. Open one to see its answer, or its design to see how it is built and change it.
- Enquiries to follow up
Enquiries not yet rung back, waiting for a visit, or visited and waiting for a quote, the oldest first, with how many days each has waited.
From Enquiries, Clients. Shows Received On, Client, Phone, Work, Description, Status, Visit Date, Days Waiting.
Calculated: Days Waiting is
DateDiff("d", [Received On], Date()).Only where
Status In ("New", "Visit booked", "Visited").- Site visits, next 14 days
Visits booked for the next fortnight, with the address, phone number and who is going.
From Enquiries, Clients, Staff. Shows Visit Date, Visiting, Client, Address, City, Postcode, Phone, Work, Description, Days Ahead.
Calculated: Visiting is
[First Name] & " " & [Last Name]; Days Ahead isDateDiff("d", Date(), [Visit Date]).Only where
Days Ahead Between 0 And 14andStatus = Visit booked.- Quotes awaiting an answer
Quotes sent and not yet accepted or declined, the oldest first, with the days since they went.
From Quotes, Clients. Shows Quote No, Client, Phone, Title, Quote Date, Valid Until, Days Since, Total.
Calculated: Days Since is
DateDiff("d", [Quote Date], Date()).Only where
Status = Sent.- Quote success by work
For each kind of work, how many quotes were accepted, declined, expired or are still out, and their value: where the firm wins work.
From Quotes, Enquiries. Shows Work, Status, Quotes, Value, with totals.
- Jobs by stage
Jobs booked and on site, with the stage each has reached, its site manager and the days left to the planned finish (below nought means late).
From Jobs, Clients, Staff. Shows Job, Client, Title, Status, Current Stage, Site Manager, Start Date, Finish Date, Days to Finish.
Calculated: Site Manager is
[First Name] & " " & [Last Name]; Days to Finish isDateDiff("d", Date(), [Finish Date]).Only where
Status In ("Booked", "On site").- Stage payments due
Stages not yet reached that should be in the next 30 days, or are late, with the payment each brings: the money coming in.
From Job Stages, Jobs, Clients. Shows Job, Client, Stage, Planned Finish, Days Left, Amount.
Calculated: Days Left is
DateDiff("d", Date(), [Planned Finish]).Only where
Days Left <= 30andDone On Is Null.- Invoiced against contract
For each job, the contract sum beside the work invoiced so far before VAT (variations included, retention not taken off), the retention still held, and what has been billed with VAT, paid and is owed.
From Jobs, Invoices. Shows Job, Title, Status, Contract Sum, Work Invoiced, Retention Held, Billed with VAT, Paid, Owing, with totals.
Calculated: Work Invoiced is
IIf([Kind] = "Retention", 0, [Gross]); Retention Held isIIf(Nz([Retention Released On], "") = "", [Retention], 0); Paid isIIf(Nz([Paid On], "") = "", 0, [Total]); Owing isIIf(Nz([Paid On], "") = "", [Total], 0).- Unpaid invoices
Invoices not yet paid, by due date, with the days overdue (below nought means not due yet).
From Invoices, Jobs, Clients. Shows Invoice No, Client, Job, Kind, Invoice Date, Due Date, Total, Days Overdue.
Calculated: Days Overdue is
DateDiff("d", [Due Date], Date()).Only where
Paid On Is Null.- Variations by job
Every variation, job by job, with its reason, amount, extra days and whether it was agreed.
From Variations, Jobs, Clients. Shows Job, Client, Variation, Raised On, Description, Reason, Amount, Extra Days, Status, Agreed On.
- Variations awaiting agreement
Variations written up and not yet signed by the client. Get these signed before the work is done.
From Variations, Jobs, Clients. Shows Variation, Job, Client, Phone, Raised On, Description, Amount, Extra Days, Days Waiting.
Calculated: Days Waiting is
DateDiff("d", [Raised On], Date()).Only where
Status = Proposed.- Job profit
Each finished job's value (contract, variations, contingency credited back) beside its labour, materials and subcontract costs, and the margin.
From Jobs, Job Ledger. Shows Job, Title, Work, Completed On, Value, Labour, Materials, Subcontract, Costs, Margin, with totals.
Calculated: Value is
IIf(([Kind] = "Contract" Or [Kind] = "Variation" Or [Kind] = "Contingency"), [Net], 0); Labour isIIf([Kind] = "Labour", [Net], 0); Materials isIIf([Kind] = "Materials" Or [Kind] = "Plant", [Net], 0); Subcontract isIIf([Kind] = "Subcontract", [Net], 0); Costs isIIf(([Kind] = "Contract" Or [Kind] = "Variation" Or [Kind] = "Contingency"), 0, [Net]); Margin isIIf(([Kind] = "Contract" Or [Kind] = "Variation" Or [Kind] = "Contingency"), [Net], 0 - [Net]).Only where
Status In ("Defects period", "Closed").- Job costs to date
The same for jobs on site: the value so far against the costs so far. The margin is only fair once the job is near its end.
From Jobs, Job Ledger. Shows Job, Title, Current Stage, Value, Labour, Materials, Subcontract, Margin So Far, with totals.
Calculated: Value is
IIf(([Kind] = "Contract" Or [Kind] = "Variation" Or [Kind] = "Contingency"), [Net], 0); Labour isIIf([Kind] = "Labour", [Net], 0); Materials isIIf([Kind] = "Materials" Or [Kind] = "Plant", [Net], 0); Subcontract isIIf([Kind] = "Subcontract", [Net], 0); Margin So Far isIIf(([Kind] = "Contract" Or [Kind] = "Variation" Or [Kind] = "Contingency"), [Net], 0 - [Net]).Only where
Status = On site.- Retentions due for release
Finished jobs still in their defects period, with when the retention can be released and how much is held. Book the snagging visit before the date.
From Jobs, Clients, Invoices. Shows Job, Client, Title, Completed On, Retention Due, Days to Release, Retention Held, with totals.
Calculated: Days to Release is
DateDiff("d", Date(), [Retention Due]).Only where
Status = Defects period.- Subcontractors unpaid bills
Subcontractors' invoices not yet paid, by subcontractor and due date, with what to pay and the days overdue.
From Job Ledger, Suppliers, Jobs. Shows Subcontractor, Trade, Job, Their Ref, Bill Date, Due Date, Net, VAT, To Pay, Days Overdue.
Calculated: To Pay is
Round([Net] + Nz([VAT]), 2); Days Overdue isDateDiff("d", [Due Date], Date()).Only where
Kind = SubcontractandPaid On Is Null.- Labour by person, last 4 weeks
The hours each person was costed to jobs over the last four weeks, and what that cost.
From Job Ledger, Staff. Shows Name, Trade, Crew, Hours, Cost, with totals.
Calculated: Name is
[First Name] & " " & [Last Name].Only where
Kind = LabourandDays Ago Between 0 And 27.- Site diary, last 3 weeks
The diary for every job over the last three weeks, newest first.
From Site Diary, Jobs. Shows Job, Title, Diary Date, Weather, On Site, Work Done, Problems, Visitors.
Only where
Days Ago Between 0 And 20.- Invoiced by month
Work invoiced each month, with the retention held and the VAT, retention releases left out.
From Invoices. Shows Month, Invoices, Work Invoiced, Retention Held, VAT, with totals.
Calculated: Month is
Format([Invoice Date], "yyyy-mm").Only where
Kind <> "Retention".- Costs by month
Costs charged to jobs each month by kind: labour, materials, plant and subcontract.
From Job Ledger. Shows Month, Kind, Cost, with totals.
Calculated: Month is
Format([Entry Date], "yyyy-mm").Only where
Kind In ("Labour", "Materials", "Plant", "Subcontract").
Forms
Every table has a form of the same name: Start Here, Business, Clients, Staff, Suppliers, Enquiries, Quotes, Quote Lines, Jobs, Job Stages, Variations, Invoices, Invoice Lines, Job Ledger, Site Diary. A form shows one record at a time and adds new ones. A field that points at another table is a list of that table's records, and the records that point at this one are listed below it.
Reports
Pages to print or save as a PDF, laid out from a query or a table.
- Jobs on the go
The jobs booked and on site with their stage and finish date, for the Monday meeting.
From the query Jobs by stage: Job, Client, Title, Status, Current Stage, Site Manager, Start Date, Finish Date, Days to Finish, with the count of Job.
- Quotes to chase
Quotes waiting for an answer, with phone numbers, for a round of calls.
From the query Quotes awaiting an answer: Quote No, Client, Phone, Title, Quote Date, Valid Until, Days Since, Total, with the total of Total.
- Stage payments coming up
Stage payments due in the next month, with a total: the cash coming in.
From the query Stage payments due: Job, Client, Stage, Planned Finish, Days Left, Amount, with the total of Amount.
- Invoices due
Unpaid invoices by client, with days overdue and a total for each.
From the query Unpaid invoices, a group for each Client: Invoice No, Job, Kind, Invoice Date, Due Date, Days Overdue, Total, with the total of Total.
- Contract account
Each job's contract sum, what has been invoiced, the retention held, paid and owing.
From the query Invoiced against contract: Job, Title, Status, Contract Sum, Work Invoiced, Retention Held, Billed with VAT, Paid, Owing, with the total of Work Invoiced, total of Retention Held, total of Owing.
- Job profit
Finished jobs with their value, costs and margin, and the totals.
From the query Job profit: Job, Title, Completed On, Value, Labour, Materials, Subcontract, Margin, with the total of Value, total of Costs, total of Margin.
- Variations register
Every variation by job, with the total each job has moved by.
From the query Variations by job, a group for each Job: Variation, Raised On, Description, Reason, Status, Extra Days, Amount, with the total of Amount.
- Retentions held
The retention held on each job in its defects period, and when it is due.
From the query Retentions due for release: Job, Client, Title, Completed On, Retention Due, Days to Release, Retention Held, with the total of Retention Held.
- Subcontractors to pay
Subcontractors' unpaid bills, a section for each, for the payment run.
From the query Subcontractors unpaid bills, a group for each Subcontractor: Job, Their Ref, Bill Date, Due Date, Days Overdue, To Pay, with the total of To Pay.
- Site diary
The last three weeks' diary, a section for each job.
From the query Site diary, last 3 weeks, a group for each Job: Diary Date, Weather, On Site, Work Done, Problems, Visitors, with the count of Diary Date.
- Monthly invoicing
Work invoiced each month with retention and VAT, and the totals.
From the query Invoiced by month: Month, Invoices, Work Invoiced, Retention Held, VAT, with the total of Work Invoiced, total of VAT.
- Start here: the everyday tasks, step by step
The guide to this database printed: each everyday task and its steps, from the Start Here table.
From the table Start Here, a group for each Task: Step, What to do.
Print templates
What the business sends out, one for each record, with its own name, address and payment details at the head from the Business table. Print one from its form, or choose which to print under Print templates, or save them as a PDF. The words in braces are filled in from the record.
Quotation
The quote sent after a site visit: the work in sections with each line's quantity, rate and amount, the price before and with VAT, how long it holds, what it leaves out, and a place for the client to sign. One prints for each quote.
One for each record of Quotes, on A4 paper.
Quotation {Number}
Prepared for · Quote number · Address of the work · Date · Town · Price holds until · Postcode · Weeks on site, about · The work
Thank you for having us round. This is our price for the work below, as we discussed on site. {Notes}
A line for each record of Quote Lines: Section, Description, Qty, Unit, Rate, Amount, VAT
Price before VAT · VAT · Price with VAT
The price is fixed for the work described. Where there is a contingency line, it is a sum held for work nobody can see before we start, such as rotten timber or a drain in the way; any such work is priced and agreed with you first as a variation, and the contingency is credited back on the final invoice. Larger work is paid by stages as it goes, a deposit when the contract is signed and then a payment as each stage is reached, as the contract sets out. Repairs are invoiced when they are finished.
Not included unless listed: planning and building control fees, the party wall surveyor, floor coverings, decorating, and moving gas or electricity meters. To go ahead, sign below and send it back, and we will draw up the contract with a start date.
Signed: Accepted by the client, For the builder
Contract
The building contract for a job, in plain words: the parties, the works, the price and the contingency, the stage payments as a table, start and finish, variations, retention, defects, the site, insurance, ending the contract and the right to cancel, with signatures. It prints for every job, repairs included. It is a starting point: have it checked by someone who knows the law where you work.
One for each record of Jobs, on A4 paper.
Building contract {Number}
The client · Contract number · Address of the works · Date of this contract · Town · Our quotation · Postcode · Contract sum, before VAT · Start · Finish, about
THIS CONTRACT is made on {Signed On} between {Business.Name} of {Business.Address}, {Business.City} {Business.Postcode} ("the Builder") and {Client.Name} of {Client.Address}, {Client.City} {Client.Postcode} ("the Client").
1. The works. The Builder will carry out the works described in quotation {Quote.Number}, {Title}, at the Client's property above. The work will be done with care and skill, with materials of the kind described in the quotation, and to meet the building regulations and any planning permission for it.
2. The price. The contract sum is {Contract Sum} plus VAT at the rate in force when each invoice is raised. It includes a contingency of {Contingency} for work that cannot be seen before the works start. The contingency is not spent by the Builder on its own account: work it was meant for is agreed as a variation under clause 5, and the whole contingency is credited to the Client on the final invoice.
3. Stage payments. The Builder will invoice the payments below as each stage is reached. Each invoice after the deposit carries any variations agreed since the last one, less the retention in clause 6. The Client will pay each invoice within {Client.Payment Days} days of its date. If a payment is late the Builder may stop work, after giving seven days' notice in writing, until it is paid.
A line for each record of Job Stages: Stage, Payment due when, Percent, Amount before VAT, Expected
Contract sum, before VAT
4. Start and finish. The Builder will start on {Start Date} and expects to finish by {Finish Date}. The Builder will tell the Client as soon as anything will hold the work up, such as bad weather, a change asked for by the Client, a problem found once work has started, or materials that cannot be had. The finish date moves by the time lost.
5. Variations. Any change to the works, whether the Client asks for it or it is found to be needed once work has started, is written on a variation order with its price and any change to the finish date, and signed by both before the work is done. Only work needed at once to keep the building safe or dry may be done first, and it is then written up within two working days.
6. Retention. The Client may hold back {Retention Percent} percent of each invoice after the deposit (none when that figure is nought). The amount held is released {Defects Months} months after the works are finished, once any defects reported in that time have been put right.
7. Defects. For {Defects Months} months after the works are finished the Builder will come back and put right, free of charge, any fault in the work or in materials the Builder supplied, when the Client reports it in writing. This does not cover fair wear and tear, damage done by others, or the hairline cracks of new plaster and timber drying out, which are filled at the end of the period.
8. The site. The Client will give access during working hours, 8 until 5 on weekdays, and the use of water and electricity. The Builder will protect the parts of the house not being worked on, keep the site tidy and safe, and take away the waste.
9. Insurance and permissions. The Builder holds public liability insurance and insures the works until they are handed over. The Client will tell their home insurer about the works before they start, and is responsible for planning permission and any party wall notices unless the quotation says otherwise.
10. Ending the contract. If either side seriously breaks this contract and does not put it right within fourteen days of being asked in writing, the other may end it. The Builder is then paid for the work properly done and the materials bought for the works up to that day.
11. Changing your mind. Where this contract is signed at the Client's home, the Client may cancel it within fourteen days of signing, without giving a reason, and any deposit is repaid in full, unless the Client asked for work to start within those fourteen days.
Signed: Signed for the Client, Signed for the Builder
Stage invoice
The VAT invoice for a deposit, a stage, completion or the retention release: the stage payment, the variations agreed since the last invoice, the contingency credited at the end, the retention taken off, the VAT and the total due.
One for each record of Invoices, on A4 paper.
VAT invoice {Number}
Invoice to · Invoice number · Address · Invoice date (tax point) · Town · Payment due · Postcode · Invoice for · Contract · Contract sum · The works
A line for each record of Invoice Lines: No, Description, Amount, VAT %, VAT
Amount before VAT · VAT · Total due
Retention held on this invoice: {Retention}. {Business.Payment Terms} Please quote {Number} with your payment, by bank transfer to {Business.Bank Details}. VAT number {Business.Tax Number}.
{Business.Document Footer}
Variation order
A change to a contract for the client to sign before the work is done: what changes, why, what it adds to or takes off the price, and the extra working days.
One for each record of Variations, on A4 paper.
Variation order {Number}
Client · Variation number · Address of the works · Date raised · Contract · Reason · The works · Status
Under clause 5 of contract {Job.Number}, the works are changed as follows:
{Description}
Change to the contract sum, before VAT · VAT · Change with VAT · Extra working days
An amount below nought is work taken out, and comes off the contract sum. The finish date moves by the extra working days shown. Once both sides have signed, the work goes ahead and the amount is added to the next stage invoice. Everything else in the contract stays as it was.
Signed: Agreed by the Client, For the Builder
Statement
Each client's invoices with what has been paid and the balance owing. It prints one for every client; choose those with something owing.
One for each record of Clients, on A4 paper.
Statement of account
Client · Days to pay · Address · Town · Postcode
A line for each record of Invoices: Invoice, Date, Contract, For, Amount, Paid, Owing
Statement date · Balance owing
An invoice with no date paid is still owed. Retention held under your contract is not shown as owing until it is invoiced at the end of the defects period. If you have paid in the last few days, thank you, and please ignore this.
Bank transfer to {Business.Bank Details}.
Back to the overview of Kilnside Builders