A useful utility bill tracker records the site, meter or account, billing period, usage, unit and cost. It also keeps the invoice reference and any question that needs resolving. Those extra fields matter when a finance colleague asks why the total changed.
Our free Excel workbook is built for teams collecting bills from several buildings. It includes fictional examples, a cost-per-unit calculation and a duplicate-reference flag. It does not read bills automatically or estimate missing consumption.
In this guide
Preview the actual workbook
Fictional entries from the downloadable file. Open the image full-size to inspect the wide table.
Record the bill before calculating a performance metric
Start with the figures printed on the bill. Keep usage and money in separate columns, and record the currency explicitly. Electricity in kWh, gas in cubic metres and water in cubic metres cannot be added into a meaningful single usage total.
| Record | Example | Check before using it |
|---|---|---|
| Site and meter/account | Shop A, meter E-01 | Does the bill cover the intended premises? |
| Invoice reference | INV-1042 | Has the same reference already been entered for this account? |
| Period start and end | 1 to 30 September | Does it overlap another bill or leave a gap? |
| Usage and unit | 12,000 kWh | Is it actual, estimated or adjusted? |
| Cost and currency | 4,200 MYR | Does the amount include tax and standing charges? |
| Reading basis | Actual | Is there a later correction? |
| Source reference | Retained invoice location | Can a reviewer open it? |
| Follow-up | Confirm meter transfer date | Who will resolve the question? |
Record the supplier's period convention consistently. Some bills include both dates. Others represent a start reading and an end reading. A monthly label alone can hide these differences.
A three-bill example
The following numbers are fictional and are not energy-price benchmarks.
| Site | Bill usage | Bill cost | Reading basis | Cost per billed unit |
|---|---|---|---|---|
| Shop A | 12,000 kWh | MYR 4,200 | Actual | MYR 0.35/kWh |
| Shop B | 8,000 kWh | MYR 3,200 | Estimated | MYR 0.40/kWh |
| Warehouse C | 0 kWh | MYR 150 | Actual | Not calculated |
For Shop A, divide 4,200 by 12,000 to get MYR 0.35 per billed kWh. This is an effective bill-cost ratio. It is not necessarily the supplier's energy tariff because the numerator may include fixed charges, tax or other items.
Warehouse C still has a cost even though its recorded usage is zero. The workbook leaves the ratio unavailable rather than dividing by zero. Shop B needs follow-up before its estimated reading is treated as a final measurement.
How to use the workbook
Use one row per bill or clearly identified bill line. The tracker provides 50 prepared rows. Keep the same grain throughout: mixing a whole invoice with its individual lines will double count.
Enter the site, account and invoice reference, then the period and original bill values. The cost-per-unit formula updates when numeric usage is greater than zero and the cost is numeric. A separate formula flags repeated combinations of site, account and invoice reference within the prepared rows.
That flag is a prompt to review, not proof of an error. A corrected bill or credit note can legitimately refer to an earlier invoice. Describe the relationship in the follow-up field and decide which records belong in your total. Blank IDs do not produce a clean duplicate-check result.
The workbook does not automatically detect overlapping dates or missing months. Review those against your expected account list and reporting calendar. If you extend beyond the prepared rows, copy the formulas and expand the duplicate-check range as explained in the workbook.
Reconcile the period before comparing sites
An October payment is not automatically October consumption. A bill might cover part of September and part of October. Keep the original invoice intact and document any allocation you make for reporting.
For a monthly review, ask each site owner to confirm the complete account list, identify closed or newly opened meters, and explain missing bills. Compare the collected periods with the reporting period. Put estimates and unresolved gaps in a visible queue instead of filling them with zero.
The ENERGY STAR small-business toolkit is a useful starting point for organising building energy information. Your own bill definitions and reporting boundary still determine what belongs in the tracker.
What to calculate next
Once the source records are complete, choose the denominator that answers your question. A hotel might use occupied room-nights. A retailer might examine electricity per square metre. Hospitals need a carefully defined energy and floor-area boundary.
Use matching periods and explain the boundary. A lower bill can reflect a shorter period, a closed floor or a credit adjustment. It does not establish improved efficiency on its own.
Prepared with AI assistance using the linked sources and original worked examples. Illustrative records are fictional. No customer results, independent specialist review or hands-on software testing are claimed.