GPTBeyond

Month-End Close Package on Campfire - Instant Demo

The close package a controller builds in Excel on top of the ledger: balance sheet and P&L from two trial balances, 12-month trend, flux against thresholds, suspense and intercompany exceptions - and four tie-outs that must hold before it goes out.

12 steps · shared by Klajdi · September 22, 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

CAMPFIRE_TrialBalance_Current.csv
account_number · VARCHARaccount · VARCHARtype · VARCHARdebit · DOUBLEcredit · DOUBLE
CAMPFIRE_TrialBalance_Prior.csv
account · VARCHARdebit · DOUBLEcredit · DOUBLE
CAMPFIRE_IncomeStatement_Month.csv
section · VARCHARaccount · VARCHAR<one column per month, YYYY-MM> · 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

  1. 01Trial balance - current month-end (sample: Halyard Software, consolidated)
    SELECT account_number, account, type, CAST(COLUMNS(* EXCLUDE (account_number, account, type)) AS DOUBLE) FROM (VALUES
  ('1000', 'Cash - operating', 'Balance Sheet', 3290882.56, NULL),
  ('1010', 'Cash - UK (GBP)', 'Balance Sheet', 296410.00, NULL),
  ('1100', 'Accounts receivable', 'Balance Sheet', 688900.00, NULL),
  ('1150', 'Intercompany receivable - UK', 'Balance Sheet', 203500.00, NULL),
  ('1200', 'Prepaid expenses', 'Balance Sheet', 81200.00, NULL),
  ('1500', 'Fixed assets', 'Balance Sheet', 742000.00, NULL),
  ('1590', 'Accumulated depreciation', 'Balance Sheet', NULL, 334000.00),
  ('1900', 'Suspense', 'Balance Sheet', 9840.00, NULL),
  ('2000', 'Accounts payable', 'Balance Sheet', NULL, 287900.00),
  ('2100', 'Accrued liabilities', 'Balance Sheet', NULL, 98400.00),
  ('2150', 'Intercompany payable - US', 'Balance Sheet', NULL, 196250.00),
  ('2400', 'Deferred revenue', 'Balance Sheet', NULL, 1163200.00),
  ('2700', 'Term loan', 'Balance Sheet', NULL, 750000.00),
  ('', 'Net Income (Prior Periods)', 'Balance Sheet', NULL, 40247.88),
  ('3000', 'Common stock & APIC', 'Balance Sheet', NULL, 2500000.00),
  ('3900', 'Retained earnings', 'Balance Sheet', NULL, 54663.01),
  ('4000', 'Subscription revenue', 'Income Statement', NULL, 496714.97),
  ('4100', 'Professional services', 'Income Statement', NULL, 52133.48),
  ('4200', 'Usage overages', 'Income Statement', NULL, 24755.41),
  ('7000', 'Interest income', 'Income Statement', NULL, 3049.19),
  ('7200', 'FX gain / loss', 'Income Statement', 12850.33, NULL),
  ('5000', 'Hosting & infrastructure', 'Income Statement', 77342.52, NULL),
  ('5100', 'Customer support', 'Income Statement', 41179.64, NULL),
  ('5200', 'Third-party licenses', 'Income Statement', 9614.85, NULL),
  ('6000', 'Salaries & wages', 'Income Statement', 229129.19, NULL),
  ('6010', 'Payroll taxes', 'Income Statement', 19042.73, NULL),
  ('6020', 'Benefits', 'Income Statement', 27465.78, NULL),
  ('6300', 'Rent & facilities', 'Income Statement', 28500.43, NULL),
  ('6400', 'Software & tools', 'Income Statement', 26728.77, NULL),
  ('6500', 'Marketing programs', 'Income Statement', 111195.31, NULL),
  ('6600', 'Professional fees', 'Income Statement', 53442.27, NULL),
  ('6700', 'Travel & entertainment', 'Income Statement', 12302.59, NULL),
  ('6800', 'Depreciation & amortization', 'Income Statement', 15061.95, NULL),
  ('6990', 'Uncategorized expense', 'Income Statement', 18420.00, NULL),
  ('7100', 'Interest expense', 'Income Statement', 6305.02, NULL)
) AS t("account_number", "account", "type", "debit", "credit")
  2. 02Trial balance - prior month-end (sample)
    SELECT account_number, account, type, CAST(COLUMNS(* EXCLUDE (account_number, account, type)) AS DOUBLE) FROM (VALUES
  ('1000', 'Cash - operating', 'Balance Sheet', 3455700.89, NULL),
  ('1010', 'Cash - UK (GBP)', 'Balance Sheet', 296410.00, NULL),
  ('1100', 'Accounts receivable', 'Balance Sheet', 612400.00, NULL),
  ('1150', 'Intercompany receivable - UK', 'Balance Sheet', 184000.00, NULL),
  ('1200', 'Prepaid expenses', 'Balance Sheet', 88700.00, NULL),
  ('1500', 'Fixed assets', 'Balance Sheet', 742000.00, NULL),
  ('1590', 'Accumulated depreciation', 'Balance Sheet', NULL, 318500.00),
  ('2000', 'Accounts payable', 'Balance Sheet', NULL, 241300.00),
  ('2100', 'Accrued liabilities', 'Balance Sheet', NULL, 132800.00),
  ('2150', 'Intercompany payable - US', 'Balance Sheet', NULL, 184000.00),
  ('2200', 'Payroll liabilities', 'Balance Sheet', NULL, 61200.00),
  ('2400', 'Deferred revenue', 'Balance Sheet', NULL, 1096500.00),
  ('2700', 'Term loan', 'Balance Sheet', NULL, 750000.00),
  ('', 'Net Income (Prior Periods)', 'Balance Sheet', NULL, 14550.77),
  ('3000', 'Common stock & APIC', 'Balance Sheet', NULL, 2500000.00),
  ('3900', 'Retained earnings', 'Balance Sheet', NULL, 54663.01),
  ('4000', 'Subscription revenue', 'Income Statement', NULL, 487290.50),
  ('4100', 'Professional services', 'Income Statement', NULL, 44129.15),
  ('4200', 'Usage overages', 'Income Statement', NULL, 21029.24),
  ('7000', 'Interest income', 'Income Statement', NULL, 3048.01),
  ('7200', 'FX gain / loss', 'Income Statement', NULL, 1798.39),
  ('5000', 'Hosting & infrastructure', 'Income Statement', 74512.48, NULL),
  ('5100', 'Customer support', 'Income Statement', 41650.84, NULL),
  ('5200', 'Third-party licenses', 'Income Statement', 9249.58, NULL),
  ('6000', 'Salaries & wages', 'Income Statement', 219532.65, NULL),
  ('6010', 'Payroll taxes', 'Income Statement', 18885.54, NULL),
  ('6020', 'Benefits', 'Income Statement', 26778.78, NULL),
  ('6300', 'Rent & facilities', 'Income Statement', 28629.18, NULL),
  ('6400', 'Software & tools', 'Income Statement', 25535.62, NULL),
  ('6500', 'Marketing programs', 'Income Statement', 36753.70, NULL),
  ('6600', 'Professional fees', 'Income Statement', 14023.74, NULL),
  ('6700', 'Travel & entertainment', 'Income Statement', 14327.96, NULL),
  ('6800', 'Depreciation & amortization', 'Income Statement', 15365.67, NULL),
  ('7100', 'Interest expense', 'Income Statement', 6352.44, NULL)
) AS t("account_number", "account", "type", "debit", "credit")
  3. 03Income statement - last 12 months (sample)
    SELECT section, account, CAST(COLUMNS(* EXCLUDE (section, account)) AS DOUBLE) FROM (VALUES
  ('Revenue', '4000 Subscription revenue', 405666.29, 410241.93, 431916.36, 425847.74, 447040.37, 451432.50, 451811.51, 473351.09, 468779.93, 489224.49, 487290.50, 496714.97),
  ('Revenue', '4100 Professional services', 47782.62, 53835.47, 42224.89, 61363.81, 38693.61, 49289.40, 57866.47, 45317.32, 51840.20, 65371.82, 44129.15, 52133.48),
  ('Revenue', '4200 Usage overages', 9395.09, 10552.33, 11862.11, 13450.37, 14124.15, 13264.63, 15730.02, 16671.38, 18051.56, 18696.33, 21029.24, 24755.41),
  ('Cost of Revenue', '5000 Hosting & infrastructure', 62671.09, 62965.26, 63760.97, 66057.39, 66771.91, 67380.65, 70666.50, 71523.90, 70815.73, 73486.69, 74512.48, 77342.52),
  ('Cost of Revenue', '5100 Customer support', 41564.44, 40478.33, 42181.23, 40060.44, 40798.58, 41632.57, 40143.88, 40972.85, 39866.45, 41413.81, 41650.84, 41179.64),
  ('Cost of Revenue', '5200 Third-party licenses', 9714.02, 9393.84, 9611.32, 9553.79, 9545.54, 9475.04, 9693.78, 9753.47, 9485.24, 9593.57, 9249.58, 9614.85),
  ('Operating Expenses', '6000 Salaries & wages', 206809.68, 211065.08, 208959.67, 206397.54, 207667.14, 211215.92, 215057.73, 220891.16, 216990.35, 220319.31, 219532.65, 229129.19),
  ('Operating Expenses', '6010 Payroll taxes', 17013.03, 17136.51, 17286.15, 18143.52, 17301.37, 17693.89, 18847.74, 19224.27, 19151.99, 19558.00, 18885.54, 19042.73),
  ('Operating Expenses', '6020 Benefits', 23796.63, 24553.24, 24659.13, 23497.33, 27063.82, 27156.12, 27158.40, 27575.10, 27747.59, 27207.11, 26778.78, 27465.78),
  ('Operating Expenses', '6300 Rent & facilities', 27780.35, 28111.45, 28761.20, 28320.03, 28026.03, 28197.56, 28296.02, 27250.71, 28671.22, 28470.35, 28629.18, 28500.43),
  ('Operating Expenses', '6400 Software & tools', 19374.08, 20350.90, 20939.75, 21622.83, 21836.00, 22792.56, 22991.10, 23881.12, 25106.72, 24669.47, 25535.62, 26728.77),
  ('Operating Expenses', '6500 Marketing programs', 35139.16, 44631.75, 61206.39, 32207.49, 40777.19, 70481.74, 44331.10, 40129.16, 53559.89, 48379.84, 36753.70, 111195.31),
  ('Operating Expenses', '6600 Professional fees', 13971.43, 13986.42, 43686.86, 13665.84, 13867.81, 13802.40, 15703.86, 13715.61, 13599.40, 14378.83, 14023.74, 53442.27),
  ('Operating Expenses', '6700 Travel & entertainment', 13234.19, 9619.06, 6611.13, 5657.91, 12363.77, 14468.34, 15179.27, 13094.43, 10780.25, 16769.22, 14327.96, 12302.59),
  ('Operating Expenses', '6800 Depreciation & amortization', 15341.59, 15242.43, 15789.71, 15950.98, 15827.94, 15784.65, 15796.05, 15723.08, 15245.87, 15516.40, 15365.67, 15061.95),
  ('Operating Expenses', '6990 Uncategorized expense', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 18420.00),
  ('Other Income & Expense', '7000 Interest income', 3012.20, 3058.97, 3055.21, 3135.81, 3184.91, 3090.18, 3181.29, 3190.78, 3184.63, 3074.82, 3048.01, 3049.19),
  ('Other Income & Expense', '7100 Interest expense', 6087.17, 6090.03, 6246.15, 6348.91, 6326.64, 6192.36, 6256.91, 6311.47, 6045.54, 6259.74, 6352.44, 6305.02),
  ('Other Income & Expense', '7200 FX gain / loss', 2001.12, -175.74, -2571.83, 2313.08, -1339.86, 2406.59, 3773.26, -833.29, -788.91, 3574.38, 1798.39, -12850.33)
) AS t("section", "account", "2025-09", "2025-10", "2025-11", "2025-12", "2026-01", "2026-02", "2026-03", "2026-04", "2026-05", "2026-06", "2026-07", "2026-08")
  4. 04Classify the current trial balance (check this step)
    SELECT
  COALESCE(account_number, '') AS account_number, account, type,
  COALESCE(debit, 0) AS debit, COALESCE(credit, 0) AS credit,
  COALESCE(debit, 0) - COALESCE(credit, 0) AS net_debit,
  CASE
    WHEN type = 'Income Statement' THEN 'Income statement'
    WHEN regexp_matches(lower(account), 'net income \(prior|current year earnings|retained earnings|common stock|apic|paid-in|equity|owner|capital|members') THEN 'Equity'
    WHEN regexp_matches(lower(account), 'accumulated depreciation|accum\.? dep|allowance for') THEN 'Asset'
    WHEN regexp_matches(lower(account), 'payable|accrued|deferred|unearned|loan|note payable|line of credit|liabilit|withh|credit card|tax payable|lease liability') THEN 'Liability'
    WHEN COALESCE(debit, 0) >= COALESCE(credit, 0) THEN 'Asset'
    ELSE 'Liability'
  END AS bucket,
  CASE
    WHEN regexp_matches(lower(account), 'cash|checking|saving|money market|bank') AND NOT regexp_matches(lower(account), 'restricted|escrow') THEN 'Cash'
    WHEN regexp_matches(lower(account), 'receivable|pledge') AND NOT regexp_matches(lower(account), 'intercompany|interco|due from') THEN 'Receivables'
    WHEN regexp_matches(lower(account), 'intercompany|interco|due from|due to') THEN 'Intercompany'
    WHEN regexp_matches(lower(account), 'suspense|uncategori|unclassified|clearing|ask my accountant|undeposited|to be reclass') THEN 'Suspense'
    WHEN regexp_matches(lower(account), 'accounts payable|trade payable|\ba/p\b') THEN 'Payables'
    WHEN regexp_matches(lower(account), 'accrued|payroll liab') THEN 'Accruals'
    WHEN regexp_matches(lower(account), 'deferred revenue|unearned') THEN 'Deferred revenue'
    ELSE 'Other'
  END AS tag
