Statement of Functional Expenses - Instant Demo

The nonprofit statement QuickBooks can't produce: every expense split into program, management and fundraising - from your classes plus an allocation table for shared costs - with uncoded dollars flagged and a tie-out to the P&L.

9 steps · shared by Klajdi · September 21, 2026

Make it your own →

No data is shared in this template. It contains only the recipe — column names and SQL logic. When you run it, your data is processed in your own browser and never leaves your machine.

What data it expects

QBO_ProfitAndLoss_Classes.csv

section · VARCHARaccount · VARCHAR<one column per QuickBooks class, plus Not Specified and Total> · DOUBLE

Connect QuickBooks Online or drop a CSV / Excel export with a similar layout — the AI adapts the workflow if your columns differ.

How it works — every step, readable

01Demo shelter - last 12 months by class (sample)

SELECT section, account, CAST(COLUMNS(* EXCLUDE (section, account)) AS DOUBLE) FROM (VALUES
  ('Income', 'Donations & contributions', NULL, NULL, NULL, NULL, 1262000, NULL, 1262000),
  ('Income', 'Grants', 148000, 62000, 95000, NULL, NULL, NULL, 305000),
  ('Income', 'Adoption fees', NULL, 181000, NULL, NULL, NULL, NULL, 181000),
  ('Income', 'Clinic & program service fees', NULL, NULL, 142000, NULL, NULL, NULL, 142000),
  ('Income', 'Special events', NULL, NULL, NULL, NULL, 118000, NULL, 118000),
  ('Other Income', 'Investment income', NULL, NULL, NULL, 22800, NULL, NULL, 22800),
  ('Expenses', 'Salaries & wages', 498000, 176000, 214000, 88000, 72000, 8000, 1056000),
  ('Expenses', 'Executive Director salary', NULL, NULL, NULL, NULL, NULL, 115200, 115200),
  ('Expenses', 'Payroll taxes & benefits', 101000, 35500, 43500, 17800, 14600, 1200, 213600),
  ('Expenses', 'Animal care & veterinary supplies', 196000, 9000, 106600, NULL, NULL, 6400, 318000),
  ('Expenses', 'Occupancy & utilities', NULL, NULL, NULL, NULL, NULL, 152400, 152400),
  ('Expenses', 'Fundraising events & mailings', NULL, NULL, NULL, NULL, 96250, 2150, 98400),
  ('Expenses', 'Professional fees', NULL, NULL, NULL, 47300, NULL, 4300, 51600),
  ('Expenses', 'Insurance', NULL, NULL, NULL, NULL, NULL, 37200, 37200),
  ('Expenses', 'Depreciation', NULL, NULL, NULL, NULL, NULL, 62400, 62400),
  ('Other Expenses', 'Interest expense', NULL, NULL, NULL, 13800, NULL, NULL, 13800)
) AS t(section, account, "Animal Care & Sheltering", "Adoption & Community Outreach", "Veterinary Clinic", "Management & General", "Fundraising & Development", "Not Specified", "Total")

02Expenses by account and class

buckets values by condition · filters to the relevant rows · sorts the output

WITH u AS (
  UNPIVOT (SELECT * FROM input_1) ON COLUMNS(* EXCLUDE (section, account)) INTO NAME class VALUE amount
),
n AS (SELECT COUNT(*) FILTER (WHERE NOT regexp_matches(lower(class), '^total')) AS class_cells FROM u)
SELECT u.section, u.account,
  CASE WHEN regexp_matches(lower(u.class), '^total') THEN 'Not Specified' ELSE u.class END AS class,
  ROUND(u.amount, 2) AS amount
FROM u, n
WHERE u.amount IS NOT NULL AND u.amount <> 0
  AND (u.section ILIKE '%expense%' OR u.section ILIKE '%cost of goods%')
  AND u.account NOT ILIKE 'total %'
  AND (NOT regexp_matches(lower(u.class), '^total') OR n.class_cells = 0)
ORDER BY u.section, u.account, class

03Map each class to program, management or fundraising (check this step)

combines data from multiple inputs · aggregates rows into summary totals · buckets values by condition

