← Back to Index

Copilot in Excel for Finance — Hands-On Labs

Four real workflows that show where Copilot actually pays off

Who this is for: Finance professionals — FP&A analysts, controllers, AP/procurement finance, and treasury — who want to move past the basics and use Copilot for the work that consumes the most hours: variance analysis, AR aging, vendor spend, and cash forecasting.
45–60 minutes Copilot in Excel Instructor-Led or Self-Paced
⚙

Setup
1

Variance
2

AR Aging
3

Vendor Spend
4

Cash Flow

What You'll Be Able to Do

This isn't a tour of every Copilot button. It's four end-to-end workflows you can take back to your desk on Monday. By the end you'll be able to:

Tailored for four finance audiences

FP&A

Variance analysis, planning, KPI work — Lab 1 is your home base.

Controllers / Accounting

Close-cycle work — AR aging in Lab 2 maps directly to your weekly grind.

Procurement / AP Finance

Spend analytics, duplicate detection, payment timing — Lab 3.

Treasury

13-week cash forecasts and liquidity flags — Lab 4 is yours.

📌
The labs are designed to be done in order — earlier labs build the prompting habits you'll lean on in later ones — but each is self-contained if you want to skip to your area.

Prerequisites & Setup

🔑Microsoft 365 Copilot license
📊Microsoft Excel (web or desktop)
☁️OneDrive or SharePoint access
🌐Internet connectivity

Download the Lab Workbook

One file, four tabs — one per lab. Download it, then upload it to your OneDrive so Copilot can work with it.

📥 Download Copilot-Excel-Finance-Labs.xlsx
⚠️
Critical: Copilot in Excel only works with files saved to OneDrive or SharePoint. After downloading, drag the file into your OneDrive folder (or upload via the web) and open it from there. Files stored only on your C: drive will show no Copilot button.

Open Copilot

  1. Open the workbook from your OneDrive in Excel (web or desktop).
  2. On the Home tab of the ribbon, click the Copilot icon. The Copilot pane opens on the right.
  3. Read the "Read Me First" tab so you know what's in each lab tab, then click the Lab 1 - Budget vs Actuals tab to begin.

Instructor Note

Before the session: verify everyone's file is in OneDrive (this is the #1 source of "Copilot doesn't work for me" tickets). Confirm Copilot appears on the Home ribbon. Have a backup link ready in case anyone's download fails.

Prompt Anatomy — Read This Before Lab 1

Most "Copilot didn't get it right" moments trace back to a vague prompt. Strong prompts share four traits:

1. Name columns explicitly

Refer to Budget Amount, not "the budget column." Copilot reads headers as ground truth.

2. State the action verb

"Add a column," "Highlight," "Sort," "Create a chart." Vague verbs ("analyze") get vague results.

3. Give thresholds & rules

"Over 10%," "above $50,000," "older than 60 days." Numbers anchor the output.

4. Specify the output shape

Column, row, chart type, pivot, new sheet. Tell Copilot where the answer lives.

❌ Vague prompt
"Look at my variance and tell me what's wrong."
✅ Strong prompt
"Add a column 'Variance %' that calculates (Actual Amount − Budget Amount) / Budget Amount, formatted as a percentage. Then highlight rows where the absolute value exceeds 10% in red."
💡
The shortcut: Imagine you're handing the request to a brand-new analyst on day one. If they'd need to ask a follow-up question, your prompt isn't specific enough yet.
Lab 1 — Budget vs Actuals Variance FP&A

Month-End Variance Analysis

🕐 12–15 minutes 📊 5 prompts ⏱ Saves ~45 min/month
▼

Why This Matters

It's the 5th business day. Actuals are in. Leadership wants commentary on variances by close-of-business. The mechanical work — variance columns, % calcs, conditional formatting, sorting, top-10 chart — is the same every month. Copilot does it in five prompts so you can spend your time writing the commentary, which is the part that actually requires judgment.

Tab to use: Lab 1 - Budget vs Actuals (51 line items across 6 departments)

A Add the Variance Columns

You need both the dollar variance and the percentage variance — the dollar amount tells you size, the percentage tells you severity.

📋 Prompt 1 Add a column called 'Variance ($)' that calculates Actual Amount minus Budget Amount for each row. Then add a column called 'Variance (%)' that calculates (Actual Amount minus Budget Amount) divided by Budget Amount, formatted as a percentage.

→ Click "Insert Column" for each suggested column.

