CPA firm practice management (cpafirm)
Clients, engagements, tax returns, time, billing, trust and a general ledger for a small-business CPA firm, with realization, WIP and AR aging queries.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Connect to the cpafirm database and describe how its tables join.
- Calculate realization, work in progress and AR aging.
- Answer practice questions from the time, billing and trust tables.
- Build the same questions in Excel and Power BI.
cpafirm is the practice-management database of a fictional small-business CPA firm: 17 people in two offices (Bellevue and Portland), about 1,000 clients, and nearly six years of work, billing, collections and bookkeeping from January 2021 to October 2026. It has 34 tables and roughly 400,000 rows, and the data is identical on SQL Server and MariaDB.
It is built to look like the reports a real firm runs: realization by partner, WIP, AR aging, staff utilization, late returns, retainer balances and a general ledger that balances.
Connect to it
- SQL Server: server
learnsql.cpetoday.com,1433, databasecpafirm, tables in thedboschema. See Connect Excel and Power BI. - In the browser: open the database page for the Playground, the SQL editor and the schema explorer.
- Dates: the data is as of 2026-10-24. That date is stored in
dataset_info.as_of_date, and the reporting views use it instead of today's date, so results do not drift.
How the firm works
Follow the money and you have the whole model.
- Clients belong to a partner and a manager, have an entity type (individual, S corporation, partnership, C corporation, nonprofit, trust) and an industry.
- The firm sells services in six service lines: Tax Preparation, Tax Planning, Bookkeeping, Payroll, Assurance and Advisory.
- Each piece of work is an engagement. Tax and assurance work is one engagement per client per tax year. Recurring services (bookkeeping, payroll, virtual CFO) are one engagement per client per calendar year, billed every month.
- Staff record time entries against engagements, at the rate for their role in that year.
- Invoices (with invoice lines) bill the work. Time entries point at the invoice that billed them; unbilled time has no invoice.
- Payments arrive and are applied to one or more invoices. Some clients prepay through a trust account (a retainer) and the firm applies it to invoices. Small balances are adjusted off.
- Every invoice, payment, adjustment and trust movement posts to the general ledger, together with monthly operating expenses.
Tables at a glance
| Area | Tables | What they hold |
|---|---|---|
| Firm | offices, staff_roles, staff |
Two offices; six roles with standard billing rates; 17 staff (one has left, one joined recently) with a manager chain |
| Lookups | entity_types, industries, referral_sources, service_lines, services |
Reference lists; services has 17 services with default fees |
| Clients | clients, client_relationships, contacts |
1,000 clients (900 active, 60 inactive, 40 prospects); ownership links between people and their entities; 1,500 contacts |
| Work | engagements, engagement_staff, tasks, tax_returns, time_entries, client_visits |
7,494 engagements, who is on each, a checklist of tasks, 6,635 federal and state returns, 70,044 time entries, 2,362 trips to client sites |
| Billing | invoices, invoice_lines, payments, payment_applications, invoice_adjustments, trust_transactions |
21,282 invoices, 20,222 payments, how payments are applied, write-offs and discounts, and retainer activity |
| General ledger | gl_accounts, gl_journal_entries, gl_journal_lines |
A 24-account chart and about 93,600 balanced lines |
| Documents | document_requests, tax_organizers, esign_requests |
Document requests to clients, tax organizers and e-signature requests (engagement letters, Form 8879) |
| Geography and fiscal | geo_states, geo_places, geo_postal_codes, fiscal_calendar |
Reference geography and a July-June fiscal calendar with the tax-season rhythm |
| Meta | dataset_info |
The as-of date |
How the tables join
clients ──< engagements >── services >── service_lines │ │ │ │ │ ├──< time_entries >── staff (time_entries.invoice_id → invoices; NULL = unbilled) │ │ ├──< tax_returns (federal and state rows per tax-prep engagement) │ │ ├──< tasks, document_requests │ │ └──< engagement_staff │ └──< invoice_lines >── invoices >── clients └──< payments ──< payment_applications >── invoices invoices ──< invoice_adjustments clients ──< trust_transactions gl_journal_lines >── gl_journal_entries (source_type + source_id point back to the invoice, payment, ...)
The engagements.partner_id and manager_id columns are copied from the client so you can report by partner without an extra join.
Rules built into the data
- Fixed fees versus hours. Tax and assurance work is billed at a fixed fee, so billed amount and hours worked do not match. That is what makes realization (billed divided by hours at standard rates) interesting. Overall it is about 87%.
- Work in process (WIP). Billable time with no invoice (
time_entries.invoice_id IS NULL) is unbilled WIP. Engagements still in progress have some. - One payment, many invoices. A payment can cover several invoices, and an invoice can be paid in parts. Use
payment_applicationsto follow the money. - Aging. An invoice is overdue when its balance (
total_amountminus applied payments minus adjustments) is above zero anddue_dateis before the as-of date. - Ledger. Debits equal credits in every entry. Revenue accounts are by service line, and
4900holds billing adjustments and discounts. - Prospects. About 40 clients have status
Prospectand no activity at all. Filter them out of client counts.
Views
| View | Use |
|---|---|
vw_invoice_balances |
Paid, adjusted and open amount for every invoice, plus days past due |
vw_ar_aging |
Open receivables by client in current, 1-30, 31-60, 61-90 and over-90 buckets |
vw_unbilled_wip |
Unbilled hours and value by engagement |
vw_engagement_realization |
Billed amount, hours, standard value, labor cost and realization per engagement |
vw_staff_monthly_hours |
Total and billable hours per person per month |
Questions to try
Which service lines earned the most in 2025?
SELECT sl.line_name, SUM(l.amount) AS billed FROM invoice_lines l JOIN invoices i ON i.invoice_id = l.invoice_id JOIN engagements e ON e.engagement_id = l.engagement_id JOIN services s ON s.service_id = e.service_id JOIN service_lines sl ON sl.service_line_id = s.service_line_id WHERE YEAR(i.issue_date) = 2025 GROUP BY sl.line_name ORDER BY billed DESC;
Bookkeeping comes first at about $1.3 million, then Tax Preparation at about $1.0 million.
Who owes the most, and how much of it is more than 90 days old?
SELECT client_name, total_due, days_over_90 FROM vw_ar_aging ORDER BY total_due DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
What is each partner's realization?
SELECT CONCAT(st.first_name, ' ', st.last_name) AS partner,
SUM(r.billed_amount) AS billed,
SUM(r.standard_value) AS standard_value,
ROUND(SUM(r.billed_amount) / SUM(r.standard_value), 3) AS realization
FROM vw_engagement_realization r
JOIN engagements e ON e.engagement_id = r.engagement_id
JOIN staff st ON st.staff_id = e.partner_id
WHERE r.standard_value > 0
GROUP BY st.staff_id, st.first_name, st.last_name
ORDER BY realization;
Which returns were filed after their extended due date?
SELECT c.client_name, t.form_type, t.tax_year, t.extended_due_date, t.filed_date FROM tax_returns t JOIN clients c ON c.client_id = t.client_id WHERE t.filed_date > COALESCE(t.extended_due_date, t.due_date) ORDER BY t.filed_date DESC;
How busy was everyone in 2025?
SELECT first_name, last_name, SUM(total_hours) AS hours,
ROUND(SUM(billable_hours) * 1.0 / SUM(total_hours), 3) AS utilization
FROM vw_staff_monthly_hours
WHERE work_year = 2025
GROUP BY staff_id, first_name, last_name
ORDER BY utilization DESC;
Where is unbilled time piling up?
SELECT c.client_name, w.unbilled_hours, w.unbilled_value FROM vw_unbilled_wip w JOIN engagements e ON e.engagement_id = w.engagement_id JOIN clients c ON c.client_id = e.client_id ORDER BY w.unbilled_value DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
These queries run on both SQL Server and MariaDB. Other ideas: write-offs by partner, revenue per client by entity type, days from engagement open to invoice, collection time by client, and trust balances by client.
Excel and Power BI
- Star model. Use
invoice_lines,time_entriesandpaymentsas fact tables, withclients,services,staffand a calendar table as dimensions. Build the calendar frominvoices.issue_date, or from the date range withdataset_info.as_of_dateas the end. - Measures to write. Billed, Hours, Billable hours, Realization % (billed over standard value), Utilization %, Open AR, Days sales outstanding, WIP value.
- Power Query. Merge
time_entriestostafffor the rate, andinvoicestopayment_applicationsto build your own aging. The modeling guide shows the same recipe for the other databases.
Maps and fiscal years
Mapping. Every client and both offices have a full address, latitude, longitude and geocode_quality, and client_visits has the origin and destination of each trip from an office to a client, with round-trip miles and drive time. See maps and fiscal calendars for the shared pattern.
The fiscal year runs July to June, named by the year it ends (FY2026 is July 2025 to June 2026). engagements, invoices, payments, time_entries, gl_journal_entries and client_visits carry fiscal_year, fiscal_quarter and fiscal_period. fiscal_calendar adds tax_season (Busy season, Extension season, Off season) and tax_deadline.
What was billed in each fiscal quarter?
SELECT fiscal_year, fiscal_quarter, COUNT(*) AS invoices, SUM(total_amount) AS billed FROM invoices WHERE fiscal_year BETWEEN 2024 AND 2026 GROUP BY fiscal_year, fiscal_quarter ORDER BY fiscal_year, fiscal_quarter;
How much do we bill in busy season versus extension season?
SELECT f.tax_season, COUNT(*) AS invoices, SUM(i.total_amount) AS billed FROM invoices i JOIN fiscal_calendar f ON f.calendar_date = i.issue_date WHERE i.fiscal_year = 2025 GROUP BY f.tax_season ORDER BY billed DESC;
How many miles did each person drive to client sites in fiscal 2025?
SELECT s.first_name, s.last_name, COUNT(*) AS visits, ROUND(SUM(v.miles_round_trip), 0) AS miles FROM client_visits v JOIN staff s ON s.staff_id = v.staff_id WHERE v.fiscal_year = 2025 GROUP BY s.staff_id, s.first_name, s.last_name ORDER BY miles DESC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
Which Washington counties have the most active clients?
SELECT c.county, COUNT(*) AS clients FROM clients c WHERE c.status = 'Active' AND c.state = 'WA' GROUP BY c.county ORDER BY clients DESC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
Clients in Arizona, California and Idaho are served remotely, so filter by distance (see the haversine query in the maps guide) before you map visits.
Realistic imperfections
Some of the mess is deliberate, so your reports have something to find: about 6% of engagements have no engagement letter on file, a few returns are filed late, some invoices are overdue or written off, some document requests are still outstanding, and the trust account holds a few unapplied retainers.
Try it on the data
Open the cpafirm database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.