Excel Copilot for Accountants

Step 1 — Protect the sources

• Store the originals in a read-only source folder.
• Create a separate working workbook.
• Record the file names, extraction dates, periods, and row counts.
• Import each source into a separate worksheet.
• Convert each source population into a named Excel Table (tblGL, tblBank, tblBudget, tblCOA).
• Remember AutoSave: edits to the working copy in OneDrive or SharePoint persist as you go—do not edit the read-only originals.

Step 2 — Add a control sheet

Create a worksheet named Control with formula-driven totals. Do not ask Copilot to invent these totals. Ask it to insert native formulas referencing the named tables, then independently inspect each formula.

Step 3 — Validate the general ledger

Use Copilot Chat or editing with Copilot to design an exception list—without changing tblGL.

Review the structure of tblGL without changing it. Identify blank required fields, invalid dates, nonnumeric amounts, duplicate JournalID-LineNumber combinations, entries outside March 2026, and AccountNumbers that do not appear in tblCOA.

Return a proposed exception design on a new worksheet named GL_Validation:
- One row per exception with JournalID, LineNumber, PostingDate, AccountNumber, Amount, and ExceptionReason.
- ExceptionReason must list every rule triggered.
- Add population row count, exception row count, and a control that sums exception Amounts for high-dollar blanks or invalid amounts.
- Do not overwrite source data. Explain every formula, filter, and assumption before applying edits.

Independently sample flagged and unflagged rows. Unmapped accounts must also update the Control sheet's Unmapped accounts difference.

Step 4 — Bank recon assist

Use tblBank and cash-related GL lines (or a prepared tblCashGL). Never force matches to clear a difference.

Create a new worksheet named Bank_Recon_Work. Do not change tblBank or the cash GL population.

Propose exact matches when amount is equal and either the reference matches or the dates are within three days. Do not automatically force one-to-many or many-to-one matches. Place uncertain and unmatched items in separate sections.

For every proposed match, show the bank reference, GL journal ID, both dates, both amounts, date difference, amount difference, and matching reason. Add control totals for the original bank population, original GL population, matched items, and unmatched items. Reconcile those controls to the Control sheet bank row count and bank amount.

• Investigate deposits in transit and outstanding checks.
• Separate bank fees, interest, voids, and stale items.
• Document why each unmatched item remains open at March 31, 2026.

Step 5 — Expense variance

Build an operating-expense variance for March 2026 using tblGL, tblBudget, and tblCOA. Avoid causal language.

Create a worksheet named Expense_Variance for ABC Coffee Shop March 2026.

Locate: use tblGL, tblBudget, and tblCOA. Match on AccountNumber.

Execute: show AccountNumber, AccountName, Actual, Budget, DollarVariance, and PctVariance for operating-expense accounts only.

Accounting rules: exclude income tax and owner distributions. Treat expense above budget as unfavorable (positive). Flag absolute variance greater than $1,000 or 10%. If ABS(Budget) is less than 100, show PctVariance as N/M. Do not state a cause unless the workbook contains supporting evidence. Add a Questions for Management section for items that need operational explanation.

Review requirements: do not overwrite sources. Add source-row count, source actual total, report actual total, and a difference check that must equal zero.

Step 6 — Journal-entry review queue

Organize investigation leads. Flags are not fraud findings.

Using the March 2026 journal population derived from tblGL (and preparer/approver fields if present), create JE_Review_Queue on a new worksheet.

Flag entries that meet any of these rules:
- Posted on a weekend.
- Manual source and amount exceeds $25,000.
- Prepared and approved by the same user (if both fields exist).
- Description blank or shorter than 10 characters.
- Account on a SensitiveAccounts list you create from tblCOA (cash, related-party, and equity).
- Posted in the final two days of March and reversed in the first two days of April (if reversal markers exist).

Show the exact rule triggered. Do not describe an entry as fraudulent. Include population count and debit/credit control totals before and after filtering.

Step 7 — Management summary + prove controls

Draft a one-page management summary from verified report sheets only—not from raw, unreconciled transactions. Then prove and approve.

Create Management_Summary for March 2026 using only verified outputs from Bank_Recon_Work, Expense_Variance, and JE_Review_Queue.

Include:
- Bank reconciliation status: matched count, unmatched bank items, unmatched GL items, and unresolved dollar difference.
- Top five expense variance flags with dollar and percentage variance (facts only).
- Count of JE review flags by rule, without fraud language.
- A Questions for Management list limited to items needing operational evidence.
- Links or cell references to each supporting worksheet.

Do not invent metrics. Do not insert synthetic sample data. Add an As-of date from the workbook period (March 31, 2026). Explain every formula and assumption.