Use Excel Copilot to Build Audit Comparison Schedules

What This Does

Excel Copilot builds formatted comparison schedules, lead schedules, and variance analyses from your client's trial balance data using plain-English instructions — replacing 30–60 minutes of manual formula construction with a 2-minute conversation.

Before You Start

• You have Microsoft 365 (business or enterprise — includes Excel with Copilot)
• Your firm's Copilot subscription is activated (check with your IT or manager)
• Client trial balance or transaction data is already in your Excel workbook
• Cost: Included in Microsoft 365 Copilot subscription (~$30/user/mo)

Steps

1. Open the workbook and find Copilot

Open the Excel file containing your client's trial balance or data. Look for the Copilot button in the Home tab ribbon (right side) — it appears as a sparkle/star icon. Click it to open the Copilot panel on the right side of the screen.

What you should see: A Copilot chat panel with a text field where you describe what you want to do.

Troubleshooting: If you don't see Copilot in the ribbon, your organization may not have it enabled. Check with your IT team — it requires Microsoft 365 Copilot licensing. Some firms have enabled it for specific groups first.

2. Describe the schedule you want

In the Copilot panel, type a plain-English description of what you need:

For a balance sheet lead schedule: "Create a comparison schedule from this trial balance data. Group accounts into these categories: Current Assets, Non-Current Assets, Current Liabilities, Non-Current Liabilities, and Equity. For each category, show the prior year total, current year total, dollar variance, and percent variance. Format dollar amounts as accounting format with negative numbers in parentheses."

For a P&L variance schedule: "Create an income statement comparison from columns C (prior year) and D (current year). Group by Revenue, Cost of Revenue, Operating Expenses, and Other. Show subtotals, variance amounts, and variance percentages. Highlight rows where the variance exceeds 10% in yellow."

3. Review and adjust the output

Copilot will generate the schedule directly in your workbook. Review it:

• Confirm accounts are grouped correctly (Copilot infers categories from account descriptions — check any that were miscategorized)
• Verify variance calculations are correct on a few spot checks
• Ask Copilot to adjust anything that needs changing: "Move account 1520 from Non-Current Assets to Current Assets" or "Add a materiality threshold column showing whether each variance exceeds $50,000"

What you should see: A formatted comparison schedule with groupings, subtotals, and variances — ready for workpaper use with minor cleanup.

4. Request formulas for ongoing use

If you want the schedule to update automatically when data changes (useful for a recurring client), ask Copilot: "Replace the calculated values with formulas that reference the source data so the schedule updates if source data changes."

Real Example

Scenario: You're building a balance sheet lead schedule for a manufacturing client. The trial balance has 85 accounts across 2 tabs (prior year and current year). Building this manually would take 45–60 minutes.

What you type in Copilot: "I have a trial balance with account numbers in column A, descriptions in B, prior year in C, current year in D. Create a formatted balance sheet lead schedule that groups accounts by category, shows subtotals, dollar variances, and percent variances. Flag any account where the variance exceeds $25,000 (our materiality threshold)."

What you get: A formatted lead schedule in 2–3 minutes. Most accounts are correctly categorized. You spend 10 minutes reviewing and correcting 3 miscategorized accounts — total time: 12 minutes vs. 50 minutes manually.

Tips

• The more specific you are about column structure and formatting preferences, the better the output. Include exact column letters and formatting requirements in your first prompt.
• If Copilot miscategorizes accounts, correct a few and ask it to "re-categorize the remaining accounts using the same logic as my corrections."
• For recurring clients, save the Copilot-generated schedule as a template workpaper — next year, paste the new trial balance and Copilot will update the schedule.

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.