Extract UK Academy Trust Utility Bills to Excel for SECR

A UK academy trust should extract academy trust utility bills to Excel as one row per bill, credit note, rebill, or supply-period line, not as one row per PDF. Each row needs the fields the trust will actually use later: academy site, supplier, account number, MPAN or MPRN, billing period, kWh consumed, unit rate, standing charge, Climate Change Levy, VAT rate, gross total, and a reading flag for actual, customer, or estimated reads.

The urgency is clearest for larger trusts in SECR scope. GOV.UK guidance on SECR for academy trusts sets out the size tests and the 40,000 kWh low-energy-user exemption, but the practical evidence still has to come from the trust's own gas, electricity, and fuel records.

That row model matters because the same workbook has three jobs. It provides the gas and electricity totals behind the SECR disclosure in the trustees' report. It gives finance and estates teams a bill-audit sheet for checking tariff rates, standing charges, estimated reads, credits, and rebills. It also creates per-site monthly evidence for a Climate Action Plan, so the trust can see where consumption is concentrated and whether efficiency work later shows up in supplier bills.

The extraction task is therefore not just "turn these PDFs into text". A useful MAT utility bill data extraction workbook preserves enough structure to answer operational questions months later. Which academy did this meter serve? Was the bill based on an estimate or an actual read? Did a credit reverse a prior period? Does the standing charge match the contract? Can the finance team trace this annual kWh total back to the original supplier evidence?

The rest of the process is a workbook-design exercise before it is a software exercise. First set the row grain and join keys. Then define the bill fields to capture. Then use the extracted rows for SECR totals, utility-bill validation, and climate-action baselines, keeping compliance judgement, emissions factors, exemptions, and audit conclusions separate from the source-data build.

Use stable keys before extracting bill values

Start the workbook with identifiers, not charges. In a multi-academy trust, the same supplier may bill several sites, a site may have more than one meter, and a meter may move from one supplier to another during the year. If the workbook cannot keep those identities stable, the kWh and cost columns will not reconcile cleanly later.

The row grain should be explicit: one row for each bill, credit note, rebill, or split supply period. A single PDF may contain several useful rows if it covers more than one supply period or includes a credit and a rebilled amount. Equally, an annual statement may summarise many months but should still be labelled differently from a supplier invoice so it does not blur the evidence trail.

Useful identity columns include:

• Trust name
• Academy site name
• Internal cost centre or site code, if used
• Supplier
• Supplier account number
• MPAN for electricity supplies
• MPRN for gas supplies
• Meter serial number
• Bill or statement number
• Source file name
• Source page
• Supply period start date
• Supply period end date

A practical MAT workbook usually works better as a set of linked tabs than as one wide sheet. Use a site register for academy names, internal site codes, cost centres, floor area, and pupil-number fields. Use a supplier and meter register for account numbers, MPANs, MPRNs, meter serials, contract dates, and framework or broker references. Keep bill rows as the extracted transaction table. Add a SECR aggregation tab for annual electricity and gas roll-ups, prior-year comparatives, and intensity-ratio inputs. Add an audit checks tab for estimated reads, unit-rate variance, CCL and VAT review flags, period gaps, and duplicate credits. Keep a source-document index that maps every row back to a file name and page reference.

MPAN and MPRN fields are especially important in a UK school estate. Site names can change, academies can join or leave the trust, and supplier account numbers can change after procurement or contract renewal. The meter reference is usually the better continuity key for electricity or gas consumption across those changes.

Not every field should come from the bill. Floor area, pupil numbers, phase, internal cost centre, and estates notes are usually trust-maintained metadata that can be joined to the extracted rows after the bill data is captured. Keeping that distinction visible reduces the risk of treating a manually added planning field as supplier evidence.

Where a meter reference is missing, split across pages, or visually unclear, leave a review note rather than forcing a match. A row marked "MPRN not visible on bill page" is easier to audit than a neat-looking workbook built on guessed identifiers.

Capture the fields finance and estates teams actually use

Once the workbook keys are settled, the extraction prompt or specification should name the fields the trust needs downstream. For a school business manager utility spreadsheet, the core bill fields are usually bill number, bill date, supplier, account number, supply start date, supply end date, gas or electricity flag, kWh consumed, standing-charge days, standing charge, unit rate, network or distribution charges where shown, half-hourly or capacity charges where relevant, other charges, Climate Change Levy, VAT rate, net amount, gross amount, payment amount, and account balance where visible.

