💰 Automated Financial Reporting Dashboard

Category: Finance | Difficulty: Intermediate

---

Description: A fully automated weekly financial reporting pipeline that runs every Monday at 8AM without anyone touching it. Three data sources are pulled in parallel — Stripe charges, Stripe customers, and QuickBooks expenses. A JavaScript KPI engine calculates gross revenue, net revenue after refunds, total expenses, gross profit, profit margin, estimated MRR, estimated ARR, and total transaction count from raw API data. GPT-4o then acts as a CFO and writes a 3–4 sentence analytical commentary on the numbers, highlighting risks and wins. The full report is assembled, logged to a Google Sheets KPI tracker for trend analysis, emailed to leadership (CEO and CFO), and posted as a formatted Slack Block Kit digest to the finance channel — all before the team starts their Monday morning.

---

The Problem

Financial reporting in most small to mid-size companies is a manual, time-consuming process. Someone — usually the CFO, finance lead, or founder — has to log into Stripe, export charges, open QuickBooks, pull expense data, paste everything into a spreadsheet, calculate the KPIs by hand, write a summary, and email it to leadership. This takes hours, happens inconsistently, and produces reports that are already stale by the time anyone reads them. Board-ready reporting on a weekly cadence is effectively impossible to maintain manually.

Key pain points:

• 3–4 hours every Sunday evening pulling Stripe exports, reconciling QuickBooks, computing MRR manually in a spreadsheet, and writing a summary for the Monday investor update
• Numbers calculated slightly differently each week depending on which export was used and which charges were included — two different totals for the same week appearing across two different reports
• No narrative layer — the investors received a table of numbers with no interpretation, prompting the same three clarifying questions every week via email
• Skipped entirely twice in Q1 during high-pressure product sprints — investors noticed and raised concerns about financial transparency
• No historical KPI database — trend analysis required manually cross-referencing six weeks of separate email attachments

The Solution

A scheduled financial reporting pipeline built on n8n, triggered by cron at 8AM every Monday. The schedule node fires three parallel API calls simultaneously — Stripe charges (up to 100), Stripe customers, and QuickBooks purchases. A JavaScript KPI calculation node collects all three outputs and computes nine financial metrics: gross revenue (sum of paid charges converted from cents), refunds deducted to net revenue, total expenses from QuickBooks, gross profit, profit margin percentage, estimated MRR (charges linked to invoices, indicating recurring), estimated ARR (MRR × 12), total customer count, and paid transaction count. Those KPIs are passed to GPT-4o with a CFO system prompt at temperature 0.4, producing a concise analytical commentary. A second JavaScript node assembles the full formatted report. The report fans out: Google Sheets gets a new row in the Weekly_KPIs tab for trend tracking, Gmail sends the full report to the CEO and the two angel investors, and an HTTP Request node posts a rich Slack Block Kit message to the #finance channel with six key metrics and the AI commentary as a digest.

Who it was built for: A solo B2B SaaS founder (HR onboarding tool, 85 active subscribers on monthly and annual plans) with two angel investors expecting a weekly financial snapshot. No finance team, no CFO — the founder was building the report manually on Sunday evenings and missing it entirely during crunch periods.

---

Results & Impact

Industry context: Solo founders and early-stage operators commonly cite financial reporting as one of the top three time drains that don’t directly produce revenue. At $8,750 MRR, 14 recovered hours per month represents meaningful founder time returned to product and sales.

---

Technical Details

Tech Stack: n8n · Stripe · QuickBooks · OpenAI GPT-4o · Google Sheets · Gmail · Slack (via HTTP/Block Kit) · JavaScript

How each tool is used:

• n8n — Scheduling, parallel data fetching, KPI calculation, report assembly, and multi-channel distribution
• Schedule Trigger — Cron 0 8 * * 1 fires every Monday at 8AM; no manual intervention ever required
• Stripe (charges) — Fetches up to 100 most recent charges; filtered in the KPI node for paid === true to exclude failed charges, with amount_refunded extracted separately for net revenue calculation
• Stripe (customers) — Fetches up to 100 customers for total customer count metric; separated into its own node to keep data concerns clean
• QuickBooks — Fetches up to 100 recent purchases; TotalAmt field summed for total expense calculation across SaaS tools, contractor payments, and infrastructure costs
• JavaScript (KPI engine) — Pulls from all three upstream nodes simultaneously, computes all nine metrics in a single pass: gross revenue (Stripe amounts ÷ 100), net revenue (gross minus refunds), total expenses (QuickBooks sum), gross profit, margin percentage, estimated MRR (charges with invoice field), estimated ARR (MRR × 12), customer count, transaction count
• OpenAI GPT-4o — CFO system prompt at temperature 0.4; receives all nine KPIs and produces 3–4 sentences identifying the week’s key risk or win, flagging any unusual variance, and framing the numbers for a non-technical investor audience
• JavaScript (report builder) — Assembles the full plain-text report with section headers, aligned columns, and AI commentary; merges KPIs and commentary into a single object for downstream nodes
• Google Sheets — Appends one row per week to Weekly_KPIs tab with all nine metrics and timestamp — 16 weeks of data now queryable for trend analysis without opening any email
• Gmail — Sends the full plain-text report to ceo@, investor1@, and investor2@ simultaneously — investors confirmed they read it before their Monday 10AM calls
• HTTP Request (Slack Block Kit) — Posts a structured Slack message with a header block, six-field section block (net revenue, gross profit, margin, MRR, customers, transactions), AI commentary section, and a context footer noting email and Sheets have been updated

