EBITDA Bridge - Last 12 Months vs the 12 Before

What moved EBITDA, line by line: the summary P&L to EBITDA, a bridge from last year's EBITDA to this year's with the biggest movers, the lines left out (D&A, interest, taxes) and a tie-out. Straight from QuickBooks.

7 steps · shared by Klajdi · September 24, 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_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

01Demo company - monthly profit and loss, last 24 months (sample)

SELECT section, account, CAST(COLUMNS(* EXCLUDE (section, account)) AS DOUBLE) FROM (VALUES
  ('Income', 'Product sales', 169713.7, 177651.53, 202075.84, 211276.1, 161908.79, 173492.1, 189491.28, 198570.27, 205365.45, 212870.48, 193933.74, 193888.51, 180961.72, 196532.91, 217288.53, 224423.44, 181501.06, 193365.88, 212473.09, 217786.74, 217552.8, 221385.15, 217033.06, 208209.47),
  ('Income', 'Service & repair revenue', 28892.78, 30027.28, 32866.79, 36114.83, 27459.61, 29875.98, 32671.17, 33676.48, 34161.19, 35273.08, 32978.52, 32075.29, 31531.82, 32662.89, 35891.16, 38110.2, 28342.6, 29951.67, 33509.49, 33835.59, 36095.76, 36112.79, 35100.74, 33578.09),
  ('Cost of Goods Sold', 'Materials', 57662.32, 59979.2, 63868.37, 69966.09, 56139.78, 58713.83, 68172.76, 68105.16, 69026.19, 73774.7, 71191.62, 68613.06, 64581.2, 68634.29, 79856.25, 85324.31, 63171.52, 68351.15, 76318.64, 78726.49, 85082.07, 84712.59, 82919.35, 81838.49),
  ('Cost of Goods Sold', 'Direct labor', 34311.18, 36751.41, 39335.1, 43453.8, 32074.21, 34736.43, 38267.58, 39328.17, 41980.18, 42541.87, 39111.78, 39842.62, 36070.85, 37818.0, 43135.14, 45621.39, 33644.46, 37723.39, 40181.89, 41265.65, 43569.3, 43420.09, 41047.87, 39843.87),
  ('Cost of Goods Sold', 'Freight in', 8076.37, 8476.86, 9055.65, 9737.48, 7190.82, 7548.41, 8526.17, 8341.54, 8436.47, 8630.98, 7710.46, 7441.53, 7210.47, 7306.45, 7700.35, 8073.74, 6248.1, 6604.23, 6924.56, 7288.92, 7302.76, 7223.06, 6649.55, 6305.8),
  ('Expenses', 'Salaries & wages', 28732.44, 29268.36, 28848.28, 29118.66, 28859.05, 29187.76, 29055.95, 28880.19, 28811.75, 29127.8, 28749.89, 28842.47, 29034.43, 33877.09, 33716.9, 33492.13, 33920.8, 33440.84, 33314.75, 33484.71, 33603.47, 33344.27, 33422.18, 33551.72),
  ('Expenses', 'Rent', 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8500.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0, 8840.0),
  ('Expenses', 'Marketing', 6875.28, 6856.08, 7457.95, 7865.84, 5305.15, 5750.57, 6410.29, 6318.64, 5825.09, 6112.16, 5659.75, 5147.48, 4729.51, 4560.28, 5052.55, 5253.19, 3548.29, 3996.79, 4549.6, 4428.85, 4283.22, 4015.48, 3884.88, 3607.88),
  ('Expenses', 'Software & subscriptions', 2095.0, 2182.32, 2170.95, 2286.27, 2277.29, 2389.36, 2438.12, 2474.77, 2585.47, 2629.79, 2709.04, 2811.48, 2807.31, 2849.97, 2956.69, 3077.66, 3095.99, 3170.16, 3347.43, 3399.0, 3454.87, 3606.25, 3615.33, 3757.49),
  ('Expenses', 'Insurance', 2600.0, 2616.73, 2633.56, 2650.51, 2667.56, 2684.73, 2702.0, 2719.38, 2736.88, 2754.49, 2772.21, 2790.05, 2808.0, 2826.07, 2844.25, 2862.55, 2880.97, 2899.5, 2918.16, 2936.93, 2955.83, 2974.85, 2993.99, 3013.25),
  ('Expenses', 'Professional fees', 2169.3, 1776.16, 1595.89, 1668.79, 1977.91, 1371.37, 2200.61, 2121.1, 1904.57, 2478.54, 1562.46, 1662.05, 1879.38, 1525.74, 2597.16, 2082.5, 1504.56, 1513.84, 2333.59, 2193.37, 2466.7, 2564.03, 2610.54, 2747.57),
  ('Expenses', 'Utilities', 1410.55, 1379.18, 1583.12, 1635.05, 1219.83, 1373.13, 1576.55, 1513.55, 1567.55, 1618.73, 1599.23, 1541.0, 1468.9, 1548.54, 1645.95, 1848.2, 1427.03, 1374.95, 1697.68, 1696.99, 1617.0, 1722.07, 1665.07, 1693.51),
  ('Expenses', 'Depreciation', 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4200.0, 4704.0, 4704.0, 4704.0, 4704.0, 4704.0, 4704.0, 4704.0, 4704.0, 4704.0, 4704.0),
  ('Other Income', 'Interest income', 303.02, 320.93, 308.48, 284.38, 338.51, 295.46, 348.34, 311.84, 327.59, 363.76, 324.12, 329.66, 361.19, 381.85, 396.34, 391.06, 416.66, 387.84, 429.79, 367.25, 383.88, 414.95, 401.48, 444.96),
  ('Other Expenses', 'Interest expense', 2400.0, 2387.66, 2375.38, 2363.16, 2351.01, 2338.92, 2326.89, 2314.92, 2303.01, 2291.17, 2279.39, 2267.66, 2256.0, 2244.4, 2232.85, 2221.37, 2209.95, 2198.58, 2187.27, 2176.02, 2164.83, 2153.7, 2142.62, 2131.6)
) AS t(section, account, "2024-09", "2024-10", "2024-11", "2024-12", "2025-01", "2025-02", "2025-03", "2025-04", "2025-05", "2025-06", "2025-07", "2025-08", "2025-09", "2025-10", "2025-11", "2025-12", "2026-01", "2026-02", "2026-03", "2026-04", "2026-05", "2026-06", "2026-07", "2026-08")

