-- Model generated by Power Migrate 0.1.0a1 from 'fpna_month_end.xlsx' (SQL target, duckdb dialect).
-- Load: run functions.sql, fill `cells(sheet, row_num, col, num, txt, flag)` with the input cells and
-- `sheet_rows(sheet, row_num)` with one row per sheet row (the harness does both), then run this file.
-- 72 steps rewrite one block of formula cells each, in dependency order; the final views carry
-- the computed sheets. Excel errors and the empty string are NULL; a blank input counts as 0 in arithmetic.

-- Sheet Assumptions: input cells as typed columns (one row per sheet row)
CREATE TABLE assumptions_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'B' THEN cell.num END) AS "B"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Assumptions'
GROUP BY spine.row_num;

-- Sheet Lookups: input cells as typed columns (one row per sheet row)
CREATE TABLE lookups_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'A' THEN cell.txt END) AS "A_txt",
       max(CASE WHEN cell.col = 'B' THEN cell.txt END) AS "B_txt",
       max(CASE WHEN cell.col = 'C' THEN cell.num END) AS "C",
       max(CASE WHEN cell.col = 'E' THEN cell.txt END) AS "E_txt",
       max(CASE WHEN cell.col = 'F' THEN cell.txt END) AS "F_txt"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Lookups'
GROUP BY spine.row_num;

-- Sheet Orders: input cells as typed columns (one row per sheet row)
CREATE TABLE orders_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'B' THEN cell.num END) AS "B",
       max(CASE WHEN cell.col = 'C' THEN cell.txt END) AS "C_txt",
       max(CASE WHEN cell.col = 'D' THEN cell.txt END) AS "D_txt",
       max(CASE WHEN cell.col = 'E' THEN cell.num END) AS "E",
       max(CASE WHEN cell.col = 'F' THEN cell.txt END) AS "F_txt",
       max(CASE WHEN cell.col = 'G' THEN cell.txt END) AS "G_txt",
       max(CASE WHEN cell.col = 'H' THEN cell.txt END) AS "H_txt",
       max(CASE WHEN cell.col = 'K' THEN cell.num END) AS "K",
       max(CASE WHEN cell.col = 'K' THEN cell.txt END) AS "K_txt"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Orders'
GROUP BY spine.row_num;

-- Sheet Revenue: input cells as typed columns (one row per sheet row)
CREATE TABLE revenue_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'A' THEN cell.txt END) AS "A_txt"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Revenue'
GROUP BY spine.row_num;

-- Sheet Costs: input cells as typed columns (one row per sheet row)
CREATE TABLE costs_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'B' THEN cell.num END) AS "B"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Costs'
GROUP BY spine.row_num;

-- Sheet 'P&L': input cells as typed columns (one row per sheet row)
CREATE TABLE p_l_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'A' THEN cell.txt END) AS "A_txt"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'P&L'
GROUP BY spine.row_num;

-- Sheet Scratch: input cells as typed columns (one row per sheet row)
CREATE TABLE scratch_00 AS
SELECT spine.row_num,
       max(CASE WHEN cell.col = 'B' THEN cell.num END) AS "B"
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Scratch'
GROUP BY spine.row_num;

-- Sheet Dashboard: input cells as typed columns (one row per sheet row)
CREATE TABLE dashboard_00 AS
SELECT spine.row_num
FROM sheet_rows AS spine
LEFT JOIN cells AS cell ON cell.sheet = spine.sheet AND cell.row_num = spine.row_num
WHERE spine.sheet = 'Dashboard'
GROUP BY spine.row_num;

-- Logic blocks, in dependency order

-- Assumptions!B14: Last refreshed  (single, 1 cells)
--   Excel: =TODAY()
CREATE TABLE assumptions_01 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 14 THEN 46304 /* TODAY() frozen to the workbook's last-saved date */ ELSE base."B" END AS "B"
FROM assumptions_00 AS base;

-- Orders!F2:F301: Month  (column, 300 cells)
--   Excel: =EOMONTH(B2,0)
--   pattern (R1C1): =EOMONTH(RC[-4],0)
CREATE TABLE orders_01 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       CASE WHEN base.row_num BETWEEN 2 AND 301 THEN xl_eomonth(COALESCE(base."B", 0), 0) ELSE NULL END AS "F",
       base."F_txt",
       base."G_txt",
       base."H_txt",
       base."K",
       base."K_txt"
FROM orders_00 AS base;

-- Orders!G2:G301: Region  (column, 300 cells)
--   Excel: =VLOOKUP(C2,Lookups!$E$3:$F$8,2,FALSE)
--   pattern (R1C1): =VLOOKUP(RC[-4],Lookups!R3C5:R8C6,2,FALSE)
CREATE TABLE orders_02 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       CASE WHEN base.row_num BETWEEN 2 AND 301 THEN (SELECT data."F_txt" FROM lookups_00 AS data WHERE data.row_num BETWEEN 3 AND 8 AND lower(data."E_txt") = lower(base."C_txt") ORDER BY data.row_num LIMIT 1) ELSE base."G_txt" END AS "G_txt",
       base."H_txt",
       base."K",
       base."K_txt"
FROM orders_01 AS base;

-- Orders!H2:H301: Unit price  (column, 300 cells)
--   Excel: =VLOOKUP(D2,Lookups!$A$3:$C$12,3,FALSE)
--   pattern (R1C1): =VLOOKUP(RC[-4],Lookups!R3C1:R12C3,3,FALSE)
CREATE TABLE orders_03 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       base."G_txt",
       CASE WHEN base.row_num BETWEEN 2 AND 301 THEN (SELECT data."C" FROM lookups_00 AS data WHERE data.row_num BETWEEN 3 AND 12 AND lower(data."A_txt") = lower(base."D_txt") ORDER BY data.row_num LIMIT 1) ELSE NULL END AS "H",
       base."H_txt",
       base."K",
       base."K_txt"
FROM orders_02 AS base;

-- Orders!I2:I301: Gross EUR  (column, 300 cells)
--   Excel: =E2*H2
--   pattern (R1C1): =RC[-4]*RC[-1]
CREATE TABLE orders_04 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       base."G_txt",
       base."H",
       base."H_txt",
       CASE WHEN base.row_num BETWEEN 2 AND 301 THEN (COALESCE(base."E", 0) * base."H") ELSE NULL END AS "I",
       base."K",
       base."K_txt"
FROM orders_03 AS base;

-- Orders!J2:J301: Discount EUR  (column, 300 cells)
--   Excel: =IF(I2>Assumptions!$B$11,I2*Assumptions!$B$12,0)
--   pattern (R1C1): =IF(RC[-1]>Assumptions!R11C2,RC[-1]*Assumptions!R12C2,0)
CREATE TABLE orders_05 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       base."G_txt",
       base."H",
       base."H_txt",
       base."I",
       CASE WHEN base.row_num BETWEEN 2 AND 301 THEN CASE WHEN (base."I" > COALESCE(params.assumptions_b11, 0)) THEN (base."I" * COALESCE(params.assumptions_b12, 0)) ELSE 0 END ELSE NULL END AS "J",
       base."K",
       base."K_txt"
FROM orders_04 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM assumptions_01 WHERE row_num = 11) AS assumptions_b11,
           (SELECT "B" FROM assumptions_01 WHERE row_num = 12) AS assumptions_b12
) AS params;

-- Orders!K2:K149: Net GBP  (column, 148 cells)
--   Excel: =(I2-J2)*FX_EUR_GBP
--   pattern (R1C1): =(RC[-2]-RC[-1])*FX_EUR_GBP
CREATE TABLE orders_06 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       base."G_txt",
       base."H",
       base."H_txt",
       base."I",
       base."J",
       CASE WHEN base.row_num BETWEEN 2 AND 149 THEN ((base."I" - base."J") * COALESCE(params.fx_eur_gbp, 0)) ELSE base."K" END AS "K",
       base."K_txt"
FROM orders_05 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM assumptions_01 WHERE row_num = 7) AS fx_eur_gbp
) AS params;

-- Orders!L2:L301: Period  (column, 300 cells)
--   Excel: =TEXT(B2,"yyyy-mm")
--   pattern (R1C1): =TEXT(RC[-10],"yyyy-mm")
CREATE TABLE orders_07 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       base."G_txt",
       base."H",
       base."H_txt",
       base."I",
       base."J",
       base."K",
       base."K_txt",
       CASE WHEN base.row_num BETWEEN 2 AND 301 THEN strftime(xl_to_date(COALESCE(base."B", 0)), '%Y-%m') ELSE NULL END AS "L_txt"
FROM orders_06 AS base;

-- Orders!K151:K301  (column, 151 cells)
--   Excel: =(I151-J151)*FX_EUR_GBP
--   pattern (R1C1): =(RC[-2]-RC[-1])*FX_EUR_GBP
CREATE TABLE orders_08 AS
SELECT base.row_num,
       base."B",
       base."C_txt",
       base."D_txt",
       base."E",
       base."F",
       base."F_txt",
       base."G_txt",
       base."H",
       base."H_txt",
       base."I",
       base."J",
       CASE WHEN base.row_num BETWEEN 151 AND 301 THEN ((base."I" - base."J") * COALESCE(params.fx_eur_gbp, 0)) ELSE base."K" END AS "K",
       base."K_txt",
       base."L_txt"