FROM input_1
WHERE account IS NOT NULL AND trim(account) <> ''
  5. 05Classify the prior trial balance
    SELECT
  COALESCE(account_number, '') AS account_number, account, type,
  COALESCE(debit, 0) AS debit, COALESCE(credit, 0) AS credit,
  COALESCE(debit, 0) - COALESCE(credit, 0) AS net_debit,
  CASE
    WHEN type = 'Income Statement' THEN 'Income statement'
    WHEN regexp_matches(lower(account), 'net income \(prior|current year earnings|retained earnings|common stock|apic|paid-in|equity|owner|capital|members') THEN 'Equity'
    WHEN regexp_matches(lower(account), 'accumulated depreciation|accum\.? dep|allowance for') THEN 'Asset'
    WHEN regexp_matches(lower(account), 'payable|accrued|deferred|unearned|loan|note payable|line of credit|liabilit|withh|credit card|tax payable|lease liability') THEN 'Liability'
    WHEN COALESCE(debit, 0) >= COALESCE(credit, 0) THEN 'Asset'
    ELSE 'Liability'
  END AS bucket,
  CASE
    WHEN regexp_matches(lower(account), 'cash|checking|saving|money market|bank') AND NOT regexp_matches(lower(account), 'restricted|escrow') THEN 'Cash'
    WHEN regexp_matches(lower(account), 'receivable|pledge') AND NOT regexp_matches(lower(account), 'intercompany|interco|due from') THEN 'Receivables'
    WHEN regexp_matches(lower(account), 'intercompany|interco|due from|due to') THEN 'Intercompany'
    WHEN regexp_matches(lower(account), 'suspense|uncategori|unclassified|clearing|ask my accountant|undeposited|to be reclass') THEN 'Suspense'
    WHEN regexp_matches(lower(account), 'accounts payable|trade payable|\ba/p\b') THEN 'Payables'
    WHEN regexp_matches(lower(account), 'accrued|payroll liab') THEN 'Accruals'
    WHEN regexp_matches(lower(account), 'deferred revenue|unearned') THEN 'Deferred revenue'
    ELSE 'Other'
  END AS tag
