Budget vs Actuals Monitoring

The finance team produced a weekly budget vs actuals report covering all 12 departments. The data source was NetSuite — actuals pulled from the GL, compared against an annual budget that had been manually loaded into a separate Excel file at the start of the year.

Every week, a financial analyst would export the current period's GL activity from NetSuite to CSV, open the budget spreadsheet, vlookup the actuals against budget by cost center and account code, calculate the dollar and percentage variance for each line, write a sentence or two of commentary for any line over 5% variance, format the whole thing into a table, and email it to the CFO and 12 department heads.

The process started Monday morning and the report was typically sent Tuesday afternoon — occasionally Wednesday if something didn't reconcile. By the time department heads received it, the data was already 2–3 days old. The CFO's Monday leadership meeting happened without the report. Department heads who wanted to understand their spend had to wait for the analyst.

The analyst estimated this process took about 8 hours per week including the export, reconciliation, commentary writing, and formatting. There was no alerting — a department could be 40% over budget mid-week and no one would know until the next weekly report.

We built a KNIME analytics workflow that runs on a schedule every Sunday at 11pm, so the report is ready before the working week begins.

The first step pulls actuals from NetSuite using their REST API. We query the Transaction Search endpoint filtered to the current accounting period, grouped by department (class in NetSuite terminology) and account. This returns a JSON payload of all GL activity. We then flatten it into a line-by-line structure: department, account code, account name, period actuals.

Budget data lives in a Google Sheet that the finance team maintains — they update it once when the annual budget is set, and again if there's a re-forecast. The workflow fetches this sheet via the Sheets API and joins it to the actuals data on the combination of department + account code. Any account in the budget that has no actuals gets a zero actuals figure; any actuals line with no corresponding budget row is flagged as "unbudgeted spend."

After joining, the workflow calculates for each line: dollar variance (actuals minus budget), percentage variance, and a flag for whether the variance exceeds the threshold for commentary (we set this at 5% and minimum $500 absolute, to avoid commentary on $10 variances).

Once all commentary is generated, the workflow assembles an HTML email. The email has a summary section at the top (total spend vs budget for the company overall, and a count of lines in each variance band), followed by a department-by-department table. Each table shows every budget line with actuals, variance, and — for flagged lines — the Claude-generated commentary. The email is sent via SendGrid to the CFO and all department heads by 7am Monday.

Separately, any individual line with a dollar variance over $10,000 also triggers an immediate Slack message to the finance-alerts channel, regardless of when it's detected. This part runs as a second KNIME workflow that fires every 6 hours throughout the week, not just Sunday night — so large variances are flagged as they accumulate, not after the fact.

• •KNIME Server (shared executor — this workflow is not latency-sensitive)
• •NetSuite REST API — requires setting up a connected app with OAuth 2.0 credentials; the main complexity was getting the search filter syntax right for period-based GL queries
• •Google Sheets as the budget source — the finance team already worked in Sheets, so this avoided building a separate budget input UI
• •Claude API for commentary — the prompt is roughly 400 tokens; each weekly run processes between 15 and 40 flagged lines, so Claude cost is under $0.10 per weekly report
• •SendGrid for HTML email delivery — report HTML assembled in a KNIME Component with a JavaScript snippet node
• •Slack webhook for real-time alerts — messages include the department, account, budgeted amount, actual amount, and variance

The NetSuite API was the primary technical challenge. NetSuite's REST API documentation is dense and the search endpoint returns data in a structure that requires careful parsing. We spent most of week 1 on this — specifically getting the period filter to align correctly with NetSuite's internal accounting period IDs rather than calendar dates.

Claude commentary quality required iteration. The first version of the prompt produced output that read too much like a disclaimer ("it is possible that spending increased due to higher vendor costs"). We rewrote the prompt three times, adding context about the company's business model and telling Claude to be specific about what to investigate rather than hedging. The final outputs are brief but actionable.

We also discovered that some department heads wanted their section only, not the full company report. We added a configuration table in the Google Sheet where each department head's email address is listed with a flag indicating whether they receive the full report or a department-filtered version. The workflow generates both variants in the same run.