FROM orders_07 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM assumptions_01 WHERE row_num = 7) AS fx_eur_gbp
) AS params;

-- Revenue!B2: Product  (single, 1 cells)
--   Excel: =EOMONTH(Assumptions!$B$4,0)
CREATE TABLE revenue_01 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(COALESCE(params.assumptions_b4, 0), 0) ELSE NULL END AS "B"
FROM revenue_00 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM assumptions_01 WHERE row_num = 4) AS assumptions_b4
) AS params;

-- Revenue!C2:M2 reads its own earlier cells; computed cell by cell below

-- Revenue!C2: Product  (single, 1 cells)
--   Excel: =EOMONTH(B2,1)
CREATE TABLE revenue_02 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."B", 1) ELSE NULL END AS "C"
FROM revenue_01 AS base;

-- Revenue!D2: Product  (single, 1 cells)
--   Excel: =EOMONTH(C2,1)
CREATE TABLE revenue_03 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."C", 1) ELSE NULL END AS "D"
FROM revenue_02 AS base;

-- Revenue!E2: Product  (single, 1 cells)
--   Excel: =EOMONTH(D2,1)
CREATE TABLE revenue_04 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."D", 1) ELSE NULL END AS "E"
FROM revenue_03 AS base;

-- Revenue!F2: Product  (single, 1 cells)
--   Excel: =EOMONTH(E2,1)
CREATE TABLE revenue_05 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."E", 1) ELSE NULL END AS "F"
FROM revenue_04 AS base;

-- Revenue!G2: Product  (single, 1 cells)
--   Excel: =EOMONTH(F2,1)
CREATE TABLE revenue_06 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."F", 1) ELSE NULL END AS "G"
FROM revenue_05 AS base;

-- Revenue!H2: Product  (single, 1 cells)
--   Excel: =EOMONTH(G2,1)
CREATE TABLE revenue_07 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."G", 1) ELSE NULL END AS "H"
FROM revenue_06 AS base;

-- Revenue!I2: Product  (single, 1 cells)
--   Excel: =EOMONTH(H2,1)
CREATE TABLE revenue_08 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."H", 1) ELSE NULL END AS "I"
FROM revenue_07 AS base;

-- Revenue!J2: Product  (single, 1 cells)
--   Excel: =EOMONTH(I2,1)
CREATE TABLE revenue_09 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."I", 1) ELSE NULL END AS "J"
FROM revenue_08 AS base;

-- Revenue!K2: Product  (single, 1 cells)
--   Excel: =EOMONTH(J2,1)
CREATE TABLE revenue_10 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."J", 1) ELSE NULL END AS "K"
FROM revenue_09 AS base;

-- Revenue!L2: Product  (single, 1 cells)
--   Excel: =EOMONTH(K2,1)
CREATE TABLE revenue_11 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."K", 1) ELSE NULL END AS "L"
FROM revenue_10 AS base;

-- Revenue!M2: Product  (single, 1 cells)
--   Excel: =EOMONTH(L2,1)
CREATE TABLE revenue_12 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       CASE WHEN base.row_num = 2 THEN xl_eomonth(base."L", 1) ELSE NULL END AS "M"
FROM revenue_11 AS base;

-- Revenue!B3:M11: P-100  (table, 108 cells)
--   Excel: =SUMIFS(Orders!$K:$K,Orders!$F:$F,B$2,Orders!$D:$D,$A3)
--   pattern (R1C1): =SUMIFS(Orders!C11:C11,Orders!C6:C6,R2C,Orders!C4:C4,RC1)
CREATE TABLE revenue_13 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_b2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."B" END AS "B",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_c2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."C" END AS "C",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_d2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."D" END AS "D",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_e2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."E" END AS "E",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_f2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."F" END AS "F",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_g2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."G" END AS "G",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_h2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."H" END AS "H",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_i2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."I" END AS "I",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_j2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."J" END AS "J",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_k2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."K" END AS "K",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_l2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."L" END AS "L",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_m2) AND (lower(data."D_txt") = lower(base."A_txt"))), 0) ELSE base."M" END AS "M"
FROM revenue_12 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM revenue_12 WHERE row_num = 2) AS revenue_b2,
           (SELECT "C" FROM revenue_12 WHERE row_num = 2) AS revenue_c2,
           (SELECT "D" FROM revenue_12 WHERE row_num = 2) AS revenue_d2,
           (SELECT "E" FROM revenue_12 WHERE row_num = 2) AS revenue_e2,
           (SELECT "F" FROM revenue_12 WHERE row_num = 2) AS revenue_f2,
           (SELECT "G" FROM revenue_12 WHERE row_num = 2) AS revenue_g2,
           (SELECT "H" FROM revenue_12 WHERE row_num = 2) AS revenue_h2,
           (SELECT "I" FROM revenue_12 WHERE row_num = 2) AS revenue_i2,
           (SELECT "J" FROM revenue_12 WHERE row_num = 2) AS revenue_j2,
           (SELECT "K" FROM revenue_12 WHERE row_num = 2) AS revenue_k2,
           (SELECT "L" FROM revenue_12 WHERE row_num = 2) AS revenue_l2,
           (SELECT "M" FROM revenue_12 WHERE row_num = 2) AS revenue_m2
) AS params;

-- Revenue!N3:N11: Total  (column, 9 cells)
--   Excel: =SUM(B3:M3)
--   pattern (R1C1): =SUM(RC[-12]:RC[-1])
CREATE TABLE revenue_14 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M",
       CASE WHEN base.row_num BETWEEN 3 AND 11 THEN (COALESCE((SELECT COALESCE(SUM(data."B"), 0) + COALESCE(SUM(data."C"), 0) + COALESCE(SUM(data."D"), 0) + COALESCE(SUM(data."E"), 0) + COALESCE(SUM(data."F"), 0) + COALESCE(SUM(data."G"), 0) + COALESCE(SUM(data."H"), 0) + COALESCE(SUM(data."I"), 0) + COALESCE(SUM(data."J"), 0) + COALESCE(SUM(data."K"), 0) + COALESCE(SUM(data."L"), 0) + COALESCE(SUM(data."M"), 0) FROM revenue_13 AS data WHERE data.row_num = base.row_num), 0)) ELSE NULL END AS "N"
FROM revenue_13 AS base;

-- Revenue!B12:N12: Total  (row, 13 cells)
--   Excel: =SUM(B3:B11)
--   pattern (R1C1): =SUM(R[-9]C:R[-1]C)
CREATE TABLE revenue_15 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."B"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."C"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."D"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."E"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."F"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."G"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."H"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."I"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."J"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."K"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."L"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."M"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."M" END AS "M",
       CASE WHEN base.row_num = 12 THEN (COALESCE((SELECT COALESCE(SUM(data."N"), 0) FROM revenue_14 AS data WHERE data.row_num BETWEEN 3 AND 11), 0)) ELSE base."N" END AS "N"
FROM revenue_14 AS base;

-- Revenue!B15:M17: UK&I  (table, 36 cells)
--   Excel: =SUMIFS(Orders!$K:$K,Orders!$F:$F,B$2,Orders!$G:$G,$A15)
--   pattern (R1C1): =SUMIFS(Orders!C11:C11,Orders!C6:C6,R2C,Orders!C7:C7,RC1)
CREATE TABLE revenue_16 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_b2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."B" END AS "B",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_c2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."C" END AS "C",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_d2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."D" END AS "D",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_e2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."E" END AS "E",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_f2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."F" END AS "F",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_g2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."G" END AS "G",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_h2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."H" END AS "H",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_i2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."I" END AS "I",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_j2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."J" END AS "J",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_k2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."K" END AS "K",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_l2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."L" END AS "L",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN COALESCE((SELECT SUM(data."K") FROM orders_08 AS data WHERE (data."F" = params.revenue_m2) AND (lower(data."G_txt") = lower(base."A_txt"))), 0) ELSE base."M" END AS "M",
       base."N"
FROM revenue_15 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM revenue_15 WHERE row_num = 2) AS revenue_b2,
           (SELECT "C" FROM revenue_15 WHERE row_num = 2) AS revenue_c2,
           (SELECT "D" FROM revenue_15 WHERE row_num = 2) AS revenue_d2,
           (SELECT "E" FROM revenue_15 WHERE row_num = 2) AS revenue_e2,
           (SELECT "F" FROM revenue_15 WHERE row_num = 2) AS revenue_f2,
           (SELECT "G" FROM revenue_15 WHERE row_num = 2) AS revenue_g2,
           (SELECT "H" FROM revenue_15 WHERE row_num = 2) AS revenue_h2,
           (SELECT "I" FROM revenue_15 WHERE row_num = 2) AS revenue_i2,
           (SELECT "J" FROM revenue_15 WHERE row_num = 2) AS revenue_j2,
           (SELECT "K" FROM revenue_15 WHERE row_num = 2) AS revenue_k2,
           (SELECT "L" FROM revenue_15 WHERE row_num = 2) AS revenue_l2,
           (SELECT "M" FROM revenue_15 WHERE row_num = 2) AS revenue_m2
) AS params;