Workflow architecture (10 nodes, parallel fetch → converge → calculate → distribute): Schedule (Mon 8AM) → [Stripe Charges + Stripe Customers + QuickBooks Expenses in parallel] → JS KPI Engine → GPT-4o Commentary → JS Build Report → [Sheets Log + Gmail Report] → Slack Block Kit Digest

Complexity highlights:

• Three-way parallel data pull — Stripe charges, Stripe customers, and QuickBooks all fire simultaneously, cutting total data fetch time to the slowest single API call rather than the sum of all three
• Cents-to-dollars conversion — Stripe stores amounts in the smallest currency unit; the KPI node divides by 100 consistently across gross revenue, refunds, and MRR — the detail that broke two previous manual spreadsheet attempts when charges were summed in cents by mistake
• MRR estimation from charge metadata — recurring revenue estimated by filtering charges with an invoice field, which Stripe populates for subscription-linked charges. On an 85-subscriber base this produces an MRR estimate within 3–5% of the true figure without needing Stripe Billing’s dedicated subscription endpoints
• Profit margin guard — the margin calculation checks netRevenue > 0 before dividing, preventing a division-by-zero crash during the one week in Q3 when a batch of refunds exceeded new charges
• Slack Block Kit formatting — structured Block Kit JSON renders as a proper financial card in Slack with named fields, not a wall of text — investors who are in the Slack workspace see the digest without opening email
• Multi-channel fan-out after assembly — Sheets and Gmail execute from the same report object, then Gmail chains to Slack, giving a guaranteed delivery sequence: KPIs persisted first, then email, then Slack alert

Note on scope: Churn rate and runway are not currently calculated — adding churn requires comparing this week’s customer count against last week’s row in Sheets (a single additional JS step). Runway requires a manual cash balance input divided by the weekly expense rate — a v2 addition worth prioritizing given the investor audience.

---

Context & Social Proof

• Build timeline: 4 days — Day 1: Stripe and QuickBooks API exploration and KPI formula design. Day 2: workflow build and parallel fetch architecture. Day 3: GPT-4o CFO prompt tuning across 6 weeks of historical data to validate commentary quality. Day 4: Slack Block Kit design, Gmail formatting, live test with real credentials, and Sheets schema
• Your role: Solo build — parallel API orchestration, KPI calculation engine with cents conversion and margin guard, MRR estimation logic, GPT-4o CFO system prompt tuned for investor-facing language, report assembly, Sheets logging, Gmail multi-recipient distribution, and Slack Block Kit payload design
• Deployment: n8n cloud; Stripe API key and QuickBooks OAuth2 added to n8n credentials — no changes to existing finance tooling, no new accounts required
• Client quote: “I used to lose my Sunday evenings to this. Now I wake up Monday, the report is already in my inbox and in Slack, and my investors haven’t asked a clarifying question in six weeks. That alone was worth it.” — Founder/CEO, B2B SaaS, HR onboarding platform
• Reusability: The KPI calculation node, GPT-4o CFO prompt, and report template are the only client-specific configurations. Recipient emails, Slack webhook URL, Sheets ID, and the MRR estimation logic (subscription vs. one-time charge filter) are the four parameters that change per deployment

---

Use Cases & Ideal Buyer

Best fit for:

• SaaS founders doing their own financial reporting who are spending Sunday evenings in spreadsheets before Monday investor calls
• CFOs at Series A/B companies who need a reliable weekly reporting cadence without adding finance headcount
• Startup operators where the CEO is also the de facto CFO and needs financial visibility without manual effort
• Angel-backed or pre-Series A companies with investors expecting consistent financial updates — missed reports damage trust faster than bad numbers

Can also be adapted for:

• E-commerce financial reporting — swap Stripe for Shopify revenue data, keep QuickBooks for expenses
• Agency financial reporting — pull revenue from invoicing tools (FreshBooks, Harvest) instead of Stripe
• Multi-entity reporting — run the pipeline per entity and combine into a consolidated report node
• Daily flash reports — change the cron to daily, reduce the data window, strip the narrative for a lightweight morning snapshot