School District Medicaid R&S Report Extraction Guide

School district Medicaid R&S report extraction turns a monthly remittance file into a claim-level spreadsheet the district can actually work from. The useful output is not a PDF summary or a loose text dump. It is one row per claim or claim line, with the student or Medicaid ID, claim number or ICN, service date, procedure code, billed amount, paid amount, outcome status, denial or adjustment codes, denial age, and the owner of the next action.

That spreadsheet is what lets a Medicaid billing tech move from "the remittance is here" to "these claims paid, these claims denied, these claims are pended, these lines need a corrected claim, these lines need documentation, and these dollars can be posted." It also gives the Medicaid coordinator and finance office a shared view of open Medicaid revenue instead of separate notes inside a billing platform, state portal, and general ledger.

The same workflow applies whether the district receives a multi-page R&S Report PDF, a remittance advice from the state Medicaid portal, or an 835 ERA-derived export from a billing system. The file format changes. The reconciliation question does not: what happened to each service claim, what evidence supports it, and what is the next action before the resubmission window closes?

This is a monthly remittance workflow, not the annual cost-settlement process. Cost settlement looks across the year at reimbursable cost, interim payments, ratios, and final settlement. R&S report reconciliation is narrower and more immediate. It turns each monthly adjudication batch into a paid, denied, partial, pended, reversed, or voided worklist so district staff can correct claims while the underlying service records are still fresh.

Identify the Remittance Artifact Before You Extract

School-based Medicaid remittance vocabulary changes by state and system. A Texas SHARS district may be looking at a TMHP or HHSC R&S Report. A New Jersey district may think in SEMI remittance terms. Pennsylvania SBAP, Oregon SBHS, Illinois SBHS, New York SSHSP, and California LEA-BOP RAD all have their own program language, portal workflows, and report names. At the desk level, the artifact is still the document or data file that tells the district what the Medicaid program did with each submitted claim.

For many districts, that artifact is a PDF or portal report with paid and denied sections. For others, the billing platform has already consumed an 835 ERA and produced an export. In both cases, the reconciliation spreadsheet needs to preserve the same business facts: claim identifier, student or Medicaid ID, service date, procedure code, billed amount, paid amount, adjustment amount, denial or remark code, and current status.

Before extraction, identify the source constraints that affect reconciliation quality. Is the report a native PDF with selectable text, a scanned image, a portal CSV, or a billing-platform export? Does it show one row per claim, one row per service line, or a summary row with detail buried below it? Does the state report use local EOB codes while the 835-derived export uses CARC, CAGC, and RARC fields? Those details determine whether the spreadsheet should be extracted directly from the remittance artifact, joined with a separate billing export, or reviewed against both.

CMS guidance on health care payment and remittance advice explains that an ERA or standard paper remit is sent after claim processing with final claim adjudication and payment information, itemized by claim and/or line, and that adjustment reasons may use Group Codes, CARCs, and RARCs. That is the bridge between the PDF R&S report and the 835 ERA. One is a human-readable remittance surface, the other is a structured transaction, but both carry claim outcome data the district has to reconcile.

The 835 only needs enough attention to map it into district work. BPR and TRN identify payment context and trace information. CLP gives the claim outcome. SVC carries service-line detail. CAS carries adjustments. LQ can carry remark-code information. Those segments matter because they become columns in the spreadsheet, not because the Medicaid coordinator needs to become an EDI implementation specialist.

Extract the Fields That Make Reconciliation Possible

The extraction target should be a stable claim-level table, not whatever columns happen to be easiest to copy from this month's report. Start with source fields: student or Medicaid ID, claim number or ICN, service date, procedure code, modifier, units, billed amount, allowed amount if present, paid amount, adjustment amount, denial or adjustment code, remark code, payment or trace context, provider or district identifier, source file name, and source page.

Keep the original report values intact, then add working fields beside them. Useful working fields include outcome bucket, denial family, denial age, owner, next action, internal due date, corrected-claim status, appeal status, and final disposition. That separation matters because the original code or status may be needed for audit support, while the working field is what staff use to sort the queue.