WITH overrides(class, functional_area) AS (VALUES
  ('Type a class name here to override it', 'Program')
),
c AS (SELECT class, ROUND(SUM(amount), 2) AS expenses FROM input_1 GROUP BY class)
SELECT c.class,
  COALESCE(o.functional_area,
    CASE
      WHEN lower(c.class) = 'not specified' THEN 'To allocate'
      WHEN regexp_matches(lower(c.class), 'shared|indirect|alloca|common cost|overhead pool') THEN 'To allocate'
      WHEN regexp_matches(lower(c.class), 'fundrais|fund rais|^development|fund development|resource development|donor|advancement|capital campaign|gala|annual appeal') THEN 'Fundraising'
      WHEN regexp_matches(lower(c.class), 'admin|management|general|g&a|overhead|finance|accounting|governance|board') THEN 'Management & General'
      ELSE 'Program'
    END) AS functional_area,
  c.expenses,
  CASE WHEN o.functional_area IS NOT NULL THEN 'Set by you in the override list at the top of this step'
       WHEN lower(c.class) = 'not specified' THEN 'Posted with no class in QuickBooks - split by the allocation table where a rule fits'
       ELSE 'Matched by words in the class name - check it, and override it at the top of this step if it is wrong' END AS mapping_note
FROM c LEFT JOIN overrides o ON lower(o.class) = lower(c.class)
ORDER BY c.expenses DESC

04Shared-cost allocation table (edit the percentages)

SELECT * FROM (VALUES
  (1, 'executive director|chief executive', 'Time study', 45.0, 40.0, 15.0),
  (2, 'rent|occupancy|utilit|lease', 'Square footage', 82.0, 11.0, 7.0),
  (3, 'insurance', 'Square footage', 82.0, 11.0, 7.0),
  (4, 'depreciation|amortization', 'Square footage', 82.0, 11.0, 7.0),
  (5, 'salar|wage|payroll|benefit', 'Staff time', 70.0, 18.0, 12.0),
  (6, 'telephone|internet|software|technology|office', 'Headcount', 74.0, 16.0, 10.0)
) AS t(rule_order, account_words, basis, program_pct, management_pct, fundraising_pct)

05Assign every dollar to a function

combines data from multiple inputs · buckets values by condition · computes running / windowed totals

WITH base AS (
  SELECT l.account, l.class, m.functional_area AS class_area, l.amount
  FROM input_1 l JOIN input_2 m ON m.class = l.class
),
matched AS (
  SELECT b.account, b.class, b.class_area, b.amount, r.rule_order, r.basis, r.program_pct, r.management_pct, r.fundraising_pct,
    ROW_NUMBER() OVER (PARTITION BY b.account, b.class ORDER BY r.rule_order) AS rn
  FROM base b LEFT JOIN input_3 r
    ON b.class_area = 'To allocate' AND regexp_matches(lower(b.account), r.account_words)
),
pick AS (SELECT * FROM matched WHERE rn = 1),
parts AS (
  SELECT account, class, class_area AS functional_area, amount, 'Coded to this class in QuickBooks' AS how
  FROM pick WHERE class_area <> 'To allocate'
  UNION ALL
  SELECT account, class, 'Program', ROUND(amount * program_pct / 100.0, 2), 'Shared cost - split by ' || lower(basis)
  FROM pick WHERE class_area = 'To allocate' AND rule_order IS NOT NULL
  UNION ALL
  SELECT account, class, 'Management & General', ROUND(amount * management_pct / 100.0, 2), 'Shared cost - split by ' || lower(basis)
  FROM pick WHERE class_area = 'To allocate' AND rule_order IS NOT NULL
  UNION ALL
  SELECT account, class, 'Fundraising',
    CASE WHEN ABS(program_pct + management_pct + fundraising_pct - 100) < 0.001
         THEN ROUND(amount - ROUND(amount * program_pct / 100.0, 2) - ROUND(amount * management_pct / 100.0, 2), 2)
         ELSE ROUND(amount * fundraising_pct / 100.0, 2) END,
    'Shared cost - split by ' || lower(basis)
  FROM pick WHERE class_area = 'To allocate' AND rule_order IS NOT NULL
  UNION ALL
  SELECT account, class, 'Unallocated', amount, 'No class and no allocation rule - code it in QuickBooks or add a rule'
  FROM pick WHERE class_area = 'To allocate' AND rule_order IS NULL
)
SELECT account, class, functional_area, amount, how FROM parts WHERE amount <> 0 ORDER BY account, functional_area

06STATEMENT OF FUNCTIONAL EXPENSES

aggregates rows into summary totals · buckets values by condition · appends result sets (e.g. a TOTAL row)