FROM input_1
WHERE account IS NOT NULL AND trim(account) <> ''
  6. 06BALANCE SHEET - current vs prior month-end
    WITH cur AS (SELECT * FROM input_1 WHERE bucket <> 'Income statement'),
pri AS (SELECT * FROM input_2 WHERE bucket <> 'Income statement'),
ni_cur AS (SELECT COALESCE(SUM(credit - debit), 0) AS v FROM input_1 WHERE bucket = 'Income statement'),
ni_pri AS (SELECT COALESCE(SUM(credit - debit), 0) AS v FROM input_2 WHERE bucket = 'Income statement'),
j AS (
  SELECT COALESCE(c.bucket, p.bucket) AS bucket, COALESCE(c.tag, p.tag) AS tag, COALESCE(c.account_number, p.account_number) AS account_number, COALESCE(c.account, p.account) AS account,
    CASE WHEN COALESCE(c.bucket, p.bucket) = 'Asset' THEN COALESCE(c.net_debit, 0) ELSE -COALESCE(c.net_debit, 0) END AS current_balance,
    CASE WHEN COALESCE(c.bucket, p.bucket) = 'Asset' THEN COALESCE(p.net_debit, 0) ELSE -COALESCE(p.net_debit, 0) END AS prior_balance
  FROM cur c FULL OUTER JOIN pri p ON p.account = c.account AND COALESCE(p.account_number, '') = COALESCE(c.account_number, '')
),
lines AS (
  SELECT CASE bucket WHEN 'Asset' THEN 1 WHEN 'Liability' THEN 3 ELSE 5 END AS ord, bucket AS section, account_number, account, tag, current_balance, prior_balance FROM j
  UNION ALL SELECT 2, 'TOTAL ASSETS', '', 'TOTAL ASSETS', '', SUM(current_balance), SUM(prior_balance) FROM j WHERE bucket = 'Asset'
  UNION ALL SELECT 4, 'TOTAL LIABILITIES', '', 'TOTAL LIABILITIES', '', SUM(current_balance), SUM(prior_balance) FROM j WHERE bucket = 'Liability'
  UNION ALL SELECT 6, 'Equity', '', 'Net income - current period (from the income-statement accounts)', 'Current earnings', (SELECT v FROM ni_cur), (SELECT v FROM ni_pri)
  UNION ALL SELECT 7, 'TOTAL EQUITY', '', 'TOTAL EQUITY', '', (SELECT SUM(current_balance) FROM j WHERE bucket = 'Equity') + (SELECT v FROM ni_cur), (SELECT SUM(prior_balance) FROM j WHERE bucket = 'Equity') + (SELECT v FROM ni_pri)
)
SELECT section, account_number, account, tag,
  ROUND(current_balance, 2) AS current_balance, ROUND(prior_balance, 2) AS prior_balance,
  ROUND(current_balance - prior_balance, 2) AS change,
  ROUND(100.0 * (current_balance - prior_balance) / NULLIF(ABS(prior_balance), 0), 1) AS change_pct
