Child Care Subsidy Billing: Can AI Match Payments to Attendance?

Yes, with a condition. Don't rely on a chat model to read a remittance and spot what is wrong; use it to build the spreadsheet that matches each child's authorised days and recorded attendance to the amount the agency paid, then lists every shortfall. Check the result by testing five children by hand and reconciling totals to the bank.

The condition is how the sums get done. Ask a chat model to add up forty rows and it may be right, or it may quietly drop one. Ask it to write the formulas and a spreadsheet adds the rows identically every time. That distinction is the difference between a reconciliation you can defend and one that merely looks tidy, and it shapes everything below. The method works on child codes, so no family's name needs to leave your premises.

Follow me on Instagram@sagnikteaches

The three records you are matching

Every subsidy reconciliation compares three things, and most mismatches come from one of them being wrong rather than from the agency.

Connect on LinkedInSagnik Bhattacharya

• The authorisation. What the agency approved for each child: start and end dates, the number of days or hours a week, the rate band and the parent's share. This lives on a letter or portal screen, and it changes when a family's circumstances change.
• Your attendance record. Sign-in and sign-out times or absence marks from your software or register. It is the evidence behind every claim.
• The remittance. What the agency actually paid for the period, by child, with any adjustment lines for earlier months.

The match is: for each child and period, what you were entitled to claim given authorisation and attendance, versus what arrived. The gap, positive or negative, is the shortfall list. How each agency treats absences, part-days and rounding differs, so your rules sheet (step one below) must come from your agency's own guidance and not from this page. I have deliberately given no rule figures for that reason.

Subscribe on YouTube@sagnikteaches

Step one: write down your agency's rules as a short sheet