Reading evidence should sit beside the charges. Capture opening meter read, closing meter read, whether the read is actual, customer, or estimated, and any bill text that explains the read type. Estimated reads are not just a detail for the estates team. They affect bill validation, accruals, and the confidence the trust can place in consumption trends.

Credits and rebills need their own columns rather than being folded into the nearest invoice total. Useful fields include credit amount, replacement bill amount, original bill reference if visible, period affected, and whether the document reverses or reissues a prior charge. If the supplier shows reduced-rate VAT, CCL exemption wording, or a reference to an exemption certificate, capture the wording and certificate reference as evidence fields rather than deciding the treatment inside the extraction step. That structure lets the reviewer preserve the audit trail instead of losing it in a corrected net total.

This is the point where extraction software can help, provided the prompt matches the workbook job. Invoice Data Extraction lets users upload PDFs, JPGs, and PNGs, describe the fields they want in natural language, and export structured Excel, CSV, or JSON, with batches up to 6,000 mixed-format files or single PDFs up to 5,000 pages so the same prompt runs across a sample academy or a full-year trust estate. For a MAT bill pack the prompt can ask for MPAN, MPRN, kWh, unit rate, standing charge, CCL, VAT, read type, credit status, rebill status, file name, and page reference, then extract gas and electricity bill data into a structured Excel workbook across a large mixed-supplier batch. The discipline is the same as when finance teams convert utility bill PDFs into Excel rows for bookkeeping: define the columns before processing, keep evidence fields visible, and review exceptions instead of hiding them.

Map extracted rows to SECR reporting evidence

For SECR, the workbook's value is traceability. GOV.UK guidance on SECR for academy trusts says an academy trust falls into scope when it meets two of three size tests, turnover or gross income of £36 million or more, balance sheet assets of £18 million or more, or 250 or more employees, and is then required to report annual UK energy consumption from gas, electricity and transport fuel in its trustees' report. The low-energy-user exemption applies only where the trust consumed 40,000 kWh or less in the reporting period.

The extracted utility rows give the trust a defensible starting point for that disclosure. Electricity kWh can be rolled up by MPAN, academy site, supplier, month, and reporting year. Gas kWh can be rolled up by MPRN on the same basis. Transport fuel — fleet diesel, grey-fleet mileage, minibus and pool-vehicle data — sits outside the gas-and-electricity bill pack and belongs in its own workbook tab, but it uses the same evidence-trail discipline so the annual disclosure can be aggregated consistently across all three fuel categories.

The workbook should also support prior-year comparatives and methodology notes. A simple SECR roll-up tab might show annual electricity kWh, annual gas kWh, prior-year totals, the intensity ratio denominator used by the trust, and links back to the bill rows that produced each figure. The source-document columns from the extraction sheet matter here because they let the preparer trace a number in the trustees' report back to a supplier invoice, credit note, rebill, or annual statement.

This does not mean the extraction workbook makes the compliance judgement. Emissions factors, intensity-ratio selection, treatment of exemptions, transport fuel scope, and final wording in the annual accounts remain matters for the trust and its advisers. The workbook's job is narrower and more concrete: preserve the bill evidence in a form that supports Academies Accounts Direction energy reporting without forcing the preparer to re-open hundreds of PDFs.

Turn the same rows into a bill-audit sheet

The same extraction sheet can become the trust's first bill-audit layer. SECR needs annual kWh totals, but finance and estates teams also need to know whether the bills themselves look right. Estimated reads, unusual unit rates, standing-charge changes, network-charge movement, half-hourly or capacity-charge movement, Climate Change Levy treatment, VAT rate changes, duplicate credits, replacement bills, out-of-period charges, and missing meter references are all easier to test once the supplier PDFs have been turned into consistent rows.

Some checks compare extracted values with contract data. The bill may show the charged unit rate and standing charge, but the agreed tariff usually comes from the contract, procurement framework documentation, supplier correspondence, or a framework-summary report. Add those expected values in separate columns, then calculate the variance. That makes the difference between "the bill says 28.6p per kWh" and "the bill agrees with the contracted rate" visible to the reviewer.

