Copilot in Excel for Month-End: Spend by Site, Budget vs Actual and Vendor Trends in Three Prompts

Month-end in property and facilities management usually starts the same way. Someone exports the AP invoices, someone else finds the budget file, and then an afternoon disappears into pivots, VLOOKUPs and a chart that never looks quite right. The questions are always the same: where did we spend, what's over budget, and who is driving it?

In this guide we ask Copilot in Excel those three questions in plain English. It builds the pivot tables, the budget comparison and the charts, and explains what it found. We checked every number by hand, and they matched.

Here's where we ended up, from one sentence:

What you'll need

• A Microsoft 365 Copilot licence. Copilot in Excel is part of Microsoft 365 Copilot.

• The workbook saved in OneDrive or SharePoint, with AutoSave on.

• Your data in Excel tables. Copilot works best when every dataset is a proper table with clear column names.

Step 1: Set up the workbook

Our workbook, AP Spend 2026, has three tables, all exported from the SharePoint lists in our invoice approval guide:

• AP Invoices: invoice number, vendor, site, GL code, category, period, description, subtotal, tax, total and status. 162 invoices from January to September.

• Budget: one row per site, GL code and month, including seasonal amounts for snow clearing and summer HVAC.

• Purchase Orders: PO number, vendor, site, amount and invoiced to date.

Three tips that made Copilot noticeably better:

• Use Insert > Table, not just a range with headers.

• Give columns names a person would understand. "Subtotal" beats "Amt1".

• Keep one row per thing: one invoice, one budget line. No merged cells or subtotal rows inside the data.

Prompt 1: Where did we spend?

Open Copilot from the Home tab and type:

Show total spend by site and month for 2026 as a PivotTable with a column chart.

Copilot thought for a few seconds, then added a new sheet with a formatted PivotTable, a clustered column chart and a short summary: 162 invoices, $337,314 in total spend, January to September.

Notice September jumps to $53,061. Hold that thought.

Prompt 2: What's over budget?

The second prompt asks Copilot to join two tables, which is where it really saves time:

Compare actual spend (Subtotal, before tax) with the Budget table by site and category for Jan to Sep 2026. Put the result on a new sheet called Budget vs Actual: which site and category combinations are over budget year to date, by how much and by what percent, and which vendors are driving each overrun. Highlight the overruns.

It built a Budget vs Actual sheet with the overruns, the variance in dollars and percent, and the vendor behind each one:

The two lines that matter:

• Lakeview elevator, 80.6% over: $24,381 against a $13,500 budget, all Metro Lift.

• Lakeview HVAC, 38.1% over: $37,712 against $27,300, all ClearAir.

We checked both against our own calculation and they were right to the dollar. Notice we said "Subtotal, before tax" in the prompt. Prompt 1 didn't say, so Copilot used the total including tax. Both are fine, but your budget is almost certainly before tax, so say so.

Prompt 3: Who is trending up?

Which vendors are trending over budget? On a new sheet called Vendor Trend, show monthly spend (Subtotal) for each vendor from Jan to Sep 2026 and add a line chart for the vendors whose spend is rising the most. Tell me in two sentences what is driving the trend.

Copilot made a vendor-by-month table, worked out a trend for each vendor with the SLOPE function, charted the five fastest risers and summed it up:

Its explanation: Metro Lift leads at about $720 a month, driven by one $13,515 month at Lakeview in September. ClearAir follows at about $593 a month, driven by Lakeview HVAC.

Where you still need a person

Copilot was right about the numbers. But look at that Metro Lift line again. It's flat all year and then shoots up in September, and that one invoice is a $12,400 elevator modernization progress bill. That isn't a maintenance trend at all. It's capital work that probably shouldn't be in the operating budget in the first place.

The ClearAir line is the opposite. It climbs in June, stays high through the summer, and includes two emergency chiller repairs. That's a real operating problem worth a conversation with the vendor.

Copilot found both in under a minute. Telling them apart still takes someone who knows the buildings. In our next post we hand that job to an agent that writes the variance comments finance always asks for.

A quick game: fix the prompt

Each of these weak prompts gives a vague or wrong answer. Can you spot what's missing? Our versions are below.

• "Show me spending."

• "What's over budget?"

• "Make a chart."

Better:

• "Show total spend by site and month for 2026 as a PivotTable." (Which measure, which split, which period.)

• "Compare Subtotal before tax with the Budget table by site and category, year to date." (Which numbers, which table, which level.)

• "Add a line chart of monthly spend for the five vendors rising fastest." (What to chart and why.)

The rule of thumb: tell Copilot what a good analyst would ask you before starting. Measure, level, period and output.

Three things we learned

• Copilot edits your workbook. It adds sheets, tables and charts, and keeps a Done and Undo button on each change. Click Undo if it went the wrong way.

• Keep the Copilot pane open while you type. We closed it once by accident and the next prompt landed in a cell. It's easy to undo, and a little funny.

• Check one number. It takes a minute and you'll trust the rest. Ours all matched.

Why this matters

The hard part of month-end isn't the maths. It's getting the invoices, the budgets and the POs into one place in a shape anyone can question. Once your data from different systems is in one place, putting AI in front of it means the pivots and charts take a sentence instead of an afternoon, and your team spends its time on the two lines that actually need a decision.

Need help?

Smart Solutions sets up Microsoft 365 Copilot, Power Platform and finance reporting for Canadian property and facilities teams, from clean data to automated month-end. If you'd rather have purchase orders, invoices and budgets in one product, take a look at ProcuraCloud, our procurement platform for small and mid-sized businesses. Next up: a Copilot Studio agent that writes your budget variance comments for you. Contact us to talk about your month-end.