Extract Fonterra Supplier Statements to Excel (NZ Dairy)

A Fonterra monthly supplier statement (and the equivalent statements from Synlait, Open Country Dairy, Westland, Tatua, and Miraka) is a financial document, but not the kind that fits an ordinary AP workflow. Each month it turns the farm's milk supply into a small set of spreadsheet-relevant data points: kgMS production for the period and season to date, an advance-rate milk payment, multi-line deductions for transport and dairy industry levies, a GST line under buyer-created tax invoice mechanics, capital share and shareholding lines, and a net deposit to the bank account. The bookkeeping job is to capture that structure cleanly, reconcile it to the bank deposit and the season-end final milk price, and then code revenue, levies, and capital or share items to the right accounts.

That summary is also why so many rural accounting teams reach for a custom approach to extract Fonterra supplier statements to Excel rather than treating each month's statement as another supplier invoice. A standard supplier-invoice workflow assumes a seller-issued tax invoice with quantity, rate, and amount columns that map straight onto a purchase. A milk statement reverses several of those assumptions at once. Production is measured in kgMS (kilograms of milk solids), not units. The price is an advance rate that steps up through the season rather than a final price agreed up front. Deductions span transport levies, the DairyNZ levy, the MPI biosecurity levy, council levies, capital share notes, advance loan repayments, and farm-feed contributions, each with its own GL home and its own GST treatment. And the GST line on the statement is the farmer's output GST under a buyer-created tax invoice (BCTI), because Fonterra (the buyer) is creating the taxable supply information on the farmer's behalf, not the other way round.

So while the milk statement is, in a structural sense, still a vendor-statement variant — and the broader pattern of extracting convert vendor statements to Excel maps onto it cleanly — the dairy specifics need their own treatment. This article walks the workflow tool-neutrally, with Fonterra as the anchor and the other NZ processors covered as variants, so nothing here assumes Figured, Farm Focus, or any other particular product is in the stack.

What follows: the anatomy of the Fonterra monthly statement field by field, how the extracted data lands in the bank reconciliation and the GST workpaper, the season-end final-price true-up that settles in October, the post-2023 Flexible Shareholding lines that now appear on the statement, the sharemilker and contract-milker income split that sits on top of the extraction, the format differences across Synlait, Open Country Dairy, Westland, Tatua and Miraka, and the recurring monthly cadence that makes all of it repeatable.

Anatomy of the Fonterra monthly supplier statement

A Fonterra monthly supplier statement walks predictably from header to net payment, even though the layout has shifted over the years and continues to drift with Flexible Shareholding and Farm Source integrations. Reading it in document order — and capturing each block as a distinct field rather than rolling values up to a total — is what makes the rest of the workflow possible.

The header block carries the supplier number (the farm code, sometimes shown as the Farm Source supplier identifier), the period dates the statement covers, and a statement number for that month. Capture all three: the supplier number is the row key for any farm-level reporting, the period is what ties the statement to a GST return period and a bank-reconciliation window, and the statement number is the audit trail when Fonterra issues an adjustment.

The production block follows. The headline figure is kgMS collected for the month, with a season-to-date cumulative sitting next to it. Underneath sit fat kg and protein kg (the two components that sum to milk solids), and any somatic cell count adjustments where the farm's grading affects payment. Capture the monthly figure and the season-to-date figure as separate fields; the cumulative column is what reconciles to the season-end final price calculation later, and losing it inside a single "production" cell makes that step painful.

Pricing comes next: the month's advance rate in dollars per kgMS, any step-up that has been applied since the prior statement, and a reference to the base milk price the season is tracking against. Step-ups happen through the season as Fonterra updates the advance rate in line with the milk price outlook; record the rate that applied for this period, not just the latest one. Different months can settle at different rates, and the schema needs to keep them straight.

The gross milk payment is the arithmetic line: monthly kgMS multiplied by the month's advance rate, in dollars, before any deductions. This is the gross revenue line for farm income.

Deductions are the densest part of the statement and the part where bookkeeping gets the most value from a clean extraction. Expect to see (in varying order across statements): a transport levy, capital share notes or share-related charges, advance loan repayments, council levies, farm feed (F&F) contributions, the DairyNZ levy, and the MPI biosecurity levy. Each is typically shown with its own line, often with a rate per kgMS and a dollar amount. Keep them separated in the extraction; they code to different GL accounts and they have different GST treatments.

