Grant Spend-Down Tracker - Instant Demo

What data it expects

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

1. 01Demo shelter - last 24 months by grant class (sample)

   SELECT section, account, CAST(COLUMNS(* EXCLUDE (section, account)) AS DOUBLE) FROM (VALUES
     ('Income', 'Grants', 198000, 60000, 120000, 45000, 25000, NULL, 448000),
     ('Income', 'Donations & contributions', NULL, NULL, NULL, NULL, NULL, 2410000, 2410000),
     ('Expenses', 'Salaries & wages', 66640.0, 18955.0, 48535.0, 24565.0, NULL, 1402000, 1560695.0),
     ('Expenses', 'Payroll taxes & benefits', 11760.0, 3345.0, 8565.0, 4335.0, NULL, 251000, 279005.0),
     ('Expenses', 'Animal care & veterinary supplies', 26730.0, 7290.0, 32220.0, 11430.0, 11340.0, 388000, 477010.0),
     ('Expenses', 'Professional fees', 2970.0, 810.0, 3580.0, 1270.0, 1260.0, 71000, 80890.0),
     ('Expenses', 'Occupancy & utilities', 10560.0, 1200.0, 2800.0, 1200.0, NULL, 262000, 277760.0),
     ('Expenses', 'Insurance', 2640.0, 300.0, 700.0, 300.0, NULL, 58000, 61940.0),
     ('Expenses', 'Fundraising events & mailings', NULL, NULL, NULL, NULL, NULL, 187000, 187000),
     ('Expenses', 'Depreciation', NULL, NULL, NULL, NULL, NULL, 124800, 124800)
   ) AS t(section, account, "State Animal Welfare Fund - Shelter Operations", "Lakeshore Community Foundation - Humane Education", "National Pet Trust - Spay and Neuter Initiative", "County Health Department - Rabies Clinic Contract", "Maple Family Foundation - Kennel Upgrade", "Not Specified", "Total")

2. 02Grant budgets from the award letters (sample - replace with yours)

   combines data from multiple inputs · sorts the output · does date arithmetic (aging / periods)

   WITH this_month AS (SELECT CAST(date_trunc('month', current_date) AS DATE) AS d),
   g(grant_name, funder, started_months_ago, term_months) AS (VALUES
     ('State Animal Welfare Fund - Shelter Operations', 'State Animal Welfare Fund', 10, 12),
     ('Lakeshore Community Foundation - Humane Education', 'Lakeshore Community Foundation', 6, 12),
     ('National Pet Trust - Spay and Neuter Initiative', 'National Pet Trust', 8, 12),
     ('County Health Department - Rabies Clinic Contract', 'County Health Department', 11, 12)
   ),
   lines(grant_name, budget_line, budget) AS (VALUES
     ('State Animal Welfare Fund - Shelter Operations', 'Personnel', 110000),
     ('State Animal Welfare Fund - Shelter Operations', 'Program supplies & services', 50000),
     ('State Animal Welfare Fund - Shelter Operations', 'Occupancy & other', 20000),
     ('Lakeshore Community Foundation - Humane Education', 'Personnel', 42000),
     ('Lakeshore Community Foundation - Humane Education', 'Program supplies & services', 15000),
     ('Lakeshore Community Foundation - Humane Education', 'Occupancy & other', 3000),
     ('National Pet Trust - Spay and Neuter Initiative', 'Personnel', 50000),
     ('National Pet Trust - Spay and Neuter Initiative', 'Program supplies & services', 64000),
     ('National Pet Trust - Spay and Neuter Initiative', 'Occupancy & other', 6000),
     ('County Health Department - Rabies Clinic Contract', 'Personnel', 30000),
     ('County Health Department - Rabies Clinic Contract', 'Program supplies & services', 13000),
     ('County Health Department - Rabies Clinic Contract', 'Occupancy & other', 2000)
   )
   SELECT g.grant_name, g.funder,
     CAST(t.d - to_months(CAST(g.started_months_ago AS INTEGER)) AS DATE) AS start_date,
     CAST(t.d - to_months(CAST(g.started_months_ago AS INTEGER)) + to_months(CAST(g.term_months AS INTEGER)) - INTERVAL 1 DAY AS DATE) AS end_date,
     l.budget_line, CAST(l.budget AS DOUBLE) AS budget
   FROM g JOIN lines l ON l.grant_name = g.grant_name, this_month t
   ORDER BY g.grant_name, l.budget_line