FROM lines
ORDER BY ord, account_number, account
  7. 07INCOME STATEMENT - this month vs last, from the trial balances
    WITH cur AS (SELECT * FROM input_1 WHERE bucket = 'Income statement'),
pri AS (SELECT * FROM input_2 WHERE bucket = 'Income statement'),
j AS (
  SELECT COALESCE(c.account_number, p.account_number) AS account_number, COALESCE(c.account, p.account) AS account,
    COALESCE(c.credit, 0) - COALESCE(c.debit, 0) AS this_month,
    COALESCE(p.credit, 0) - COALESCE(p.debit, 0) AS prior_month
  FROM cur c FULL OUTER JOIN pri p ON p.account = c.account AND COALESCE(p.account_number, '') = COALESCE(c.account_number, '')
),
typed AS (
  SELECT *, CASE WHEN this_month >= 0 AND prior_month >= 0 THEN 'Revenue / other income' WHEN this_month <= 0 AND prior_month <= 0 THEN 'Expense' ELSE 'Mixed sign' END AS line_kind FROM j
)
SELECT line_kind, account_number, account,
  ROUND(this_month, 2) AS this_month_net_credit, ROUND(prior_month, 2) AS prior_month_net_credit,
  ROUND(this_month - prior_month, 2) AS change,
  ROUND(100.0 * (this_month - prior_month) / NULLIF(ABS(prior_month), 0), 1) AS change_pct,
  'Credit-positive: revenue shows positive, expenses negative - net income = SUM of this column' AS note