-- Revenue!N15:N17  (column, 3 cells)
--   Excel: =SUM(B15:M15)
--   pattern (R1C1): =SUM(RC[-12]:RC[-1])
CREATE TABLE revenue_17 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M",
       CASE WHEN base.row_num BETWEEN 15 AND 17 THEN (COALESCE((SELECT COALESCE(SUM(data."B"), 0) + COALESCE(SUM(data."C"), 0) + COALESCE(SUM(data."D"), 0) + COALESCE(SUM(data."E"), 0) + COALESCE(SUM(data."F"), 0) + COALESCE(SUM(data."G"), 0) + COALESCE(SUM(data."H"), 0) + COALESCE(SUM(data."I"), 0) + COALESCE(SUM(data."J"), 0) + COALESCE(SUM(data."K"), 0) + COALESCE(SUM(data."L"), 0) + COALESCE(SUM(data."M"), 0) FROM revenue_16 AS data WHERE data.row_num = base.row_num), 0)) ELSE base."N" END AS "N"
FROM revenue_16 AS base;

-- Revenue!B19:M19: Orders in month  (row, 12 cells)
--   Excel: =COUNTIF(Orders!$F:$F,B$2)
--   pattern (R1C1): =COUNTIF(Orders!C6:C6,R2C)
CREATE TABLE revenue_18 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_b2)) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_c2)) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_d2)) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_e2)) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_f2)) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_g2)) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_h2)) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_i2)) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_j2)) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_k2)) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_l2)) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 19 THEN (SELECT COUNT(*) FROM orders_08 AS data WHERE (data."F" = params.revenue_m2)) ELSE base."M" END AS "M",
       base."N"
FROM revenue_17 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM revenue_17 WHERE row_num = 2) AS revenue_b2,
           (SELECT "C" FROM revenue_17 WHERE row_num = 2) AS revenue_c2,
           (SELECT "D" FROM revenue_17 WHERE row_num = 2) AS revenue_d2,
           (SELECT "E" FROM revenue_17 WHERE row_num = 2) AS revenue_e2,
           (SELECT "F" FROM revenue_17 WHERE row_num = 2) AS revenue_f2,
           (SELECT "G" FROM revenue_17 WHERE row_num = 2) AS revenue_g2,
           (SELECT "H" FROM revenue_17 WHERE row_num = 2) AS revenue_h2,
           (SELECT "I" FROM revenue_17 WHERE row_num = 2) AS revenue_i2,
           (SELECT "J" FROM revenue_17 WHERE row_num = 2) AS revenue_j2,
           (SELECT "K" FROM revenue_17 WHERE row_num = 2) AS revenue_k2,
           (SELECT "L" FROM revenue_17 WHERE row_num = 2) AS revenue_l2,
           (SELECT "M" FROM revenue_17 WHERE row_num = 2) AS revenue_m2
) AS params;

-- Costs!C2:M2 reads its own earlier cells; computed cell by cell below

-- Costs!C2: Month index  (single, 1 cells)
--   Excel: =B2+1
CREATE TABLE costs_01 AS
SELECT base.row_num,
       base."B",
       CASE WHEN base.row_num = 2 THEN (COALESCE(base."B", 0) + 1) ELSE NULL END AS "C"
FROM costs_00 AS base;

-- Costs!D2: Month index  (single, 1 cells)
--   Excel: =C2+1
CREATE TABLE costs_02 AS
SELECT base.row_num,
       base."B",
       base."C",
       CASE WHEN base.row_num = 2 THEN (base."C" + 1) ELSE NULL END AS "D"
FROM costs_01 AS base;

-- Costs!E2: Month index  (single, 1 cells)
--   Excel: =D2+1
CREATE TABLE costs_03 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       CASE WHEN base.row_num = 2 THEN (base."D" + 1) ELSE NULL END AS "E"
FROM costs_02 AS base;

-- Costs!F2: Month index  (single, 1 cells)
--   Excel: =E2+1
CREATE TABLE costs_04 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       CASE WHEN base.row_num = 2 THEN (base."E" + 1) ELSE NULL END AS "F"
FROM costs_03 AS base;

-- Costs!G2: Month index  (single, 1 cells)
--   Excel: =F2+1
CREATE TABLE costs_05 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       CASE WHEN base.row_num = 2 THEN (base."F" + 1) ELSE NULL END AS "G"
FROM costs_04 AS base;

-- Costs!H2: Month index  (single, 1 cells)
--   Excel: =G2+1
CREATE TABLE costs_06 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       CASE WHEN base.row_num = 2 THEN (base."G" + 1) ELSE NULL END AS "H"
FROM costs_05 AS base;

-- Costs!I2: Month index  (single, 1 cells)
--   Excel: =H2+1
CREATE TABLE costs_07 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       CASE WHEN base.row_num = 2 THEN (base."H" + 1) ELSE NULL END AS "I"
FROM costs_06 AS base;

-- Costs!J2: Month index  (single, 1 cells)
--   Excel: =I2+1
CREATE TABLE costs_08 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       CASE WHEN base.row_num = 2 THEN (base."I" + 1) ELSE NULL END AS "J"
FROM costs_07 AS base;

-- Costs!K2: Month index  (single, 1 cells)
--   Excel: =J2+1
CREATE TABLE costs_09 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       CASE WHEN base.row_num = 2 THEN (base."J" + 1) ELSE NULL END AS "K"
FROM costs_08 AS base;

-- Costs!L2: Month index  (single, 1 cells)
--   Excel: =K2+1
CREATE TABLE costs_10 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       CASE WHEN base.row_num = 2 THEN (base."K" + 1) ELSE NULL END AS "L"
FROM costs_09 AS base;

-- Costs!M2: Month index  (single, 1 cells)
--   Excel: =L2+1
CREATE TABLE costs_11 AS
SELECT base.row_num,
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       CASE WHEN base.row_num = 2 THEN (base."L" + 1) ELSE NULL END AS "M"
FROM costs_10 AS base;

-- Costs!B3:M3: Headcount  (row, 12 cells)
--   Excel: =ROUND(HeadcountStart*(1+GrowthRate)^(B2-1),0)
--   pattern (R1C1): =ROUND(HeadcountStart*(1+GrowthRate)^(R[-1]C-1),0)
CREATE TABLE costs_12 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (COALESCE(params.costs_b2, 0) - 1))), 0) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_c2 - 1))), 0) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_d2 - 1))), 0) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_e2 - 1))), 0) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_f2 - 1))), 0) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_g2 - 1))), 0) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_h2 - 1))), 0) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_i2 - 1))), 0) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_j2 - 1))), 0) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_k2 - 1))), 0) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_l2 - 1))), 0) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 3 THEN xl_round((COALESCE(params.headcount_start, 0) * xl_power((1 + COALESCE(params.growth_rate, 0)), (params.costs_m2 - 1))), 0) ELSE base."M" END AS "M"
FROM costs_11 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM assumptions_01 WHERE row_num = 8) AS headcount_start,
           (SELECT "B" FROM assumptions_01 WHERE row_num = 5) AS growth_rate,
           (SELECT "B" FROM costs_11 WHERE row_num = 2) AS costs_b2,
           (SELECT "C" FROM costs_11 WHERE row_num = 2) AS costs_c2,
           (SELECT "D" FROM costs_11 WHERE row_num = 2) AS costs_d2,
           (SELECT "E" FROM costs_11 WHERE row_num = 2) AS costs_e2,
           (SELECT "F" FROM costs_11 WHERE row_num = 2) AS costs_f2,
           (SELECT "G" FROM costs_11 WHERE row_num = 2) AS costs_g2,
           (SELECT "H" FROM costs_11 WHERE row_num = 2) AS costs_h2,
           (SELECT "I" FROM costs_11 WHERE row_num = 2) AS costs_i2,
           (SELECT "J" FROM costs_11 WHERE row_num = 2) AS costs_j2,
           (SELECT "K" FROM costs_11 WHERE row_num = 2) AS costs_k2,
           (SELECT "L" FROM costs_11 WHERE row_num = 2) AS costs_l2,
           (SELECT "M" FROM costs_11 WHERE row_num = 2) AS costs_m2
) AS params;