02Line up the last 12 months and the 12 before

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

-- Monthly P&L → one row per account per month, tagged with its window.
-- Windows: the last 12 complete months vs the 12 before. With fewer than 24 months of books,
-- both windows shrink to the same length so the comparison stays like-for-like.
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}$')),
r AS (SELECT period, ROW_NUMBER() OVER (ORDER BY period DESC) AS months_back, COUNT(*) OVER () AS n FROM m),
w AS (SELECT LEAST(12, CAST(FLOOR(MAX(n) / 2) AS INTEGER)) AS len FROM r)
SELECT l.section, l.account, l.period, ROUND(l.amount, 2) AS amount,
  CASE WHEN r.months_back <= w.len THEN 'Current' WHEN r.months_back <= 2 * w.len THEN 'Prior' ELSE 'Older' END AS time_window,
  w.len AS window_months
FROM long l JOIN r ON r.period = l.period CROSS JOIN w
WHERE l.account NOT ILIKE 'total %' AND l.amount IS NOT NULL
ORDER BY l.period, l.section, l.account

03Every P&L line - prior vs current, effect on EBITDA

aggregates rows into summary totals · buckets values by condition · filters to the relevant rows

-- Each P&L line: prior vs current window, whether it's inside EBITDA, and its effect on EBITDA.
-- EBITDA = revenue - cost of goods sold - operating expenses, leaving out depreciation,
-- amortization, interest, income taxes and the "Other income / expense" sections.
WITH a AS (
  SELECT section, account,
    COALESCE(SUM(amount) FILTER (WHERE time_window = 'Prior'), 0) AS prior_period,
    COALESCE(SUM(amount) FILTER (WHERE time_window = 'Current'), 0) AS current_period,
    MAX(window_months) AS window_months
  FROM input_1 GROUP BY section, account
)
SELECT section,
  CASE WHEN section ILIKE '%other%' THEN 'Outside EBITDA (other income/expense)'
       WHEN account ILIKE '%depreciat%' OR account ILIKE '%amortiz%' OR account ILIKE '%interest%' OR account ILIKE '%income tax%' OR account ILIKE '%taxes on income%' THEN 'Outside EBITDA (D&A, interest, taxes)'
       WHEN section ILIKE '%income%' THEN 'Revenue'
       WHEN section ILIKE '%cost of goods%' OR section ILIKE '%cost of sales%' THEN 'Cost of goods sold'
       ELSE 'Operating expenses' END AS bucket,
  account,
  ROUND(prior_period, 2) AS prior_period, ROUND(current_period, 2) AS current_period,
  ROUND(current_period - prior_period, 2) AS change,
  -- effect on EBITDA: more revenue helps, more cost hurts
  ROUND(CASE WHEN section ILIKE '%other%' OR (account ILIKE '%depreciat%' OR account ILIKE '%amortiz%' OR account ILIKE '%interest%' OR account ILIKE '%income tax%' OR account ILIKE '%taxes on income%') THEN 0
             WHEN section ILIKE '%income%' THEN current_period - prior_period
             ELSE -(current_period - prior_period) END, 2) AS ebitda_effect,
  window_months