WITH a AS (
  SELECT account,
    SUM(CASE WHEN functional_area = 'Program' THEN amount ELSE 0 END) AS program_services,
    SUM(CASE WHEN functional_area = 'Management & General' THEN amount ELSE 0 END) AS management_and_general,
    SUM(CASE WHEN functional_area = 'Fundraising' THEN amount ELSE 0 END) AS fundraising,
    SUM(CASE WHEN functional_area = 'Unallocated' THEN amount ELSE 0 END) AS unallocated,
    SUM(amount) AS total
  FROM input_1 GROUP BY account
),
t AS (SELECT SUM(program_services) AS p, SUM(management_and_general) AS m, SUM(fundraising) AS f, SUM(unallocated) AS u, SUM(total) AS tt FROM a),
lines AS (
  SELECT 1 AS ord, account AS line_item, program_services, management_and_general, fundraising, unallocated, total FROM a
  UNION ALL SELECT 2, 'TOTAL EXPENSES', p, m, f, u, tt FROM t
  UNION ALL SELECT 3, 'Share of total expenses (%)', 100.0 * p / NULLIF(tt, 0), 100.0 * m / NULLIF(tt, 0), 100.0 * f / NULLIF(tt, 0), 100.0 * u / NULLIF(tt, 0), 100.0 FROM t
)
SELECT line_item,
  ROUND(program_services, CASE WHEN ord = 3 THEN 1 ELSE 2 END) AS program_services,
  ROUND(management_and_general, CASE WHEN ord = 3 THEN 1 ELSE 2 END) AS management_and_general,
  ROUND(fundraising, CASE WHEN ord = 3 THEN 1 ELSE 2 END) AS fundraising,
  ROUND(unallocated, CASE WHEN ord = 3 THEN 1 ELSE 2 END) AS unallocated,
  ROUND(total, CASE WHEN ord = 3 THEN 1 ELSE 2 END) AS total
FROM lines ORDER BY ord, total DESC

07Tie-out to the QuickBooks P&L

buckets values by condition · filters to the relevant rows

WITH u AS (
  UNPIVOT (SELECT * FROM input_2) ON COLUMNS(* EXCLUDE (section, account)) INTO NAME class VALUE amount
),
pl AS (
  SELECT
    SUM(amount) FILTER (WHERE regexp_matches(lower(class), '^total')) AS quickbooks_total,
    SUM(amount) FILTER (WHERE NOT regexp_matches(lower(class), '^total')) AS sum_of_class_columns
  FROM u
  WHERE (section ILIKE '%expense%' OR section ILIKE '%cost of goods%') AND account NOT ILIKE 'total %'
),
st AS (SELECT total AS statement_total FROM input_1 WHERE line_item = 'TOTAL EXPENSES')
SELECT 'Total expenses' AS check_item,
  ROUND(COALESCE(pl.quickbooks_total, pl.sum_of_class_columns), 2) AS quickbooks_profit_and_loss,
  ROUND(st.statement_total, 2) AS functional_statement,
  ROUND(st.statement_total - COALESCE(pl.quickbooks_total, pl.sum_of_class_columns), 2) AS tie_out_difference,
  CASE WHEN pl.quickbooks_total IS NULL THEN 'QuickBooks sent no Total column - compared with the sum of the class columns'
       ELSE 'Compared with the Total column QuickBooks itself reports' END AS compared_with
FROM pl, st

08Program, management and fundraising ratios

filters to the relevant rows

WITH u AS (
  UNPIVOT (SELECT * FROM input_2) ON COLUMNS(* EXCLUDE (section, account)) INTO NAME class VALUE amount
),
inc AS (
  SELECT COALESCE(SUM(amount) FILTER (WHERE regexp_matches(lower(class), '^total')),
                  SUM(amount) FILTER (WHERE NOT regexp_matches(lower(class), '^total'))) AS contributions
  FROM u
  WHERE section ILIKE '%income%' AND section NOT ILIKE 'other%'
    AND regexp_matches(lower(account), 'donat|contribut|gift|grant|bequest|pledge|event|appeal|sponsor|campaign')
),
t AS (SELECT * FROM input_1 WHERE line_item = 'TOTAL EXPENSES')
SELECT t.total AS total_expenses, t.program_services, t.management_and_general, t.fundraising, t.unallocated,
  ROUND(100.0 * t.program_services / NULLIF(t.total, 0), 1) AS program_pct,
  ROUND(100.0 * t.management_and_general / NULLIF(t.total, 0), 1) AS management_pct,
  ROUND(100.0 * t.fundraising / NULLIF(t.total, 0), 1) AS fundraising_pct,
  ROUND(100.0 * t.unallocated / NULLIF(t.total, 0), 1) AS unallocated_pct,
  ROUND(inc.contributions, 2) AS contributions_and_grants,
  ROUND(t.fundraising / NULLIF(inc.contributions, 0), 2) AS cost_to_raise_a_dollar