Before any AI, put the rules that decide the amount into five or six plain lines. A filled-in example for an illustrative centre (the numbers are invented; use your agency's):

• Paid by authorised days, up to the weekly maximum on the authorisation.
• An absence day is payable for up to [number] days per child per month; beyond that, not payable.
• Attendance is rounded to [quarter-hour or half-day] units.
• Claims must be submitted by [date each month]; late claims are paid in the following cycle.
• Parent co-payment is [amount or percentage] of the daily rate, collected by the centre.
• Adjustments for earlier months appear on the remittance as separate lines.

The square brackets are things to fill from your agency's guidance or the portal's help text. If you cannot find a rule, ask the agency or your provider representative in writing and keep the answer with the sheet. This page is the single most valuable thing you will create in the whole exercise, because everything after it is mechanical.

Step two: strip names and build a code list

Names stay out of the AI tool. Make a lookup sheet in your own spreadsheet with three columns: child code (C001, C002 and so on), the case or reference number from the agency, and the name. The lookup never leaves your computer. In the exports you plan to analyse, delete the name column and replace the case number with the code using a lookup formula. You can ask the AI how to do that without showing it the data: "I have a sheet with a column of case numbers in column B and a lookup table on another tab; write the formula that returns the child code." What a daycare may put into ChatGPT sets out the wider rules, and the same principle applies here: the tool sees structure and codes, not families.

If you would rather not upload even coded data, you can do the whole job by describing the columns. Type five invented rows with the same headings and ask for formulas; paste those formulas into your real sheet. It takes slightly longer and keeps all the real data in one place.

Step three: line the three records up in one sheet

Create one tab per record and a fourth for the reconciliation. The reconciliation tab has one row per child code per period and these columns:

This is the point where AI earns its keep. Describe the table and the rules and ask for the formulas.

I run a daycare. In Excel, my Reconciliation tab has these columns:
A child code, B authorised days per week, C attended days in the period,
D absence days in the period, E payable-absence limit (a cell on the Rules tab,
named AbsLimit), F expected payment, G paid, H difference.
Write formulas for: claimable days = attended days plus the smaller of
absence days and AbsLimit, but never more than authorised days times the number
of weeks in the period (cell Rules!B2). Expected payment = claimable days times
the daily rate (Rules!B3) minus the parent share per day (Rules!B4). A flag column
that says SHORT if the difference is below -0.50, OVER if above 0.50,
NONE if paid is zero and expected is above zero, otherwise OK.
Explain each formula in one sentence.

An illustrative answer returns four formulas using MIN, a SUMIFS over the attendance tab and a nested IF for the flag. Check the details it commonly gets wrong: it may use the number of weeks as a fixed 4 when your period has 4.4, it may ignore the cap, or it may write the flag in an order that labels a zero payment "SHORT" before reaching the "NONE" test. Fix the order so the most specific condition comes first. For help with formulas that do not behave, the ChatGPT and Excel guide shows how to feed an error back and get a repair.

A worked example: a 40-place centre, one month

Say an illustrative centre has 40 children on the register, 14 of them subsidised. The agency's remittance for the month shows 14 lines. The daily rate is $52, the parent share is $9 a day, so the agency should pay $43 a day for each claimable day. The month has 22 weekdays (4.4 weeks), and the agency pays for up to two absence days per child per month. The attendance tab shows the following for six of the 14 children (the rest are fine, which is normal).

Three lines need attention (C011 is fine: it had four absences, but the cap means only two count). C007 was paid for 11 days, not 13. The family moved from two to three days a week at the start of the month, and your authorisation tab already holds the new figure, but the agency appears to have applied the old two-day pattern for the first fortnight, two days fewer than the child attended. The difference is 2 days at $43, which is $86. The likely fix is sending the agency the changed-hours approval, and the evidence is the attendance record. C013 has no payment at all, which suggests the claim was not submitted or the authorisation lapsed; it needs a portal check the same day. C018 was overpaid by one day, and the lesson there is to flag it: overpayments are often recovered later from a future remittance, and finding it first lets you explain it to the parent rather than discover it as a deduction. The total shortfall in the table is $86 plus $731 minus the $43 overpayment, so $774 net, from six children out of fourteen. Illustrative numbers, but the pattern of one lapsed authorisation, one stale record and one overpayment is realistic.

Step four: the shortfall list, as a document you can act on

Filter the Flag column to anything that is not OK and copy those rows to a new tab. For each, add an owner (who chases it), a reason (stale authorisation, unsubmitted claim, rounding, absence limit) and a date due. Then ask AI for a tidy summary of the table you pasted, using codes only.

Here is a table of subsidy differences for one month, using child codes only.
Group the rows by likely cause (stale authorisation, no payment, overpayment),
write one sentence per group on what to check first, and give me the total
shortfall and total overpayment. Do not guess at causes the data does not show.

A good illustrative reply groups the three flags, suggests checking authorisation dates for C007, the submission log for C013 and the remittance adjustment lines for C018, and gives totals. Check the totals against your sheet. If the model's total differs from the spreadsheet's, trust the spreadsheet and find why: usually it counted a row twice.

Step five: parent top-up notices that explain, not accuse

When the agency pays less than the rate for a legitimate reason, such as an absence beyond the payable limit, the parent may owe a top-up. This is a delicate message, because a parent on a subsidy is often on a tight budget. Keep it factual and give them time. A template for AI to refine, with placeholders for you to fill locally:

Subject: Your child care account for [month]

Hi [first name],

This month [child's first name] attended [number] days and was absent [number]
days. The subsidy covers [number] of the absence days, so [number] days were not
covered. That leaves a balance of $[amount] on your account.

If you think we have this wrong, or your circumstances have changed, please
reply and we will check the record with you. Otherwise the balance is due by
[date]. If paying in one go is difficult, we can agree smaller payments.

[your name]

Ask the AI to soften or shorten it, but give it the template and not the family's details. Read the output for two things: that it does not promise anything the centre has not decided (such as waiving the balance), and that it does not blame the agency or the parent. A second use: ask it to write a plain-English paragraph explaining how the absence limit works for your information pack, drawing only on the rules sheet you wrote. The late-pickup wording tutorial shows the same tone for a related fee.

How state and regional portals differ and what to look up

Every area runs its own system, so treat the list below as questions to answer from your agency's guidance, not as facts about any one of them.

If your centre takes children from more than one agency, make a separate rules block and a separate tab for each. Mixing them in one sheet is the quickest way to break the flags.

Two edge cases that need their own rows

The mid-month starter. A child joins on the 11th. The authorisation may start on the date the agency processes the form, not the date the child first attended. Say the authorisation starts on the 15th, but the child attended on the 11th, 12th and 13th. Those three days sit outside it, so the formula should cap claimable days at zero before the start date. Add two date columns to the authorisation tab (start and end) and a SUMIFS on attendance dates that falls between them. Ask the AI: "count rows in the Attendance tab where the code matches and the date is on or after the authorisation start and on or before the end." The days outside the window become a separate list, "attended, not authorised", which is a conversation with the family and the agency about backdating, not an error to hide. In the illustrative month above, three such days at $43 is $129 of exposure on one child.

The split between two agencies. A centre with children funded by two different agencies cannot use one daily rate or one absence limit. Add an agency column on the lookup sheet and pull the rate and limit from the rules tab by agency, using a lookup. The prompt to give the AI is short: "My Rules tab has agencies in column A, daily rate in B, absence limit in C. Rewrite the claimable-days and expected-payment formulas to look up rate and limit by the agency code in column B of the Reconciliation tab." Then test with one child from each agency, by hand, before trusting the rest.

Three months of perfect matches that were wrong

An illustrative centre ran the reconciliation for three months and found every line matched. That felt too good, and it was. The "paid" column was filled by a lookup on the child code, and when a code was missing from the remittance tab the lookup returned the previous value instead of an error because the formula used an approximate match. Zero-payment children therefore looked paid. The tell was the bank check: the sum of the Paid column was higher than the deposit by exactly one child's monthly amount. The fix was an exact-match lookup (the final argument set to FALSE, or XLOOKUP with an explicit "not found" result) and the Total-to-bank check made permanent as the first line of the sheet. Ask the AI to review your lookup formulas specifically for approximate-match behaviour; it is a well-known trap and it finds it quickly.

Turning this into a monthly routine that takes an hour

Once the sheet exists the routine is short. On the day the remittance lands: paste the new attendance export and remittance into their tabs (names removed), check the total to bank, filter the flags, assign owners and send any top-up notices. A realistic first-month time for a 14-subsidised-child centre is around three hours, mostly spent finding and fixing codes. By month three it drops to about an hour, because the rules sheet and formulas are stable and the exceptions are few. Keep a short log on a separate tab: date reconciled, total short, total over, number resolved. After a quarter you will see which cause recurs. If it is stale authorisations, the fix is a front-desk step when families tell you about changed hours; if it is unsubmitted claims, the fix is a calendar reminder two days before the window closes. The AI is good at helping you draft that front-desk checklist, and poor at knowing which cause is yours, which is why the log matters.

Mistakes that wreck the match

• Duplicate codes. Two children sharing a code after a copy-paste error make every figure for both wrong. Add a COUNTIF check that every code appears once on the lookup sheet.
• Dates stored as text. An attendance export with dates as text will not filter by period. Convert them first; the AI can write the conversion formula.
• Part-weeks at a period boundary. A week that straddles two months confuses a "per week" cap. Compute the cap on actual days in the period.
• Sign-out times missing. If staff forget to sign a child out, the day may not count. Add an exceptions column listing days with no sign-out before you reconcile.
• Trusting the model's totals. It will state a total with confidence. Always compare to the spreadsheet sum.

How to check the result before you rely on it

1. Total to bank. Sum the Paid column and compare it to the deposit. If they differ, a line is missing or an adjustment has been double counted.
2. Hand-check five children. Choose one matched, one short, one with absences, one part-week and one new starter. Work each from the register and authorisation without looking at the sheet.
3. Rerun last month. A month you already settled should reconcile to the same answer you reached by hand.
4. Keep the evidence. Save the coded reconciliation, the remittance and the lookup with the date. If the agency queries a claim you can reproduce the working.
5. Review the rules sheet each term. Agencies change rates and limits; an old limit in your formula can cost you months of small shortfalls.

If your software offers its own matching, test it against the same checks. The comparison of two common daycare platforms shows what is real and what is marketing. For other admin jobs that sit around billing, nine admin jobs from enquiry to pickup is a good map, and if you want to talk through how your own attendance and subsidy data should flow, that is what my AI implementation consultation covers.

Questions daycare owners ask about subsidy reconciliation

Can I upload the remittance file straight into ChatGPT?

Only after swapping every name for a code. Remittances carry children's names, case numbers and payment amounts. Use a code column you hold locally, upload the coded sheet, and map codes back to names in your own spreadsheet when you write notices.

Should AI do the arithmetic or write the formulas?

Have it write the formulas, then let the spreadsheet do the sums. A chat model can slip on long columns of figures, while a formula gives the same answer every time and can be audited cell by cell. Use AI for the structure and the explanation.

How often should I reconcile?

Each payment cycle, within a few days of the remittance arriving. Many agencies set a window for queries or corrections, so a monthly-only habit can leave you unable to challenge an underpayment. Check your agency's published window and diarise it.

What if my software already says it reconciles?

Test it once against a hand-checked month. Some tools match totals, not children, so an overpayment on one child can hide an underpayment on another. If the tool agrees with your sheet for three cycles, rely on it; if not, keep the sheet as a second check.

Can AI draft the notice to parents?

Yes, from the amounts and dates only. Use a template with a first-name placeholder, add the child code locally, and read the result. Keep the tone factual: what was billed, what the agency paid, what the top-up is and the date it is due.

Further reads

• Adult Day Care Software for a Small Centre: Billing and AI Notes Checklist — Attendance and billing checks in a related setting.
• AI Receptionist for a Daycare: Tour Bookings, Calls and Pricing — Free up the front desk while you tidy billing.
• Daycare Inspection Checklist: Prepare Your Records With AI — Get attendance and billing records inspection-ready.
• Daycare Staffing Ratios: Plan the Daily Schedule With AI — Plan rooms and ratios from the same attendance data.
• AI Automation for Education: Five Workflows, Tools and Costs — Other education admin workflows worth automating.
• Daycare Incident Reports With AI: Factual, Name-Free Narratives — Another factual, name-free writing job.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: agency subsidy guidance varies by area and none is quoted; the method is standard spreadsheet reconciliation practice, and every rate and figure is illustrative.