FROM a
ORDER BY ABS(current_period - prior_period) DESC

04Summary P&L down to EBITDA

buckets values by condition · appends result sets (e.g. a TOTAL row) · filters to the relevant rows

-- Summary P&L down to EBITDA, both windows side by side.
WITH t AS (
  SELECT
    SUM(prior_period) FILTER (WHERE bucket = 'Revenue') AS rev_p, SUM(current_period) FILTER (WHERE bucket = 'Revenue') AS rev_c,
    SUM(prior_period) FILTER (WHERE bucket = 'Cost of goods sold') AS cogs_p, SUM(current_period) FILTER (WHERE bucket = 'Cost of goods sold') AS cogs_c,
    SUM(prior_period) FILTER (WHERE bucket = 'Operating expenses') AS opex_p, SUM(current_period) FILTER (WHERE bucket = 'Operating expenses') AS opex_c,
    MAX(window_months) AS n
  FROM input_1
),
v AS (SELECT *, COALESCE(rev_p,0) - COALESCE(cogs_p,0) AS gp_p, COALESCE(rev_c,0) - COALESCE(cogs_c,0) AS gp_c,
             COALESCE(rev_p,0) - COALESCE(cogs_p,0) - COALESCE(opex_p,0) AS e_p, COALESCE(rev_c,0) - COALESCE(cogs_c,0) - COALESCE(opex_c,0) AS e_c FROM t)
SELECT line, prior_period, current_period, ROUND(current_period - prior_period, 2) AS change,
  CASE WHEN line ILIKE '%margin%' THEN NULL ELSE ROUND(100.0 * (current_period - prior_period) / NULLIF(ABS(prior_period), 0), 1) END AS change_pct, window_months FROM (
  SELECT 1 AS o, 'Revenue' AS line, ROUND(rev_p, 2) AS prior_period, ROUND(rev_c, 2) AS current_period, n AS window_months FROM v
  UNION ALL SELECT 2, 'Cost of goods sold', ROUND(cogs_p, 2), ROUND(cogs_c, 2), n FROM v
  UNION ALL SELECT 3, 'Gross profit', ROUND(gp_p, 2), ROUND(gp_c, 2), n FROM v
  UNION ALL SELECT 4, 'Gross margin %', ROUND(100.0 * gp_p / NULLIF(rev_p, 0), 1), ROUND(100.0 * gp_c / NULLIF(rev_c, 0), 1), n FROM v
  UNION ALL SELECT 5, 'Operating expenses (excl. D&A)', ROUND(opex_p, 2), ROUND(opex_c, 2), n FROM v
  UNION ALL SELECT 6, 'EBITDA', ROUND(e_p, 2), ROUND(e_c, 2), n FROM v
  UNION ALL SELECT 7, 'EBITDA margin %', ROUND(100.0 * e_p / NULLIF(rev_p, 0), 1), ROUND(100.0 * e_c / NULLIF(rev_c, 0), 1), n FROM v
) ORDER BY o