-- Costs!B4:M4: Salary cost  (row, 12 cells)
--   Excel: =B3*AvgSalary/12
--   pattern (R1C1): =R[-1]C*AvgSalary/12
CREATE TABLE costs_13 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 4 THEN ((params.costs_b3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 4 THEN ((params.costs_c3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 4 THEN ((params.costs_d3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 4 THEN ((params.costs_e3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 4 THEN ((params.costs_f3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 4 THEN ((params.costs_g3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 4 THEN ((params.costs_h3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 4 THEN ((params.costs_i3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 4 THEN ((params.costs_j3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 4 THEN ((params.costs_k3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 4 THEN ((params.costs_l3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 4 THEN ((params.costs_m3 * COALESCE(params.avg_salary, 0)) / NULLIF(12, 0)) ELSE base."M" END AS "M"
FROM costs_12 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM costs_12 WHERE row_num = 3) AS costs_b3,
           (SELECT "B" FROM assumptions_01 WHERE row_num = 9) AS avg_salary,
           (SELECT "C" FROM costs_12 WHERE row_num = 3) AS costs_c3,
           (SELECT "D" FROM costs_12 WHERE row_num = 3) AS costs_d3,
           (SELECT "E" FROM costs_12 WHERE row_num = 3) AS costs_e3,
           (SELECT "F" FROM costs_12 WHERE row_num = 3) AS costs_f3,
           (SELECT "G" FROM costs_12 WHERE row_num = 3) AS costs_g3,
           (SELECT "H" FROM costs_12 WHERE row_num = 3) AS costs_h3,
           (SELECT "I" FROM costs_12 WHERE row_num = 3) AS costs_i3,
           (SELECT "J" FROM costs_12 WHERE row_num = 3) AS costs_j3,
           (SELECT "K" FROM costs_12 WHERE row_num = 3) AS costs_k3,
           (SELECT "L" FROM costs_12 WHERE row_num = 3) AS costs_l3,
           (SELECT "M" FROM costs_12 WHERE row_num = 3) AS costs_m3
) AS params;

-- Costs!B5:M5: Rent  (row, 12 cells)
--   Excel: =MonthlyRent
--   pattern (R1C1): =MonthlyRent
CREATE TABLE costs_14 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 5 THEN COALESCE(params.monthly_rent, 0) ELSE base."M" END AS "M"
FROM costs_13 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM assumptions_01 WHERE row_num = 10) AS monthly_rent
) AS params;

-- Costs!B6:M6: Total costs  (row, 12 cells)
--   Excel: =SUM(B4:B5)
--   pattern (R1C1): =SUM(R[-2]C:R[-1]C)
CREATE TABLE costs_15 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."B"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."C"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."D"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."E"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."F"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."G"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."H"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."I"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."J"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."K"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."L"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 6 THEN (COALESCE((SELECT COALESCE(SUM(data."M"), 0) FROM costs_14 AS data WHERE data.row_num BETWEEN 4 AND 5), 0)) ELSE base."M" END AS "M"
FROM costs_14 AS base;

-- 'P&L'!B2:M2: Month  (row, 12 cells)
--   Excel: =Revenue!B2
--   pattern (R1C1): =Revenue!RC
CREATE TABLE p_l_01 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 2 THEN params.revenue_b2 ELSE NULL END AS "B",
       CASE WHEN base.row_num = 2 THEN params.revenue_c2 ELSE NULL END AS "C",
       CASE WHEN base.row_num = 2 THEN params.revenue_d2 ELSE NULL END AS "D",
       CASE WHEN base.row_num = 2 THEN params.revenue_e2 ELSE NULL END AS "E",
       CASE WHEN base.row_num = 2 THEN params.revenue_f2 ELSE NULL END AS "F",
       CASE WHEN base.row_num = 2 THEN params.revenue_g2 ELSE NULL END AS "G",
       CASE WHEN base.row_num = 2 THEN params.revenue_h2 ELSE NULL END AS "H",
       CASE WHEN base.row_num = 2 THEN params.revenue_i2 ELSE NULL END AS "I",
       CASE WHEN base.row_num = 2 THEN params.revenue_j2 ELSE NULL END AS "J",
       CASE WHEN base.row_num = 2 THEN params.revenue_k2 ELSE NULL END AS "K",
       CASE WHEN base.row_num = 2 THEN params.revenue_l2 ELSE NULL END AS "L",
       CASE WHEN base.row_num = 2 THEN params.revenue_m2 ELSE NULL END AS "M"
FROM p_l_00 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM revenue_18 WHERE row_num = 2) AS revenue_b2,
           (SELECT "C" FROM revenue_18 WHERE row_num = 2) AS revenue_c2,
           (SELECT "D" FROM revenue_18 WHERE row_num = 2) AS revenue_d2,
           (SELECT "E" FROM revenue_18 WHERE row_num = 2) AS revenue_e2,
           (SELECT "F" FROM revenue_18 WHERE row_num = 2) AS revenue_f2,
           (SELECT "G" FROM revenue_18 WHERE row_num = 2) AS revenue_g2,
           (SELECT "H" FROM revenue_18 WHERE row_num = 2) AS revenue_h2,
           (SELECT "I" FROM revenue_18 WHERE row_num = 2) AS revenue_i2,
           (SELECT "J" FROM revenue_18 WHERE row_num = 2) AS revenue_j2,
           (SELECT "K" FROM revenue_18 WHERE row_num = 2) AS revenue_k2,
           (SELECT "L" FROM revenue_18 WHERE row_num = 2) AS revenue_l2,
           (SELECT "M" FROM revenue_18 WHERE row_num = 2) AS revenue_m2
) AS params;

-- 'P&L'!B3:M3: Revenue  (row, 12 cells)
--   Excel: =Revenue!B12
--   pattern (R1C1): =Revenue!R[9]C
CREATE TABLE p_l_02 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 3 THEN params.revenue_b12 ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 3 THEN params.revenue_c12 ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 3 THEN params.revenue_d12 ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 3 THEN params.revenue_e12 ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 3 THEN params.revenue_f12 ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 3 THEN params.revenue_g12 ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 3 THEN params.revenue_h12 ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 3 THEN params.revenue_i12 ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 3 THEN params.revenue_j12 ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 3 THEN params.revenue_k12 ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 3 THEN params.revenue_l12 ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 3 THEN params.revenue_m12 ELSE base."M" END AS "M"
FROM p_l_01 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM revenue_18 WHERE row_num = 12) AS revenue_b12,
           (SELECT "C" FROM revenue_18 WHERE row_num = 12) AS revenue_c12,
           (SELECT "D" FROM revenue_18 WHERE row_num = 12) AS revenue_d12,
           (SELECT "E" FROM revenue_18 WHERE row_num = 12) AS revenue_e12,
           (SELECT "F" FROM revenue_18 WHERE row_num = 12) AS revenue_f12,
           (SELECT "G" FROM revenue_18 WHERE row_num = 12) AS revenue_g12,
           (SELECT "H" FROM revenue_18 WHERE row_num = 12) AS revenue_h12,
           (SELECT "I" FROM revenue_18 WHERE row_num = 12) AS revenue_i12,
           (SELECT "J" FROM revenue_18 WHERE row_num = 12) AS revenue_j12,
           (SELECT "K" FROM revenue_18 WHERE row_num = 12) AS revenue_k12,
           (SELECT "L" FROM revenue_18 WHERE row_num = 12) AS revenue_l12,
           (SELECT "M" FROM revenue_18 WHERE row_num = 12) AS revenue_m12
) AS params;

-- 'P&L'!B4:M4: Costs  (row, 12 cells)
--   Excel: =Costs!B6
--   pattern (R1C1): =Costs!R[2]C
CREATE TABLE p_l_03 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 4 THEN params.costs_b6 ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 4 THEN params.costs_c6 ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 4 THEN params.costs_d6 ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 4 THEN params.costs_e6 ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 4 THEN params.costs_f6 ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 4 THEN params.costs_g6 ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 4 THEN params.costs_h6 ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 4 THEN params.costs_i6 ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 4 THEN params.costs_j6 ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 4 THEN params.costs_k6 ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 4 THEN params.costs_l6 ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 4 THEN params.costs_m6 ELSE base."M" END AS "M"
FROM p_l_02 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM costs_15 WHERE row_num = 6) AS costs_b6,
           (SELECT "C" FROM costs_15 WHERE row_num = 6) AS costs_c6,
           (SELECT "D" FROM costs_15 WHERE row_num = 6) AS costs_d6,
           (SELECT "E" FROM costs_15 WHERE row_num = 6) AS costs_e6,
           (SELECT "F" FROM costs_15 WHERE row_num = 6) AS costs_f6,
           (SELECT "G" FROM costs_15 WHERE row_num = 6) AS costs_g6,
           (SELECT "H" FROM costs_15 WHERE row_num = 6) AS costs_h6,
           (SELECT "I" FROM costs_15 WHERE row_num = 6) AS costs_i6,
           (SELECT "J" FROM costs_15 WHERE row_num = 6) AS costs_j6,
           (SELECT "K" FROM costs_15 WHERE row_num = 6) AS costs_k6,
           (SELECT "L" FROM costs_15 WHERE row_num = 6) AS costs_l6,
           (SELECT "M" FROM costs_15 WHERE row_num = 6) AS costs_m6
) AS params;

-- 'P&L'!B5:M5: Gross margin  (row, 12 cells)
--   Excel: =B3-B4
--   pattern (R1C1): =R[-2]C-R[-1]C
CREATE TABLE p_l_04 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 5 THEN (params.p_l_b3 - params.p_l_b4) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 5 THEN (params.p_l_c3 - params.p_l_c4) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 5 THEN (params.p_l_d3 - params.p_l_d4) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 5 THEN (params.p_l_e3 - params.p_l_e4) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 5 THEN (params.p_l_f3 - params.p_l_f4) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 5 THEN (params.p_l_g3 - params.p_l_g4) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 5 THEN (params.p_l_h3 - params.p_l_h4) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 5 THEN (params.p_l_i3 - params.p_l_i4) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 5 THEN (params.p_l_j3 - params.p_l_j4) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 5 THEN (params.p_l_k3 - params.p_l_k4) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 5 THEN (params.p_l_l3 - params.p_l_l4) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 5 THEN (params.p_l_m3 - params.p_l_m4) ELSE base."M" END AS "M"
FROM p_l_03 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM p_l_03 WHERE row_num = 3) AS p_l_b3,
           (SELECT "B" FROM p_l_03 WHERE row_num = 4) AS p_l_b4,
           (SELECT "C" FROM p_l_03 WHERE row_num = 3) AS p_l_c3,
           (SELECT "C" FROM p_l_03 WHERE row_num = 4) AS p_l_c4,
           (SELECT "D" FROM p_l_03 WHERE row_num = 3) AS p_l_d3,
           (SELECT "D" FROM p_l_03 WHERE row_num = 4) AS p_l_d4,
           (SELECT "E" FROM p_l_03 WHERE row_num = 3) AS p_l_e3,
           (SELECT "E" FROM p_l_03 WHERE row_num = 4) AS p_l_e4,
           (SELECT "F" FROM p_l_03 WHERE row_num = 3) AS p_l_f3,
           (SELECT "F" FROM p_l_03 WHERE row_num = 4) AS p_l_f4,
           (SELECT "G" FROM p_l_03 WHERE row_num = 3) AS p_l_g3,
           (SELECT "G" FROM p_l_03 WHERE row_num = 4) AS p_l_g4,
           (SELECT "H" FROM p_l_03 WHERE row_num = 3) AS p_l_h3,
           (SELECT "H" FROM p_l_03 WHERE row_num = 4) AS p_l_h4,
           (SELECT "I" FROM p_l_03 WHERE row_num = 3) AS p_l_i3,
           (SELECT "I" FROM p_l_03 WHERE row_num = 4) AS p_l_i4,
           (SELECT "J" FROM p_l_03 WHERE row_num = 3) AS p_l_j3,
           (SELECT "J" FROM p_l_03 WHERE row_num = 4) AS p_l_j4,
           (SELECT "K" FROM p_l_03 WHERE row_num = 3) AS p_l_k3,
           (SELECT "K" FROM p_l_03 WHERE row_num = 4) AS p_l_k4,
           (SELECT "L" FROM p_l_03 WHERE row_num = 3) AS p_l_l3,
           (SELECT "L" FROM p_l_03 WHERE row_num = 4) AS p_l_l4,
           (SELECT "M" FROM p_l_03 WHERE row_num = 3) AS p_l_m3,
           (SELECT "M" FROM p_l_03 WHERE row_num = 4) AS p_l_m4
) AS params;

-- 'P&L'!B6:M6: Margin %  (row, 12 cells)
--   Excel: =IFERROR(B5/B3,"n/a")
--   pattern (R1C1): =IFERROR(R[-1]C/R[-3]C,"n/a")
CREATE TABLE p_l_05 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_b5 / NULLIF(params.p_l_b3, 0)), NULL) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_c5 / NULLIF(params.p_l_c3, 0)), NULL) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_d5 / NULLIF(params.p_l_d3, 0)), NULL) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_e5 / NULLIF(params.p_l_e3, 0)), NULL) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_f5 / NULLIF(params.p_l_f3, 0)), NULL) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_g5 / NULLIF(params.p_l_g3, 0)), NULL) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_h5 / NULLIF(params.p_l_h3, 0)), NULL) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_i5 / NULLIF(params.p_l_i3, 0)), NULL) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_j5 / NULLIF(params.p_l_j3, 0)), NULL) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_k5 / NULLIF(params.p_l_k3, 0)), NULL) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_l5 / NULLIF(params.p_l_l3, 0)), NULL) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 6 THEN COALESCE((params.p_l_m5 / NULLIF(params.p_l_m3, 0)), NULL) ELSE base."M" END AS "M"
FROM p_l_04 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM p_l_04 WHERE row_num = 5) AS p_l_b5,
           (SELECT "B" FROM p_l_04 WHERE row_num = 3) AS p_l_b3,
           (SELECT "C" FROM p_l_04 WHERE row_num = 5) AS p_l_c5,
           (SELECT "C" FROM p_l_04 WHERE row_num = 3) AS p_l_c3,
           (SELECT "D" FROM p_l_04 WHERE row_num = 5) AS p_l_d5,
           (SELECT "D" FROM p_l_04 WHERE row_num = 3) AS p_l_d3,
           (SELECT "E" FROM p_l_04 WHERE row_num = 5) AS p_l_e5,
           (SELECT "E" FROM p_l_04 WHERE row_num = 3) AS p_l_e3,
           (SELECT "F" FROM p_l_04 WHERE row_num = 5) AS p_l_f5,
           (SELECT "F" FROM p_l_04 WHERE row_num = 3) AS p_l_f3,
           (SELECT "G" FROM p_l_04 WHERE row_num = 5) AS p_l_g5,
           (SELECT "G" FROM p_l_04 WHERE row_num = 3) AS p_l_g3,
           (SELECT "H" FROM p_l_04 WHERE row_num = 5) AS p_l_h5,
           (SELECT "H" FROM p_l_04 WHERE row_num = 3) AS p_l_h3,
           (SELECT "I" FROM p_l_04 WHERE row_num = 5) AS p_l_i5,
           (SELECT "I" FROM p_l_04 WHERE row_num = 3) AS p_l_i3,
           (SELECT "J" FROM p_l_04 WHERE row_num = 5) AS p_l_j5,
           (SELECT "J" FROM p_l_04 WHERE row_num = 3) AS p_l_j3,
           (SELECT "K" FROM p_l_04 WHERE row_num = 5) AS p_l_k5,
           (SELECT "K" FROM p_l_04 WHERE row_num = 3) AS p_l_k3,
           (SELECT "L" FROM p_l_04 WHERE row_num = 5) AS p_l_l5,
           (SELECT "L" FROM p_l_04 WHERE row_num = 3) AS p_l_l3,
           (SELECT "M" FROM p_l_04 WHERE row_num = 5) AS p_l_m5,
           (SELECT "M" FROM p_l_04 WHERE row_num = 3) AS p_l_m3
) AS params;