FROM typed
ORDER BY CASE line_kind WHEN 'Revenue / other income' THEN 0 WHEN 'Expense' THEN 1 ELSE 2 END, account_number, account
  8. 0812-month trend - sign convention detected from the data
    WITH long AS (
  UNPIVOT (SELECT * FROM input_1) ON COLUMNS(* EXCLUDE (section, account)) INTO NAME period VALUE amount
),
m AS (SELECT DISTINCT period FROM long WHERE regexp_matches(period, '^[0-9]{4}-[0-9]{2}') ORDER BY period),
kind AS (
  SELECT section, account, period, amount,
    CASE WHEN regexp_matches(lower(section), 'revenue|sales|income') AND NOT regexp_matches(lower(section), 'cost|expense') THEN 'revenue'
         WHEN regexp_matches(lower(section), 'other income|other') AND regexp_matches(lower(account), 'gain|interest income|other income') THEN 'revenue'
         ELSE 'expense' END AS k
  FROM long WHERE amount IS NOT NULL AND regexp_matches(period, '^[0-9]{4}-[0-9]{2}')
),
signs AS (
  SELECT SIGN(COALESCE(SUM(amount) FILTER (WHERE k = 'revenue'), 1)) AS rev_sign,
         SIGN(COALESCE(SUM(amount) FILTER (WHERE k = 'expense'), 1)) AS exp_sign
  FROM kind
),
norm AS (
  SELECT kind.*, CASE WHEN k = 'revenue' THEN amount * signs.rev_sign ELSE amount * signs.exp_sign END AS positive_amount FROM kind, signs
),
per AS (
  SELECT period,
    SUM(positive_amount) FILTER (WHERE k = 'revenue') AS revenue,
    SUM(positive_amount) FILTER (WHERE k = 'expense') AS expenses
  FROM norm GROUP BY period
)
SELECT p.period, ROUND(p.revenue, 2) AS revenue, ROUND(p.expenses, 2) AS expenses, ROUND(p.revenue - p.expenses, 2) AS net_income,
  ROUND(100.0 * (p.revenue - p.expenses) / NULLIF(p.revenue, 0), 1) AS margin_pct,
  ROUND(100.0 * (p.revenue - LAG(p.revenue) OVER (ORDER BY p.period)) / NULLIF(LAG(p.revenue) OVER (ORDER BY p.period), 0), 1) AS revenue_growth_pct,
  CASE WHEN s.exp_sign > 0 THEN 'Expenses arrive positive in this file' ELSE 'Expenses arrive negative in this file' END AS sign_convention_detected
