Convert NHSBSA FP34 Schedule of Payments to Excel

To convert NHSBSA FP34 Schedule of Payments to Excel, do not try to force the whole PDF into one worksheet. Extract each payment section into its own table, normalise the contractor, branch, payment month, dispensing month and source-page fields, then reconcile the totals against PMR or Prescription Item Report exports before investigating variance by payment bucket.

The useful workbook separates summary totals, reimbursement buckets, service fees, Pharmacy Data counts, local payments, recoveries and audit references. That structure gives a contractor or pharmacy accountant something they can pivot, filter and trace back to the original schedule. A page-by-page copy gives them another static document.

This matters because the official data route still leaves an operational gap. The NHSBSA electronic payment schedules guidance says electronic FP34 data is not a like-for-like replacement for the paper version and is not readily exported to Microsoft Excel, so contractors need an IT solution to read and manipulate the data. In practice, that means the month-end job is not just "get the schedule into Excel". It is to create a controlled dataset that can be joined to dispensing, claims, service and cost evidence.

Official NHSBSA and Community Pharmacy England guidance is still the place to check what a Schedule of Payments section means. The missing layer is the working model: which tables to build, which fields to extract, which join keys to preserve, and how to review FP34C reconciliation PMR dispensing variance without losing the audit trail.

Design the Excel tables around FP34 payment sections

A reliable FP34 Schedule of Payments breakdown spreadsheet starts with the document's sections, not with the PDF's visual layout. If a line only carries "amount" and "description", the reviewer later has to infer whether it belonged to drug reimbursement, appliance reimbursement, service fees, local payments or a recovery. That is how month-end work becomes a manual re-reading exercise.

Use separate tables for separate analytical jobs:

• Schedule summary: contractor, branch, account or location identifier, payment month, total paid, advance payment, reconciliation payment and any opening or closing balance fields shown on the schedule.
• Reimbursement and payment buckets: drugs, appliances, fees, allowances, charges, expensive items, specials, adjustments and other section-level amounts.
• Advanced-service fees: Pharmacy First, hypertension case-finding, NMS, contraception, flu vaccination, COVID vaccination and any locally commissioned services shown separately.
• Pharmacy Data counts: forms and items received by NHSBSA, forms and items declared on the FP34C, and any count difference that needs review.
• Local or other authorised payments: local schemes, one-off payments and entries that do not map cleanly to national reimbursement buckets.
• Recoveries and clawbacks: negative lines, prior-period adjustments and missed-item recoveries.
• Source audit: source file, source page, section heading, original line description, extraction date and review status.

Each extracted row should carry the same control fields: contractor name, branch name, contractor code, ODS code, account number or branch identifier where present, payment month, dispensing month where shown, section name, line description, count, amount, source page, source file and review status. If extraction software provides a confidence flag, keep it. If the workbook is built manually, use a reviewed or unreviewed status column instead.

That schema makes the finance questions answerable. The summary table explains cash paid. The payment-bucket table explains advance and reconciliation movement. The service-fee table explains Pharmacy First or hypertension case-finding movement. The Pharmacy Data table explains form and item count differences. The audit table explains where the numbers came from when a contractor, accountant or area manager challenges a variance.

The Pharmacy Data block deserves special treatment because it is often the first practical check. A difference between items declared on the FP34C and items received or priced by NHSBSA does not automatically prove a loss, but it tells the reviewer where to look before spending time on more granular reimbursement analysis.

Choose the extraction route for PDFs, eSchedules and historic files

PDF schedules and electronic FP34 data should be treated as different inputs. An eSchedule is closer to a database feed when the contractor has access to it and has software that can read it. A PDF Schedule of Payments is still common in evidence packs, historic reviews, accountant handovers and mixed monthly files, so the extraction process has to cope with both.

There are three realistic routes into Excel.

Manual key-in works for a single branch if the schedule is short and the reviewer knows the document well. It also creates the highest control burden: section headings can be missed, service-fee lines can be mapped inconsistently, and negative adjustments can be typed without their source context. For a pharmacy accountant handling many clients, the labour cost compounds quickly.

Electronic FP34 data is the better raw input where it is available and the contractor's systems can read it. It still needs an import process, a mapping table and review rules before it becomes a monthly close workbook. "Electronic" does not by itself mean "ready for variance analysis".