-- 'P&L'!B7:M7: Tax  (row, 12 cells)
--   Excel: =MAX(0,B5)*TaxRate
--   pattern (R1C1): =MAX(0,R[-2]C)*TaxRate
CREATE TABLE p_l_06 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_b5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_c5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_d5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_e5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_f5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_g5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_h5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_i5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_j5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_k5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_l5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 7 THEN (COALESCE(greatest(0, params.p_l_m5), 0) * COALESCE(params.tax_rate, 0)) ELSE base."M" END AS "M"
FROM p_l_05 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM p_l_05 WHERE row_num = 5) AS p_l_b5,
           (SELECT "B" FROM assumptions_01 WHERE row_num = 6) AS tax_rate,
           (SELECT "C" FROM p_l_05 WHERE row_num = 5) AS p_l_c5,
           (SELECT "D" FROM p_l_05 WHERE row_num = 5) AS p_l_d5,
           (SELECT "E" FROM p_l_05 WHERE row_num = 5) AS p_l_e5,
           (SELECT "F" FROM p_l_05 WHERE row_num = 5) AS p_l_f5,
           (SELECT "G" FROM p_l_05 WHERE row_num = 5) AS p_l_g5,
           (SELECT "H" FROM p_l_05 WHERE row_num = 5) AS p_l_h5,
           (SELECT "I" FROM p_l_05 WHERE row_num = 5) AS p_l_i5,
           (SELECT "J" FROM p_l_05 WHERE row_num = 5) AS p_l_j5,
           (SELECT "K" FROM p_l_05 WHERE row_num = 5) AS p_l_k5,
           (SELECT "L" FROM p_l_05 WHERE row_num = 5) AS p_l_l5,
           (SELECT "M" FROM p_l_05 WHERE row_num = 5) AS p_l_m5
) AS params;

-- 'P&L'!B8:M8: Net result  (row, 12 cells)
--   Excel: =B5-B7
--   pattern (R1C1): =R[-3]C-R[-1]C
CREATE TABLE p_l_07 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 8 THEN (params.p_l_b5 - params.p_l_b7) ELSE base."B" END AS "B",
       CASE WHEN base.row_num = 8 THEN (params.p_l_c5 - params.p_l_c7) ELSE base."C" END AS "C",
       CASE WHEN base.row_num = 8 THEN (params.p_l_d5 - params.p_l_d7) ELSE base."D" END AS "D",
       CASE WHEN base.row_num = 8 THEN (params.p_l_e5 - params.p_l_e7) ELSE base."E" END AS "E",
       CASE WHEN base.row_num = 8 THEN (params.p_l_f5 - params.p_l_f7) ELSE base."F" END AS "F",
       CASE WHEN base.row_num = 8 THEN (params.p_l_g5 - params.p_l_g7) ELSE base."G" END AS "G",
       CASE WHEN base.row_num = 8 THEN (params.p_l_h5 - params.p_l_h7) ELSE base."H" END AS "H",
       CASE WHEN base.row_num = 8 THEN (params.p_l_i5 - params.p_l_i7) ELSE base."I" END AS "I",
       CASE WHEN base.row_num = 8 THEN (params.p_l_j5 - params.p_l_j7) ELSE base."J" END AS "J",
       CASE WHEN base.row_num = 8 THEN (params.p_l_k5 - params.p_l_k7) ELSE base."K" END AS "K",
       CASE WHEN base.row_num = 8 THEN (params.p_l_l5 - params.p_l_l7) ELSE base."L" END AS "L",
       CASE WHEN base.row_num = 8 THEN (params.p_l_m5 - params.p_l_m7) ELSE base."M" END AS "M"