FROM t, inc

09SUMMARY - what the auditor and the board will ask

aggregates rows into summary totals · buckets values by condition · appends result sets (e.g. a TOTAL row)

WITH r AS (SELECT * FROM input_1),
cm AS (SELECT * FROM input_2),
a AS (SELECT * FROM input_3),
t AS (SELECT * FROM input_4),
real_classes AS (SELECT COUNT(*) AS n,
    COUNT(*) FILTER (WHERE functional_area = 'Program') AS p,
    COUNT(*) FILTER (WHERE functional_area = 'Management & General') AS m,
    COUNT(*) FILTER (WHERE functional_area = 'Fundraising') AS f
  FROM cm WHERE lower(class) <> 'not specified'),
noclass AS (SELECT COALESCE(SUM(amount), 0) AS amt, COUNT(DISTINCT account) AS accts FROM a WHERE lower(class) = 'not specified'),
split AS (SELECT COALESCE(SUM(amount), 0) AS amt, COUNT(DISTINCT account) AS accts, COUNT(DISTINCT how) AS bases FROM a WHERE how LIKE 'Shared cost%'),
split_names AS (SELECT string_agg(account, ', ') AS names FROM (SELECT account FROM a WHERE how LIKE 'Shared cost%' GROUP BY account ORDER BY SUM(amount) DESC LIMIT 3)),
unal AS (SELECT COALESCE(SUM(amount), 0) AS amt, COUNT(DISTINCT account) AS accts FROM a WHERE functional_area = 'Unallocated'),
unal_top AS (SELECT account, SUM(amount) AS amt FROM a WHERE functional_area = 'Unallocated' GROUP BY account ORDER BY amt DESC LIMIT 1),
rows_out AS (
  SELECT 1 AS ord, 'THE STATEMENT' AS area,
    (SELECT (CASE WHEN (total_expenses) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(total_expenses)) AS BIGINT))) || ' of expenses over the period: program ' || (CAST(ROUND(program_pct, 1) AS VARCHAR) || '%') || ', management ' || (CAST(ROUND(management_pct, 1) AS VARCHAR) || '%') || ', fundraising ' || (CAST(ROUND(fundraising_pct, 1) AS VARCHAR) || '%')
            || CASE WHEN unallocated_pct > 0 THEN ', not yet allocated ' || (CAST(ROUND(unallocated_pct, 1) AS VARCHAR) || '%') ELSE '' END FROM r) AS finding,
    'None needed - the full statement, account by account, is the step named STATEMENT OF FUNCTIONAL EXPENSES' AS next_step
  UNION ALL
  SELECT 2, 'NO CLASSES IN USE',
    'Nothing in this period is coded to a class, so QuickBooks cannot split expenses by function',
    'Turn on class tracking (QuickBooks Plus and above) and code expenses to program, management or fundraising classes - until then only the allocation table can split costs'
  FROM real_classes WHERE n = 0
  UNION ALL
  SELECT 3, 'PROGRAM SPENDING',
    (SELECT (CAST(ROUND(program_pct, 1) AS VARCHAR) || '%') || ' of expenses go to programs (' || (CASE WHEN (program_services) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(program_services)) AS BIGINT))) || ')' FROM r),
    (SELECT CASE WHEN unallocated_pct > 5 THEN 'Not final - ' || (CAST(ROUND(unallocated_pct, 1) AS VARCHAR) || '%') || ' of expenses are still unallocated'
                 WHEN program_pct >= 65 THEN 'None needed - above the 65% charity watchdogs commonly look for'
                 ELSE 'Below the 65% charity watchdogs commonly look for - check the class mapping and the shared-cost split before the board sees it' END FROM r)
  UNION ALL
  SELECT 4, 'POSTED WITH NO CLASS',
    (SELECT (CASE WHEN (amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(amt)) AS BIGINT))) || ' across ' || accts || CASE WHEN accts = 1 THEN ' account' ELSE ' accounts' END || ' was posted with no class in QuickBooks' FROM noclass),
    (SELECT (CASE WHEN (split.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(split.amt)) AS BIGINT))) || ' of it is shared cost split by your allocation table; ' || (CASE WHEN (unal.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(unal.amt)) AS BIGINT))) || ' has no rule and sits in the Unallocated column' FROM split, unal)
  FROM noclass WHERE amt > 0
  UNION ALL
  SELECT 5, 'SHARED COSTS',
    (SELECT (CASE WHEN (split.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(split.amt)) AS BIGINT))) || ' in ' || split.accts || CASE WHEN split.accts = 1 THEN ' account' ELSE ' accounts' END || ' split across the three functions - largest: ' || split_names.names FROM split, split_names),
    'Check the percentages in the allocation table against this year''s floor plan and time records - the basis has to be documented'
  FROM split WHERE amt > 0
  UNION ALL
  SELECT 6, 'STILL UNALLOCATED',
    CASE WHEN (SELECT amt FROM unal) = 0 THEN 'Every expense dollar has a class or an allocation rule'
         ELSE (SELECT (CASE WHEN (unal.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(unal.amt)) AS BIGINT))) || ' in ' || unal.accts || CASE WHEN unal.accts = 1 THEN ' account' ELSE ' accounts' END || ' - largest: ' || unal_top.account || ' ' || (CASE WHEN (unal_top.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(unal_top.amt)) AS BIGINT))) FROM unal, unal_top) END,
    CASE WHEN (SELECT amt FROM unal) = 0 THEN 'Clean - nothing left to code'
         ELSE 'Code these in QuickBooks or add a rule to the allocation table - the auditor will ask' END
  UNION ALL
  SELECT 7, 'CLASS MAPPING',
    (SELECT n || ' classes: ' || p || ' program, ' || m || ' management, ' || f || ' fundraising' FROM real_classes),
    'Check the mapping step - classes are matched by words in their names, and you can override any of them'
  FROM real_classes WHERE n > 0
  UNION ALL
  SELECT 8, 'COST TO RAISE A DOLLAR',
    (SELECT '$' || format('{:.2f}', cost_to_raise_a_dollar) || ' of fundraising cost for every $1 of contributions and grants (' || (CASE WHEN (fundraising) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(fundraising)) AS BIGINT))) || ' against ' || (CASE WHEN (contributions_and_grants) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(contributions_and_grants)) AS BIGINT))) || ')' FROM r),
    (SELECT CASE WHEN cost_to_raise_a_dollar <= 0.35 THEN 'None needed - under the 35 cents commonly cited as the ceiling'
                 ELSE 'Above the 35 cents commonly cited as the ceiling - worth a line in the board pack' END FROM r)
  FROM r WHERE cost_to_raise_a_dollar IS NOT NULL
  UNION ALL
  SELECT 9, 'TIE-OUT TO THE P&L',
    (SELECT 'Statement ' || (CASE WHEN (functional_statement) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(functional_statement)) AS BIGINT))) || ' against QuickBooks ' || (CASE WHEN (quickbooks_profit_and_loss) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(quickbooks_profit_and_loss)) AS BIGINT))) || ' - difference $' || format('{:.2f}', tie_out_difference) FROM t),
    (SELECT CASE WHEN ABS(tie_out_difference) < 1 THEN 'Ties - every expense dollar on the P&L is on the statement exactly once'
                 ELSE 'Does not tie - check that every allocation rule adds up to 100%' END FROM t)
  UNION ALL
  SELECT 99, 'VERDICT',
    (SELECT CASE WHEN (SELECT n FROM real_classes) = 0 THEN 'No classes in use yet - the statement cannot be split by function'
                 ELSE 'Programs ' || (CAST(ROUND(r.program_pct, 1) AS VARCHAR) || '%') || ' of expenses - '
                      || CASE WHEN unal.amt > 0 THEN (CASE WHEN (unal.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(unal.amt)) AS BIGINT))) || ' still to code' ELSE 'fully coded' END
                      || CASE WHEN ABS(t.tie_out_difference) < 1 THEN ', ties to the P&L' ELSE ', does NOT tie to the P&L' END END
     FROM r, unal, t),
    'Built from QuickBooks classes plus your allocation table - every number traces back to its account'
)
SELECT ord, area, finding, next_step FROM rows_out ORDER BY ord

Run this on your books

Free to try — no sign-up, no card. The workflow runs in your browser; your data never leaves your machine.

Make it your own →