3. 03Expenses by account and grant

   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 grant_name VALUE amount
   )
   SELECT section, account,
     CASE WHEN lower(grant_name) = 'not specified' THEN 'No grant tagged (general funds)' ELSE grant_name END AS grant_name,
     ROUND(amount, 2) AS amount
   FROM u
   WHERE amount IS NOT NULL AND amount <> 0
     AND (section ILIKE '%expense%' OR section ILIKE '%cost of goods%')
     AND account NOT ILIKE 'total %'
     AND NOT regexp_matches(lower(grant_name), '^total')
   ORDER BY grant_name, account

4. 04Map each account to a budget line (check this step)

   aggregates rows into summary totals · buckets values by condition · sorts the output

   SELECT account, ROUND(SUM(amount), 2) AS spending_in_the_period,
     CASE WHEN regexp_matches(lower(account), 'salar|wage|payroll|benefit|401|pension|fica|workers comp') THEN 'Personnel'
          WHEN regexp_matches(lower(account), 'rent|occupancy|utilit|insurance|telephone|internet|office|depreciation|interest|bank') THEN 'Occupancy & other'
          ELSE 'Program supplies & services' END AS budget_line,
     'Matched by words in the account name - check it against the lines in your award budgets' AS mapping_note
   FROM input_1
   GROUP BY account
   ORDER BY spending_in_the_period DESC

5. 05Spending by grant and budget line

   combines data from multiple inputs · aggregates rows into summary totals · sorts the output

   SELECT l.grant_name, m.budget_line, ROUND(SUM(l.amount), 2) AS spent
   FROM input_1 l JOIN input_2 m ON m.account = l.account
   GROUP BY l.grant_name, m.budget_line
   ORDER BY l.grant_name, m.budget_line

6. 06BUDGET VS ACTUAL - every grant, line by line

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

   WITH b AS (SELECT * FROM input_1),
   a AS (SELECT * FROM input_2 WHERE grant_name <> 'No grant tagged (general funds)'),
   bg AS (SELECT DISTINCT grant_name FROM b),
   names AS (
     SELECT q.grant_name AS quickbooks_name, MIN(bg.grant_name) AS budget_name
     FROM (SELECT DISTINCT grant_name FROM a) q LEFT JOIN bg
       ON lower(trim(q.grant_name)) = lower(trim(bg.grant_name))
       OR ends_with(lower(trim(q.grant_name)), ':' || lower(trim(bg.grant_name)))
     GROUP BY q.grant_name
   ),
   spent AS (
     SELECT COALESCE(n.budget_name, a.grant_name) AS grant_name, a.budget_line, SUM(a.spent) AS spent
     FROM a JOIN names n ON n.quickbooks_name = a.grant_name
     GROUP BY COALESCE(n.budget_name, a.grant_name), a.budget_line
   ),
   j AS (
     SELECT COALESCE(b.grant_name, s.grant_name) AS grant_name, COALESCE(b.budget_line, s.budget_line) AS budget_line,
       b.budget, COALESCE(s.spent, 0) AS spent
     FROM b FULL OUTER JOIN spent s ON s.grant_name = b.grant_name AND s.budget_line = b.budget_line
   )
   SELECT j.grant_name, j.budget_line,
     ROUND(j.budget, 2) AS budget, ROUND(j.spent, 2) AS spent,
     ROUND(j.budget - j.spent, 2) AS remaining,
     ROUND(100.0 * j.spent / NULLIF(j.budget, 0), 1) AS pct_spent,
     j.grant_name IN (SELECT grant_name FROM bg) AS budget_on_file,
     j.grant_name IN (SELECT budget_name FROM names WHERE budget_name IS NOT NULL) AS found_in_quickbooks,
     CASE WHEN j.grant_name NOT IN (SELECT grant_name FROM bg) THEN 'No budget on file'
          WHEN j.budget IS NULL THEN 'Spending on a line the budget does not have'
          WHEN j.spent > j.budget * 1.10 THEN 'Over by more than 10%'
          WHEN j.spent > j.budget THEN 'Over budget'
          ELSE 'Within budget' END AS line_status
   FROM j
   ORDER BY j.grant_name, j.budget_line