FROM p_l_06 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM p_l_06 WHERE row_num = 5) AS p_l_b5,
           (SELECT "B" FROM p_l_06 WHERE row_num = 7) AS p_l_b7,
           (SELECT "C" FROM p_l_06 WHERE row_num = 5) AS p_l_c5,
           (SELECT "C" FROM p_l_06 WHERE row_num = 7) AS p_l_c7,
           (SELECT "D" FROM p_l_06 WHERE row_num = 5) AS p_l_d5,
           (SELECT "D" FROM p_l_06 WHERE row_num = 7) AS p_l_d7,
           (SELECT "E" FROM p_l_06 WHERE row_num = 5) AS p_l_e5,
           (SELECT "E" FROM p_l_06 WHERE row_num = 7) AS p_l_e7,
           (SELECT "F" FROM p_l_06 WHERE row_num = 5) AS p_l_f5,
           (SELECT "F" FROM p_l_06 WHERE row_num = 7) AS p_l_f7,
           (SELECT "G" FROM p_l_06 WHERE row_num = 5) AS p_l_g5,
           (SELECT "G" FROM p_l_06 WHERE row_num = 7) AS p_l_g7,
           (SELECT "H" FROM p_l_06 WHERE row_num = 5) AS p_l_h5,
           (SELECT "H" FROM p_l_06 WHERE row_num = 7) AS p_l_h7,
           (SELECT "I" FROM p_l_06 WHERE row_num = 5) AS p_l_i5,
           (SELECT "I" FROM p_l_06 WHERE row_num = 7) AS p_l_i7,
           (SELECT "J" FROM p_l_06 WHERE row_num = 5) AS p_l_j5,
           (SELECT "J" FROM p_l_06 WHERE row_num = 7) AS p_l_j7,
           (SELECT "K" FROM p_l_06 WHERE row_num = 5) AS p_l_k5,
           (SELECT "K" FROM p_l_06 WHERE row_num = 7) AS p_l_k7,
           (SELECT "L" FROM p_l_06 WHERE row_num = 5) AS p_l_l5,
           (SELECT "L" FROM p_l_06 WHERE row_num = 7) AS p_l_l7,
           (SELECT "M" FROM p_l_06 WHERE row_num = 5) AS p_l_m5,
           (SELECT "M" FROM p_l_06 WHERE row_num = 7) AS p_l_m7
) AS params;

-- 'P&L'!B9: Net YTD  (single, 1 cells)
--   Excel: =B8
CREATE TABLE p_l_08 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num = 9 THEN params.p_l_b8 ELSE base."B" END AS "B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_07 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM p_l_07 WHERE row_num = 8) AS p_l_b8
) AS params;

-- 'P&L'!C9:M9 reads its own earlier cells; computed cell by cell below

-- 'P&L'!C9: Net YTD  (single, 1 cells)
--   Excel: =B9+C8
CREATE TABLE p_l_09 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       CASE WHEN base.row_num = 9 THEN (base."B" + params.p_l_c8) ELSE base."C" END AS "C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_08 AS base
CROSS JOIN (
    SELECT (SELECT "C" FROM p_l_08 WHERE row_num = 8) AS p_l_c8
) AS params;

-- 'P&L'!D9: Net YTD  (single, 1 cells)
--   Excel: =C9+D8
CREATE TABLE p_l_10 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       CASE WHEN base.row_num = 9 THEN (base."C" + params.p_l_d8) ELSE base."D" END AS "D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_09 AS base
CROSS JOIN (
    SELECT (SELECT "D" FROM p_l_09 WHERE row_num = 8) AS p_l_d8
) AS params;

-- 'P&L'!E9: Net YTD  (single, 1 cells)
--   Excel: =D9+E8
CREATE TABLE p_l_11 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       CASE WHEN base.row_num = 9 THEN (base."D" + params.p_l_e8) ELSE base."E" END AS "E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_10 AS base
CROSS JOIN (
    SELECT (SELECT "E" FROM p_l_10 WHERE row_num = 8) AS p_l_e8
) AS params;

-- 'P&L'!F9: Net YTD  (single, 1 cells)
--   Excel: =E9+F8
CREATE TABLE p_l_12 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       CASE WHEN base.row_num = 9 THEN (base."E" + params.p_l_f8) ELSE base."F" END AS "F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_11 AS base
CROSS JOIN (
    SELECT (SELECT "F" FROM p_l_11 WHERE row_num = 8) AS p_l_f8
) AS params;

-- 'P&L'!G9: Net YTD  (single, 1 cells)
--   Excel: =F9+G8
CREATE TABLE p_l_13 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       CASE WHEN base.row_num = 9 THEN (base."F" + params.p_l_g8) ELSE base."G" END AS "G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_12 AS base
CROSS JOIN (
    SELECT (SELECT "G" FROM p_l_12 WHERE row_num = 8) AS p_l_g8
) AS params;

-- 'P&L'!H9: Net YTD  (single, 1 cells)
--   Excel: =G9+H8
CREATE TABLE p_l_14 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       CASE WHEN base.row_num = 9 THEN (base."G" + params.p_l_h8) ELSE base."H" END AS "H",
       base."I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_13 AS base
CROSS JOIN (
    SELECT (SELECT "H" FROM p_l_13 WHERE row_num = 8) AS p_l_h8
) AS params;

-- 'P&L'!I9: Net YTD  (single, 1 cells)
--   Excel: =H9+I8
CREATE TABLE p_l_15 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       CASE WHEN base.row_num = 9 THEN (base."H" + params.p_l_i8) ELSE base."I" END AS "I",
       base."J",
       base."K",
       base."L",
       base."M"
FROM p_l_14 AS base
CROSS JOIN (
    SELECT (SELECT "I" FROM p_l_14 WHERE row_num = 8) AS p_l_i8
) AS params;

-- 'P&L'!J9: Net YTD  (single, 1 cells)
--   Excel: =I9+J8
CREATE TABLE p_l_16 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       CASE WHEN base.row_num = 9 THEN (base."I" + params.p_l_j8) ELSE base."J" END AS "J",
       base."K",
       base."L",
       base."M"
FROM p_l_15 AS base
CROSS JOIN (
    SELECT (SELECT "J" FROM p_l_15 WHERE row_num = 8) AS p_l_j8
) AS params;

-- 'P&L'!K9: Net YTD  (single, 1 cells)
--   Excel: =J9+K8
CREATE TABLE p_l_17 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       CASE WHEN base.row_num = 9 THEN (base."J" + params.p_l_k8) ELSE base."K" END AS "K",
       base."L",
       base."M"
FROM p_l_16 AS base
CROSS JOIN (
    SELECT (SELECT "K" FROM p_l_16 WHERE row_num = 8) AS p_l_k8
) AS params;

-- 'P&L'!L9: Net YTD  (single, 1 cells)
--   Excel: =K9+L8
CREATE TABLE p_l_18 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       CASE WHEN base.row_num = 9 THEN (base."K" + params.p_l_l8) ELSE base."L" END AS "L",
       base."M"
FROM p_l_17 AS base
CROSS JOIN (
    SELECT (SELECT "L" FROM p_l_17 WHERE row_num = 8) AS p_l_l8
) AS params;

-- 'P&L'!M9: Net YTD  (single, 1 cells)
--   Excel: =L9+M8
CREATE TABLE p_l_19 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."C",
       base."D",
       base."E",
       base."F",
       base."G",
       base."H",
       base."I",
       base."J",
       base."K",
       base."L",
       CASE WHEN base.row_num = 9 THEN (base."L" + params.p_l_m8) ELSE base."M" END AS "M"
FROM p_l_18 AS base
CROSS JOIN (
    SELECT (SELECT "M" FROM p_l_18 WHERE row_num = 8) AS p_l_m8
) AS params;

-- 'P&L'!B10:M10: Quarter  (row, 12 cells)
--   Excel: ="Q"&ROUNDUP(Costs!B2/3,0)
--   pattern (R1C1): ="Q"&ROUNDUP(Costs!R[-8]C/3,0)
CREATE TABLE p_l_20 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((COALESCE(params.costs_b2, 0) / NULLIF(3, 0)), 0))) ELSE NULL END AS "B_txt",
       base."C",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_c2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "C_txt",
       base."D",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_d2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "D_txt",
       base."E",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_e2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "E_txt",
       base."F",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_f2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "F_txt",
       base."G",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_g2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "G_txt",
       base."H",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_h2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "H_txt",
       base."I",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_i2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "I_txt",
       base."J",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_j2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "J_txt",
       base."K",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_k2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "K_txt",
       base."L",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_l2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "L_txt",
       base."M",
       CASE WHEN base.row_num = 10 THEN concat('Q', xl_text(xl_roundup((params.costs_m2 / NULLIF(3, 0)), 0))) ELSE NULL END AS "M_txt"
