Build your billing and collections forecast:
24 months ahead, from two questions.
With Chargebee MCP, your subscriptions, invoices, transactions and customers are live and interlinked. Point your AI client at them and it returns a working billing and collections forecast engine in one artifact. No spreadsheet rebuild, no vlookups, no waiting on multiple teams.
Billing and collections in one engine, built from your own cadences and net terms. Move across an executive summary, billing detail, collections detail and two assumption tabs you can edit in the browser. Every total on screen is computed from the rows beneath it, so nothing drifts out of sync.
* Anonymized sample shown. Company names, IDs and figures are illustrative. Some subscriptions were added to the sample to cover billing frequencies and statuses the source site did not contain.
Cut the scavenger hunt and the lookups to two questions.
The forecast used to begin by collecting the pieces. Billing data, the subscription list, invoices and transactions each sat with a different team, and every extract arrived in its own format, on its own schedule, with no shared key you could join on. New business, expansion and churn came from somewhere else again. Before anyone could forecast anything, someone had to go and ask several teams for a file.
Then the manual part. Line up four billing cadences by hand and work out which subscriptions bill again inside the window. Apply a collection-rate assumption nobody has re-derived in years. Convert the currencies. Vlookup across sheets because the data was never in one place to begin with. It took days, and by the time it reconciled the underlying data had already moved.
Now it is one prompt. It asks two questions, tells you exactly what it exported, waits for you to confirm the counts, and builds. From there you are editing assumptions in the browser rather than rebuilding the model.
The story is already there.
Most billing systems record the current state of a subscription and little else. What it costs today, when it renews. The commercial story around it (the cadence it bills on, the terms the customer actually pays on, how the money has historically arrived) lives in other systems, or in someone's spreadsheet, or nowhere at all.
Chargebee is built differently. The full event chain is stored against every record and stays queryable. None of it is parked in a separate system or a spreadsheet. Billing period, net terms, and payment history sit on the same record as the subscription itself, so a forecast does not have to reassemble them from four places. Every settled transaction records how that customer really paid, rather than what they once put on file. And twelve months of invoices and payments sit behind the collection rate, so it can be derived instead of inherited.
Connect an AI client over the Model Context Protocol and that chain becomes readable in natural language. That is what makes a 24-month forecast possible without a warehouse, a BI tool, or an analyst in the loop.
None of this is new data. What is new is being able to ask for it in one sentence.
Paste this into any MCP-connected AI client to run the recipe on your own data. It stays collapsed by default, so business readers can skip the technical detail.
The recipe does not start by running queries. It asks two questions, and it asks them one at a time.
Scope and cohort
Which subscriptions to include, in natural language. “All active and non-renewing subscriptions.” “Customers active in the last three months.” “Everyone activated in H1.” It then works out what that implies and states the dates back to you: “I'll treat 18 August as today and use 1 January to 18 August as the cohort window.” You correct it if it is wrong. It never asks you for a timezone offset, because asking a finance lead for a UTC offset is asking them to do the machine's job.
Seed assumptions
New deals, upgrades and churn, entered as ARR. These seed the forecast and stay editable inside the dashboard afterwards, so this is a starting point rather than a commitment.
A checkpoint is a deliberate stop. Before any arithmetic happens, the recipe pulls the whole dataset, tells you exactly what it got, and waits for you to agree. It reports in the form “exported 4,382 subscriptions, 61,204 invoices, 58,900 transactions, 4,010 customers.” You confirm the counts look right, then it builds.
The checkpoint earns its place twice over. The first reason is that nothing gets projected from a partial pull. A model that stops at the first page of results and extrapolates the rest returns a forecast that looks complete and is quietly wrong. A record count is the cheapest way to catch that, and it costs you one glance before the numbers reach a board pack.
The second reason is that the data stays in the conversation. The export was loaded into the chat rather than written to a file and forgotten, so once the dashboard is built you can keep asking questions of the same dataset. Slice it a different way, check a single customer, test a different assumption, or ask for a variant of the forecast. None of it needs a second export, which is usually the slowest part of the whole job.
Point the prompt at your own site. It takes a brand URL, reads the colour palette from there, and builds the dashboard in your colours instead of Chargebee's.
Every calculation also ships with a stated default and an open invitation to change it: the billing frequency grouping, the collection-rate window, the roll-forward method, how assumptions are allocated, and how collections are shifted. Say the word and it recalculates.
Show the full promptHide the full prompt
You're going to build a Billing and Collections Forecast Engine dashboard for my Chargebee site using the Chargebee MCP connector for all data. Output should be a single self-contained HTML file — no server, no build step, all data baked in as JS constants, so I can open it in a browser or share it with my team.
Before you start, ask me questions in order. Format each one neatly: a short intro line, then the specific inputs as a bulleted list with an example answer under each, labeled with progress like "Question 1 of 2". Wait for my answer before moving to the next question.
QUESTION 1 OF 2: SCOPE & COHORT
• Which subscriptions or cohort should I include? (e.g. "pull all Active + Non-Renewing subscriptions", or "customers active in the last three months", or "customers activated in H1 of this year")
Once I answer, don't ask me for a timezone offset or a raw cutoff date. Instead, work out and state the exact date range and "today" cutoff my answer implies — e.g. if I say "last three months," tell me the exact calendar months that resolves to; if I say "H1 of this year," tell me the exact Jan 1–Jun 30 dates you're using. Present it as a statement ("I'll treat [date] as today and use [range] as the cohort window"), not a question, and let me correct it if it's wrong.
QUESTION 2 OF 2: FORECAST SEED ASSUMPTIONS
• What are your starting assumptions for New Deals, Upgrades, and Churn, in ARR $, to seed the forecast? You can change these later inside the dashboard itself. (e.g. "$100K new deals and $50K upgrades starting next month, no churn assumed")
Once I've answered both — export the full dataset before running any queries or calculations. Use Chargebee MCP's Export Data toolset (not paginated list_* calls) to pull complete exports for: subscriptions (all statuses in my cohort), invoices (covering at minimum my cohort window plus the trailing 12 months for the collections rate), transactions, and customers. Wait for each export job to finish and retrieve the full result set — don't stop at the first page. Once done, report back to me the total record count for each dataset (e.g. "exported 4,382 subscriptions, 61,204 invoices, 58,900 transactions, 4,010 customers") so I can confirm the data is complete before you compute anything. If the export toolset isn't available or a job fails, fall back to paginated list calls but page through every result exhaustively and tell me the total record count you ended up with — never silently stop at page 1 and project the rest.
Once the export is confirmed complete, extract these fields from the exported data (not fresh list calls) for the calculations below:
DATA TO FETCH (FROM THE EXPORT)
• Active + Non-Renewing + Cancelled subscriptions: id, customer_id, customer name, status, currency, MRR, billing period (1/3/6/12 months = Monthly/Quarterly/Semi-Annual/Annual), next_billing_amount (excl. tax — never the invoice total), next_billing_at (raw Unix timestamp, do not convert), cancelled_at, plan_id.
• Customer net_term_days (from the customer export, not the subscription object).
• Current-month invoices already issued (sub_total excl. tax, currency, date, status, amount_paid).
• Per-subscription historical invoices from Jan 1 of this year through today — build this as a per-subscription-per-month table, not a single blended total. This is what actually populates each subscription's row in the detail tables.
• Active coupons/discounts per subscription (discounted amount, full price after expiry, expiry timestamp).
• Real payment method + gateway per subscription — pull this from each subscription's most recent SETTLED transaction record, not from the customer's stored default payment source. If a customer's auto_collection is "off", their card on file is never actually charged, so verify against a real transaction rather than assuming.
• Full trailing 12 months of invoices + payments (for the collections rate calculation below) — from the export, not a sample of "trailing N transactions."
• Billed but unpaid invoices — for every invoice in the cohort, the amount still outstanding (sub_total excl. tax minus amount_paid), with its invoice date, due date and status. Pull the open ones explicitly (status payment_due and not_paid) rather than inferring them from a gap, and keep them per subscription per month so the cash can be scheduled instead of shown as one lump sum.
CALCULATIONS — for each of these, use the default formula unless I tell you otherwise:
• Billing Frequency Ratio (BFR) — default: group active subscriptions' MRR by billing period (Monthly/Quarterly/Semi-Annual/Annual), express each as a % of total MRR; show all four buckets even if one is 0%. Tell me if you want a different grouping or weighting.
• Collections rate — default: (total payments collected excl. tax) ÷ (total invoiced excl. tax) over the full trailing 12 months by invoice date, baked as a fixed rate with a per-month override in the UI. Tell me if you want a different window or a different definition.
• Forecast roll-forward — default: for each subscription, roll forward from next_billing_at in steps of its billing period, using calendar-month arithmetic clamped at month-end (e.g. Jan 31 + 1 month = Feb 28); use real invoice data instead of projecting for months already elapsed this year. Tell me if you want a different roll-forward method.
• New/Upgrade/Churn assumptions — default: entered as ARR and spread evenly across the months of the calendar year they're entered in (ARR ÷ 12 per month, no rollover to next January). Label the allocation method on screen so it is obvious whether the figure is blended or split by billing bucket — do not imply a bucket split you haven't actually performed. Tell me if you want a different allocation method.
• Assumption overlay in collections — default: assumption amounts flow into the collections forecast as well as the billing forecast, shifted by the same net-terms lag and multiplied by the same collection rate as subscription billing. State the lag you applied. An assumption that moves billing but leaves collections untouched is a bug, not a simplification.
• Collections shift — default: shift the billing month by ceil(net_terms / 30) months, then take either 100% of billing or billing × collection rate depending on which mode I pick in the UI. Tell me if you want a different shift calculation.
• Active discounts — default: apply every live coupon to projected billing and to the collections that follow from it. A percentage coupon multiplies the billing amount by (1 − pct/100), a fixed coupon subtracts its amount, and the result floors at zero, so a 100% discount must project zero billing rather than list price. Only apply a coupon while it is actually live: treat a null expiry as never expiring, and stop applying it after its expiry month. Months already elapsed come from real invoices, which are already net of discount, so never discount those a second time. If a discount takes billing to zero while MRR still reads list price, say so on screen rather than letting the two figures quietly disagree.
• Billed but not yet collected — default: treat every invoice already issued and not fully paid as cash still to arrive, and schedule it rather than dropping it into one month. Spread each balance evenly across ceil(net_terms / 30) months from its due month, so a 60-day account cannot land its whole balance at once, and pull anything already overdue forward to the current month. Surface the schedule as an editable row per month on the Executive Summary, generated only from the months that actually carry outstanding cash rather than a fixed number of rows, and scale the per-subscription figures pro-rata when I override a month total. Give each subscription its own outstanding column in Collections Detail, and include it in the footer total.
• The current month is already partly real — default: for the current month, use payments actually received rather than projecting billing × rate. If you project the current month and also count its unpaid invoices as outstanding, you have counted the same month twice. State plainly which months are actuals and which are projections.
• Upgrades — if an events API is available, derive the upgrade run-rate from mrr_updated and subscription_changed events over a recent window instead of asking me to guess it, and tell me which window you used. Falling back to invoice-over-invoice growth per subscription is fine. Telling me upgrades cannot be derived from the data is not.
TOTALS AND RECONCILIATION
• Give every detail table a Total column per row and a totals footer row, both computed at render time from the visible cells.
• State in the footer label whether those totals include the assumption overlay or only real subscription billing, and keep that choice consistent between the footer, the KPI tiles and the chart. If any two of them are deliberately scoped differently, say so on screen rather than leaving me to reconcile them.
• Normalise status values before using them as keys or CSS classes. Statuses contain hyphens and spaces ("Non-Renewing"), so lowercase them and replace any run of spaces or hyphens with a single underscore. A filter that silently matches nothing is worse than one that throws.
HANDLING CANCELLED SUBSCRIPTIONS
Show a cancelled subscription's real actuals up to its cancellation date in its own row, but exclude it entirely from every rolled-up total (KPIs, column totals, BFR) — note the exclusion in a small banner instead of silently dropping the subscription from the file.
DASHBOARD STRUCTURE (5 tabs, no access gate — build it open, anyone with the file can view it)
1. Executive Summary — KPI tiles (portfolio MRR, projected billing, expected collections, subscription count), the BFR breakdown, a monthly breakdown table, and a 12/24-month chart. In the chart, stack the assumption overlay on top of subscription billing in its own colour rather than merging them into one bar, draw collections as a line over the top, and include a legend naming all three series. I need to see at a glance how much of a month is real billing and how much is my own assumption. Put the editable outstanding-invoice panel beside the BFR breakdown so it uses the space already there rather than adding another full-width row.
2. Billing Detail — one row per subscription with MRR shown in BOTH native currency and USD (separate permanent columns, plus a toggle to switch which currency the monthly cells show), sorted descending by MRR in USD. Include search, status/frequency filters, a 12/24-month toggle, a per-row Total column, a totals footer row, and CSV/Excel export. Mark already-billed current-month cells distinctly.
3. Collections Detail — same layout/sort/currency-toggle/Total-column/footer pattern as Billing Detail, plus the payment method, gateway and outstanding-balance columns, and a mode toggle between "Billing + Net Terms" and "Rate + Net Terms". Default to "Rate + Net Terms" so the collections rate table is live the moment the file opens; booting into a mode that ignores those rates makes the entire Collections Assumptions tab look broken.
4. Billing Assumptions — editable table of New Deals/Upgrades/Churn/Overage/Professional Charges by month, with past and current months locked, and a reset control that restores the live-derived seeds.
5. Collections Assumptions — editable collections rate by month with a slider and a numeric override, past months locked, and a reset control that restores the baked rate. Render the rate as a percentage, never as a raw decimal or a bare integer.
EDITABLE ASSUMPTIONS — INTERACTION RULES (every editable surface, including the outstanding-invoice panel on the Executive Summary)
• Use the same two-button pattern on both tabs: a reset control, and an "Apply & refresh" button that commits. Edits are staged until applied, and the apply button shows how many months are pending.
• Stage edits on every keystroke, not on blur. If I type a number and the apply button stays disabled because I haven't tabbed out of the field, the dashboard looks broken even though nothing is wrong.
• If an apply action cannot change anything in the current view — for example, editing collection rates while Collections Detail is in "Billing + Net Terms" mode, which collects 100% of billing and ignores rates entirely — say so plainly and tell me what to switch. Never report a successful recalculation that did not happen.
• If a month I edited falls outside the currently visible chart horizon, name that month and tell me how to bring it into view.
• Scroll the assumption tables vertically, not horizontally.
• Keep every filter and mode control in sync with state. A dropdown still reading "All frequencies" after I picked "Annual", or one I can't switch back, is a defect.
BUILD CONSTRAINTS
• No external chart library (e.g. Chart.js) — CDN scripts can silently fail to load in sandboxed preview environments. Build the chart as inline SVG generated from the same forecast data used in the tables.
• Excel export via SheetJS from a CDN is fine since it only runs on a user click, but guard it with a check and fall back to CSV export (no external dependency) if the library didn't load.
• Every total shown anywhere in the dashboard must be computed at render time from the underlying per-subscription data — never hand-type a separate aggregate number that could drift out of sync with the detail tables.
• Exports have to survive Excel. Write CSV as UTF-8 with a byte-order mark and declare charset=utf-8, or non-USD currency symbols and any dash placeholder arrive as mojibake. Never write a dash into an exported cell as a stand-in for an empty value: export a genuinely empty cell and keep the dash on screen only.
• Give every chart label an explicit fill colour. Text that inherits its colour can render invisible against a dark panel. Put each series total next to its legend label rather than the series name alone, so the legend doubles as a summary.
• Match the dashboard to our brand. Our website is [YOUR COMPANY URL]: read the colour palette from there and apply it consistently across the tabs, tables and chart. Keep past months, the current month and future months visually distinct from one another so the timeline stays readable at a glance.
Once built, tell me the file name and where you saved it, and flag any data-quality issues you ran into along the way (e.g. missing net terms, ambiguous currency, subscriptions with no recent transaction to source a payment method from) — including any gaps between what the export returned and what you expected.Two answers in, and you get a five-tab dashboard in a single HTML file: executive summary, billing detail, collections detail, and two editable assumption tabs. It opens in a browser and can be sent to anyone.
Requirements
A Chargebee account, and an AI client that supports MCP (Claude, ChatGPT, Cursor, and others) with Chargebee connected.
Setup
Connect Chargebee to your AI client using OAuth. No API keys or technical setup required. This is a long recipe with a lot of data behind it, so use the strongest model your client offers, and expect to be asked to confirm the record counts before it computes.
Supported clients
Claude (web and desktop), ChatGPT, Claude Code, Gemini, Cursor, VS Code, Windsurf, Zed, and any MCP-compatible client.