What to expect

  • Two new columns with formula-driven values (click any cell — you'll see a real Excel formula in the bar)
  • Variance ($) shows positive numbers for overspend, negative for underspend
  • Variance (%) formatted as a percentage with parentheses or a minus sign for negatives
💡
Trust check: Click any cell in your new "Variance ($)" column and check the formula bar. You'll see something like =[@[Actual Amount]]-[@[Budget Amount]]. Copilot didn't paste a number — it wrote a formula that will recalc if any actual changes. This is what makes the output auditable.

B Highlight Material Variances

You don't care about a department $200 over on office supplies. You care about the lines that move the P&L.

📋 Prompt 2 Highlight any row where the absolute value of Variance (%) is greater than 10%. Use red fill for unfavorable variances (over budget) and yellow fill for favorable variances (under budget).

What to expect

  • Conditional formatting rules applied automatically
  • The handful of material variances jump out visually
  • You can see the underlying rules by going to Home → Conditional Formatting → Manage Rules

C Sort to Surface the Story

📋 Prompt 3 Sort the table by the absolute value of Variance (%) in descending order so the biggest variances appear at the top, regardless of direction.

→ Click "Apply" to commit the sort.

D Roll Up to Department

Now zoom out — leadership wants the story by department, not by GL line.

📋 Prompt 4 Create a summary table on a new sheet showing, by Department: total Budget Amount, total Actual Amount, total Variance ($), and Variance (%). Sort by Variance ($) descending.

→ Click "Add to a new sheet"

E Visualize the Top 10

📋 Prompt 5 On the Lab 1 sheet, create a horizontal bar chart of the top 10 GL accounts by absolute Variance ($). Color bars red for unfavorable variances and green for favorable.

What to expect

  • An embedded chart you can drop into your variance commentary deck
  • Visual at-a-glance ranking of where to focus your write-up
⚠️
Verify before you brief: Spot-check one or two of the highlighted variances against the source data. Copilot is right ~95% of the time on math like this, but the 5% always shows up in front of the CFO. Build the verify step into your workflow.

Self-Check — Lab 1

Lab 2 — AR Aging & Collections Controllers

From Raw Invoices to a Prioritized Call List

🕐 12–15 minutes 📊 5 prompts ⏱ Saves ~90 min/week
▼

Why This Matters

The AR team usually pulls a raw invoice extract on Monday, spends 90 minutes building an aging report, and then hands a generic spreadsheet to collections. Copilot turns that into a prioritized call list — high-value, deep-aged accounts at the top — in about 10 minutes. Better: the logic is reusable next week with one click of refresh.

Tab to use: Lab 2 - AR Aging (~95 open and partially-paid invoices across 20 customers, dated relative to today)

A Days Outstanding + Aging Buckets

📋 Prompt 1 Add a column called 'Days Outstanding' that calculates the number of days between today and the Due Date for each invoice. Then add a column called 'Aging Bucket' that categorizes each invoice into one of: 'Current' (not yet due), '1-30 Days', '31-60 Days', '61-90 Days', or '90+ Days' based on Days Outstanding.

What to expect

  • Days Outstanding column with a TODAY-based formula
  • Aging Bucket column populated with one of the five labels using nested IF/IFS logic
💡
Tip: Click a cell in "Aging Bucket" and look at the formula. Copilot will have written something like =IF([@[Days Outstanding]]<=0,"Current",IF(...)). If you want to change a threshold (e.g., move 90 to 75), you can edit the formula directly or just ask Copilot to redo the column with new thresholds.

B Filter to Open Items Only

You only want to chase invoices with a Balance Due — paid-in-full rows are clutter.

📋 Prompt 2 Filter the table to show only rows where Balance Due is greater than zero.

C Aging Summary by Customer

📋 Prompt 3 Create a pivot table on a new sheet showing total Balance Due by Customer Name (rows) and Aging Bucket (columns). Include row and column totals. Sort customers by total Balance Due descending.

→ Click "Add to a new sheet"

What to expect

  • Customer-by-bucket pivot showing where exposure is concentrated
  • Largest balances at the top — the customers worth a call

D Assign a Collection Priority

This is the moment Copilot does something you couldn't easily do with a quick formula — it applies multi-criteria business logic to every row.

📋 Prompt 4 Back on the main aging table, add a column called 'Collection Priority' that assigns: - 'High' if Balance Due is over $25,000 AND Aging Bucket is '61-90 Days' or '90+ Days' - 'Medium' if Balance Due is between $5,000 and $25,000 AND Aging Bucket is '31-60 Days' or later - 'Low' for everything else Then sort the table by Collection Priority (High first), then by Balance Due descending.

What to expect

  • A 'Collection Priority' column with three values populated by formula
  • High-priority accounts at the top of the sheet — your call list

E A Weighted DSO Snapshot

📋 Prompt 5 Calculate the weighted-average Days Outstanding across all open invoices, weighted by Balance Due. Show the result in a single cell labeled 'Weighted DSO' below the table.
⚠️
Date sensitivity: Copilot uses today's date for Days Outstanding. If you're preparing the report for the close period — say, month-end May while it's June 2nd — tell Copilot explicitly: "Use May 31, 2026 as the reference date, not today."

Self-Check — Lab 2

Lab 3 — Vendor Spend & Savings ID Procurement / AP

12 Months of AP Data — Find the Money

🕐 12–15 minutes 📊 5 prompts ⏱ Surfaces 5-15% savings opportunities
▼

Why This Matters

AP exports thousands of invoices a year. Most of the value is locked in three questions: Who are our biggest vendors? Are we paying anyone twice? Are we leaving discounts on the table by paying too early? Each of those typically takes a day of pivot-table wrangling. Copilot answers all three in one session.

Tab to use: Lab 3 - Vendor Spend (~260 invoices across 25 vendors covering the last 12 months — duplicates and early-pay risks intentionally planted)

A Top Vendors by Spend

📋 Prompt 1 Create a pivot table on a new sheet showing total Amount by Vendor Name, sorted descending. Also show the count of invoices for each vendor.

→ Click "Add to a new sheet"

B Pareto: The 80/20

In most enterprises, ~20% of vendors drive ~80% of spend. Find them.

📋 Prompt 2 On the vendor summary sheet, add a column called 'Cumulative % of Spend' that shows the running cumulative percentage as you go down the sorted list. Then highlight the rows that together make up the first 80% of total spend — these are our strategic vendors.

What to expect

  • A running cumulative-% column
  • The "top vendors" cohort visually highlighted — likely 5–10 vendors

C Flag Potential Duplicates

This is the find-the-money moment. Duplicate payments are pure cash recovery if you catch them.

📋 Prompt 3 On the main Vendor Spend table, add a column called 'Possible Duplicate' that flags 'YES' for any invoice that shares the same Vendor Name AND the same Amount as another invoice dated within 7 days. Otherwise leave it blank.

What to expect

  • A handful of rows flagged YES (there are intentional duplicates in this dataset)
  • These are candidates for AP review — not all will be true duplicates, but each deserves a look
💡
In production: Tune the window. 7 days catches obvious double-keys; 30 days catches re-billed invoices; same-month-same-amount catches recurring-payment errors. Different windows, different recoveries.

D Payment-Cycle Analysis

Paying too late costs you relationships. Paying too early costs you cash — and may forfeit early-pay discounts you've already negotiated.

📋 Prompt 4 Add a column called 'Days to Pay' that calculates the number of days between Invoice Date and Payment Date. Then create a summary on a new sheet showing, by Vendor Name: total invoices, total spend, and average Days to Pay. Sort by average Days to Pay ascending.

E Spot Early-Pay Risks

📋 Prompt 5 On the payment-cycle summary, highlight any vendor where average Days to Pay is under 15 days AND total spend exceeds $100,000. These are vendors where we may be paying significantly faster than required — worth renegotiating terms or checking for missed early-pay discounts.

What to expect

  • A short list of vendors flagged as renegotiation candidates
  • Each is a concrete conversation with procurement worth having
📌
Outcome: By the end of this lab you should have three actionable artifacts — a strategic-vendor list, a duplicate-review queue, and a payment-terms renegotiation list. Each maps to a real procurement workflow.

Self-Check — Lab 3

Lab 4 — 13-Week Cash Flow Forecast Treasury

Extend the Roll & Flag Liquidity Risk

🕐 10–12 minutes 📊 4 prompts ⏱ Saves 30+ min weekly + reduces error risk
▼

Why This Matters

Treasury teams update a 13-week cash forecast every Monday. Extending the model, copying formulas, refreshing the chart, and checking for threshold breaches is mostly mechanical — and mechanical work in a high-stakes model is exactly where copy-paste errors happen. Copilot extends the roll, recalculates net cash flow, and flags weeks below your minimum threshold in a single sequence.

Tab to use: Lab 4 - Cash Flow Forecast (12 weeks of forecast data — your job is to extend to 13 and find the liquidity dip)

A Extend the Roll by One Week

Treasury forecasts roll forward weekly. The 13th week needs to be added with the same structure as the prior rows.

📋 Prompt 1 Add a new row at the bottom of the table. Set the Week Ending Date to one week after the last existing date. Copy the formula pattern from the row above for Beginning Cash Balance (which should pull from the prior week's Ending Cash Balance) and for Ending Cash Balance. Leave the inflow and outflow columns blank for me to fill in manually.

What to expect

  • A new row with the correct date
  • Beginning Cash Balance referencing the previous row's Ending Cash Balance
  • Ending Cash Balance formula in place but showing the same as Beginning (because the inflow/outflow inputs are blank)

Now type realistic estimates into the new row's inflow/outflow cells and watch the Ending Cash Balance recalculate automatically.

B Add Net Cash Flow

📋 Prompt 2 Add a column called 'Net Cash Flow' that calculates the net change for each week: Operating Receipts + Other Inflows minus (Operating Disbursements + Capital Expenditures + Debt Service + Other Outflows).

C Flag Weeks Below the Liquidity Threshold

Most treasury policies require a minimum cash buffer. Below that threshold you trigger a revolver draw, accelerate collections, or push out payments.

📋 Prompt 3 Highlight in red any row where Ending Cash Balance falls below $5,000,000. Add a column called 'Below Threshold' that shows 'YES' for those rows and is blank otherwise.

What to expect

  • At least one week (around mid-forecast) flagged in red — this is the planted liquidity dip
  • The Below Threshold column makes the same information filterable and exportable

D Build the CFO View

The chart is what actually goes in the Monday email to the CFO.

📋 Prompt 4 Create a line chart showing Ending Cash Balance over all weeks. Add a horizontal reference line at $5,000,000 to visualize the minimum liquidity threshold. Title the chart "13-Week Cash Position vs Minimum Threshold".

What to expect

  • A line chart with the cash trajectory and a clear threshold line
  • The dip below threshold is visually obvious
  • This is the chart you paste into the treasury report
⚠️
Sanity-check before sending: Confirm the Beginning Cash Balance of each week ties to the prior week's Ending. Treasury models are notorious for breaking this chain after edits — Copilot is good but never trust a cash forecast you haven't walked once.

Self-Check — Lab 4

Troubleshooting

Issue Solution
I don't see a Copilot button in Excel Confirm the file is opened from OneDrive or SharePoint, not your local drive. Then check that your Copilot license is active. If the button still doesn't appear, contact your IT admin — Copilot may not be enabled for your tenant.
Copilot says "I can't work with this data" The data must be in a formatted Excel Table, not just a range of cells. The lab tabs in this workbook are already formatted as tables, but if you're using your own data, select the range and press Ctrl+T first.
Copilot returns the wrong column or misses a value Be explicit about column names exactly as they appear in the header row. Refer to 'Budget Amount' not "the budget." Quotes around column names also help.
The "Insert Column" / "Apply" button doesn't appear Wait a few seconds for Copilot to finish processing. If the action buttons still don't appear, rephrase the prompt with a more explicit action verb ("Add a column called X" instead of "Calculate X").
I want to undo a change Copilot made Press Ctrl+Z (Cmd+Z on Mac) immediately. Copilot changes are standard Excel edits — they undo like any manual change. For bigger rollbacks, use File → Info → Version History to restore a prior snapshot.
The aging buckets use the wrong reference date Copilot defaults to TODAY(). For month-end or quarter-end reports, tell it explicitly: "Use March 31, 2026 as the reference date for Days Outstanding."
Copilot is slow or times out Very large datasets (100,000+ rows) can slow Copilot down. For the lab workbook this shouldn't happen — if it does, refresh the page and try the prompt again.
The formula Copilot wrote looks wrong Trust your instincts. Click the cell, inspect the formula, and either edit it directly or ask Copilot to "Rewrite the formula in [column] to instead [your correction]." Always spot-check before sharing with stakeholders.

Discussion Questions

Use these to debrief in instructor-led sessions, or reflect on them individually:

  1. Of the four labs you just did, which one maps closest to a recurring task on your own desk? What part of that task is mechanical (Copilot-friendly) versus judgment-driven (your value-add)?
  2. In Lab 1, Copilot wrote variance formulas. In Lab 2, it applied multi-criteria business logic. Where on that spectrum does most of your current Excel work sit?
  3. What review steps would you build into your workflow before sharing a Copilot-generated output with your CFO, controller, or auditors?
  4. How would you adapt Lab 3's duplicate-detection prompt to your organization's actual fraud and error patterns? What other "find the money" prompts would you write?
  5. If Copilot saves you 30–90 minutes on each of these recurring tasks, what higher-value work would you redirect that time toward?

Key Takeaways

01

Real formulas, not magic

Every column Copilot adds is a real Excel formula you can inspect, edit, and audit. There is no black box.

02

Specificity wins

Column names, thresholds, action verbs, output shape. The four traits of a prompt that works the first time.

03

Verify before you brief

Copilot is right most of the time, but the exceptions show up in front of leadership. Spot-check is the discipline that makes you safe to delegate to it.

04

Recurring > one-off

The biggest wins are the tasks you do every week or month. Save your prompts — they're a library that compounds.

What's Next

Now that you've seen the pattern, the goal is to apply it to your own data.

💡
Pro move: Share your best prompts with your team. The first time someone in your group writes a great prompt for variance commentary or vendor analysis, that prompt becomes everyone's. That's where the team-level productivity gain lives.

Copilot in Excel for Finance — Training Labs

Designed for enterprise finance teams · ~45–60 minute hands-on session