FROM p_l_19 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM costs_15 WHERE row_num = 2) AS costs_b2,
           (SELECT "C" FROM costs_15 WHERE row_num = 2) AS costs_c2,
           (SELECT "D" FROM costs_15 WHERE row_num = 2) AS costs_d2,
           (SELECT "E" FROM costs_15 WHERE row_num = 2) AS costs_e2,
           (SELECT "F" FROM costs_15 WHERE row_num = 2) AS costs_f2,
           (SELECT "G" FROM costs_15 WHERE row_num = 2) AS costs_g2,
           (SELECT "H" FROM costs_15 WHERE row_num = 2) AS costs_h2,
           (SELECT "I" FROM costs_15 WHERE row_num = 2) AS costs_i2,
           (SELECT "J" FROM costs_15 WHERE row_num = 2) AS costs_j2,
           (SELECT "K" FROM costs_15 WHERE row_num = 2) AS costs_k2,
           (SELECT "L" FROM costs_15 WHERE row_num = 2) AS costs_l2,
           (SELECT "M" FROM costs_15 WHERE row_num = 2) AS costs_m2
) AS params;

-- 'P&L'!B13:B16: Revenue  (column, 4 cells)
--   Excel: =SUMIFS($B$3:$M$3,$B$10:$M$10,$A13)
--   pattern (R1C1): =SUMIFS(R3C2:R3C13,R10C2:R10C13,RC1)
CREATE TABLE p_l_21 AS
SELECT base.row_num,
       base."A_txt",
       CASE WHEN base.row_num BETWEEN 13 AND 16 THEN COALESCE((SELECT COALESCE(CASE WHEN (lower(row_2."B_txt") = lower(base."A_txt")) THEN row_1."B" END, 0) + COALESCE(CASE WHEN (lower(row_2."C_txt") = lower(base."A_txt")) THEN row_1."C" END, 0) + COALESCE(CASE WHEN (lower(row_2."D_txt") = lower(base."A_txt")) THEN row_1."D" END, 0) + COALESCE(CASE WHEN (lower(row_2."E_txt") = lower(base."A_txt")) THEN row_1."E" END, 0) + COALESCE(CASE WHEN (lower(row_2."F_txt") = lower(base."A_txt")) THEN row_1."F" END, 0) + COALESCE(CASE WHEN (lower(row_2."G_txt") = lower(base."A_txt")) THEN row_1."G" END, 0) + COALESCE(CASE WHEN (lower(row_2."H_txt") = lower(base."A_txt")) THEN row_1."H" END, 0) + COALESCE(CASE WHEN (lower(row_2."I_txt") = lower(base."A_txt")) THEN row_1."I" END, 0) + COALESCE(CASE WHEN (lower(row_2."J_txt") = lower(base."A_txt")) THEN row_1."J" END, 0) + COALESCE(CASE WHEN (lower(row_2."K_txt") = lower(base."A_txt")) THEN row_1."K" END, 0) + COALESCE(CASE WHEN (lower(row_2."L_txt") = lower(base."A_txt")) THEN row_1."L" END, 0) + COALESCE(CASE WHEN (lower(row_2."M_txt") = lower(base."A_txt")) THEN row_1."M" END, 0) FROM p_l_20 AS row_1, p_l_20 AS row_2 WHERE row_1.row_num = 3 AND row_2.row_num = 10), 0) ELSE base."B" END AS "B",
       base."B_txt",
       base."C",
       base."C_txt",
       base."D",
       base."D_txt",
       base."E",
       base."E_txt",
       base."F",
       base."F_txt",
       base."G",
       base."G_txt",
       base."H",
       base."H_txt",
       base."I",
       base."I_txt",
       base."J",
       base."J_txt",
       base."K",
       base."K_txt",
       base."L",
       base."L_txt",
       base."M",
       base."M_txt"
FROM p_l_20 AS base;

-- 'P&L'!C13:C16: Net result  (column, 4 cells)
--   Excel: =SUMIFS($B$8:$M$8,$B$10:$M$10,$A13)
--   pattern (R1C1): =SUMIFS(R8C2:R8C13,R10C2:R10C13,RC1)
CREATE TABLE p_l_22 AS
SELECT base.row_num,
       base."A_txt",
       base."B",
       base."B_txt",
       CASE WHEN base.row_num BETWEEN 13 AND 16 THEN COALESCE((SELECT COALESCE(CASE WHEN (lower(row_2."B_txt") = lower(base."A_txt")) THEN row_1."B" END, 0) + COALESCE(CASE WHEN (lower(row_2."C_txt") = lower(base."A_txt")) THEN row_1."C" END, 0) + COALESCE(CASE WHEN (lower(row_2."D_txt") = lower(base."A_txt")) THEN row_1."D" END, 0) + COALESCE(CASE WHEN (lower(row_2."E_txt") = lower(base."A_txt")) THEN row_1."E" END, 0) + COALESCE(CASE WHEN (lower(row_2."F_txt") = lower(base."A_txt")) THEN row_1."F" END, 0) + COALESCE(CASE WHEN (lower(row_2."G_txt") = lower(base."A_txt")) THEN row_1."G" END, 0) + COALESCE(CASE WHEN (lower(row_2."H_txt") = lower(base."A_txt")) THEN row_1."H" END, 0) + COALESCE(CASE WHEN (lower(row_2."I_txt") = lower(base."A_txt")) THEN row_1."I" END, 0) + COALESCE(CASE WHEN (lower(row_2."J_txt") = lower(base."A_txt")) THEN row_1."J" END, 0) + COALESCE(CASE WHEN (lower(row_2."K_txt") = lower(base."A_txt")) THEN row_1."K" END, 0) + COALESCE(CASE WHEN (lower(row_2."L_txt") = lower(base."A_txt")) THEN row_1."L" END, 0) + COALESCE(CASE WHEN (lower(row_2."M_txt") = lower(base."A_txt")) THEN row_1."M" END, 0) FROM p_l_21 AS row_1, p_l_21 AS row_2 WHERE row_1.row_num = 8 AND row_2.row_num = 10), 0) ELSE base."C" END AS "C",
       base."C_txt",
       base."D",
       base."D_txt",
       base."E",
       base."E_txt",
       base."F",
       base."F_txt",
       base."G",
       base."G_txt",
       base."H",
       base."H_txt",
       base."I",
       base."I_txt",
       base."J",
       base."J_txt",
       base."K",
       base."K_txt",
       base."L",
       base."L_txt",
       base."M",
       base."M_txt"
FROM p_l_21 AS base;

-- Dashboard!B3: Total net revenue  (single, 1 cells)
--   Excel: =SUM(Revenue!B12:M12)
CREATE TABLE dashboard_01 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 3 THEN (COALESCE((SELECT COALESCE(SUM(data."B"), 0) + COALESCE(SUM(data."C"), 0) + COALESCE(SUM(data."D"), 0) + COALESCE(SUM(data."E"), 0) + COALESCE(SUM(data."F"), 0) + COALESCE(SUM(data."G"), 0) + COALESCE(SUM(data."H"), 0) + COALESCE(SUM(data."I"), 0) + COALESCE(SUM(data."J"), 0) + COALESCE(SUM(data."K"), 0) + COALESCE(SUM(data."L"), 0) + COALESCE(SUM(data."M"), 0) FROM revenue_18 AS data WHERE data.row_num = 12), 0)) ELSE NULL END AS "B"
FROM dashboard_00 AS base;

-- Dashboard!B4: Total net result  (single, 1 cells)
--   Excel: =SUM('P&L'!B8:M8)+Scratch!B2
CREATE TABLE dashboard_02 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 4 THEN ((COALESCE((SELECT COALESCE(SUM(data."B"), 0) + COALESCE(SUM(data."C"), 0) + COALESCE(SUM(data."D"), 0) + COALESCE(SUM(data."E"), 0) + COALESCE(SUM(data."F"), 0) + COALESCE(SUM(data."G"), 0) + COALESCE(SUM(data."H"), 0) + COALESCE(SUM(data."I"), 0) + COALESCE(SUM(data."J"), 0) + COALESCE(SUM(data."K"), 0) + COALESCE(SUM(data."L"), 0) + COALESCE(SUM(data."M"), 0) FROM p_l_22 AS data WHERE data.row_num = 8), 0)) + COALESCE(params.scratch_b2, 0)) ELSE base."B" END AS "B"
FROM dashboard_01 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM scratch_00 WHERE row_num = 2) AS scratch_b2
) AS params;