Then the GST line, under buyer-created tax invoice mechanics — Fonterra issues the tax invoice on the farmer's behalf — and finally the net milk payment, which is what hits the bank account.

Units matter throughout, and they are easy to compress incorrectly. kgMS is kilograms of milk solids (fat plus protein). The advance rate is dollars per kgMS. Gross milk payment is dollars. Most levies are stated as a rate per kgMS and an aggregate dollar amount. Keep units, rates, and amounts in separate columns; the numbers reconcile cleanly only when each one is in its own field. According to the Fonterra Farmgate Milk Price methodology, the aggregate amount payable to farmer shareholders is usually referred to as a Farmgate Milk Price per kgMS — the unit pricing convention that the entire monthly statement is built around.

Two more practical points. First, format variation within Fonterra is real. Section ordering, the grouping of deduction lines, and the labels used for share-related entries have shifted with Flexible Shareholding (covered in a later section) and with successive updates to the Farm Source statement template. Build the extraction to be field-driven by label rather than position-driven by row number, or every layout change becomes a schema rewrite.

Second, a workable schema looks like this: one row per statement at the header level, carrying supplier number, period, monthly and season-to-date kgMS, gross milk payment, deductions sub-total, GST, and net milk payment; with a child table of one row per deduction line, preserving the deduction code or label, the rate per kgMS where stated, and the dollar amount. That child table is what feeds the deduction lines into the right GL accounts later, and what makes the season-cumulative deduction view possible without rebuilding it from scratch.

Reconciling the statement to bank deposits, GST, and the BCTI workpaper

Once the statement is extracted, the reconciliation runs in a fixed order: post the lines to the GL, tie the net to the bank deposit, route the GST output and the GST input deductions to the GST workpaper, then check the cumulative position against the season-to-date figures. Doing the steps out of order is what produces the reconciling-item drift that haunts dairy supplier statement reconciliation work for NZ rural firms at year-end.

Take the bank tie first because it is the most concrete. The net milk payment on the statement should equal the bank deposit from Fonterra for the corresponding period, with the deposit landing on or about the 20th of the following month. If the bank feed shows the deposit on a different date, or for a different amount, work backwards from the statement's deductions block — a reissued advance loan repayment or a corrected levy line is the usual culprit. Match by amount and statement number, not by date alone, because the deposit cadence drifts in seasonal pinch points.

The buyer-created tax invoice mechanic is what makes the GST treatment unusual, and the bookkeeping needs to mirror it correctly. Fonterra issues the tax invoice on the farmer's behalf because the buyer is determining the price (the advance rate) and standardising supplier information across thousands of farmers; a Fonterra BCTI GST statement therefore acts as both a supplier-payment record and a tax invoice for GST purposes, all in one document. The farmer remains the supplier for GST purposes — Fonterra is the recipient creating the taxable supply information. The GST line on the statement is the farmer's GST output for the milk supply, not an input GST on a purchase.

GL coding follows from that. Gross milk payment codes to farm income (revenue). Transport levies, the DairyNZ levy, the MPI biosecurity levy, council levies, and farm feed contributions code to their respective expense GL accounts. Advance loan repayments are not an expense at all; they reduce the loan or advance asset on the balance sheet. Capital share notes and share-related charges are balance-sheet items handled in the Flexible Shareholding section that follows. The GST line on the statement codes to GST output, flowing to the GST workpaper.

The GST workpaper itself takes two flows from a single statement. The output GST sits in Box 5 outputs for the GST return period — milk income is taxable supply for a GST-registered farmer, and the BCTI is the source document for that output. The deductions on the statement that carry GST then create input GST entries in the opposite direction: the transport levy, the DairyNZ levy where it is taxable, F&F contributions to a registered supplier, and any council or processor charges with GST attached are all input GST that the farmer can claim. The single statement is therefore both an output GST line and a series of input GST lines, and the workpaper has to capture both. Working out the period totals follows the same shape as the broader return preparation; the steps to prepare a NZ GST101A return from supplier invoices cover how the output and input boxes assemble across all source documents.

Bank reconciliation across the season is the other piece dairy farm bookkeeping milk income reconciliation has to handle. Cumulative monthly advance payments should reconcile to cumulative bank deposits across the season, but timing differences accrue in any month where Fonterra has applied a retroactive correction, where a deduction has been adjusted after the fact, or where a cut-off date has shifted. The reconciliation lives at the cumulative season-to-date level, not month by month — chasing month-by-month reconciling differences is wasted effort when Fonterra adjusts retroactively, and the cumulative view is what survives the season-end final-price calculation in the next section.

