Watercare Bill Tracker Excel - Free Template

1. Open the Instructions sheet first. It explains which Bill Tracker cells are inputs and which values calculate automatically.
2. Maintain the account reference on Rates & Lists. Add the Watercare account number, property name, suburb, property type and wastewater factor, then check the GST rate shown as 15%.
3. Enter one invoice per row on Bill Tracker. Add the Bill ID, Watercare Account No., billing dates, bill date and due date exactly as shown on the official invoice.
4. Enter the opening and closing meter readings in kL. The Water Usage column calculates the difference; check that the result agrees with the Watercare invoice.
5. Copy the invoice charges excluding GST into Water Charge, Wastewater Charge, Fixed Service Charge and Other Charges. The workbook calculates Subtotal ex GST, GST and Total Bill.
6. Enter a Paid Date when payment is made and select Paid, Awaiting payment, Overdue or Disputed in Payment Status. Add a bank reference or query in Notes.
7. Review Dashboard throughout the year. It summarises the 2026 calendar year, monthly usage and billed amounts, and counts bills by payment status.

This workbook suits anyone managing more than one Watercare account or simply wanting a reliable history of household water costs. A property manager can record several Auckland properties, a small commercial landlord can monitor tenant-related services, and a household can compare monthly usage without searching through emails every time a bill arrives.

For property managers and landlords

Suppose you manage six Auckland units and receive one invoice per property each month. Six bills over 12 months creates 72 rows, comfortably within the prepared Bill Tracker range of rows 2 to 101. Enter each Watercare Account No. and the lookup formulas bring through the property name, suburb and type from Rates & Lists.

The account reference includes sample properties in Auckland, Manukau, Henderson, Albany, Newmarket, Onehunga and Howick. Replace those populated examples with your own details rather than assuming the sample accounts belong to you. The account number is the useful key: it prevents a bill for one property being filed against another with a similar address.

At bill arrival and month-end

Enter a bill when it arrives, then revisit the row when payment is made. For example, an invoice with 18 kL of usage and charges of $52.00 water, $41.00 wastewater, $28.00 fixed service and $0.00 other charges has a $121.00 subtotal; at 15% GST, the total is $139.15.

At month-end, the Dashboard gives a quick view of billed value and usage for the 2026 calendar year. A facilities administrator can use that view before approving payments, while a household can spot a sharp kL increase before it becomes an expensive leak.

The workbook starts with a GST Rate of 15%, the standard New Zealand rate. It applies that editable rate to the subtotal calculated from the four charge columns, so a $200.00 subtotal produces $30.00 GST and a $230.00 total. Use the official Watercare invoice as the source for each charge and do not replace invoice figures with an estimate based only on meter usage.

Keep the invoice behind the spreadsheet

The spreadsheet is a tracking record, not a substitute for the original bill. Inland Revenue expects business records to be kept for 7 years. Save the Watercare PDF or paper invoice with a matching Bill ID, particularly where the account relates to a GST-registered rental, commercial property or business premises.

Tax treatment can differ between a private household, residential accommodation, commercial premises and mixed-use property. The Instructions sheet therefore specifically says to copy charges from the official invoice and check GST treatment where applicable. That is the right technical approach: preserve the source document and avoid inventing a GST claim from the template total.

Use dates that reconcile to the bill

Enter Billing Period Start and End, Bill Date and Due Date as DD/MM/YYYY dates. The Dashboard groups monthly results using Bill Date. A bill dated 31/03/2026 belongs in March on the dashboard even if its service period began in February.

Payment due dates also matter operationally. If a bill is due on 20/04/2026 and remains unpaid after that date, the Overdue? formula returns Yes. That flag is not a legal demand or Watercare account status; it is a spreadsheet reminder based on TODAY(), the due date and whether Payment Status is Paid.

The most expensive errors usually start with a small data-entry shortcut. A manager types 1,205 instead of 12,052 kL, leaves the opening reading blank, or enters a GST-inclusive amount in a column labelled ex GST. The workbook will calculate from what you enter, so a tidy result can still be wrong.

Meter readings and charge lines

Imagine a closing reading of 8,474 kL and an opening reading of 8,450 kL. Usage should be 24 kL. If the digits are reversed and 8,744 is entered, the displayed usage becomes 294 kL, more than 12 times higher. That distorts the Dashboard total and can hide a real leak when you compare months.

Another common problem is combining all charges into Water Charge. A $61.00 water charge, $53.00 wastewater charge, $28.00 fixed charge and $2.00 adjustment should total $144.00 ex GST, not $61.00. The missing $83.00 then affects the bill total and any payment approval based on the spreadsheet.

Payment records that lose the audit trail

Entering Paid without a Paid Date means Days to Pay cannot calculate. Entering a Paid Date but leaving the status as Awaiting payment makes the Dashboard undercount completed bills and can leave the overdue flag showing Yes after the due date.

For example, six unpaid invoices averaging $139.15 represent $834.90 that still needs action. That may be a simple oversight, but it takes time to reconcile and can lead to late-payment follow-up. Use Notes for the bank reference, disputed line or contact date rather than relying on memory.

Account mix-ups

If two properties are similar, selecting the wrong Watercare Account No. can attach the right dollars to the wrong suburb. Check the populated Property Name and Suburb after entering an account number; if they do not match the invoice, stop before saving the row.

The easiest way to keep this workbook alive is to connect it to an event that already happens. Set aside 10 minutes when each Watercare invoice is downloaded, then spend another 10 minutes on the day you make the payment. That is more reliable than trying to reconstruct 12 months of bills at 31 March.

A short monthly process

• Save the official invoice using the Bill ID, such as WB-2026-014, and enter the invoice row immediately.
• Check opening and closing kL against the bill before entering the charge lines.
• On payment day, enter Paid Date, choose Paid and add the bank reference in Notes.
• At month-end, check Dashboard totals against your bank account and investigate every Overdue? value of Yes.

Keep Rates & Lists controlled. Add new accounts there first, use the available property type validation, and keep wastewater factors between 0 and 1. The Bill Tracker formulas use VLOOKUP to pull account details, so changing an account reference can change the displayed property information for existing rows.

Know when Excel has reached its limit

This file has 100 prepared tracking rows, no charts on Bill Tracker and no automatic import from Watercare. If you are handling 20 properties every month, that is 240 bills a year and the prepared range will be full before the second year ends. At that point, Xero or MYOB will usually provide stronger document storage, bank reconciliation and approval controls.

For a household or a small portfolio, Excel remains practical if you keep one file per reporting period, protect the formula columns and retain the invoices separately. The Dashboard is a review tool; it should not be treated as proof that every bill has been paid unless the payment fields have been updated.