7. 07Spending pace - where each grant lands if nothing changes

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

   WITH cutoff AS (SELECT CAST(date_trunc('month', current_date) - INTERVAL 1 DAY AS DATE) AS as_of),
   g AS (SELECT grant_name, ANY_VALUE(funder) AS funder, MIN(start_date) AS start_date, MAX(end_date) AS end_date, SUM(budget) AS award FROM input_2 GROUP BY grant_name),
   s AS (SELECT grant_name, SUM(spent) AS spent, BOOL_OR(budget_on_file) AS budget_on_file, BOOL_OR(found_in_quickbooks) AS found_in_quickbooks FROM input_1 GROUP BY grant_name),
   j AS (
     SELECT s.grant_name, g.funder, g.start_date, g.end_date, g.award, s.spent, s.budget_on_file, s.found_in_quickbooks, cutoff.as_of,
       CAST(g.end_date - cutoff.as_of AS INTEGER) AS days_left,
       LEAST(100.0, GREATEST(0.0, 100.0 * CAST(cutoff.as_of - g.start_date + 1 AS DOUBLE) / NULLIF(CAST(g.end_date - g.start_date + 1 AS DOUBLE), 0))) AS pct_of_period_elapsed
     FROM s LEFT JOIN g ON g.grant_name = s.grant_name, cutoff
   )
   SELECT grant_name, funder, start_date, end_date, as_of,
     ROUND(award, 2) AS award, ROUND(spent, 2) AS spent, ROUND(award - spent, 2) AS remaining,
     ROUND(pct_of_period_elapsed, 1) AS pct_of_period_elapsed,
     ROUND(100.0 * spent / NULLIF(award, 0), 1) AS pct_spent,
     days_left,
     ROUND(GREATEST(0, award - spent * 100.0 / NULLIF(pct_of_period_elapsed, 0)), 0) AS unspent_at_end_if_pace_holds,
     CASE WHEN NOT budget_on_file THEN 'No budget on file'
          WHEN NOT found_in_quickbooks THEN 'Not found in QuickBooks'
          WHEN days_left < 0 THEN 'Ended'
          WHEN days_left <= 90 AND award - spent > 0.05 * award THEN 'Ends within 90 days with money left'
          WHEN 100.0 * spent / NULLIF(award, 0) - pct_of_period_elapsed > 10 THEN 'Spending ahead of the calendar'
          WHEN pct_of_period_elapsed - 100.0 * spent / NULLIF(award, 0) > 10 THEN 'Spending behind the calendar'
          ELSE 'On pace' END AS status
   FROM j
   ORDER BY CASE WHEN budget_on_file THEN 0 ELSE 1 END, days_left