Final-price true-up at season end and the October payout

The Fonterra milk season runs 1 June to 31 May. Through that window, the cash that arrives each month is an advance — paid at an advance rate that steps up as Fonterra updates its view of the milk price outlook. At season close on 31 May, Fonterra reconciles cumulative advance against the final Farmgate Milk Price calculation; the difference is paid out (or, rarely, clawed back) in October following.

The arithmetic is straightforward when the schema has held the cumulative figures cleanly. Take season-cumulative kgMS, multiply by the announced final milk price per kgMS, and subtract the cumulative advance payments already received during the season. The residual is the October payout. Statement language varies — final payment, season-end payment, wash-up — but the calculation is the same in each case.

The financial-year question is where it gets tidy or untidy depending on the entity's balance date. Most NZ dairy farm entities operate to a 31 May or 31 March balance date, so the October payout straddles year-ends in either case. The accounting treatment is accrual at season close: at 31 May, raise the milk-income receivable at the announced final milk price (less cumulative advance already received), so the prior season's milk income is fully recognised in the year it relates to. The October cash receipt then settles the receivable rather than re-recognising income. As a journal: at season-end, debit accrued milk income (receivable) and credit milk income; on October payout, debit cash and credit accrued milk income.

Fonterra usually carries a season-end summary on the May statement, or issues a separate annual statement around October that lists cumulative kgMS, the final milk price per kgMS, total final-price entitlement, less cumulative advance paid, equals October payment. Extract that summary into the same row schema as the monthly statements so the wash-up ties directly to the running monthly schedule and the cumulative figure that has been building all season.

Build the schema to handle a clawback as well. In seasons where the advance rate has run ahead of the final milk price, the October figure can be a deduction rather than a payment. That is rare but it does happen, and the schema needs to accept a negative final-price residual without breaking the prior-year accrual. The accrual at 31 May still books at the announced final price; if that is below cumulative advance, the receivable raised is negative (a payable), and the October cash movement settles it the other way.

Flexible Shareholding and capital share lines on the statement

Since June 2023, Fonterra's Flexible Shareholding regime has changed what farmers must hold and what shows up on the supplier statement. Under the prior regime, suppliers held one share per kgMS supplied. Under Flexible Shareholding, the band is wider — broadly, one share per three to four kgMS depending on entity status — and share standardisation is an annual event with adjustments that flow onto the statement. The Fonterra Flexible Shareholding statement lines that result are not ordinary expenses, and coding them as if they were is one of the more common reasons a dairy farm balance sheet drifts away from the share register.

Several distinct line types can appear, each with its own balance-sheet treatment. Capital share notes (the deferred-share-purchase instrument that lets a supplier acquire shares progressively) sit as a long-term receivable until they mature and convert to ordinary shares. Share standardisation entries adjust the holding when actual season-cumulative supply pushes the farmer outside the permitted band. Dividends paid on existing shares (the regular dividend declared on Co-operative Shares) are dividend income, which is separate from milk income and carries imputation credits where applicable. Share buyback or sale proceeds reflect a disposal of an asset on the balance sheet rather than ordinary income. And where a farmer is on a structured share-up plan, share-purchase deductions appear in the deductions block as a reduction of cash paid in exchange for an equity asset acquired.

The GL coding pattern that falls out of this is consistent: shareholding (the equity instrument itself) is an asset on the balance sheet held at cost; share purchases are an asset acquisition rather than an expense; share sales are an asset disposal, with any gain or loss recognised against the carrying value; capital share notes are a long-term receivable that converts on maturity; dividend receipts are dividend income with imputation credits attached. None of those lines belongs in farm income, and none of them belongs in farm operating expenses.

Share standardisation is the seasonal event that makes the schema earn its keep. At season end, Fonterra calculates the farmer's required shareholding band against actual kgMS supplied and notes the variance. The next-season statement carries adjustment lines if the farmer is over- or under-held. The bookkeeping treatment then depends on what the farmer chooses to do with the variance — true the holding up by purchasing or selling shares (which moves cash and the share asset), or operate as an unshared supplier within the permitted bands (which leaves the share register unchanged but may affect dividend entitlement on the unshared portion). Either way, the statement line is the trigger; the policy choice is the farmer's, and the rural accountant's role is to record what was decided correctly.