FROM per p, signs s
ORDER BY p.period
  9. 09Flux - what moved, against $25k and 10% thresholds
    WITH bs AS (
  SELECT 'Balance sheet' AS statement, section AS section_or_kind, account, current_balance AS current_value, prior_balance AS prior_value, change, change_pct, tag
  FROM input_1 WHERE section IN ('Asset', 'Liability', 'Equity') AND tag <> 'Current earnings'
),
pl AS (
  SELECT 'Income statement' AS statement, line_kind, account, this_month_net_credit, prior_month_net_credit, change, change_pct, '' AS tag
  FROM input_2
),
u AS (
  SELECT * FROM bs
  UNION ALL SELECT * FROM pl
)
SELECT statement, section_or_kind, account, ROUND(current_value, 2) AS current_value, ROUND(prior_value, 2) AS prior_value, ROUND(change, 2) AS change, change_pct,
  CASE WHEN ABS(change) >= 25000 AND ABS(COALESCE(change_pct, 100)) >= 10 THEN 'Explain - over both thresholds ($25k and 10%)'
       WHEN ABS(change) >= 25000 THEN 'Large dollar move'
       WHEN ABS(COALESCE(change_pct, 0)) >= 10 THEN 'Large percentage move'
       ELSE '' END AS flux_flag
FROM u
WHERE change <> 0
ORDER BY ABS(change) DESC
LIMIT 25
  10. 10Exceptions - suspense, wrong-sign balances, intercompany, accruals
    WITH c AS (SELECT * FROM input_1),
bs AS (SELECT * FROM input_2),
rows_out AS (
  SELECT 1 AS ord, 'Suspense / uncategorized balance' AS check_item, account, ROUND(net_debit, 2) AS amount,
    'Should be zero at month-end - every dollar here is a posting nobody has classified' AS why
  FROM c WHERE tag = 'Suspense' AND bucket <> 'Income statement' AND ABS(net_debit) >= 1
  UNION ALL
  SELECT 2, 'Negative where it should not be', account, ROUND(net_debit, 2),
    CASE WHEN tag = 'Cash' THEN 'A credit-balance cash account is an overdraft or a posting error' ELSE 'A debit-balance payable / accrual usually means a payment was booked twice or the accrual was never made' END
  FROM c WHERE (tag = 'Cash' AND net_debit < -1) OR (tag IN ('Payables', 'Accruals', 'Deferred revenue') AND net_debit > 1)
  UNION ALL
  SELECT 3, 'Intercompany does not eliminate', 'Intercompany accounts, net',
    ROUND(SUM(net_debit), 2), 'Due-from and due-to should net to zero in a consolidated trial balance - the difference is an unrecorded or mis-dated intercompany entry'
  FROM c WHERE tag = 'Intercompany' HAVING ABS(SUM(net_debit)) >= 1
  UNION ALL
  SELECT 4, 'Accrual balance dropped sharply', account, ROUND(change, 2),
    CASE WHEN current_balance = 0 THEN 'Cleared to zero at month-end - the month-end accrual for this account is missing unless it was intentionally settled' ELSE 'Accruals that fall by more than a third at month-end are often reversed and not re-accrued' END
  FROM bs WHERE tag = 'Accruals' AND prior_balance > 0 AND (prior_balance - current_balance) / prior_balance >= 0.34
  UNION ALL
  SELECT 5, 'Uncategorized expense this month', account, ROUND(-this_month_net_credit, 2),
    'Code it to the right account before the close - it is sitting in an uncategorized line'
  FROM (SELECT account, this_month_net_credit FROM input_3 WHERE regexp_matches(lower(account), 'uncategori|unclassified|suspense|ask my accountant') AND ABS(this_month_net_credit) >= 1)
)
SELECT ord, check_item, account, amount, why FROM rows_out ORDER BY ord, ABS(amount) DESC
  11. 11TIE-OUT - foots, balances, and net income agrees across sources
    WITH cur AS (SELECT * FROM input_1), pri AS (SELECT * FROM input_2), bs AS (SELECT * FROM input_3), tr AS (SELECT * FROM input_4),