For a pharmacy accountant, the practical target is usually consistent NHSBSA schedule PDF to CSV or Excel output for every client month, with electronic FP34 data treated as another source feed that still has to pass through the same bucket mapping and review controls.

Prompt-based extraction sits between those routes. The workflow is to define the workbook tables first, then extract the PDF into that shape. With Invoice Data Extraction, the contractor or accountant can upload recurring FP34 PDFs, apply a saved prompt for the schedule summary, payment buckets, service fees, Pharmacy Data counts, local payments, recoveries and audit columns, then download the result as Excel, CSV or JSON. That makes financial document extraction into Excel part of the reconciliation process rather than a separate data-entry task.

The prompt should be specific enough to protect the audit trail. Ask for the source page, section heading and original line description for every amount. Ask for blank fields rather than guesses where the schedule does not show a branch identifier or dispensing month. Ask for negative values to stay negative, and for subtotals to be labelled as subtotals rather than mixed with transactional lines.

The same principle applies if the output is going into Power BI or an accountancy workbook instead of Excel alone. Keep the extracted table narrow, consistent and traceable. A beautiful spreadsheet that cannot be tied back to the source schedule is not a month-end control.

Reconcile FP34 totals against PMR and PIR exports

The reconciliation chain should run in a fixed order: FP34 total, section-level FP34 workbook, PMR or dispensing export, Prescription Item Report where available, then a variance list for review. Skipping straight from a PDF total to a PMR report usually leaves too many possible explanations.

Start by agreeing the period. FP34 payment month and dispensing month are not always the same analytical period, especially where advance payments and later reconciliation payments are involved. A dispensing month may not be fully reconcilable until the final reconciliation payment is reflected. The advance-to-final movement should be tracked as its own line, not hidden inside an unexplained variance.

The PMR or dispensing export then gives the operational side of the comparison. Systems such as ProScript Connect, Cegedim Pharmacy Manager, Positive Solutions, Sonar and Titan can support monthly dispensing reports, but the exact columns differ. The join keys to preserve are branch, dispensing month, payment month, form count, item count, patient charge totals, service type, claim date or submission period, and any line or item identifier the export provides.

For a high-level reconciliation, compare FP34 section totals to PMR totals by branch and month. For a tighter review, split by payment bucket and service type. Pharmacy First claims should be compared against Pharmacy First service submissions, not against the whole advanced-services total. Item-count differences should be separated from value differences, because a count match with a value variance points to pricing, reimbursement or charge treatment rather than missing volume.

The variance table should be explicit enough to hand to another reviewer: FP34 section, PMR or PIR source, expected value, paid value, count variance, value variance, reason category, owner, review status, evidence link or source page, and final explanation.

The NHSBSA Prescription Item Report can strengthen the review where the contractor has access to it. PIR data can help explain how item-level pricing or transparency data rolls into a Schedule of Payments value. That is different from a PMR export, which records what the pharmacy believes it dispensed or submitted. The gap between those two views is where many useful questions sit.

The same basic control pattern appears in other reimbursement-heavy healthcare workflows. A Medicare and DVA rebate reconciliation workflow is governed by different rules, but the spreadsheet logic is similar: payment advice totals become useful only when they are joined to clinical or operational records and reviewed by variance type.

Investigate the variance patterns that matter

The variance list is most useful when each difference is grouped by likely source. A pound-value difference with no classification becomes a long email thread. A difference tagged as count mismatch, endorsement review, service-claim timing, local-payment timing, recovery or margin review gives the team a next action.

Start with form and item counts. If the FP34C declared count, PMR count and NHSBSA Pharmacy Data count do not agree, investigate the period, branch and submission evidence before reviewing value differences. The cause may be timing, a late submission, a rejected or delayed item, or a difference in how the PMR report was filtered.

Then review endorsement and pricing patterns. Missing or switched-off endorsements can leave dispensed lines needing manual investigation. Expensive items and specials deserve separate review because the expected reimbursement may depend on evidence, tariff treatment or invoice cost support. A drug tariff price vs reimbursement variance pharmacy teams notice in the workbook should be traced to the Schedule of Payments, PMR, PIR where available, and acquisition-cost evidence before anyone records it as a permanent margin issue.