05Lines left out of EBITDA (D&A, interest, taxes, other)

filters to the relevant rows · sorts the output

-- Lines left out of EBITDA, so nothing disappears silently.
SELECT bucket, account, prior_period, current_period, change FROM input_1
WHERE bucket LIKE 'Outside%' ORDER BY bucket, ABS(change) DESC

06EBITDA BRIDGE - what moved it (open Chart for the waterfall)

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

-- The bridge: prior EBITDA → the lines that moved it → current EBITDA.
-- The 6 biggest movers get their own bar; everything else is one "All other lines" bar.
-- bridge_start / bridge_end are the floating bar edges the waterfall chart draws.
WITH l AS (SELECT account, bucket, ebitda_effect FROM input_1 WHERE bucket NOT LIKE 'Outside%' AND ebitda_effect <> 0),
r AS (SELECT *, ROW_NUMBER() OVER (ORDER BY ABS(ebitda_effect) DESC, account) AS rk FROM l),
top AS (SELECT account AS label, bucket, ebitda_effect AS amount, rk FROM r WHERE rk <= 6
        UNION ALL SELECT 'All other lines', 'Mixed', SUM(ebitda_effect), 7 FROM r WHERE rk > 6 HAVING COUNT(*) > 0),
e AS (SELECT MAX(prior_period) FILTER (WHERE line = 'EBITDA') AS p, MAX(current_period) FILTER (WHERE line = 'EBITDA') AS c, MAX(window_months) AS n FROM input_2),
steps AS (
  SELECT 0 AS bridge_order, 'EBITDA, prior ' || n || ' months' AS bridge_step, 'total' AS bridge_kind, p AS amount FROM e
  UNION ALL SELECT rk, label, CASE WHEN amount >= 0 THEN 'increase' ELSE 'decrease' END, amount FROM top
  UNION ALL SELECT 99, 'EBITDA, last ' || n || ' months', 'total', c FROM e
),
run AS (SELECT *, SUM(CASE WHEN bridge_kind = 'total' AND bridge_order = 0 THEN amount WHEN bridge_kind <> 'total' THEN amount ELSE 0 END)
          OVER (ORDER BY bridge_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum FROM steps)
SELECT bridge_order, bridge_step, bridge_kind, ROUND(amount, 2) AS amount,
  ROUND(CASE WHEN bridge_kind = 'total' THEN 0 ELSE cum - amount END, 2) AS bridge_start,
  ROUND(CASE WHEN bridge_kind = 'total' THEN amount ELSE cum END, 2) AS bridge_end
FROM run ORDER BY bridge_order

07WHAT MOVED EBITDA - the story in six lines

buckets values by condition · appends result sets (e.g. a TOTAL row) · filters to the relevant rows

-- Inputs: 1 summary to EBITDA, 2 bridge, 3 lines, 4 excluded lines
SELECT ord, area, finding, next_step FROM (
WITH s AS (SELECT MAX(prior_period) FILTER (WHERE line = 'EBITDA') AS p, MAX(current_period) FILTER (WHERE line = 'EBITDA') AS c,
                  MAX(prior_period) FILTER (WHERE line = 'EBITDA margin %') AS mp, MAX(current_period) FILTER (WHERE line = 'EBITDA margin %') AS mc,
                  MAX(prior_period) FILTER (WHERE line = 'Revenue') AS rp, MAX(current_period) FILTER (WHERE line = 'Revenue') AS rc,
                  MAX(window_months) AS n FROM input_1),
up AS (SELECT account, ebitda_effect FROM input_3 WHERE bucket NOT LIKE 'Outside%' AND ebitda_effect > 0 ORDER BY ebitda_effect DESC LIMIT 1),
dn AS (SELECT account, ebitda_effect FROM input_3 WHERE bucket NOT LIKE 'Outside%' AND ebitda_effect < 0 ORDER BY ebitda_effect ASC LIMIT 1),
tie AS (SELECT (SELECT MAX(amount) FILTER (WHERE bridge_order = 0) FROM input_2) + (SELECT COALESCE(SUM(amount), 0) FROM input_2 WHERE bridge_kind <> 'total')
               - (SELECT MAX(amount) FILTER (WHERE bridge_order = 99) FROM input_2) AS gap)
SELECT 1 AS ord, 'EBITDA' AS area,
  (CASE WHEN (p) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(p)) AS BIGINT))) || ' → ' || (CASE WHEN (c) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(c)) AS BIGINT))) || ' (' || (CASE WHEN (c - p) < 0 THEN '-$' ELSE '+$' END || format('{:,}', CAST(ROUND(ABS(c - p)) AS BIGINT))) || ', ' || COALESCE(CAST(ROUND(100.0 * (c - p) / NULLIF(ABS(p), 0), 1) AS VARCHAR), 'n/a') || '%)' AS finding,
  'Last ' || n || ' months vs the ' || n || ' before' AS next_step FROM s