ni_tb AS (SELECT COALESCE(SUM(credit - debit), 0) AS v FROM cur WHERE bucket = 'Income statement'),
close_month AS (SELECT MAX(period) AS p FROM tr),
ni_is AS (SELECT net_income AS v FROM tr WHERE period = (SELECT p FROM close_month)),
checks AS (
  SELECT 1 AS ord, 'Current trial balance foots (debits = credits)' AS check_item, ROUND(SUM(debit), 2) AS expected, ROUND(SUM(credit), 2) AS actual FROM cur
  UNION ALL SELECT 2, 'Prior trial balance foots (debits = credits)', ROUND(SUM(debit), 2), ROUND(SUM(credit), 2) FROM pri
  UNION ALL SELECT 3, 'Balance sheet balances: assets = liabilities + equity (incl. current earnings)',
    (SELECT current_balance FROM bs WHERE section = 'TOTAL ASSETS'),
    (SELECT current_balance FROM bs WHERE section = 'TOTAL LIABILITIES') + (SELECT current_balance FROM bs WHERE section = 'TOTAL EQUITY')
  UNION ALL SELECT 4, 'Net income for the month: trial balance vs 12-month income statement (cross-source)', ROUND((SELECT v FROM ni_tb), 2), ROUND(COALESCE((SELECT v FROM ni_is), 0), 2)
)
SELECT ord, check_item, ROUND(expected, 2) AS expected, ROUND(actual, 2) AS actual, ROUND(actual - expected, 2) AS tie_out_difference,
  CASE WHEN ABS(actual - expected) < 1 THEN 'Ties' ELSE 'Does not tie' END AS status
FROM checks ORDER BY ord
  12. 12CLOSE PACKAGE - what to tell the reviewer
    WITH bs AS (SELECT * FROM input_1), pl AS (SELECT * FROM input_2), tr AS (SELECT * FROM input_3), mv AS (SELECT * FROM input_4), ex AS (SELECT * FROM input_5), tie AS (SELECT * FROM input_6),
