Downloads

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

How the firm works

Follow the money and you have the whole model.

  1. Clients belong to a partner and a manager, have an entity type (individual, S corporation, partnership, C corporation, nonprofit, trust) and an industry.
  2. The firm sells services in six service lines: Tax Preparation, Tax Planning, Bookkeeping, Payroll, Assurance and Advisory.
  3. 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.
  4. Staff record time entries against engagements, at the rate for their role in that year.
  5. Invoices (with invoice lines) bill the work. Time entries point at the invoice that billed them; unbilled time has no invoice.
  6. 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.
  7. 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

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

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.