How to Keep Monthly Reports Accurate When You Use AI for Drafting Commentary

Stop AI-generated commentary from breaking your monthly reports — a template and workflow that keep numbers and narrative in sync

Hook: You want AI to speed up monthly reporting, not create extra cleanup and audit headaches. In 2026 many UK small businesses use AI to draft commentary — but without structure, narratives drift from the numbers. This guide gives a practical Excel template and step-by-step workflow that separates numeric sheets from AI-drafted commentary, runs automated consistency checks, and routes drafts through a clear approval flow so outputs are trustworthy and auditable.

The problem in 2026: AI drafts fast, but can mislead

Two recent trends explain why this problem matters now. First, organisations increasingly treat AI as a productivity engine for execution tasks — drafting sections, summarising trends, writing headlines — while humans keep strategy and sign-off. Second, AI still hallucinates numbers or uses stale context unless you control inputs. The January 2026 industry coverage highlighted this tension: teams love execution gains but struggle with quality and governance.

"Use AI for execution — but never as a blind source of truth."

That means your reporting process must be redesigned: keep the authoritative numeric engine separate from any AI draft, force explicit numeric tokens when AI writes commentary, and verify every numeric reference with automatic checks before sign-off.

Core idea: separate numeric engine + narrative layer + validation + approval

We propose a template built around four pillars. Each maps to a worksheet (or set of sheets) in a single Excel workbook and integrates with Power Query, the Data Model (Power Pivot), and modern Office automation (Power Automate / Office Scripts):

• Numeric Engine — all raw data, staging via Power Query, canonical measures in the data model and pivot tables.
• Key Metrics Table — an explicit export of the values AI may reference (named ranges / structured table).
• Narrative Drafts — AI-generated commentary that references metrics using enforced tokens/placeholders, not free-text numbers.
• Validation & Approval — automated checks that compare narrative tokens (or extracted numbers) to the Numeric Engine and an approval workflow with audit trail.

Why tokens matter (and how to enforce them)

Ask your AI to include only token placeholders for numeric references, for example: [[Revenue_MoM]], [[GrossMarginPct]], [[Units_Sold]]. Tokens are deterministic strings your workbook can reliably parse. If the AI returns raw numbers, parsing and error detection become brittle.

Example token style we recommend: double square brackets with a canonical key. The AI prompt includes the Key Metrics table (or a short JSON summary) and instructs the model: "Use only tokens like [[Revenue_MoM]] where a metric must appear; do not write numeric values directly."

How to build the Key Metrics table (step-by-step)

1. Use Power Query to load data sources (sales, finance, CRM). Keep queries raw and separate — don't overwrite historic data.
2. Create a Staging sheet with cleaned records (date, product, region, revenue, costs, units).
3. Load the staging table into the Data Model and build pivot tables and DAX measures for metrics you need (Revenue, Cost, Gross Margin, YoY, MoM, Avg Order Value).
4. Export those metrics into a single structured table named KeyMetrics with columns: MetricKey, MetricValue, MetricUnit, SnapshotDate, SourcePivot.
5. Define named ranges for each MetricKey (eg Key_Revenue_MoM = INDEX(KeyMetrics[MetricValue], MATCH("Revenue_MoM", KeyMetrics[MetricKey],0))).

Named ranges make it easy for Office Scripts, Power Automate and manual links to refer to the values reliably.

Power Query + Data Model tips for robust numbers

Power Query is the best place to standardise dates, currencies and units before anything touches AI. In 2026 Power Query keeps getting smarter: prefer using query parameters for snapshot date, and cache staging tables as connections-only to avoid accidental edits.

Quick checklist:

• Use a single Date table in the Data Model and mark it as a Date table for DAX time intelligence.
• Create DAX measures for deltas and percentages so you have one canonical formula for each metric.
• Round in the KeyMetrics export using your report precision rules (e.g., round to thousands or 2 d.p.).

How to get AI to draft commentary that stays honest

When calling an LLM or Copilot-style assistant, send only the KeyMetrics table (or a small JSON payload) rather than the full dataset. Include clear instructions to use token placeholders for every numeric reference.

Sample prompt (shortened):

