<!-- https://learnsql.cpetoday.com/analyze/cpa-firm -->
# CPA firm practice management (cpafirm)

`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`, database `cpafirm`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** open [the database page](/databases/cpafirm) 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.

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

- **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_applications` to follow the money.
- **Aging.** An invoice is overdue when its balance (`total_amount` minus applied payments minus adjustments) is above zero and `due_date` is before the as-of date.
- **Ledger.** Debits equal credits in every entry. Revenue accounts are by service line, and `4900` holds billing adjustments and discounts.
- **Prospects.** About 40 clients have status `Prospect` and 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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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_entries` and `payments` as fact tables, with `clients`, `services`, `staff` and a calendar table as dimensions. Build the calendar from `invoices.issue_date`, or from the date range with `dataset_info.as_of_date` as 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_entries` to `staff` for the rate, and `invoices` to `payment_applications` to build your own aging. The [modeling guide](/analyze/learnsql-models) 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](/analyze/maps-and-fiscal) 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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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.