Estimated readings deserve their own flag because they can distort both cost and consumption trends. A run of estimated electricity bills may make one academy look stable until an actual read catches up months later. A gas meter with repeated estimates may hide seasonal movement that matters for estates planning. Filtering by estimated, actual, and customer reads helps the trust decide which rows need follow-up before using the figures for audit or reporting.

Credits and rebills should stay as separate rows even when they net to zero over time. Replacing the original bill with the corrected amount makes the workbook look cleaner, but it hides the sequence of supplier evidence. For the utility bill audit and reconciliation process at the back end of the workbook, the reviewer needs to see which document created the charge, which document reversed it, and which period the rebill replaced.

Source file and page fields turn the spreadsheet from a summary into an audit tool. When a row shows a sudden standing-charge increase or an unexpected CCL amount, the reviewer can jump straight back to the supplier document instead of searching a shared drive for the right PDF.

Handle multi-site and multi-supplier mess without flattening the evidence

Academy-trust utility packs rarely arrive as a tidy set of monthly invoices from one supplier. A trust may have EDF bills for one group of sites, British Gas bills for another, E.ON Next bills after a supplier switch, and legacy statements from a previous contract period. The workbook has to absorb those layouts without pretending they are identical documents.

Partial billing periods are one common problem. A bill may cover three weeks because a contract started mid-month, or it may span two reporting periods. Do not force those rows into calendar months at extraction stage. Capture the actual supply start and end dates first, then use a roll-up formula or review process to apportion later if the trust's reporting method requires it.

Academies joining or leaving the trust need similar care. A site may only contribute consumption for part of the financial year. A supplier account may remain in an old school name for several months after conversion or transfer. The row should preserve the supplier's wording and the trust's current site code, so the finance team can explain the link rather than overwrite the evidence.

Some bills place the MPAN or MPRN on a different page from the totals. Others include a meter serial number but no clear meter point reference on the charge page. Those cases should create review flags, not guessed matches. A practical workbook might use fields such as "meter reference confidence", "identifier source page", and "manual review note" so the exception is visible before totals are relied on.

CCL and VAT questions also belong in review fields where the answer depends on context outside the bill. Boarding accommodation, residential use, exemption certificates, mixed-use sites, reduced-rate VAT indicators, or supplier references to exemption forms may need separate judgement. The extraction row can preserve the CCL amount, VAT rate, supplier description, exemption wording, certificate reference, and review flag if visible, but it should not silently decide the tax treatment.

The evidence-preservation logic is not unique to the UK. A trust can compare its workflow with the equivalent multi-site utility bill workflow in a Hong Kong context, but the UK academy-trust version needs UK-specific identifiers, SECR fields, and trust-site context.

Use the workbook as evidence for climate-action planning

A well-structured extraction workbook also gives the trust a repeatable energy baseline. Once each gas and electricity row has site, meter, period, and kWh fields, the estates or sustainability lead can build monthly site-level views without re-entering the same supplier data for a separate Climate Action Plan spreadsheet.

The useful planning fields are usually joined to the extracted rows rather than pulled from the bills themselves. Floor area, pupil numbers, boarding provision, building age, heating type, project dates, and occupancy changes may explain why one academy consumes more energy than another, but they are trust context, not supplier invoice data. Keeping those columns separate makes the workbook clearer when finance, estates, and trustees review the evidence.

The same structure can help test whether energy-efficiency work is visible in later periods. If an LED upgrade, boiler change, controls project, or behaviour campaign has a known date, the trust can compare consumption before and after that date by site and meter. The workbook will not prove causation on its own, but it gives the trust a cleaner evidence base than a folder of PDFs and annual totals.

This utility-bill workflow sits within broader invoice processing patterns across the education sector: preserve the source document, extract the fields the finance team actually uses, keep exceptions visible, and export data in a form that can be reviewed. For academy trust utility bills, the difference is the field set, MPAN and MPRN, kWh, CCL, VAT, reading type, credit status, rebill status, and site evidence.

In practice the order is straightforward: agree the row grain and field list, then test a small sample across different suppliers and sites before processing the full year. Review the missing meter references, estimated reads, credits, and rebills the sample surfaces — that is the workbook structure earning its place before it has to survive the full estate.