8. 08SUMMARY - what the funder reports will show

   buckets values by condition · computes running / windowed totals · appends result sets (e.g. a TOTAL row)

   WITH p AS (SELECT * FROM input_1),
   b AS (SELECT * FROM input_2),
   l AS (SELECT * FROM input_3),
   tracked AS (SELECT COUNT(*) AS n, COALESCE(SUM(award), 0) AS award, COALESCE(SUM(spent), 0) AS spent, MAX(as_of) AS as_of FROM p WHERE status NOT IN ('No budget on file', 'Not found in QuickBooks')),
   atrisk AS (SELECT COUNT(*) AS n, COALESCE(SUM(remaining), 0) AS amt FROM p WHERE status = 'Ends within 90 days with money left'),
   atrisk_top AS (SELECT grant_name, remaining, days_left FROM p WHERE status = 'Ends within 90 days with money left' ORDER BY remaining DESC LIMIT 1),
   offpace AS (SELECT COUNT(*) AS n FROM p WHERE status IN ('Spending ahead of the calendar', 'Spending behind the calendar')),
   offpace_top AS (SELECT grant_name, status, pct_of_period_elapsed, pct_spent FROM p WHERE status IN ('Spending ahead of the calendar', 'Spending behind the calendar') ORDER BY ABS(pct_spent - pct_of_period_elapsed) DESC LIMIT 1),
   over AS (SELECT COUNT(*) AS n FROM b WHERE line_status = 'Over by more than 10%'),
   over_top AS (SELECT grant_name, budget_line, spent - budget AS over_amt, 100.0 * (spent - budget) / budget AS over_pct FROM b WHERE line_status = 'Over by more than 10%' ORDER BY spent - budget DESC LIMIT 1),
   nobud AS (SELECT COUNT(*) AS n, COALESCE(SUM(spent), 0) AS amt FROM p WHERE status = 'No budget on file'),
   nobud_names AS (SELECT string_agg(grant_name, '; ') AS names FROM (SELECT grant_name FROM p WHERE status = 'No budget on file' ORDER BY spent DESC LIMIT 3)),
   notfound AS (SELECT COUNT(*) AS n FROM p WHERE status = 'Not found in QuickBooks'),
   untagged AS (SELECT COALESCE(SUM(amount), 0) AS amt FROM l WHERE grant_name = 'No grant tagged (general funds)'),
   tagged AS (SELECT COUNT(DISTINCT grant_name) AS n FROM l WHERE grant_name <> 'No grant tagged (general funds)'),
   rows_out AS (
     SELECT 1 AS ord, 'GRANTS TRACKED' AS area,
       (SELECT n || ' grants with a budget on file: ' || (CASE WHEN (award) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(award)) AS BIGINT))) || ' awarded, ' || (CASE WHEN (spent) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(spent)) AS BIGINT))) || ' spent through ' || strftime(as_of, '%b %d, %Y') FROM tracked) AS finding,
       'None needed - budgets come from the table in the second step; spending comes from QuickBooks, by the class each cost is coded to' AS next_step
     FROM tracked WHERE n > 0
     UNION ALL
     SELECT 2, 'NO GRANT TAGS FOUND',
       'No expense in this period is coded to a class in QuickBooks',
       'This template expects one QuickBooks class per grant. If you tag grants to customers or projects instead, export the P&L by customer from QuickBooks, drop it in, and tell the chat to use it'
     FROM tagged WHERE n = 0
     UNION ALL
     SELECT 3, 'MONEY THAT COULD GO BACK',
       CASE WHEN (SELECT n FROM atrisk) = 0 THEN 'No grant ends within 90 days with more than 5% unspent'
            ELSE (SELECT atrisk.n || CASE WHEN atrisk.n = 1 THEN ' grant ends' ELSE ' grants end' END || ' within 90 days with ' || (CASE WHEN (atrisk.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(atrisk.amt)) AS BIGINT))) || ' unspent - largest: ' || atrisk_top.grant_name || ', ' || (CASE WHEN (atrisk_top.remaining) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(atrisk_top.remaining)) AS BIGINT))) || ' left with ' || atrisk_top.days_left || ' days to go' FROM atrisk, atrisk_top) END,
       CASE WHEN (SELECT n FROM atrisk) = 0 THEN 'None needed - nothing is at risk of going back this quarter'
            ELSE 'Plan the spending or ask the funder for a no-cost extension now - not in the last month' END
     FROM tracked WHERE n > 0
     UNION ALL
     SELECT 4, 'SPENDING PACE',
       CASE WHEN (SELECT n FROM offpace) = 0 THEN 'Every grant is within 10 points of where the calendar says it should be'
            ELSE (SELECT offpace.n || CASE WHEN offpace.n = 1 THEN ' grant is' ELSE ' grants are' END || ' more than 10 points off the calendar - furthest: ' || offpace_top.grant_name || ', ' || (CAST(ROUND(offpace_top.pct_of_period_elapsed, 1) AS VARCHAR) || '%') || ' through its period and ' || (CAST(ROUND(offpace_top.pct_spent, 1) AS VARCHAR) || '%') || ' spent' FROM offpace, offpace_top) END,
       CASE WHEN (SELECT n FROM offpace) = 0 THEN 'None needed - spending tracks the grant periods'
            ELSE 'Ahead of the calendar runs out early; behind it hands money back - the pace step shows where each grant lands if nothing changes' END
     FROM tracked WHERE n > 0
     UNION ALL
     SELECT 5, 'BUDGET LINES OVER',
       CASE WHEN (SELECT n FROM over) = 0 THEN 'No budget line is more than 10% over'
            ELSE (SELECT over.n || CASE WHEN over.n = 1 THEN ' budget line is' ELSE ' budget lines are' END || ' more than 10% over - largest: ' || over_top.budget_line || ' on ' || over_top.grant_name || ', ' || (CASE WHEN (over_top.over_amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(over_top.over_amt)) AS BIGINT))) || ' over (' || (CAST(ROUND(over_top.over_pct, 1) AS VARCHAR) || '%') || ')' FROM over, over_top) END,
       CASE WHEN (SELECT n FROM over) = 0 THEN 'None needed - every line is inside its budget or within 10%'
            ELSE 'Many funders want approval before more than 10% moves between lines - check the award terms before the report goes in' END
     FROM tracked WHERE n > 0
     UNION ALL
     SELECT 6, 'SPENDING WITH NO BUDGET ON FILE',
       (SELECT nobud.n || CASE WHEN nobud.n = 1 THEN ' class in QuickBooks carries ' ELSE ' classes in QuickBooks carry ' END || (CASE WHEN (nobud.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(nobud.amt)) AS BIGINT))) || ' of spending with no budget in the table: ' || COALESCE(nobud_names.names, '') || CASE WHEN nobud.n > 3 THEN '; ...' ELSE '' END FROM nobud, nobud_names),
       'Add award budgets for the ones that are grants - the name has to match the QuickBooks class. Classes that are programs, not grants, can be ignored'
     FROM nobud WHERE n > 0
     UNION ALL
     SELECT 7, 'BUDGETS NOT FOUND IN QUICKBOOKS',
       (SELECT n || CASE WHEN n = 1 THEN ' budget in the table matches' ELSE ' budgets in the table match' END || ' no class in QuickBooks' FROM notfound),
       'These are usually the sample budgets - replace them with your own award letters, or fix the grant names so they match QuickBooks'
     FROM notfound WHERE n > 0
     UNION ALL
     SELECT 8, 'NOT TAGGED TO ANY GRANT',
       (SELECT (CASE WHEN (amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(amt)) AS BIGINT))) || ' of expenses carry no class' FROM untagged),
       'Activity only - normal for general funds; scan it for grant costs that were never coded to their grant'
     FROM untagged WHERE amt > 0
     UNION ALL
     SELECT 99, 'VERDICT',
       (SELECT CASE WHEN tagged.n = 0 THEN 'No classes in QuickBooks yet - nothing to track'
                    WHEN tracked.n = 0 THEN tagged.n || ' classes in QuickBooks - none has a budget in the table yet'
                    ELSE tracked.n || ' grants tracked - ' || (CASE WHEN (atrisk.amt) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(atrisk.amt)) AS BIGINT))) || ' could go back within 90 days, ' || over.n || CASE WHEN over.n = 1 THEN ' budget line' ELSE ' budget lines' END || ' more than 10% over' END
        FROM tagged, tracked, atrisk, over),
       'Spending comes from QuickBooks; budgets come from your award letters - re-run it each month before the board and funder reports'
   )
   SELECT ord, area, finding, next_step FROM rows_out ORDER BY ord