Since the unit fund and Trading Among Farmers reforms, share value is a market-driven figure. Whether the share asset on the balance sheet needs fair-value attention at year-end depends on the farm entity's accounting policy. That is a policy choice the entity's accountant or adviser owns, and the answer often differs between a small operating partnership and a corporate-farming entity with formal financial reporting requirements.

Sharemilker and contract-milker income splits from the statement

Where a farm is run under a sharemilking or contract-milking agreement, the Fonterra statement still belongs to the legal supplier — the farm-owner entity whose name sits on the BCTI. The split that gets the milk income (or the contract milker's fee) to the right party happens after the extraction, in the workpaper, against the contract terms. Bake the split into the extraction itself and the raw data carries assumptions that break the moment the contract changes mid-season.

So extract the statement at face value as if for the farm-owner entity. Apply the contract's split mechanic in the workpaper that posts to the two sets of books. That discipline is what makes the same monthly extraction usable across owner-operated farms, sharemilker arrangements, and contract-milker arrangements without rewriting it for each.

The contract types and their split points run as follows. A 50/50 sharemilker takes a defined share of milk income — typically 50% on a true 50/50 contract, with variable-order contracts running lower depending on the agreement. A lower-order sharemilker (29%, 39%, or whatever the contract sets) takes a smaller percentage, often with different deduction allocations attached to it. A contract milker is paid a contracted rate — usually a dollar-per-kgMS fee — and takes no share of milk income at all. The contract milker invoices the farm owner separately on their own tax invoice; the Fonterra statement supplies the kgMS figure that drives their fee but does not interact with their invoicing directly.

The gross-versus-net split distinction matters and is contract-specific. Some agreements split off the gross milk payment, with the sharemilker taking their percentage of the gross and deductions allocated according to the contract — the sharemilker often bears their share of farm working expenses but not the farm owner's share-related deductions. Others split off the net, with all deductions taken at the farm-owner level before the residual is split. The contract dictates which, and the workpaper has to mirror the contract terms exactly. Sharemilker milk income reconciliation done against the wrong split point is a recurring source of year-end correction entries.

The dual-entity bookkeeping reality follows from the BCTI ownership. The farm-owner entity records the full milk income from the Fonterra statement as the GST-registered supplier, because the BCTI is in the farm-owner's name. The sharemilker payment from the farm owner is then an expense in the farm-owner's books. The sharemilker, in their own books, records milk income from the farm owner — not from Fonterra — with their own GST treatment depending on whether the sharemilker is registered. The BCTI does not flow to the sharemilker; the sharemilker has either a tax invoice from the farm owner (if the farm owner issues one) or a buyer-created arrangement of their own with the farm owner, depending on how the parties have set the agreement up.

Contract-milker mechanics close out the same way. The contract milker is paid by the farm owner per kgMS supplied — using the kgMS figure pulled from the Fonterra statement as the data source — and issues a tax invoice to the farm owner for the contracted fee. The Fonterra statement remains the farm-owner's statement and the farm-owner's milk income; the contract milker's books carry their fee income, separately tracked.

Synlait, Open Country Dairy, Westland, Tatua and Miraka statement variants

Structurally, every NZ dairy processor's monthly statement is a kgMS-driven document with an advance rate, deductions, and a final-price true-up at season end. The Fonterra-anchored schema in this article carries directly onto the others. The differences are in pricing structure, premium components, the composition of the deductions block, and the cadence of the final-price residual. A field-driven (label-mapped) schema absorbs every processor; a position-driven schema breaks on the first switched supplier.

Synlait pays on the standard kgMS / advance / deductions / net structure but layers premium components on top. Synlait supplier statement extraction needs to break out the a2 Milk programme premium where applicable, the Lead With Pride farm-assurance premium, and any contract-specific incentive payments as their own columns rather than rolling them into a single milk-income figure. Premium components often code to different farm-management reporting buckets, and they sometimes flow on different timing from the underlying advance rate. Capture each premium as a labelled line so the GL split downstream matches the way the farm tracks them.

Open Country Dairy pays on a different cadence again. OCD operates monthly advances and a final wash-up that has historically settled faster than Fonterra's October pattern, so the timing of the season-end residual sits earlier in the calendar. The statement format reads closer to a straightforward purchase confirmation than a Farm Source-style report, but the same fields — kgMS, advance rate, gross, deductions, GST, net — are present and extract the same way. Extracting an Open Country Dairy statement to Excel is mechanically the simplest of the multi-co-op set, but the pricing model means the wash-up arithmetic and the wash-up timeline both differ from Fonterra; expect the residual figure to land on a different date and against a different methodology.

Westland Milk Products has been Yili-owned since 2019, and the post-acquisition statement format and pricing structure shifted. Payment runs on a contracted-price basis with different settlement terms; the schema is unchanged but the reconciliation against the season-cumulative is to a contracted milk price rather than a co-op final-price calculation. For Westland suppliers, the season-end true-up is more like a fixed-price contract reconciliation than a Fonterra-style wash-up.

Tatua is a small co-op that has historically paid the highest net price per kgMS in the industry. Payment runs largely on a contracted-price basis with end-of-season distributions sitting on top. The statement format is simpler in line composition than Fonterra's; extraction is straightforward, and the share component differs because of Tatua's specific co-op structure rather than tracking Fonterra's Flexible Shareholding regime.

Miraka, with Maori-trust ownership and a UHT-focused production model, follows the standard kgMS / advance / deductions pattern on the supplier statement. The bookkeeping difference for Miraka suppliers is in processor-specific deduction lines and any iwi-related share or distribution arrangements that depend on the supplier's contract — those need treating on their own terms rather than mapping straight onto a Fonterra share-line equivalent.

Practical close on the multi-co-op work: build the extraction schema field-driven, keyed off the labels on each statement, rather than position-driven against fixed row numbers. A field-driven schema captures kgMS, rate, gross, each deduction line, GST, and net wherever they appear on the page, regardless of layout. The same Excel template then absorbs all six processors, and the work of supporting a new supplier is mapping its labels rather than building a fresh schema from scratch.

Building a recurring monthly extraction workflow

The monthly cadence settles into a fixed pattern once the schema is built. Statements arrive on the same rough timetable each month — Fonterra suppliers download from Farm Source, Synlait suppliers pick up from the supplier portal, OCD and the smaller processors usually email a PDF — and the work runs through the same five steps: extract to the standardised schema, post to the GL using the coding pattern walked through in this article, tie the net to the bank deposit, route the GST output and input lines to the workpaper, then file the source PDFs against the period folder.

The schema discipline established earlier — header row per statement, child table per deduction line, source-file and page references on every row — is what makes the cadence repeatable. The references are what matter at year-end, when the auditor or the farm owner asks where a specific levy figure came from; the answer takes seconds rather than minutes because the trace back to the source PDF is built in. The same schema absorbs every processor when the field mapping is label-driven, so switching a client from Fonterra to OCD does not mean rebuilding the workpaper.

Once that schema exists, monthly milk statements stop being ad-hoc invoice data entry and become a structured recurring extraction job. That framing is the practical reason rural accounting teams build out a workflow to extract dairy supplier statements to Excel on a standing monthly cadence rather than handling each statement as a one-off — the work is repeatable, the schema is stable, and the seasonal events plug into it rather than running alongside it.

For a rural firm running this across a portfolio, the milk-statement workflow is one piece of a broader agriculture accounts payable automation pattern that also covers fertiliser and feed accounts, ACC self-employed invoices, animal-health supplier invoices, contractor invoices for grass and silage work, and the rest of the farm AP cycle. Treating the milk statement extraction as one repeatable workflow inside that larger pattern lets the team standardise across the whole farm books rather than carving the milk income off as a separate exercise.

A prompt-based extraction tool fits this workflow shape directly. Our product takes a PDF (Fonterra Farm Source statements, Synlait monthly statements, Open Country Dairy PDFs, and so on) and a natural-language prompt describing what to capture, and returns a structured Excel, CSV, or JSON file. For dairy supplier statements, that translates into a saved prompt per processor: one prompt asks for one row per Fonterra statement at the header level (supplier number, period, monthly and season-to-date kgMS, gross, deductions sub-total, GST, net), plus child rows per deduction line preserving each deduction's label, rate, and amount. A second saved prompt does the same for Synlait, with extra columns for the a2 Milk and Lead With Pride premium lines. A third covers OCD. Saved in the prompt library, those prompts apply to each new month's PDFs without rewriting; the same prompt produces the same schema whether the batch is one farm's statements or a portfolio of fifty. Every output row carries the source file and page reference, so the audit trail back to the original statement is preserved automatically.

The monthly extraction, the GL coding, and the reconciliation are all repeatable work. The seasonal events — the May 31 final-price true-up, the October payout, share standardisation at season end — are where the schedule gets reviewed in detail. Build the monthly cadence so those seasonal events fall out of the existing workpaper as a natural extension rather than as a parallel exercise.