-- Dashboard!B5: Best month  (single, 1 cells)
--   Excel: =INDEX('P&L'!B2:M2,MATCH(MAX('P&L'!B8:M8),'P&L'!B8:M8,0))
CREATE TABLE dashboard_03 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 5 THEN (SELECT CASE CAST(floor((SELECT CASE WHEN data."B" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 1 WHEN data."C" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 2 WHEN data."D" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 3 WHEN data."E" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 4 WHEN data."F" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 5 WHEN data."G" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 6 WHEN data."H" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 7 WHEN data."I" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 8 WHEN data."J" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 9 WHEN data."K" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 10 WHEN data."L" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 11 WHEN data."M" = COALESCE((SELECT greatest(MAX(data."B"), MAX(data."C"), MAX(data."D"), MAX(data."E"), MAX(data."F"), MAX(data."G"), MAX(data."H"), MAX(data."I"), MAX(data."J"), MAX(data."K"), MAX(data."L"), MAX(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 12 END FROM p_l_22 AS data WHERE data.row_num = 8)) AS INTEGER) WHEN 1 THEN data."B" WHEN 2 THEN data."C" WHEN 3 THEN data."D" WHEN 4 THEN data."E" WHEN 5 THEN data."F" WHEN 6 THEN data."G" WHEN 7 THEN data."H" WHEN 8 THEN data."I" WHEN 9 THEN data."J" WHEN 10 THEN data."K" WHEN 11 THEN data."L" WHEN 12 THEN data."M" END FROM p_l_22 AS data WHERE data.row_num = 2) ELSE base."B" END AS "B"
FROM dashboard_02 AS base;

-- Dashboard!B6: Worst month  (single, 1 cells)
--   Excel: =INDEX('P&L'!B2:M2,MATCH(MIN('P&L'!B8:M8),'P&L'!B8:M8,0))
CREATE TABLE dashboard_04 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 6 THEN (SELECT CASE CAST(floor((SELECT CASE WHEN data."B" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 1 WHEN data."C" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 2 WHEN data."D" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 3 WHEN data."E" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 4 WHEN data."F" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 5 WHEN data."G" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 6 WHEN data."H" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 7 WHEN data."I" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 8 WHEN data."J" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 9 WHEN data."K" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 10 WHEN data."L" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 11 WHEN data."M" = COALESCE((SELECT least(MIN(data."B"), MIN(data."C"), MIN(data."D"), MIN(data."E"), MIN(data."F"), MIN(data."G"), MIN(data."H"), MIN(data."I"), MIN(data."J"), MIN(data."K"), MIN(data."L"), MIN(data."M")) FROM p_l_22 AS data WHERE data.row_num = 8), 0) THEN 12 END FROM p_l_22 AS data WHERE data.row_num = 8)) AS INTEGER) WHEN 1 THEN data."B" WHEN 2 THEN data."C" WHEN 3 THEN data."D" WHEN 4 THEN data."E" WHEN 5 THEN data."F" WHEN 6 THEN data."G" WHEN 7 THEN data."H" WHEN 8 THEN data."I" WHEN 9 THEN data."J" WHEN 10 THEN data."K" WHEN 11 THEN data."L" WHEN 12 THEN data."M" END FROM p_l_22 AS data WHERE data.row_num = 2) ELSE base."B" END AS "B"
FROM dashboard_03 AS base;

-- Dashboard!B7: Loss-making months  (single, 1 cells)
--   Excel: =COUNTIF('P&L'!B8:M8,"<0")
CREATE TABLE dashboard_05 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 7 THEN (SELECT CASE WHEN (row_1."B" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."C" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."D" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."E" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."F" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."G" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."H" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."I" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."J" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."K" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."L" < 0.0) THEN 1 ELSE 0 END + CASE WHEN (row_1."M" < 0.0) THEN 1 ELSE 0 END FROM p_l_22 AS row_1 WHERE row_1.row_num = 8) ELSE base."B" END AS "B"
FROM dashboard_04 AS base;

-- Dashboard!B8: Average margin (profitable months)  (single, 1 cells)
--   Excel: =AVERAGEIF('P&L'!B6:M6,">0")
CREATE TABLE dashboard_06 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 8 THEN (SELECT (COALESCE(CASE WHEN (row_1."B" > 0.0) THEN row_1."B" END, 0) + COALESCE(CASE WHEN (row_1."C" > 0.0) THEN row_1."C" END, 0) + COALESCE(CASE WHEN (row_1."D" > 0.0) THEN row_1."D" END, 0) + COALESCE(CASE WHEN (row_1."E" > 0.0) THEN row_1."E" END, 0) + COALESCE(CASE WHEN (row_1."F" > 0.0) THEN row_1."F" END, 0) + COALESCE(CASE WHEN (row_1."G" > 0.0) THEN row_1."G" END, 0) + COALESCE(CASE WHEN (row_1."H" > 0.0) THEN row_1."H" END, 0) + COALESCE(CASE WHEN (row_1."I" > 0.0) THEN row_1."I" END, 0) + COALESCE(CASE WHEN (row_1."J" > 0.0) THEN row_1."J" END, 0) + COALESCE(CASE WHEN (row_1."K" > 0.0) THEN row_1."K" END, 0) + COALESCE(CASE WHEN (row_1."L" > 0.0) THEN row_1."L" END, 0) + COALESCE(CASE WHEN (row_1."M" > 0.0) THEN row_1."M" END, 0)) / NULLIF(CASE WHEN CASE WHEN (row_1."B" > 0.0) THEN row_1."B" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."C" > 0.0) THEN row_1."C" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."D" > 0.0) THEN row_1."D" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."E" > 0.0) THEN row_1."E" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."F" > 0.0) THEN row_1."F" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."G" > 0.0) THEN row_1."G" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."H" > 0.0) THEN row_1."H" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."I" > 0.0) THEN row_1."I" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."J" > 0.0) THEN row_1."J" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."K" > 0.0) THEN row_1."K" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."L" > 0.0) THEN row_1."L" END IS NULL THEN 0 ELSE 1 END + CASE WHEN CASE WHEN (row_1."M" > 0.0) THEN row_1."M" END IS NULL THEN 0 ELSE 1 END, 0) FROM p_l_22 AS row_1 WHERE row_1.row_num = 6) ELSE base."B" END AS "B"
FROM dashboard_05 AS base;

-- Dashboard!B9: Average headcount  (single, 1 cells)
--   Excel: =AVERAGE(Costs!B3:M3)
CREATE TABLE dashboard_07 AS
SELECT base.row_num,
       CASE WHEN base.row_num = 9 THEN ((COALESCE((SELECT COALESCE(SUM(data."B"), 0) + COALESCE(SUM(data."C"), 0) + COALESCE(SUM(data."D"), 0) + COALESCE(SUM(data."E"), 0) + COALESCE(SUM(data."F"), 0) + COALESCE(SUM(data."G"), 0) + COALESCE(SUM(data."H"), 0) + COALESCE(SUM(data."I"), 0) + COALESCE(SUM(data."J"), 0) + COALESCE(SUM(data."K"), 0) + COALESCE(SUM(data."L"), 0) + COALESCE(SUM(data."M"), 0) FROM costs_15 AS data WHERE data.row_num = 3), 0)) / NULLIF(COALESCE((SELECT COALESCE(COUNT(data."B"), 0) + COALESCE(COUNT(data."C"), 0) + COALESCE(COUNT(data."D"), 0) + COALESCE(COUNT(data."E"), 0) + COALESCE(COUNT(data."F"), 0) + COALESCE(COUNT(data."G"), 0) + COALESCE(COUNT(data."H"), 0) + COALESCE(COUNT(data."I"), 0) + COALESCE(COUNT(data."J"), 0) + COALESCE(COUNT(data."K"), 0) + COALESCE(COUNT(data."L"), 0) + COALESCE(COUNT(data."M"), 0) FROM costs_15 AS data WHERE data.row_num = 3), 0), 0)) ELSE base."B" END AS "B"
FROM dashboard_06 AS base;

-- NOT CONVERTED Dashboard!B10  =COUNTIF(Orders!H:H,"#N/A")
--   reason: criterion '#N/A' matches error cells, which are NULL in SQL

-- Dashboard!B12: Headline  (single, 1 cells)
--   Excel: =CONCATENATE("Net result ",TEXT(B4,"#,##0")," GBP across ",COUNTA(Revenue!B2:M2)," months")
CREATE TABLE dashboard_08 AS
SELECT base.row_num,
       base."B",
       CASE WHEN base.row_num = 12 THEN concat('Net result ', format('{:,.0f}', xl_round(params.dashboard_b4, 0)), ' GBP across ', xl_text(((SELECT SUM(CASE WHEN data."B" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."C" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."D" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."E" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."F" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."G" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."H" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."I" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."J" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."K" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."L" IS NOT NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN data."M" IS NOT NULL THEN 1 ELSE 0 END) FROM revenue_18 AS data WHERE data.row_num = 2))), ' months') ELSE NULL END AS "B_txt"
FROM dashboard_07 AS base
CROSS JOIN (
    SELECT (SELECT "B" FROM dashboard_07 WHERE row_num = 4) AS dashboard_b4
) AS params;

-- Final state of every sheet

CREATE VIEW "assumptions" AS SELECT * FROM assumptions_01;
CREATE VIEW "lookups" AS SELECT * FROM lookups_00;
CREATE VIEW "orders" AS SELECT * FROM orders_08;
CREATE VIEW "revenue" AS SELECT * FROM revenue_18;
CREATE VIEW "costs" AS SELECT * FROM costs_15;
CREATE VIEW "p_l" AS SELECT * FROM p_l_22;
CREATE VIEW "scratch" AS SELECT * FROM scratch_00;
CREATE VIEW "dashboard" AS SELECT * FROM dashboard_08;