Excel with AI for Finance Teams: Expense Tracking, Budget Planning, Profit Reports & Cash Flow

Learn how to run core finance workflows in Excel using Microsoft Copilot and ChatGPT — from logging expenses to forecasting cash flow. Each task shows the AI prompt and the Excel formula that does the heavy lifting.

Table of Contents

• 1. Expense Tracking
• 2. Budget Planning
• 3. Profit Reports
• 4. Cash Flow

How to Use AI in This Tutorial

Every task below follows the same two-step AI pattern:

1. Microsoft Copilot (inside Excel) — open the Copilot pane from the ribbon (or press Alt + the Copilot key). Type your request in plain English; Copilot builds the table, pivot, or chart for you.
2. ChatGPT (alongside Excel) — when you need an exact formula or logic, paste your column layout into ChatGPT and ask for the precise Excel formula, then paste it back into the cell.

Use Copilot to build and explore, ChatGPT to get the exact formula. Both are shown in every section.

1. Expense Tracking

Expense tracking means recording every business cost and grouping it so you can see where money goes. In Excel this is usually a flat table of transactions that AI helps you clean, categorize, and summarize.

Why it is useful: Clean category totals are the foundation for budgets, tax filing, and spotting overspending early.

Excel setup — keep one row per expense:

Copilot prompt (in the Excel sidebar):

"Add a column that categorizes each vendor, then create a pivot table showing total spend per category."

ChatGPT prompt (to get a formula):

"Write an Excel formula that sums the Amount column only where Category equals Travel."

Excel formula (total per category):

=SUMIF(C:C, "Travel", D:D)

Simple explanation: SUMIF checks column C for the word "Travel" and adds the matching amounts from column D. Swap "Travel" for any category, or point the criteria at a cell to make it dynamic: =SUMIF(C:C, G2, D:D).

Common mistake: Mixing text formats (e.g., "travel" vs "Travel") splits one category into two. Ask Copilot to standardize categories before summing.

2. Budget Planning

Budget planning sets spending limits per category and compares them against actuals to find variance — the gap between plan and reality.

Excel formula (variance):

=Actual - Budget

Place actuals in B2 and budget in C2, then in D2:

=B2 - C2

Flag over-spend automatically:

=IF(B2>C2, "Over budget", "OK")

Copilot prompt:

"Build a monthly budget vs actual dashboard from this table, highlighting categories that are over budget in red."

ChatGPT prompt:

"Give me an Excel formula that shows the percentage a department is over or under its budget."

Percentage variance:

=(Actual - Budget) / Budget

Format the cell as a percentage.

Best practice: Keep budget and actuals in the same row so variance formulas stay simple and Copilot can chart them side by side.

3. Profit Reports

A profit report shows how much money remains after costs. Gross profit is revenue minus the cost of goods sold (COGS); the margin tells you the percentage kept.

Excel formulas:

Gross Profit = Revenue - COGS
Margin       = (Revenue - COGS) / Revenue

With Revenue in B2 and COGS in C2:

=B2 - C2
=(B2 - C2) / B2

Copilot prompt:

"Summarize gross profit and margin by product line from this sales sheet, and list the three weakest products."

ChatGPT prompt:

"Write an Excel formula that returns 'Low margin' when the margin is below 20% and 'Healthy' otherwise."

=IF((B2-C2)/B2 < 0.2, "Low margin", "Healthy")

Simple explanation: The IF compares the margin to 20%. Below that threshold it labels the row so you can triage products quickly.

4. Cash Flow

Cash flow tracks money moving in and out so you never run dry. The key number is the running balance — what's left after each transaction.

Excel table: Date, Inflow, Outflow, Balance.

Running balance formula (balance in D2, first row has a starting balance in D1):

=D1 + B2 - C2

Drag it down; each row adds inflow and subtracts outflow from the prior balance.

Copilot prompt:

"From this cash flow history, forecast the next 3 months and flag any month where the balance goes negative."

ChatGPT prompt:

"Create an Excel formula that calculates the minimum cash balance over the next 12 weeks from a list of dated inflows and outflows."

Best practice: Use a separate Inflow and Outflow column (not a single signed amount) so Copilot and humans can read the movement at a glance.