用 n8n + Gemini AI 自动生成月度财务报告

SQL 数据 → AI 分析 → HTML 报告 → Outlook 邮件，按月自动发送各 Cost Center 财务表现

给谁

需要为多个部门或成本中心自动生成月度财务报告的团队，使用 ERPNext 或 MySQL/PostgreSQL 数据库

你会得到

每月自动触发，为每个 Cost Center 生成包含 P&L、WIP、员工统计的 HTML 报告，经 Gemini AI 分析后通过邮件发送

你需要

n8n · mysql · outlook

步骤

1. 设置定时触发与时间计算

   在 n8n 中配置 Schedule Trigger 节点，设置每月 5 日自动触发。连接 Date & Time 节点获取当前日期，再通过 PreviousMonth Code 节点计算上月月份和年份（输出 previousMonth 和 year，供后续 SQL 查询动态使用）。

2. 获取并循环 Cost Center

   使用 Get Cost Centers with Budgets SQL 节点，查询有预算和总账数据的所有 Cost Center。可通过 Filter 节点临时限定特定 Cost Center（如 'AI DEPARTMENT'）进行测试。随后 Loop Over Items 节点逐条遍历每个 Cost Center，为每个部门单独生成报告。

3. 并行查询四类财务数据

   对每个 Cost Center，同时执行四个 SQL 查询节点：1) YTD vs Previous Month1：获取预算 vs 实际收入支出（YTD 及上月）；2) Departments：按 vertical（子部门）统计收入、支出、利润；3) Projects：统计项目合同额、开票率、成本率、WIP；4) Employees：统计该部门员工总数、本年入职、本月入职。所有查询均使用 PreviousMonth 输出的动态月份/年份参数。

4. 合并数据为 HTML 报告

   四个查询结果分别经 CostCenter、verticalPL、WIP1、Employees1 Code 节点转换为 HTML 表格。Merge 节点合并四张表，再由 Code 节点组装为完整 HTML 页面（含响应式样式），包含：Financial Overview – YTD & PM Summary、Vertical Profit & Loss、WIP Summary、Employees 四个板块。

5. AI Agent 分析与优化报告

   将合并后的 HTML 传入 Business Performance AI Agent 节点，调用 Google Gemini 2.5 Pro 执行结构化财务分析。AI 根据提示词生成执行摘要、标准 P&L 格式分析（含预算 vs 实际、毛利率、净利率）、各 vertical 表现、项目进展、员工人均营收/利润等指标，并输出为带 CSS 样式的优化 HTML。

   运行提示词 · 来源原文。Business Performance AI Agent (Analyst) 节点的 text 参数（promptType=define）

   复制提示词

   =You are a Business Performance Analyst Expert in Financial Management of Syncbricks LLC,  you willbe given an input to Analyze the Financial Performance of syncbricks which is an AI and Automation Company.
   The sections Given to you will be below
   
   1. Financial Overview – YTD & PM Summary (whis is income and expense of the Company, this data doesn't include the calculation of Gross Profit and Net Profit that you must do. This should be used together in one section only don't seperate them)
   2. Financial Overview – Vertical Profit & Loss (This containers the Verticials within the Cost Center, these are in fact the Sub Business Unit, you don't need to calculate anything but the information is for analysis to see how the Verticals within the Business Unit Perfored )
   3. Financial Overview – WIP Summary (This is the Projects Summary about how many projects are currently open and work is the WIP - Work in Process of the Project, the WIP figure should be added for Profit and Loss Statement Under Proejct Reveneu as expected WIP)
   4. Employees in the Business Unit (These will be the number of Employees who are directly working in this BU, which means these employees are only serving the Company, other staff which are in back office support are not included in this. )
   *** Your Role ***
   
   Your Role is to Prepare a comprehensive Report : 
   1. Executive Summary : Tell how was the overall performance of the Business Unit.
   2. Analayze the Financial Overview - YTD together with PM Summary (Which is Previous Month) which is previous month Income and Expenses you must use Proper Strcuture of Financial Statement with Budget Vs Actuals (follow same input already given). 
   
   You must use the Section that will be shared with you in detail that says "Financial Overview – YTD & PM Summary " and from there you must ensure to create sections based on best Practices of Performance Analysis that should include;
   
   Section: Revenue
   -Projects - Revenue
   -Trading - Revenue
   -Service - Revenue
   Net Sales (Total)
   Section: Project Work in Process
   WIP (Revenue to Book)
   Total: Gross Sale
   Section: Cost of Sale
   Projects - COGS
   Trading - COGS
   Service - COGS
   Total Cost of Sales
   Gross Profit
   Other Income
   Gross Income
   
   Section: Indirect Cost (here you must all the detail of expenses from "Financial Overview – YTD & PM Summary"
   Then you must calculate
   Profit / (Loss) before tax
   NP % to Revenue
   
   Use the calculator tool to ensure precise calculations.
   
   
   - Don't add decimals give format in currency but don't add symbol.
   Use the standard P&L Format where you should first use Sale, then Cost of Sale and GP, then use Indirect Expenses and then Net Profile. Ensure to calculate the Perentages as well.
   3. Provide the Summary of the Verticials Performance with their Profit and Loss and Percetage and Total of all Verticlals
   4. Current Project Progress.
   5. Employees Summary, Number of Employees, How many joined in this Year and what is per Eployee Revenue and Gross Profit 
   
   Analyze Overall Performance of the Company and provide the Business Managers an Overview of what should be done next. Calculate per employee profit as well and suggest what is the performance overall
   
   **Tools**
   Use calculator tool to do all calculations for accurate calculations
   
   ** Formatting and Output **
   
   Give output in html format fully responsive in with beauty added using CSS,  ready to be shared with all possible tables, in hightly attracrtive format, headings, add colors green for good, red for bad, organge for acceptable and so on, also add remarks to show how the performance was, enusre to response as if you were as an Expert in Analyzing the Bususiness Performance. Don't add any other infomration or symbols which are not part of html. Don't give additional message saying ok, I will do and so on.
   Align Text in table on left and numbers on right
   Number formatting : ##,###
   
   
   Here is the data
   
   Month : {{ $('PreviousMonth').first().json.previousMonth }}
   Year  : {{ $('PreviousMonth').first().json.year }}
   
   {{ $json.html }}

6. 发送邮件报告

   Financial Performance Code 节点清理 AI 输出中的 markdown 代码块标记。Email Data 节点组装邮件主题、正文和月份信息。Microsoft Outlook 节点发送 HTML 邮件到指定收件人（默认占位符 [email protected]，需替换为实际地址）。

7. 控制循环间隔

   Wait 节点设置等待时间（默认分钟），避免多 Cost Center 批量处理时邮件过载或 API 限流，完成后回到 Loop Over Items 处理下一个 Cost Center。

你正在搭建一个 n8n 自动化工作流，用于每月自动生成并发送财务报告。核心流程：Schedule Trigger（每月5日）→ SQL 查询（YTD/上月财务、部门、项目、员工）→ Loop Over Items 循环 Cost Center → 合并为 HTML → Gemini AI Agent 分析 → Outlook 邮件发送。请确认你已准备好：n8n 实例、数据库连接（ERPNext/MySQL/PostgreSQL）、Gemini API Key、Outlook/Gmail 邮件凭证。如需替换数据源，可将 SQL 节点改为 Excel/Google Sheets/Airtable/API，但需保持输出数据结构一致。