close_month AS (SELECT MAX(period) AS p FROM tr),
m AS (SELECT * FROM tr WHERE period = (SELECT p FROM close_month)),
prev AS (SELECT * FROM tr WHERE period = (SELECT MAX(period) FROM tr WHERE period < (SELECT p FROM close_month))),
ni_tb AS (SELECT COALESCE(SUM(this_month_net_credit), 0) AS v FROM pl),
cash AS (SELECT COALESCE(SUM(current_balance), 0) AS now, COALESCE(SUM(prior_balance), 0) AS before FROM bs WHERE tag = 'Cash'),
burn AS (SELECT COALESCE(SUM(expenses), 0) / NULLIF(COUNT(*), 0) AS avg_exp FROM (SELECT expenses FROM tr ORDER BY period DESC LIMIT 3)),
dr AS (SELECT COALESCE(SUM(current_balance), 0) AS now, COALESCE(SUM(prior_balance), 0) AS before FROM bs WHERE tag = 'Deferred revenue'),
flag AS (SELECT COUNT(*) AS n FROM mv WHERE flux_flag LIKE 'Explain%'),
top_mv AS (SELECT account, change, change_pct FROM mv WHERE flux_flag LIKE 'Explain%' ORDER BY ABS(change) DESC LIMIT 1),
exc AS (SELECT COUNT(*) AS n FROM ex),
susp AS (SELECT COALESCE(SUM(ABS(amount)), 0) AS v, COUNT(*) AS n FROM ex WHERE check_item LIKE 'Suspense%' OR check_item LIKE 'Uncategorized%'),
acc AS (SELECT COUNT(*) AS n FROM ex WHERE check_item LIKE 'Accrual%'),
ic AS (SELECT amount FROM ex WHERE check_item LIKE 'Intercompany%' LIMIT 1),
bad AS (SELECT COUNT(*) AS n FROM tie WHERE status <> 'Ties'),
sign AS (SELECT ANY_VALUE(sign_convention_detected) AS s FROM tr),
rows_out AS (
  SELECT 1 AS ord, 'THE MONTH' AS area,
    (SELECT m.period || ': revenue ' || (CASE WHEN (m.revenue) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(m.revenue)) AS BIGINT))) || ', expenses ' || (CASE WHEN (m.expenses) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(m.expenses)) AS BIGINT))) || ', net income ' || (CASE WHEN (m.net_income) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(m.net_income)) AS BIGINT))) || ' (' || (CAST(ROUND(m.margin_pct, 1) AS VARCHAR) || '%') || ' margin)' FROM m) AS finding,
    (SELECT CASE WHEN m.revenue_growth_pct IS NULL THEN 'First month in the file' WHEN m.revenue_growth_pct >= 0 THEN 'Revenue up ' || (CAST(ROUND(m.revenue_growth_pct, 1) AS VARCHAR) || '%') || ' on the month before' ELSE 'Revenue down ' || (CAST(ROUND(ABS(m.revenue_growth_pct), 1) AS VARCHAR) || '%') || ' on the month before' END FROM m) AS next_step
  FROM m
  UNION ALL
  SELECT 2, 'TIE-OUTS',
    (SELECT CASE WHEN n = 0 THEN 'All four checks tie: both trial balances foot, the balance sheet balances, and the month''s net income agrees between the trial balance and the income statement'
                 ELSE n || ' of 4 checks do not tie - see the tie-out step' END FROM bad),
    (SELECT CASE WHEN n = 0 THEN 'Ties - the package can go to the reviewer' ELSE 'Do not send the package until the tie-out step is clean' END FROM bad)
  UNION ALL
  SELECT 3, 'CASH',
    (SELECT (CASE WHEN (cash.now) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(cash.now)) AS BIGINT))) || ' at month-end, ' || CASE WHEN cash.now >= cash.before THEN 'up ' ELSE 'down ' END || (CASE WHEN (ABS(cash.now - cash.before)) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(ABS(cash.now - cash.before))) AS BIGINT))) || ' in the month - ' || ROUND(cash.now / NULLIF(burn.avg_exp, 0), 1) || ' months of expenses at the last three months'' run rate' FROM cash, burn),
    (SELECT CASE WHEN cash.now / NULLIF(burn.avg_exp, 0) < 6 THEN 'Under six months of runway at this rate - the board should see this' ELSE 'None needed' END FROM cash, burn)
  UNION ALL
  SELECT 4, 'DEFERRED REVENUE',
    (SELECT (CASE WHEN (dr.now) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(dr.now)) AS BIGINT))) || ' billed but not yet earned, ' || CASE WHEN dr.now >= dr.before THEN 'up ' ELSE 'down ' END || (CASE WHEN (ABS(dr.now - dr.before)) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(ABS(dr.now - dr.before))) AS BIGINT))) || ' in the month' FROM dr),
    (SELECT CASE WHEN dr.now > dr.before THEN 'Growing deferred revenue = bookings ahead of recognition - a healthy sign for a subscription business' ELSE 'Falling deferred revenue = recognizing faster than billing - check renewals' END FROM dr)
  FROM dr WHERE dr.now <> 0 OR dr.before <> 0
  UNION ALL
  SELECT 5, 'EXCEPTIONS TO CLEAR',
    (SELECT CASE WHEN exc.n = 0 THEN 'No suspense balances, no wrong-sign accounts, intercompany eliminates'
                 ELSE exc.n || CASE WHEN exc.n = 1 THEN ' item' ELSE ' items' END || ' - '
                      || CASE WHEN susp.n > 0 THEN (CASE WHEN (susp.v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(susp.v)) AS BIGINT))) || ' in suspense or uncategorized' ELSE '' END
                      || CASE WHEN susp.n > 0 AND ic.amount IS NOT NULL THEN '; ' ELSE '' END
                      || CASE WHEN ic.amount IS NOT NULL THEN 'intercompany off by ' || (CASE WHEN (ic.amount) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(ic.amount)) AS BIGINT))) ELSE '' END
                      || CASE WHEN acc.n > 0 THEN '; ' || acc.n || ' accrual' || CASE WHEN acc.n = 1 THEN '' ELSE 's' END || ' cleared or cut sharply' ELSE '' END END
     FROM exc, susp, acc LEFT JOIN ic ON true),
    (SELECT CASE WHEN n = 0 THEN 'Clean' ELSE 'Each one is listed in the exceptions step with the reason it matters' END FROM exc)
  UNION ALL
  SELECT 6, 'WHAT MOVED',
    (SELECT flag.n || CASE WHEN flag.n = 1 THEN ' account is' ELSE ' accounts are' END || ' over both flux thresholds ($25k and 10%)'
            || COALESCE(' - largest: ' || top_mv.account || ' ' || CASE WHEN top_mv.change >= 0 THEN '+' ELSE '-' END || (CASE WHEN (ABS(top_mv.change)) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(ABS(top_mv.change))) AS BIGINT))) || ' (' || (CAST(ROUND(top_mv.change_pct, 1) AS VARCHAR) || '%') || ')', '')
     FROM flag LEFT JOIN top_mv ON true),
    (SELECT CASE WHEN n = 0 THEN 'None needed' ELSE 'Write one line per flagged account - that is the flux commentary the reviewer will ask for' END FROM flag)
  UNION ALL
  SELECT 7, 'SIGN CONVENTION',
    (SELECT s FROM sign),
    'Detected from the data, then confirmed by the cross-source net income tie-out - not assumed'
  UNION ALL
  SELECT 99, 'VERDICT',
    (SELECT CASE WHEN bad.n > 0 THEN 'Package does NOT tie - ' || bad.n || ' check(s) open'
                 ELSE 'Package ties - ' || exc.n || CASE WHEN exc.n = 1 THEN ' exception' ELSE ' exceptions' END || ' to clear and ' || flag.n || ' flux item' || CASE WHEN flag.n = 1 THEN '' ELSE 's' END || ' to explain before it goes out' END
     FROM bad, exc, flag),
    'Every figure traces to a trial-balance line - re-run it next month-end and the same checks run again'
)
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 →