Write a concise monthly commentary (<=350 words) for the board. Use only these tokens for numeric references: [[Revenue_MoM]], [[Revenue_YoY]], [[GrossMarginPct]], [[Units_Sold]]. Do not include numeric figures directly. After the commentary, include a "ChangeLog" line listing datapoints used.
  

By forcing tokens you control where numbers appear and you can check they map to the canonical values before publishing.

Automated consistency checks — two practical methods

Method A: Token-based comparison (recommended). After the AI draft is inserted into the Narrative sheet, build formulas to substitute tokens with KeyMetrics values and present a "live rendered" draft. You can also show a side-by-side numeric check.

Sample substitution formula approach (in Excel 365):

1. Put the AI text in cell Narrative!A2.
2. Create a small lookup table Tokens with TokenKey and TokenValue (TokenValue = named range or INDEX lookup to KeyMetrics).
3. Use a LET + REDUCE pattern to iterate replacements (or a simple nested SUBSTITUTE if token count is small). Example (conceptual):

=LET(text, Narrative!A2,
 tokenList, Tokens[TokenKey],
 valueList, Tokens[TokenValue],
 result, REDUCE(text, SEQUENCE(ROWS(Tokens)), LAMBDA(acc,i, SUBSTITUTE(acc, INDEX(tokenList,i), TEXT(INDEX(valueList,i), "#,##0.0")))),
 result)
  

This returns a rendered commentary that matches KeyMetrics. You then compute a simple flag cell: "OK" if every token was replaced with a value; "Missing Token" if not.

Method B: Extract numeric values from free-text (fallback). If AI inserted raw numbers, use Power Query or Office Scripts to extract numeric substrings and match them to KeyMetrics. Power Query's Text.Select or Text.RegexReplace functions can isolate numbers and then you can compare with the canonical values within a tolerance.

Simple mismatch formula

When you have a numeric value from the narrative (NarrativeValue) and the canonical value (ActualValue), use:

=IF(ActualValue=0, IF(NarrativeValue=0, "OK", "Mismatch"), IF(ABS((NarrativeValue-ActualValue)/ActualValue) > 0.01, "Mismatch", "OK"))
  

Here 1% is the tolerance. Adjust to 0.005 or 0.05 depending on context.

Approval workflow and auditability — from simple to automated

Small teams need a compact in-sheet approval flow; larger teams should use Power Automate/Teams to route approvals and write back to the sheet. Both options share the same fields in the workbook:

• DraftID, Version, Author, NarrativeText, RenderedText, CheckStatus, CheckNotes
• ApprovalStatus (Draft / In Review / Approved / Rejected)
• Approver, ApprovalTimestamp, ApproverComment

Manual Excel approval (for small teams)

1. Protect the Numeric Engine sheet (Review > Protect Sheet) to prevent accidental edits.
2. Enable Data Validation on ApprovalStatus (dropdown list) and conditional formatting (green for Approved, red for Rejected).
3. Use a simple macro or Office Script to append an audit row each time ApprovalStatus changes.

Automated approval (Power Automate + Teams)

In 2026 most teams connect Excel Online (stored in SharePoint/OneDrive) to Power Automate. A typical flow:

1. User clicks "Request Approval" (a button that triggers a Power Automate flow).
2. Flow sends an adaptive card to the approver in Teams with RenderedText and CheckStatus.
3. Approver approves or rejects; the flow writes Approver, ApprovalTimestamp and ApproverComment back into the workbook via the Excel connector or calls an Office Script to append an audit entry.

This approach creates a tamper-evident trail and integrates with your existing identity provider (Azure AD) for single sign-on auditability.

Audit trail: record everything you might need for compliance

Even if you don't have formal audit needs today, store the following in a ChangeLog sheet:

• User (Environment.UserName or Office identity)
• Action (DraftCreated, DraftEdited, ApprovalRequested, Approved, Rejected)
• Timestamp (UTC)
• Version and SnapshotDate
• KeyMetrics snapshot (hash or full list)

Office Scripts example (conceptual) to append an audit row:

async function main(workbook: ExcelScript.Workbook, action:string, draftId:string, user:string) {
  const sheet = workbook.getWorksheet("ChangeLog");
  sheet.getRange("A1").getSurroundingRegion().getLastRow() // append logic...
  sheet.getRange("A1").getOffsetRange(nextRow,0).setValues([[new Date().toISOString(), action, draftId, user]]);
}
  