Line-level detail is worth preserving when the remittance adjudicates services differently within the same claim. A student may have multiple service dates, procedure codes, modifiers, or units inside one batch. If extraction collapses those lines into a single note, the district loses the ability to see which service was paid, which was denied, and which needs correction.

This is where document extraction becomes concrete. If the district receives a multi-page PDF, scan, or exported remittance document, Invoice Data Extraction can help staff extract R&S report data into a spreadsheet by uploading the file, describing the claim-level columns needed, and downloading Excel, CSV, or JSON output. The product supports prompt-based extraction from PDFs and image files, including large batches and very long PDFs, but it is not a Medicaid billing platform, clearinghouse, SIS, or 835 auto-posting engine. It helps create the structured data the reconciliation workflow needs.

A good extraction prompt should ask for one row per claim line where line-level adjudication appears, preserve all denial and remark codes exactly as shown, include source page references, and add blank working columns for owner, next action, due date, and disposition. The spreadsheet should make review easier without rewriting the official remittance record.

After extraction, spot-check a small sample against the original report before staff start working the queue. Compare paid amounts, denial codes, service dates, and source pages across several paid and denied claims. If a multi-line claim was collapsed, or a denial code was merged into free text, fix the extraction schema before the spreadsheet becomes the team's source of truth.

Classify Paid, Denied, Partial, Pended, Reversed, and Voided Lines

The first pass after extraction is outcome classification. Keep the source status text from the R&S report or 835 export, then assign a normalized bucket the district can sort: paid, denied, partial, pended or suspended, reversed, voided, and void-and-replace. Do not overwrite the source value. The source value is evidence; the normalized bucket is a work-management layer.

Denied and adjusted claims need a second layer of classification. CARC, CAGC, RARC, and state-specific EOB codes should stay in separate fields, even when the report displays them close together. CAGC shows the broad adjustment group, CARC explains the adjustment reason, RARC adds remark context, and a local EOB code may reflect a state-specific denial taxonomy. Keeping those fields separate lets the coordinator sort recurring code families without burying the official codes in a notes column.

Avoid turning the spreadsheet into a frozen code manual. Code lists and state crosswalks can change, and some states use local codes that do not map cleanly to a national reason-code label. For broader healthcare remittance field design, EOB data extraction to Excel is the closer reference point; for school Medicaid operations, the more useful move is to group denials by action.

A practical denial review sheet usually separates resubmittable corrections from appeal items, documentation follow-up, eligibility or authorization issues, and write-off candidates. A missing modifier, mismatched units, or invalid procedure code may point to a corrected or replacement claim. A denied service with supporting documentation may require reconsideration or appeal. A claim outside the controlling deadline may need finance review before it is written off. The goal is not to label every denial perfectly on day one; it is to make the next action visible and owned.

Match Each Remittance Row Back to the Service Log

The remittance spreadsheet is not reconciled until each paid, denied, adjusted, or pended row can be traced back to the district's own service evidence. The R&S report says how Medicaid adjudicated the claim. The district still needs to know which service log, encounter record, treatment note, IEP service entry, or billing export produced that claim.

The best matching keys are usually a combination, not a single field. Student or Medicaid ID narrows the population. Service date, procedure code, HCPCS or CPT code, modifier, units, provider or therapist, claim number, ICN, and billing batch help distinguish similar services. Where the billing platform assigns its own internal claim identifier, keep that field beside the state ICN so staff can move between systems without guessing.

This is the step that catches ordinary but expensive mismatches: a paid amount that does not match the expected amount, a duplicate service date, a missing modifier, units that differ between the service log and claim, a student eligibility issue, a documentation gap, or a claim that appears in the billing system but never shows up in the state remittance. Those mismatches are hard to see if the R&S report is reviewed as a PDF and the service log lives somewhere else.

When a row does not match cleanly, do not force it into the closest service record just to clear the exception. Flag it as unmatched, keep the source remittance fields visible, and assign it for review. A small unmatched queue is more defensible than a spreadsheet that appears reconciled because uncertain matches were silently accepted.