UNION ALL SELECT 2, 'MARGIN',
  'EBITDA margin ' || COALESCE(CAST(mp AS VARCHAR), 'n/a') || '% → ' || COALESCE(CAST(mc AS VARCHAR), 'n/a') || '% on revenue of ' || (CASE WHEN (rc) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(rc)) AS BIGINT))),
  CASE WHEN mc >= mp THEN 'None needed - margin held or improved' ELSE 'Margin slipped - the bars below show where it went' END FROM s
UNION ALL SELECT 3, 'BIGGEST LIFT', COALESCE((SELECT account || ' ' || (CASE WHEN (ebitda_effect) < 0 THEN '-$' ELSE '+$' END || format('{:,}', CAST(ROUND(ABS(ebitda_effect)) AS BIGINT))) FROM up), 'Nothing added to EBITDA'), 'Keep it going - check it is repeatable, not one-off'
UNION ALL SELECT 4, 'BIGGEST DRAG', COALESCE((SELECT account || ' ' || (CASE WHEN (ebitda_effect) < 0 THEN '-$' ELSE '+$' END || format('{:,}', CAST(ROUND(ABS(ebitda_effect)) AS BIGINT))) FROM dn), 'Nothing took away from EBITDA'),
  CASE WHEN (SELECT COUNT(*) FROM dn) > 0 THEN 'Ask why - pricing, volume or a one-off?' ELSE 'None found' END
UNION ALL SELECT 5, 'LEFT OUT OF EBITDA',
  (SELECT COUNT(*) FROM input_4) || ' line(s): depreciation, interest, taxes and other income/expense',
  'Listed in the step named Lines left out of EBITDA - move any that belong inside'
UNION ALL SELECT 6, 'TIE-OUT', 'Bridge adds up to the difference in EBITDA: gap ' || (CASE WHEN ((SELECT gap FROM tie)) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS((SELECT gap FROM tie))) AS BIGINT))),
  CASE WHEN ABS((SELECT gap FROM tie)) < 0.05 THEN 'Ties - every dollar of the change is explained' ELSE 'Does not tie - check the bridge step' END
UNION ALL SELECT 7, 'VERDICT',
  CASE WHEN c >= p THEN 'EBITDA UP ' || (CASE WHEN (c - p) < 0 THEN '-$' ELSE '+$' END || format('{:,}', CAST(ROUND(ABS(c - p)) AS BIGINT))) || ' - led by ' || COALESCE((SELECT account FROM up), 'several lines')
       ELSE 'EBITDA DOWN ' || (CASE WHEN (c - p) < 0 THEN '-$' ELSE '+$' END || format('{:,}', CAST(ROUND(ABS(c - p)) AS BIGINT))) || ' - hit hardest by ' || COALESCE((SELECT account FROM dn), 'several lines') END,
  'Open the bridge step and switch to Chart to see the waterfall' FROM s
) 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 →