Disclaimer: This article is adapted from a talk by Christian Martinez at our CFO Summit, which you can watch ondemand in its original format on your dashboard – and follow along with his example.

In finance and accounting, a lot of us spend our time forecasting. But if we're honest, many of those minutes and hours don't actually go into the forecast itself.

They go into data exports, rebuilding the same assumptions over and over, and, when we try to bring AI into the process, re-explaining the context to every new model.

Copilot in Excel can remove most of that work, as long as you set it up properly.

I'll walk you through four things: how to structure your data so Copilot can really use it, the prompts you can use to build assumptions, scenarios, trend analysis and variance flags, how to get Copilot to write Python code that runs directly in Excel, and finally, how to visualize your forecasts once they're built.

How I’m thinking about AI after 27 years in finance
After 27 years in finance, the fundamentals haven’t changed. We’re still chasing revenue, margins, and cash.
Finance AllianceGeeta Malhotra

Start with your data structure

The first and most important thing is your data structure. Copilot works very well for financial forecasting when your data is structured in the most optimized way, and that's usually not how our files look today.

A lot of us really like our Excel files with 30 tabs, four of which aren't really needed, maybe five tabs of different datasets, and empty cells everywhere.

You'll often see totals, like total fixed assets, mixed in with account-level data, and sometimes even notes or comments sitting inside the tables. That kind of data is hard to use in pivot tables, hard to use in BI tools like Power BI, and very hard for AI to use.

So my first recommendation is to structure your data so that each row is one general ledger account or one particular set of transactions. No comments, and no totals mixed in with the details, just the values you're using to forecast.

It can still be in wide format, with your accounts and descriptions down the side and January, February and so on running horizontally. That's already a good structure for AI.

If you want an even better structure, normalize it. Each row is still one general ledger account, but it's also one month and one value. In this format, you include dimensions like report, category and subcategory as their own columns.

There are no repeated headers, no totals and no formatting issues. This structure works really well for BI tools like Power BI and Tableau, it's very good for automation, and it's very good for AI-driven analysis and forecasting.

What will define a high-performing CFO by 2030?
The high-performing CFO of the next few years will be someone who got very good at knowing which few things were worth going deep on.
Finance AllianceTeresa Garanhel

What to do with comments

I often get asked what to do with comments. There are two main types. The first is a comment you leave for a human colleague, something like "the 120,000 in January isn't fully understood yet." AI doesn't need those, so delete them.

The second type carries real information, for example, that January through August are actuals and September through December are forecast. That kind of comment can be incorporated into the table itself.

Just add a column called "version" and label each month as actuals or forecast.

Tables, named ranges and clear labels

In a nutshell, format everything as a native Excel table, with one row per account, period and value. Use named ranges for all your drivers and assumptions.

You don't need everything in a single table; it can be spread across multiple tables, as long as they're all structured the same way.

For expert advice like this straight to your inbox, sign up for Pro+ membership.

You'll also get access to hundreds of hours of exclusive video content, a complimentary Summit ticket, and so much more.

So, what are you waiting for?

Get Pro+

If you structure your data as tables, Copilot can read all of it natively, and it improves the way it analyzes your data.

If you name your assumptions, for example a range that holds the Q1 growth rate, Copilot already has that context. And if you want to show Copilot the outputs you expect, keep them clearly labeled on their own tab.

When you separate inputs from outputs, Copilot understands what data needs to feed the forecast and what end result you're expecting from it.