Expert guide
Automate Monthly Report Commentary in Excel with AI
Excel does the maths, AI drafts the words, finance signs off
Call us at +1 (385) 386-3860
Short answer: build a variance table in Excel (actual, budget, prior year, differences in amount and percent), flag lines that exceed materiality thresholds, add a short Known Drivers list, and send only the flagged lines and drivers to an AI model with instructions to describe, not speculate. Paste the draft into the report, review and sign off. A full monthly pipeline is part of our Excel dashboard and BI reporting work.
Month-end packs follow the same rhythm everywhere: the numbers are ready on day five, and the commentary takes until day eight. Writing "Revenue was $412k, 6% above budget, driven by the West region" twenty times is not analysis; it is transcription. AI is good at transcription. It is bad at knowing why your West region did well. The design below uses each for what it is good at.
Step 1: Build a Proper Variance Table
| Column | Formula idea | Why it matters |
|---|---|---|
| Line | Account or KPI name | What the reader recognises |
| Actual / Budget / PY | SUMIFS from the ledger or data model | Single source of truth |
| Var vs Budget | =Actual − Budget | Absolute materiality |
| Var % vs Budget | =IF(Budget=0,"",Var/ABS(Budget)) | Relative materiality; ABS keeps sign sensible for costs |
| Favourable? | Depends on line type (revenue up good, cost up bad) | Avoids AI calling a cost overrun 'growth' |
| Material? | =AND(ABS(Var)>=AmtThreshold, ABS(Var%)>=PctThreshold) | Only these lines get commentary |
The Favourable column is the one most teams skip and most AI drafts get wrong. A 12 percent increase in fuel cost is not good news. Calculate direction in Excel so the model does not have to infer it.
Step 2: Add Known Drivers
Create a small table where the finance lead types facts that explain movements this month. These are the only explanations the AI is allowed to use.
Known drivers – October 2026 - Price increase of 4% effective 1 October on all service lines. - West branch: two new commercial contracts started 6 October. - Fuel: fleet expanded from 14 to 17 vans in September. - Marketing: trade show (one-off, $18,400) moved from November to October.
Step 3: Generate the Input for AI
Build a single cell that assembles the material lines into compact text. This is what you send, which keeps tokens low and avoids sending raw ledger data.
=TEXTJOIN(CHAR(10), TRUE,
FILTER(tblVar[Line] & ": actual " & TEXT(tblVar[Actual], "$#,##0") &
", budget " & TEXT(tblVar[Budget], "$#,##0") &
", var " & TEXT(tblVar[Var], "+$#,##0;-$#,##0") &
" (" & TEXT(tblVar[Var %], "+0%;-0%") & ")" &
", " & IF(tblVar[Favourable?], "favourable", "adverse"),
tblVar[Material?]))Step 4: The Commentary Prompt
You draft management report commentary for a services company. Write 4–6 short bullet points for the month, most material first. Rules: - Use only the figures provided. Do not recalculate or round differently. - Explain a movement ONLY if a Known Driver clearly applies; name it. - If no driver applies, write "Driver to be confirmed" — do not guess. - Say favourable/adverse as given. Plain business English, no hype. - Max 30 words per bullet. Material lines: [paste the TEXTJOIN cell] Known drivers: [paste the drivers table]
"Driver to be confirmed" is the most valuable line the AI can write. It turns an unexplained variance into a question for the budget holder instead of an invented story in the board pack.
Example output
• Revenue $412,300, +$24,100 (+6%) vs budget, favourable. Driven by the 4% price increase from 1 October and two new West branch commercial contracts.
• Marketing $31,900, +$18,400 (+136%) vs budget, adverse. Timing only: the trade show moved from November to October.
• Fuel $22,700, +$3,900 (+21%) vs budget, adverse. Fleet expanded from 14 to 17 vans in September.
• Subcontractor costs $58,200, +$7,600 (+15%) vs budget, adverse. Driver to be confirmed.
Step 5: Review and Sign Off
Check every figure in the draft against the variance table. The prompt says not to recalculate; verify it complied.
Send each 'Driver to be confirmed' to the budget holder with the figure.
Edit tone for the audience: a board pack and a branch managers' email read differently.
Store the final commentary next to the month's numbers for next year's comparison.
Where This Works Beyond Finance
| Report | Typical material lines | Typical known drivers |
|---|---|---|
| Sales performance | Revenue by region, rep, product | Promotions, new hires, lost accounts |
| Operations KPIs | On-time delivery, jobs per day, rework | Staffing gaps, weather, new equipment |
| Clinic or practice reports | Visits, no-show rate, collections | Provider leave, schedule changes, payer delays |
| Inventory | Stock value, turns, stockouts | Supplier delays, seasonal build |
Automating the Whole Pack
In a mature setup, the ledger or sales data refreshes through Power Query, the variance table recalculates, the AI draft is generated by a button or a scheduled job, and the finished pack is saved as PDF and emailed. Consolidating several entities or branches first is covered by Excel data consolidation; the month-end steps before reporting are in our month-end close checklist. We build the end-to-end version, with logging of every AI call, as AI workflow automation.
FAQ
AI report commentary FAQ
For finance and operations teams producing monthly packs