Districts may work inside Frontline IEP, IEP Direct, PCG EDPlan, Embrace Education, Tienet, Goalbook IEP, or another SIS and Medicaid billing platform. The article is not about choosing among those systems. The practical question is whether the district can export enough service and billing data to join against the remittance spreadsheet. If not, reconciliation becomes manual lookup work each month.

The same business office may also be handling related district AP documents, such as regional service agency monthly bill extraction. That is a different document and a different control cycle, but the discipline is similar: preserve source detail, structure the data, and reconcile it against the district's internal records before finance relies on it.

Build the Denied-Claim Resubmission Worklist

A denial spreadsheet becomes operational when it has dates, owners, and action categories. At minimum, the worklist should show the original service date, claim submission date if available, remittance date, first-denial date, current age, denial age, controlling resubmission or appeal deadline, internal due date, owner, next action, evidence needed, corrected-claim status, appeal status, and final disposition.

Be careful with deadline assumptions. School-based Medicaid resubmission and appeal windows vary by state program, payer rule, claim type, and sometimes by whether the district is submitting a corrected claim, replacement claim, or appeal. The spreadsheet should calculate aging from the district's controlling trigger date, which may be the remittance date, first-denial date, original service date, or another state-defined date, not from a generic 60-day or 90-day assumption. Many teams still use an internal due date that is earlier than the formal deadline so the coordinator has time to review corrections before submission.

Prioritization should be visible. High-dollar denials near deadline need attention before low-dollar items with more runway. Fixable missing-data denials should not sit behind claims that require external documentation. Recurring CARC or local EOB code families should be grouped so the coordinator can see whether a process issue is causing repeat denials, such as a modifier problem, service authorization mismatch, eligibility issue, or incomplete treatment note.

The owner field is not administrative clutter. Without ownership, the worklist becomes a filtered spreadsheet that everyone assumes someone else is handling. A useful worklist names who is correcting the billing record, who is requesting documentation, who is reviewing appeal viability, and who is approving write-off. Open claims should stay visible until final disposition, including resubmitted, paid after correction, appealed, denied after appeal, voided, or written off.

Management review should focus on unresolved patterns, not every line. A coordinator may review the full worklist, while the special-ed director or finance accountant needs trend views: dollars denied by reason family, claims approaching deadline, denials waiting on provider documentation, and expected Medicaid revenue that should not yet be posted as final.

Tie Monthly Remittance to GL Posting and Year-End Cost Settlement

Paid remittance lines support Medicaid revenue posting, but they still need enough detail for finance to understand what was paid and why. The GL entry may not need every CARC, RARC, student ID, or service date, but the reconciliation file should retain that detail so the district can answer later questions about a payment batch, adjustment, reversal, or audit sample.

Denied, pended, reversed, and voided lines should remain visible outside the revenue posting entry. A paid amount can move into the GL once it has been matched and approved through the district's process. An unresolved denial is not the same thing as lost revenue, and a pended claim is not the same thing as a denial. Treating those categories separately prevents finance from either overstating expected Medicaid revenue or losing track of recoverable claims.

Clean monthly reconciliation also reduces year-end cleanup. The annual process behind school district Medicaid cost settlement reconciliation has a different purpose, but it is easier when monthly claim outcomes, interim payments, reversals, disallowances, and open denial dispositions are already organized. A district that waits until year-end to understand remittance exceptions has to reconstruct old service evidence, old billing batches, and old denial decisions after the people involved have moved on to newer claims.

The practical implementation order is straightforward:

• Preserve the original remittance fields and source page or file reference.
• Normalize outcome buckets without deleting source status text.
• Match claim lines to service logs, encounter records, and billing exports.
• Assign owners and due dates to denied and pended claims.
• Age open items from the district's controlling state rule.
• Keep paid lines, unresolved denials, reversals, voids, and write-offs visible to finance until final disposition.

That is the point of the R&S extraction spreadsheet: not just to make the remittance easier to read, but to give the Medicaid office, special-ed administration, and finance office one defensible claim-level record for monthly follow-up.