Service-fee differences need their own bucket. Pharmacy First, hypertension case-finding, NMS, contraception, flu vaccination and COVID vaccination entries can differ because of claim timing, service classification, submission evidence or payment-month treatment. A Pharmacy First payment Schedule of Payments breakdown should therefore keep the service line separate from general dispensing reimbursement and from other advanced-service fees.

Recoveries and clawbacks are another common source of confusion. They may appear weeks after the underlying dispensing or service event, so a negative line in the current payment month may belong in the current cash movement but not in the current dispensing-month performance view. The workbook should preserve both views.

Margin review sits one step further along the chain. Where reimbursement appears low against expected Drug Tariff treatment, Part VIIIB concession pricing or Cat M movement, the next question is whether the pharmacy's acquisition cost and supplier evidence support the same conclusion. That is an investigation path, not a diagnosis from the FP34 alone.

Add margin and branch controls for monthly close

For a single branch, the monthly close can stay compact: extract the Schedule of Payments, agree the payment and dispensing periods, join to the PMR export, review count differences, review service-fee differences, investigate recoveries, and record whether each variance is explained, timing-related or still open. The workbook should become part of the close file, not an informal working sheet that disappears after the bank reconciliation.

For a multi-site group, the same process needs a combined view. Extract each branch into the same table structure, append the branch identifier, then pivot by branch, month, service fee and variance type. The point is not to review every line with equal effort. It is to find outlier branches, repeated service-fee differences, unusual recovery patterns and count mismatches that do not match the group's submission records.

For a specialist pharmacy accountant, standardisation is the main value. A portfolio workbook can use one template per client per month, with the same payment-bucket mapping and the same review-status fields. The client-facing output can then be short: total paid, unexplained variance, material service-fee movements, recoveries, and open evidence requests.

Margin work needs both sides of the transaction. The FP34 and PMR side explains reimbursement and volume. The supplier invoice side explains acquisition cost, discounts, specials, concessions and evidence for expensive items. For UK pharmacy groups, extracting AAH, Phoenix and Alliance invoice PDFs to Excel gives the acquisition-cost evidence the FP34 cannot show on its own. Where the schedule includes Schedule 2 or 3 lines, the same discipline should extend to tying CD wholesaler invoice lines back to the pharmacy's CD register so the controlled-drugs audit trail sits alongside the reimbursement evidence. Treating those invoices with the same discipline as the Schedule of Payments is where pharmacy invoice processing controls become relevant to the reimbursement review.

The model also translates to other payment documents. NDIS remittance advice extraction has different terminology and a different funding system, but the operational issue is familiar: turn a recurring payment advice into structured rows, join it to operational records, and review exceptions by type rather than by document page.

Once the workbook is standard, it can feed Power BI or a branch dashboard. Keep the Excel file as the controlled source, then build reporting on top of it. If the dashboard and the workbook disagree, the workbook should still show the source schedule, page and section behind each amount.

Keep the workbook auditable enough to trust

The extracted workbook is only useful if someone can defend it later. Every row should preserve the source file, source page, schedule section, original line description, extracted amount, count where relevant, extraction date and review status. If a variance is challenged, the reviewer should be able to move from the Excel row back to the exact part of the Schedule of Payments without re-reading the whole document.

Use a locked mapping table for payment buckets and service types. The same FP34 line description should not be treated as a service fee in one month, a local payment in the next month and an adjustment in the month after that unless the source schedule genuinely changed. Accountants handling multiple clients should keep the mapping consistent across the portfolio while still allowing client-specific local scheme labels.

Set review rules before the month closes. A variance may need review because it is a material pound value, a repeated branch pattern, an unexplained count mismatch, a service-fee movement that does not match submissions, or a recovery that cannot be tied to a prior-period event. The threshold can differ by contractor, but the reason for review should be visible in the workbook.

Ownership should also be explicit. In an owner-managed pharmacy, the contractor may review exceptions with the dispenser or manager who understands the submissions. In an accountancy practice, the preparer can classify the variance and the reviewer can challenge open items before the client pack is issued. In a multi-site group, branch managers should see the exceptions that need local evidence, while finance owns the combined reporting and close file.

That control structure is what turns community pharmacy FP34 reconciliation Excel work from a one-off spreadsheet into a repeatable finance process: extracted rows, stable mappings, clear review rules and source evidence kept with the month-end file.