Free Cash Flow Calculation - Last 12 Months

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. 01Load QBO Monthly Profit & Loss

   SELECT * FROM read_csv_auto('QBO_ProfitAndLoss_Month.csv', header=true)

2. 02Load QBO Trial Balance / Opening Balance Sheet

   SELECT * FROM read_csv_auto('QBO_TrialBalance.csv', header=true)

3. 03Monthly P&L Summary and EBITDA

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

   WITH typed_input AS (
     SELECT
       section,
       account,
       CAST("2025-09" AS VARCHAR) AS "2025-09",
       CAST("2025-10" AS VARCHAR) AS "2025-10",
       CAST("2025-11" AS VARCHAR) AS "2025-11",
       CAST("2025-12" AS VARCHAR) AS "2025-12",
       CAST("2026-01" AS VARCHAR) AS "2026-01",
       CAST("2026-02" AS VARCHAR) AS "2026-02",
       CAST("2026-03" AS VARCHAR) AS "2026-03",
       CAST("2026-04" AS VARCHAR) AS "2026-04",
       CAST("2026-05" AS VARCHAR) AS "2026-05",
       CAST("2026-06" AS VARCHAR) AS "2026-06",
       CAST("2026-07" AS VARCHAR) AS "2026-07",
       CAST("2026-08" AS VARCHAR) AS "2026-08"
     FROM input_1
   ),
   unpivoted AS (
     UNPIVOT typed_input
     ON COLUMNS(* EXCLUDE (section, account))
     INTO NAME period VALUE raw_amount
   ),
   cleaned AS (
     SELECT
       CAST(strptime(period || '-01', '%Y-%m-%d') AS DATE) AS period_date,
       TRIM(CAST(section AS VARCHAR)) AS section,
       TRIM(CAST(account AS VARCHAR)) AS account,
       COALESCE(TRY_CAST(REPLACE(CAST(raw_amount AS VARCHAR), ',', '') AS DOUBLE), 0) AS amount
     FROM unpivoted
     WHERE account IS NOT NULL
       AND LOWER(TRIM(CAST(account AS VARCHAR))) NOT LIKE '%total%'
   ),
   summarized AS (
     SELECT
       period_date,
       SUM(CASE WHEN LOWER(section) LIKE 'income%' THEN amount ELSE 0 END) AS revenue,
       SUM(CASE WHEN LOWER(section) LIKE 'cost of goods sold%' THEN amount ELSE 0 END) AS cogs,
       SUM(CASE WHEN LOWER(section) LIKE 'expenses%' THEN amount ELSE 0 END) AS operating_expenses,
       SUM(
         CASE
           WHEN LOWER(account) LIKE '%depreciation%'
             OR LOWER(account) LIKE '%amortization%'
           THEN amount
           ELSE 0
         END
       ) AS depreciation_amortization
     FROM cleaned
     GROUP BY period_date
   )
   SELECT
     period_date AS period,
     ROUND(revenue, 2) AS "Revenue",
     ROUND(cogs, 2) AS "COGS",
     ROUND(revenue - cogs, 2) AS "Gross Profit",
     ROUND(operating_expenses, 2) AS "Operating Expenses",
     ROUND(depreciation_amortization, 2) AS "Depreciation & Amortization",
     ROUND(revenue - cogs - operating_expenses + depreciation_amortization, 2) AS "EBITDA"
   FROM summarized
   ORDER BY period

4. 04Opening Trading Working Capital from Trial Balance

   buckets values by condition · filters to the relevant rows

   WITH cleaned AS (
     SELECT
       TRIM(CAST(account AS VARCHAR)) AS account,
       TRIM(CAST("type" AS VARCHAR)) AS account_type,
       COALESCE(TRY_CAST(REPLACE(CAST(debit AS VARCHAR), ',', '') AS DOUBLE), 0) AS debit,
       COALESCE(TRY_CAST(REPLACE(CAST(credit AS VARCHAR), ',', '') AS DOUBLE), 0) AS credit
     FROM input_1
     WHERE LOWER(TRIM(CAST("type" AS VARCHAR))) = 'balance sheet'
   ), classified AS (
     SELECT
       account,
       debit,
       credit,
       CASE
         WHEN LOWER(account) LIKE '%accounts receivable%' OR LOWER(account) IN ('ar', 'a/r') OR LOWER(account) LIKE '%a/r%' THEN 'AR'
         WHEN LOWER(account) LIKE '%inventory%' THEN 'Inventory'
         WHEN LOWER(account) LIKE '%accounts payable%' OR LOWER(account) IN ('ap', 'a/p') OR LOWER(account) LIKE '%a/p%' THEN 'AP'
         ELSE 'Other'
       END AS wc_component
     FROM cleaned
   ), balances AS (
     SELECT
       DATE '2025-09-01' AS period,
       SUM(CASE WHEN wc_component = 'AR' THEN debit - credit ELSE 0 END) AS ar,
       SUM(CASE WHEN wc_component = 'Inventory' THEN debit - credit ELSE 0 END) AS inventory,
       SUM(CASE WHEN wc_component = 'AP' THEN credit - debit ELSE 0 END) AS ap
     FROM classified
   )
   SELECT
     period,
     ROUND(ar, 2) AS "Opening AR",
     ROUND(inventory, 2) AS "Opening Inventory",
     ROUND(ap, 2) AS "Opening AP",
     ROUND(ar + inventory - ap, 2) AS "Opening Trading Working Capital"
   FROM balances

5. 05Monthly EBITDA, Trading WC, CAPEX and Free Cash Flow Bridge

   combines data from multiple inputs · sorts the output

   SELECT
     p.period AS "Period",
     p."Revenue",
     p."COGS",
     p."Gross Profit",
     p."Operating Expenses",
     p."Depreciation & Amortization",
     p."EBITDA",
     COALESCE(w."Opening AR", 0) AS "Opening AR",
     COALESCE(w."Opening Inventory", 0) AS "Opening Inventory",
     COALESCE(w."Opening AP", 0) AS "Opening AP",
     COALESCE(w."Opening Trading Working Capital", 0) AS "Opening Trading Working Capital",
     CAST(NULL AS DOUBLE) AS "Change in Trading Working Capital",
     CAST(NULL AS DOUBLE) AS "CAPEX",
     CAST(NULL AS DOUBLE) AS "Free Cash Flow",
     p."EBITDA" AS "FCF Before WC and CAPEX",
     CASE
       WHEN w.period IS NOT NULL THEN 'Opening WC loaded from 2025-08-31 TB; monthly BS snapshots required for monthly TWC changes, CAPEX, and full FCF'
       ELSE 'Monthly BS snapshots required for TWC changes, CAPEX, and full FCF'
     END AS "Data Completeness Flag"
   FROM input_1 p
   LEFT JOIN input_2 w
     ON p.period = w.period
   ORDER BY p.period