Forecast and Report Multi-channel Tax Liabilities with Openai, Gmail, Sheets…

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "nj0tmvCC9IYTp2op",
  "name": "Multi-Channel Revenue Tax Liability Forecasting and Reporting System",
  "tags": [],
  "nodes": [
    {
      "id": "b97ae7d0-92db-4081-ae2d-451f2197ede7",
      "name": "Monthly Schedule",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -928,
        192
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "months",
              "triggerAtHour": 9
            }
          ]
        }
      },
      "typeVersion": 1.3
    },
    {
      "id": "b5040bf9-e5e5-401f-8c15-db79c9cdf6b9",
      "name": "Workflow Configuration",
      "type": "n8n-nodes-base.set",
      "position": [
        -704,
        192
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "id-1",
              "name": "incomeTaxRate",
              "type": "number",
              "value": 0.25
            },
            {
              "id": "id-2",
              "name": "vatRate",
              "type": "number",
              "value": 0.2
            },
            {
              "id": "id-3",
              "name": "gstRate",
              "type": "number",
              "value": 0.15
            },
            {
              "id": "id-4",
              "name": "withholdingTaxRate",
              "type": "number",
              "value": 0.1
            },
            {
              "id": "id-5",
              "name": "forecastMonths",
              "type": "number",
              "value": 6
            },
            {
              "id": "id-6",
              "name": "taxAgentEmail",
              "type": "string",
              "value": "<__PLACEHOLDER_VALUE__Tax agent email address__>"
            },
            {
              "id": "id-7",
              "name": "revenueChannels",
              "type": "array",
              "value": "[\"Shopify\", \"Amazon\", \"Website\", \"Wholesale\"]"
            },
            {
              "id": "id-8",
              "name": "anomalyThreshold",
              "type": "number",
              "value": 2
            },
            {
              "id": "id-9",
              "name": "smallBusinessThreshold",
              "type": "number",
              "value": 100000
            },
            {
              "id": "id-10",
              "name": "mediumBusinessThreshold",
              "type": "number",
              "value": 500000
            },
            {
              "id": "id-11",
              "name": "standardDeduction",
              "type": "number",
              "value": 12950
            },
            {
              "id": "id-12",
              "name": "historicalDataApiUrl",
              "type": "string",
              "value": "<__PLACEHOLDER_VALUE__Historical revenue API endpoint__>"
            }
          ]
        },
        "includeOtherFields": true
      },
      "typeVersion": 3.4
    },
    {
      "id": "f572bf39-d656-45a8-81f1-0b5ecec52427",
      "name": "Aggregate Multi-Channel Revenue",
      "type": "n8n-nodes-base.code",
      "position": [
        -480,
        96
      ],
      "parameters": {
        "jsCode": "// Aggregate multi-channel revenue data from different sources\n// Calculate total revenue per channel and overall total\n\nconst items = $input.all();\n\n// Initialize revenue tracking object\nconst revenueByChannel = {};\nlet overallTotal = 0;\n\n// Process each item and aggregate by channel\nfor (const item of items) {\n  const channel = item.json.channel || 'Unknown';\n  const revenue = parseFloat(item.json.revenue) || 0;\n  \n  // Add to channel total\n  if (!revenueByChannel[channel]) {\n    revenueByChannel[channel] = 0;\n  }\n  revenueByChannel[channel] += revenue;\n  \n  // Add to overall total\n  overallTotal += revenue;\n}\n\n// Format the aggregated data\nconst channelBreakdown = Object.entries(revenueByChannel).map(([channel, total]) => ({\n  channel,\n  revenue: total,\n  percentage: ((total / overallTotal) * 100).toFixed(2)\n}));\n\n// Return aggregated results\nreturn [\n  {\n    json: {\n      overallTotal,\n      channelBreakdown,\n      totalChannels: Object.keys(revenueByChannel).length,\n      reportDate: new Date().toISOString(),\n      period: 'monthly'\n    }\n  }\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "92d6de1d-d2da-4576-9f24-3b70cb2c86ef",
      "name": "Calculate Tax Liabilities",
      "type": "n8n-nodes-base.code",
      "position": [
        1328,
        368
      ],
      "parameters": {
        "jsCode": "// Calculate Tax Liabilities\n// This code calculates various tax liabilities based on aggregated revenue\n\n// Get aggregated revenue data from previous node\nconst revenueData = $input.all();\n\n// Get tax rates from Workflow Configuration node\nconst config = $('Workflow Configuration').first().json;\n\n// Initialize tax calculation results\nconst taxResults = [];\n\n// Process each revenue item\nfor (const item of revenueData) {\n  const revenue = item.json;\n  \n  // Extract revenue amounts\n  const totalRevenue = revenue.totalRevenue || 0;\n  const domesticRevenue = revenue.domesticRevenue || 0;\n  const internationalRevenue = revenue.internationalRevenue || 0;\n  \n  // Get tax rates from configuration (with defaults if not set)\n  const incomeTaxRate = config.incomeTaxRate || 0.21;\n  const vatRate = config.vatRate || 0.20;\n  const gstRate = config.gstRate || 0.10;\n  const withholdingTaxRate = config.withholdingTaxRate || 0.15;\n  \n  // Calculate different tax liabilities\n  const incomeTax = totalRevenue * incomeTaxRate;\n  const vat = domesticRevenue * vatRate;\n  const gst = internationalRevenue * gstRate;\n  const withholdingTax = internationalRevenue * withholdingTaxRate;\n  \n  // Calculate total tax liability\n  const totalTaxLiability = incomeTax + vat + gst + withholdingTax;\n  \n  // Calculate net revenue after tax\n  const netRevenue = totalRevenue - totalTaxLiability;\n  \n  // Create result object\n  taxResults.push({\n    json: {\n      ...revenue,\n      taxCalculations: {\n        incomeTax: parseFloat(incomeTax.toFixed(2)),\n        vat: parseFloat(vat.toFixed(2)),\n        gst: parseFloat(gst.toFixed(2)),\n        withholdingTax: parseFloat(withholdingTax.toFixed(2)),\n        totalTaxLiability: parseFloat(totalTaxLiability.toFixed(2)),\n        netRevenue: parseFloat(netRevenue.toFixed(2))\n      },\n      taxRatesApplied: {\n        incomeTaxRate,\n        vatRate,\n        gstRate,\n        withholdingTaxRate\n      },\n      calculationDate: new Date().toISOString()\n    }\n  });\n}\n\nreturn taxResults;"
      },
      "typeVersion": 2
    },
    {
      "id": "79262d31-382d-4ff3-b1a0-cd7919969ede",
      "name": "Generate Rolling Forecast",
      "type": "n8n-nodes-base.code",
      "position": [
        1552,
        368
      ],
      "parameters": {
        "jsCode": "// Generate rolling forecast for the next 6 months based on historical revenue trends and calculated tax liabilities\n\nconst items = $input.all();\n\n// Extract historical data from previous nodes\nconst historicalData = items.map(item => item.json);\n\n// Calculate average monthly revenue growth rate\nlet totalRevenue = 0;\nlet revenueCount = 0;\n\nhistoricalData.forEach(data => {\n  if (data.revenue) {\n    totalRevenue += data.revenue;\n    revenueCount++;\n  }\n});\n\nconst avgMonthlyRevenue = revenueCount > 0 ? totalRevenue / revenueCount : 0;\n\n// Calculate growth rate (simplified - assumes 5% monthly growth)\nconst growthRate = 0.05;\n\n// Get current tax liability rate\nconst taxRate = historicalData[0]?.taxRate || 0.25;\n\n// Generate 6-month rolling forecast\nconst forecast = [];\nconst currentDate = new Date();\n\nfor (let i = 1; i <= 6; i++) {\n  const forecastDate = new Date(currentDate);\n  forecastDate.setMonth(currentDate.getMonth() + i);\n  \n  const projectedRevenue = avgMonthlyRevenue * Math.pow(1 + growthRate, i);\n  const projectedTaxLiability = projectedRevenue * taxRate;\n  const netRevenue = projectedRevenue - projectedTaxLiability;\n  \n  forecast.push({\n    month: forecastDate.toISOString().slice(0, 7),\n    monthName: forecastDate.toLocaleString('default', { month: 'long', year: 'numeric' }),\n    projectedRevenue: Math.round(projectedRevenue * 100) / 100,\n    projectedTaxLiability: Math.round(projectedTaxLiability * 100) / 100,\n    netRevenue: Math.round(netRevenue * 100) / 100,\n    growthRate: growthRate * 100 + '%',\n    taxRate: taxRate * 100 + '%'\n  });\n}\n\n// Return forecast data\nreturn forecast.map(item => ({ json: item }));"
      },
      "typeVersion": 2
    },
    {
      "id": "51cde6e7-0e08-46be-845d-3abd487de36e",
      "name": "Format Report Data",
      "type": "n8n-nodes-base.set",
      "position": [
        2000,
        336
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "id-1",
              "name": "reportMonth",
              "type": "string",
              "value": "={{ $now.format('MMMM yyyy') }}"
            },
            {
              "id": "id-2",
              "name": "reportDate",
              "type": "string",
              "value": "={{ $now.toISO() }}"
            },
            {
              "id": "id-3",
              "name": "totalRevenue",
              "type": "number",
              "value": "={{ $json.totalRevenue }}"
            },
            {
              "id": "id-4",
              "name": "incomeTax",
              "type": "number",
              "value": "={{ $json.incomeTax }}"
            },
            {
              "id": "id-5",
              "name": "vat",
              "type": "number",
              "value": "={{ $json.vat }}"
            },
            {
              "id": "id-6",
              "name": "gst",
              "type": "number",
              "value": "={{ $json.gst }}"
            },
            {
              "id": "id-7",
              "name": "withholdingTax",
              "type": "number",
              "value": "={{ $json.withholdingTax }}"
            },
            {
              "id": "id-8",
              "name": "totalTaxLiability",
              "type": "number",
              "value": "={{ $json.totalTaxLiability }}"
            },
            {
              "id": "id-9",
              "name": "forecast",
              "type": "object",
              "value": "={{ $json.forecast }}"
            },
            {
              "id": "id-10",
              "name": "revenueByChannel",
              "type": "object",
              "value": "={{ $json.revenueByChannel }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "cba2aef7-a63f-424b-b0ff-148627581f28",
      "name": "Send Report to Tax Agent",
      "type": "n8n-nodes-base.gmail",
      "position": [
        2224,
        48
      ],
      "parameters": {
        "sendTo": "={{ $('Workflow Configuration').first().json.taxAgentEmail }}",
        "message": "=<h2>Monthly Tax Liability Report</h2>\n\n<h3>Report Period: {{ $json.reportMonth }}</h3>\n\n<h3>Revenue Summary</h3>\n<table border=\"1\" cellpadding=\"8\" cellspacing=\"0\" style=\"border-collapse: collapse; width: 100%;\">\n  <tr>\n    <th>Channel</th>\n    <th>Revenue</th>\n  </tr>\n  <tr>\n    <td>Total Revenue</td>\n    <td>${{ $json.totalRevenue }}</td>\n  </tr>\n</table>\n\n<h3>Tax Liabilities Breakdown</h3>\n<table border=\"1\" cellpadding=\"8\" cellspacing=\"0\" style=\"border-collapse: collapse; width: 100%;\">\n  <tr>\n    <th>Tax Type</th>\n    <th>Amount</th>\n  </tr>\n  <tr>\n    <td>Income Tax</td>\n    <td>${{ $json.incomeTax }}</td>\n  </tr>\n  <tr>\n    <td>Sales Tax</td>\n    <td>${{ $json.salesTax }}</td>\n  </tr>\n  <tr>\n    <td>Total Tax Liability</td>\n    <td><strong>${{ $json.totalTaxLiability }}</strong></td>\n  </tr>\n</table>\n\n<h3>Rolling Forecast</h3>\n<p>Next Quarter Projected Revenue: ${{ $json.forecastRevenue }}</p>\n<p>Next Quarter Projected Tax: ${{ $json.forecastTax }}</p>\n\n<p>Please review the attached report and let me know if you have any questions.</p>\n\n<p>Best regards,<br>Automated Tax Reporting System</p>",
        "options": {},
        "subject": "=Monthly Tax Liability Report - {{ $json.reportMonth }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "e78c4f22-7635-492b-84c8-6fcea07b827e",
      "name": "Store in Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2224,
        240
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "reportDate"
          ]
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "Tax Forecasts"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "<__PLACEHOLDER_VALUE__Google Sheets document ID__>"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "aa9c2324-aad7-41ab-a047-dbc53baaf70f",
      "name": "Store in Airtable",
      "type": "n8n-nodes-base.airtable",
      "position": [
        2224,
        432
      ],
      "parameters": {
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "<__PLACEHOLDER_VALUE__Airtable base ID__>"
        },
        "table": {
          "__rl": true,
          "mode": "id",
          "value": "<__PLACEHOLDER_VALUE__Airtable table ID__>"
        },
        "options": {},
        "operation": "create"
      },
      "typeVersion": 2.1
    },
    {
      "id": "569bb1f7-1cf6-4e9e-abf5-372a0cf4fff3",
      "name": "Fetch Historical Revenue Data",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -480,
        304
      ],
      "parameters": {
        "url": "<__PLACEHOLDER_VALUE__Historical revenue API endpoint__>",
        "options": {},
        "sendHeaders": true,
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        }
      },
      "typeVersion": 4.3
    },
    {
      "id": "c13d854c-fe24-401b-8cb0-e218b5aabb67",
      "name": "Merge Historical and Current Data",
      "type": "n8n-nodes-base.merge",
      "position": [
        -240,
        256
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.2
    },
    {
      "id": "adcf3e72-051e-4d6d-8483-b309f8605c8a",
      "name": "Calculate Revenue Statistics",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -16,
        256
      ],
      "parameters": {
        "options": {},
        "fieldsToSummarize": {
          "values": [
            {
              "field": "revenue",
              "aggregation": "sum"
            },
            {
              "field": "revenue",
              "aggregation": "average"
            },
            {
              "field": "revenue",
              "aggregation": "min"
            },
            {
              "field": "revenue",
              "aggregation": "max"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "1d8a4e95-1a5b-4a38-8782-0f18ba38e2f2",
      "name": "Check Tax Threshold",
      "type": "n8n-nodes-base.if",
      "position": [
        432,
        256
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": false,
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "id-1",
              "operator": {
                "type": "boolean",
                "operation": "equals"
              },
              "leftValue": "={{ $json.hasAnomaly }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "a2ceff9e-a895-4509-aeae-4468bd13eb28",
      "name": "Route by Revenue Tier",
      "type": "n8n-nodes-base.switch",
      "position": [
        656,
        112
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "outputKey": "Small Business",
              "conditions": {
                "options": {
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "operator": {
                      "type": "number",
                      "operation": "lt"
                    },
                    "leftValue": "={{ $json.totalRevenue }}",
                    "rightValue": 100000
                  }
                ]
              },
              "renameOutput": true
            },
            {
              "outputKey": "Medium Business",
              "conditions": {
                "options": {
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "operator": {
                      "type": "number",
                      "operation": "gte"
                    },
                    "leftValue": "={{ $json.totalRevenue }}",
                    "rightValue": 100000
                  },
                  {
                    "operator": {
                      "type": "number",
                      "operation": "lt"
                    },
                    "leftValue": "={{ $json.totalRevenue }}",
                    "rightValue": 500000
                  }
                ]
              },
              "renameOutput": true
            }
          ]
        },
        "options": {
          "fallbackOutput": "extra",
          "renameFallbackOutput": "Large Business"
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "fd83e5bd-cb64-4562-a8df-c8e85769a759",
      "name": "Calculate Tax Deductions",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        0
      ],
      "parameters": {
        "jsCode": "// Calculate Tax Deductions for Small Businesses\n// Includes standard deduction, business expenses, and depreciation allowances\n\nconst items = $input.all();\nconst results = [];\n\nfor (const item of items) {\n  const revenue = item.json.revenue || item.json.totalRevenue || 0;\n  const expenses = item.json.expenses || 0;\n  \n  // Standard deduction for small businesses (typically a fixed amount or percentage)\n  const standardDeduction = Math.min(12950, revenue * 0.05); // $12,950 or 5% of revenue, whichever is lower\n  \n  // Business expenses deduction (operating costs, supplies, utilities, etc.)\n  const businessExpensesDeduction = expenses * 0.85; // 85% of reported expenses are deductible\n  \n  // Depreciation allowances (for equipment, vehicles, property)\n  const depreciableAssets = item.json.assets || 0;\n  const depreciationRate = 0.20; // 20% annual depreciation using MACRS\n  const depreciationAllowance = depreciableAssets * depreciationRate;\n  \n  // Section 179 deduction (immediate expensing for qualifying equipment)\n  const section179Limit = 1080000; // 2023 limit\n  const qualifyingEquipment = item.json.qualifyingEquipment || 0;\n  const section179Deduction = Math.min(section179Limit, qualifyingEquipment);\n  \n  // Home office deduction (if applicable)\n  const homeOfficeSquareFeet = item.json.homeOfficeSquareFeet || 0;\n  const homeOfficeDeduction = Math.min(homeOfficeSquareFeet * 5, 1500); // $5 per sq ft, max $1,500\n  \n  // Calculate total deductions\n  const totalDeductions = \n    standardDeduction + \n    businessExpensesDeduction + \n    depreciationAllowance + \n    section179Deduction + \n    homeOfficeDeduction;\n  \n  // Calculate taxable income after deductions\n  const taxableIncome = Math.max(0, revenue - totalDeductions);\n  \n  // Calculate tax savings (assuming 25% tax rate)\n  const taxRate = 0.25;\n  const taxSavings = totalDeductions * taxRate;\n  \n  results.push({\n    json: {\n      ...item.json,\n      deductions: {\n        standardDeduction: Math.round(standardDeduction * 100) / 100,\n        businessExpensesDeduction: Math.round(businessExpensesDeduction * 100) / 100,\n        depreciationAllowance: Math.round(depreciationAllowance * 100) / 100,\n        section179Deduction: Math.round(section179Deduction * 100) / 100,\n        homeOfficeDeduction: Math.round(homeOfficeDeduction * 100) / 100,\n        totalDeductions: Math.round(totalDeductions * 100) / 100\n      },\n      taxableIncome: Math.round(taxableIncome * 100) / 100,\n      taxSavings: Math.round(taxSavings * 100) / 100,\n      effectiveTaxRate: revenue > 0 ? Math.round((taxableIncome / revenue) * 10000) / 100 : 0,\n      calculationDate: new Date().toISOString()\n    }\n  });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "c6d84e71-615d-44d9-bc6c-7575d0898d5a",
      "name": "Apply Progressive Tax Brackets",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        192
      ],
      "parameters": {
        "jsCode": "// Apply Progressive Tax Brackets\n// Calculate tax liability using tiered rates for medium and large businesses\n// Tax brackets: 10%, 20%, 30%, 37% based on income thresholds\n\nconst items = $input.all();\nconst results = [];\n\n// Define progressive tax brackets\nconst taxBrackets = [\n  { threshold: 0, rate: 0.10, name: 'Tier 1 (0-50k)' },\n  { threshold: 50000, rate: 0.20, name: 'Tier 2 (50k-100k)' },\n  { threshold: 100000, rate: 0.30, name: 'Tier 3 (100k-250k)' },\n  { threshold: 250000, rate: 0.37, name: 'Tier 4 (250k+)' }\n];\n\n// Process each item\nfor (const item of items) {\n  const income = item.json.totalRevenue || item.json.income || 0;\n  \n  let totalTax = 0;\n  let remainingIncome = income;\n  const bracketBreakdown = [];\n  \n  // Calculate tax for each bracket\n  for (let i = 0; i < taxBrackets.length; i++) {\n    const currentBracket = taxBrackets[i];\n    const nextBracket = taxBrackets[i + 1];\n    \n    if (remainingIncome <= 0) break;\n    \n    let taxableInThisBracket = 0;\n    \n    if (nextBracket) {\n      // Not the highest bracket\n      const bracketCap = nextBracket.threshold - currentBracket.threshold;\n      taxableInThisBracket = Math.min(remainingIncome, bracketCap);\n    } else {\n      // Highest bracket - tax all remaining income\n      taxableInThisBracket = remainingIncome;\n    }\n    \n    const taxForBracket = taxableInThisBracket * currentBracket.rate;\n    totalTax += taxForBracket;\n    remainingIncome -= taxableInThisBracket;\n    \n    if (taxableInThisBracket > 0) {\n      bracketBreakdown.push({\n        bracket: currentBracket.name,\n        rate: currentBracket.rate * 100 + '%',\n        taxableAmount: Math.round(taxableInThisBracket * 100) / 100,\n        taxAmount: Math.round(taxForBracket * 100) / 100\n      });\n    }\n  }\n  \n  // Calculate effective tax rate\n  const effectiveTaxRate = income > 0 ? (totalTax / income) * 100 : 0;\n  \n  results.push({\n    json: {\n      ...item.json,\n      progressiveTaxCalculation: {\n        totalIncome: Math.round(income * 100) / 100,\n        totalTax: Math.round(totalTax * 100) / 100,\n        effectiveTaxRate: Math.round(effectiveTaxRate * 100) / 100 + '%',\n        netIncome: Math.round((income - totalTax) * 100) / 100,\n        bracketBreakdown: bracketBreakdown,\n        calculationDate: new Date().toISOString()\n      }\n    }\n  });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "9fd6ce70-63eb-42e6-92a5-94612d8ad044",
      "name": "Detect Anomalies",
      "type": "n8n-nodes-base.code",
      "position": [
        208,
        256
      ],
      "parameters": {
        "jsCode": "// Detect revenue anomalies using statistical analysis (z-score method)\n// Flag outliers that exceed 2 standard deviations from the mean\n\nconst items = $input.all();\n\n// Extract revenue values from all items\nconst revenues = items.map(item => {\n  const revenue = item.json.revenue || item.json.totalRevenue || item.json.projectedRevenue || 0;\n  return parseFloat(revenue);\n}).filter(rev => !isNaN(rev) && rev > 0);\n\n// Check if we have enough data points\nif (revenues.length < 3) {\n  return items.map(item => ({\n    json: {\n      ...item.json,\n      anomalyDetection: {\n        isAnomaly: false,\n        reason: 'Insufficient data points for statistical analysis',\n        dataPoints: revenues.length\n      }\n    }\n  }));\n}\n\n// Calculate mean (average)\nconst mean = revenues.reduce((sum, val) => sum + val, 0) / revenues.length;\n\n// Calculate standard deviation\nconst squaredDiffs = revenues.map(val => Math.pow(val - mean, 2));\nconst variance = squaredDiffs.reduce((sum, val) => sum + val, 0) / revenues.length;\nconst stdDev = Math.sqrt(variance);\n\n// Z-score threshold (2 standard deviations)\nconst zScoreThreshold = 2;\n\n// Process each item and detect anomalies\nconst results = items.map(item => {\n  const revenue = item.json.revenue || item.json.totalRevenue || item.json.projectedRevenue || 0;\n  const revenueValue = parseFloat(revenue);\n  \n  // Calculate z-score for this revenue value\n  const zScore = stdDev !== 0 ? Math.abs((revenueValue - mean) / stdDev) : 0;\n  \n  // Determine if this is an anomaly\n  const isAnomaly = zScore > zScoreThreshold;\n  \n  // Determine anomaly type\n  let anomalyType = 'normal';\n  if (isAnomaly) {\n    anomalyType = revenueValue > mean ? 'unusually_high' : 'unusually_low';\n  }\n  \n  return {\n    json: {\n      ...item.json,\n      anomalyDetection: {\n        isAnomaly,\n        zScore: parseFloat(zScore.toFixed(2)),\n        threshold: zScoreThreshold,\n        anomalyType,\n        revenue: revenueValue,\n        mean: parseFloat(mean.toFixed(2)),\n        stdDev: parseFloat(stdDev.toFixed(2)),\n        deviationFromMean: parseFloat((revenueValue - mean).toFixed(2)),\n        percentageDeviation: parseFloat(((revenueValue - mean) / mean * 100).toFixed(2)),\n        detectionDate: new Date().toISOString()\n      }\n    }\n  };\n});\n\n// Add summary statistics\nconst anomalyCount = results.filter(r => r.json.anomalyDetection.isAnomaly).length;\nconst anomalyRate = (anomalyCount / results.length * 100).toFixed(2);\n\n// Add summary to first item\nif (results.length > 0) {\n  results[0].json.anomalyDetection.summary = {\n    totalItems: results.length,\n    anomaliesDetected: anomalyCount,\n    anomalyRate: `${anomalyRate}%`,\n    mean: parseFloat(mean.toFixed(2)),\n    stdDev: parseFloat(stdDev.toFixed(2)),\n    minRevenue: Math.min(...revenues),\n    maxRevenue: Math.max(...revenues)\n  };\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "853c325f-cfdb-4a3c-9eb7-d1b0f04da49a",
      "name": "Format Alert Data",
      "type": "n8n-nodes-base.set",
      "position": [
        656,
        384
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "id-1",
              "name": "alertType",
              "type": "string",
              "value": "Revenue Anomaly Detected"
            },
            {
              "id": "id-2",
              "name": "alertMessage",
              "type": "string",
              "value": "={{ 'Unusual revenue pattern detected: ' + $json.anomalyDetails }}"
            },
            {
              "id": "id-3",
              "name": "severity",
              "type": "string",
              "value": "High"
            },
            {
              "id": "id-4",
              "name": "timestamp",
              "type": "string",
              "value": "={{ $now.toISO() }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "83ac3974-86c0-49b1-b31f-c236efa8bfe0",
      "name": "Send Anomaly Alert",
      "type": "n8n-nodes-base.gmail",
      "position": [
        880,
        384
      ],
      "parameters": {
        "sendTo": "={{ $('Workflow Configuration').first().json.taxAgentEmail }}",
        "message": "=<h2 style=\"color: #d32f2f;\">\u26a0\ufe0f Revenue Anomaly Alert</h2>\n\n<h3>Anomaly Details</h3>\n<table border=\"1\" cellpadding=\"8\" cellspacing=\"0\" style=\"border-collapse: collapse; width: 100%;\">\n  <tr>\n    <th>Timestamp</th>\n    <td>{{ $json.timestamp }}</td>\n  </tr>\n  <tr>\n    <th>Severity Level</th>\n    <td><strong style=\"color: {{ $json.severityLevel === 'High' ? '#d32f2f' : ($json.severityLevel === 'Medium' ? '#f57c00' : '#388e3c') }}\">{{ $json.severityLevel }}</strong></td>\n  </tr>\n  <tr>\n    <th>Anomaly Type</th>\n    <td>{{ $json.anomalyType }}</td>\n  </tr>\n  <tr>\n    <th>Expected Value</th>\n    <td>${{ $json.expectedValue }}</td>\n  </tr>\n  <tr>\n    <th>Actual Value</th>\n    <td>${{ $json.actualValue }}</td>\n  </tr>\n  <tr>\n    <th>Deviation</th>\n    <td>{{ $json.deviation }}%</td>\n  </tr>\n</table>\n\n<h3>Recommended Actions</h3>\n<ul>\n  <li>{{ $json.recommendedAction1 }}</li>\n  <li>{{ $json.recommendedAction2 }}</li>\n  <li>{{ $json.recommendedAction3 }}</li>\n</ul>\n\n<h3>Impact Assessment</h3>\n<p>{{ $json.impactDescription }}</p>\n\n<p style=\"margin-top: 20px;\"><strong>Please review this anomaly immediately and take appropriate action.</strong></p>\n\n<p>Best regards,<br>Automated Tax Monitoring System</p>",
        "options": {},
        "subject": "=ALERT: Revenue Anomaly Detected - {{ $json.timestamp }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "62b16b45-2484-46bb-aa29-bd812e2c7856",
      "name": "Combine Tax Calculations",
      "type": "n8n-nodes-base.merge",
      "position": [
        1104,
        128
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.2
    },
    {
      "id": "ef282ea0-3880-4724-8dd9-b8a92827f39f",
      "name": "Generate Scenario Analysis",
      "type": "n8n-nodes-base.code",
      "position": [
        1776,
        432
      ],
      "parameters": {
        "jsCode": "// Generate Scenario Analysis for Tax Forecasting\n// Creates best-case, worst-case, and most-likely scenarios with different growth rates and market conditions\n\nconst items = $input.all();\n\n// Get forecast data from previous node\nconst forecastData = items.map(item => item.json);\n\n// Define scenario parameters\nconst scenarios = {\n  bestCase: {\n    name: 'Best Case',\n    growthRate: 0.15,  // 15% monthly growth\n    marketCondition: 'Strong market expansion',\n    taxEfficiency: 0.95,  // 5% tax optimization\n    description: 'Optimistic scenario with strong market growth and effective tax planning'\n  },\n  mostLikely: {\n    name: 'Most Likely',\n    growthRate: 0.05,  // 5% monthly growth\n    marketCondition: 'Stable market conditions',\n    taxEfficiency: 1.0,  // No tax optimization\n    description: 'Realistic scenario based on historical trends'\n  },\n  worstCase: {\n    name: 'Worst Case',\n    growthRate: -0.05,  // -5% monthly decline\n    marketCondition: 'Economic downturn',\n    taxEfficiency: 1.05,  // 5% additional tax burden\n    description: 'Conservative scenario with market contraction'\n  }\n};\n\n// Get base revenue from forecast data\nconst baseRevenue = forecastData[0]?.projectedRevenue || 100000;\nconst baseTaxRate = forecastData[0]?.taxRate ? parseFloat(forecastData[0].taxRate) / 100 : 0.25;\n\n// Generate scenarios for next 6 months\nconst scenarioResults = [];\n\nfor (const [scenarioKey, scenario] of Object.entries(scenarios)) {\n  const monthlyProjections = [];\n  const currentDate = new Date();\n  \n  for (let month = 1; month <= 6; month++) {\n    const forecastDate = new Date(currentDate);\n    forecastDate.setMonth(currentDate.getMonth() + month);\n    \n    // Calculate projected revenue with growth rate\n    const projectedRevenue = baseRevenue * Math.pow(1 + scenario.growthRate, month);\n    \n    // Calculate tax liability with efficiency factor\n    const taxLiability = projectedRevenue * baseTaxRate * scenario.taxEfficiency;\n    \n    // Calculate net revenue\n    const netRevenue = projectedRevenue - taxLiability;\n    \n    monthlyProjections.push({\n      month: forecastDate.toISOString().slice(0, 7),\n      monthName: forecastDate.toLocaleString('default', { month: 'long', year: 'numeric' }),\n      projectedRevenue: Math.round(projectedRevenue * 100) / 100,\n      taxLiability: Math.round(taxLiability * 100) / 100,\n      netRevenue: Math.round(netRevenue * 100) / 100\n    });\n  }\n  \n  // Calculate totals for the scenario\n  const totalRevenue = monthlyProjections.reduce((sum, m) => sum + m.projectedRevenue, 0);\n  const totalTax = monthlyProjections.reduce((sum, m) => sum + m.taxLiability, 0);\n  const totalNet = monthlyProjections.reduce((sum, m) => sum + m.netRevenue, 0);\n  \n  scenarioResults.push({\n    json: {\n      scenario: scenario.name,\n      scenarioKey: scenarioKey,\n      description: scenario.description,\n      parameters: {\n        growthRate: (scenario.growthRate * 100).toFixed(2) + '%',\n        marketCondition: scenario.marketCondition,\n        taxEfficiency: scenario.taxEfficiency,\n        baseTaxRate: (baseTaxRate * 100).toFixed(2) + '%'\n      },\n      monthlyProjections: monthlyProjections,\n      sixMonthTotals: {\n        totalRevenue: Math.round(totalRevenue * 100) / 100,\n        totalTaxLiability: Math.round(totalTax * 100) / 100,\n        totalNetRevenue: Math.round(totalNet * 100) / 100,\n        averageMonthlyRevenue: Math.round((totalRevenue / 6) * 100) / 100\n      },\n      generatedAt: new Date().toISOString()\n    }\n  });\n}\n\nreturn scenarioResults;"
      },
      "typeVersion": 2
    },
    {
      "id": "26b2a0b4-1801-4a62-85f4-7fced38b8411",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        528,
        -528
      ],
      "parameters": {
        "color": 6,
        "width": 480,
        "height": 352,
        "content": "\n## Prerequisites\nActive e-commerce platform accounts with API access. Payment processor credentials.\n## Use Cases\nAutomated monthly sales tax calculations for multi-state e-commerce. \n## Customization\nModify tax calculation rules for specific jurisdiction requirements. \n## Benefits\nReduces tax preparation time by 80% through end-to-end automation. "
      },
      "typeVersion": 1
    },
    {
      "id": "707ec656-deb9-4ab2-94ea-8ac0f0e3b646",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        96,
        -496
      ],
      "parameters": {
        "width": 384,
        "height": 336,
        "content": "## Setup Steps\n1. Configure e-commerce API credentials for transaction access\n2. Set up payment processor integrations (Stripe, PayPal) for revenue reconciliation\n3. Add accounting system credentials (QuickBooks, Xero) for financial data\n4. Configure OpenAI API key for anomaly detection and tax analysis\n5. Set Gmail OAuth credentials for tax team alert notifications\n6. Link Google Sheets for report storage and audit trail documentation\n7. Connect Airtable workspace for structured tax record management"
      },
      "typeVersion": 1
    },
    {
      "id": "0617d844-ff60-4cb3-a747-699283982373",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -832,
        -448
      ],
      "parameters": {
        "width": 896,
        "height": 288,
        "content": "## How It Works\nThis workflow automates tax compliance by aggregating multi-channel revenue data, calculating jurisdiction-specific tax obligations, detecting anomalies, and generating submission-ready reports for tax authorities. Designed for finance teams, tax professionals, and e-commerce operations, it solves the challenge of manually reconciling transactions across multiple sales channels, applying complex tax rules, and preparing compliant filings under tight deadlines. The system triggers monthly or on-demand, fetching revenue data from e-commerce platforms, payment processors, and accounting systems. Transaction records flow through validation layers that merge historical context, classify revenue streams, and calculate tax obligations using jurisdiction-specific rules engines. AI models detect anomalies in tax calculations, identify unusual deduction patterns, and flag potential audit risks. The workflow routes revenue data by tax jurisdiction, applies progressive tax brackets, and generates formatted reports matching authority specifications. Critical anomalies trigger immediate alerts to tax teams via Gmail, while finalized reports store in Google Sheets and Airtable for audit trails. This eliminates 80% of manual tax preparation work, ensures multi-jurisdiction compliance, and reduces filing errors."
      },
      "typeVersion": 1
    },
    {
      "id": "b75db707-2e79-4488-8015-e16ca07452dc",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        176,
        -112
      ],
      "parameters": {
        "color": 7,
        "width": 1296,
        "height": 864,
        "content": "## AI Anomaly Detection  & Alert\nAnalyzes calculated tax obligations using AI models to identify unusual patterns, deduction anomalies, and potential audit triggers.\n**Why** - Catches calculation errors and suspicious patterns before submission, reducing audit risk and compliance penalties."
      },
      "typeVersion": 1
    },
    {
      "id": "94d69234-5e2c-443f-b077-25dacf74df9e",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -304,
        -112
      ],
      "parameters": {
        "color": 7,
        "width": 464,
        "height": 896,
        "content": "## Tax Calculation Engine \nRoutes transactions by jurisdiction, applies tax rates, calculates obligations, and handles progressive brackets using rule-based logic.\n**Why** - Automates complex tax calculations across multiple jurisdictions, preventing errors from manual formula application."
      },
      "typeVersion": 1
    },
    {
      "id": "2d2cd2c4-197b-4267-9ab5-77e6f2f7ecc5",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -976,
        -112
      ],
      "parameters": {
        "color": 7,
        "width": 656,
        "height": 864,
        "content": "## Revenue Aggregation \nFetches transaction data from multiple e-commerce platforms, payment gateways, and accounting systems via scheduled triggers.\n**Why** - Consolidates fragmented revenue streams into unified datasets, eliminating manual export/import cycles across platforms."
      },
      "typeVersion": 1
    },
    {
      "id": "66116014-1018-4d0f-98de-76128be4dcb0",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1504,
        -128
      ],
      "parameters": {
        "color": 7,
        "width": 960,
        "height": 1024,
        "content": "\n## Report Generation \nFormats tax data into authority-specific submission templates, generates supporting documentation, and creates audit-ready reports.\n**Why** - Produces compliant filings matching exact regulatory formats, eliminating manual document preparation and formatting errors."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "availableInMCP": false,
    "executionOrder": "v1"
  },
  "versionId": "d781f7fd-b025-49c9-8576-06cc5c200cad",
  "connections": {
    "Detect Anomalies": {
      "main": [
        [
          {
            "node": "Check Tax Threshold",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Monthly Schedule": {
      "main": [
        [
          {
            "node": "Workflow Configuration",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Alert Data": {
      "main": [
        [
          {
            "node": "Send Anomaly Alert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Report Data": {
      "main": [
        [
          {
            "node": "Send Report to Tax Agent",
            "type": "main",
            "index": 0
          },
          {
            "node": "Store in Google Sheets",
            "type": "main",
            "index": 0
          },
          {
            "node": "Store in Airtable",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Tax Threshold": {
      "main": [
        [
          {
            "node": "Format Alert Data",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Route by Revenue Tier",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Route by Revenue Tier": {
      "main": [
        [
          {
            "node": "Calculate Tax Deductions",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Apply Progressive Tax Brackets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Workflow Configuration": {
      "main": [
        [
          {
            "node": "Aggregate Multi-Channel Revenue",
            "type": "main",
            "index": 0
          },
          {
            "node": "Fetch Historical Revenue Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Tax Deductions": {
      "main": [
        [
          {
            "node": "Combine Tax Calculations",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Combine Tax Calculations": {
      "main": [
        [
          {
            "node": "Calculate Tax Liabilities",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Tax Liabilities": {
      "main": [
        [
          {
            "node": "Generate Rolling Forecast",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Rolling Forecast": {
      "main": [
        [
          {
            "node": "Format Report Data",
            "type": "main",
            "index": 0
          },
          {
            "node": "Generate Scenario Analysis",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Scenario Analysis": {
      "main": [
        [
          {
            "node": "Format Report Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Revenue Statistics": {
      "main": [
        [
          {
            "node": "Detect Anomalies",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Historical Revenue Data": {
      "main": [
        [
          {
            "node": "Merge Historical and Current Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Apply Progressive Tax Brackets": {
      "main": [
        [
          {
            "node": "Combine Tax Calculations",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Aggregate Multi-Channel Revenue": {
      "main": [
        [
          {
            "node": "Calculate Tax Liabilities",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge Historical and Current Data",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Merge Historical and Current Data": {
      "main": [
        [
          {
            "node": "Calculate Revenue Statistics",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• gmailOAuth2
• googleSheetsOAuth2Api

About this workflow

This workflow automates tax compliance by aggregating multi-channel revenue data, calculating jurisdiction-specific tax obligations, detecting anomalies, and generating submission-ready reports for tax authorities. Designed for finance teams, tax professionals, and e-commerce…

Source: https://n8n.io/workflows/12735/ — original creator credit. Request a take-down →

More Data & Sheets workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.