In practice you'll call this from Power Automate or a Workbook button.

Practical checks to include in every monthly report

• Token completeness: every token in the narrative must exist in KeyMetrics.
• Numeric match: substituted narrative values must equal KeyMetrics within tolerance.
• Rounding and units: narrative uses the same rounding and unit as KeyMetrics (thousands vs units).
• Percent vs points: clarity if a change is percentage points or percent.
• Top-down reconciliation: totals in narrative match pivot table totals (e.g., total revenue equals sum of region revenues).
• Staleness check: snapshot date in narrative matches the latest KeyMetrics snapshot.

Example: quick, reproducible flow for a small UK retailer

Scenario: monthly board pack. Steps:

1. Refresh Power Query (staging data from Shopify & Xero).
2. Refresh Data Model and KeyMetrics table.
3. Run an Office Script that exports KeyMetrics JSON and calls your LLM to create a draft with tokens.
4. Paste AI draft into Narrative sheet; run in-sheet REDUCE function to render text and compute token flags.
5. If any flag = Mismatch, the author fixes KeyMetrics or regenerates draft; otherwise author sets ApprovalStatus = In Review.
6. Trigger Power Automate: sends adaptive card to approver. Approver approves and the flow writes back approval info and logs the audit row.

With this process you avoid the common failure: AI writing exact figures that were calculated from last month's draft rather than the refreshed dataset.

Case study — how tokens stopped a bad monthly narrative

A small chain of coffee shops used AI to write monthly narratives. One month the AI quoted last month's revenue as this month's; the board questioned credibility. After adopting a token rule and the KeyMetrics-driven workflow above, every numeric reference was validated before distribution. The team saved an average of 2 hours per report and reduced post-distribution corrections to zero in six months.

2026 trends and future-proofing your process

Expect these trends to shape reporting over the next 12–24 months:

• Tighter audit expectations: regulators and auditors increasingly expect documented data lineage for reports that used AI.
• Native LLM integrations: Copilot-style features are becoming common inside Excel; still, the same governance rules apply — treat AI as an executive assistant, not the source of truth.
• Automated validation become standard: more Excel functions and connectors will add regex and token utilities; Power Query will further simplify text extraction from commentary.
• Teams will standardise tokens: shared token dictionaries across departments will reduce ambiguity and speed automation.

Quick implementation checklist (30–90 day plan)

1. Week 1: Build Power Query staging and canonical KeyMetrics table.
2. Week 2: Create Narrative and Token lookup; test token substitution formulas.
3. Week 3: Add validation rules and rounding/unit checks; create sample AI prompt that uses tokens.
4. Week 4: Pilot with one report; add manual audit logging and protect numeric sheets.
5. Month 2: Automate approval with Power Automate; extend token dictionary.
6. Month 3: Run a retrospective and tune tolerance thresholds and rounding rules.

Final takeaways — what to do tomorrow

• Do not let AI write numeric values directly into final narrative. Enforce tokens.
• Keep one canonical numeric source (Power Query + Data Model) and export a KeyMetrics table for AI to reference.
• Automate consistency checks and fail the approval flow if anything mismatches beyond your tolerance.
• Log and audit every approval — even small teams benefit from a simple ChangeLog.

Where to get the template and training

If you want a ready-made, UK-focused Excel template that implements everything in this article (Power Query staging, KeyMetrics export, token-driven Narrative sheet, validation formulas and a sample Power Automate flow), we offer a downloadable package plus a 90-minute workshop that walks your team through customizing tokens and thresholds. In 2026 we see organisations adopting these patterns fast — the sooner you enforce structure, the more reliable your AI-assisted reporting will be.

Call to action: Download the free template and sample Power Automate flow from our site and sign up for a short workshop to set thresholds and tokens for your business. Make AI your drafting assistant — not the source of blame when numbers don't add up.

Related Reading

• Desk Diffusers for Creatives: Scents That Boost Focus Without Distracting Colleagues
• How to Use Sports-Model Probabilities to Size Positions and Manage Dividend Risk
• Microwavable vs Electric vs Rechargeable: Which Cat Warming Solution Is Right for Your Home?
• Teaching Intertextuality Through Music: Mitski’s New Album and Gothic Influences
• Art & Arrival: Planning a Trip Around Major Biennales and Island Art Weeks