A four-step framework for CFOs to control AI token costs.

Read Now

Master Excel Copilot's Latest AI Features with Christian Wattig

Last updated: November 202661 min watch

Speakers

  • Christian Wattig, Founder, Inside FP&A

  • Jaco Schoeman, Associate Director of Strategic Finance, Drivetrain

It’s the middle of October, and sales has booked only 20% of the month’s target. That’s a signal to act, and flagging it is the weekly pacing model’s job. To do that, the model has to calculate the percentage of the month elapsed, whether the month has 28, 30, or 31 days. Building that formula by hand takes an analyst time that could otherwise be spent working with the head of sales to figure out why the month is behind.

Christian Wattig, founder of Inside FP&A, expects AI to win back some of that time. In his survey of finance leaders, lack of time was the top reason they didn’t work more with cross-functional leaders.

He predicts that reconciliations, copy-pasting between spreadsheets, and producing standard reports like the P&L will largely disappear from finance work within a few years, and he said some companies are already using AI for that work.

In a Drivetrain webinar hosted by Jaco Schoeman, associate director of strategic finance, Christian showed how by handing parts of his Excel model-building to ChatGPT and Copilot.

This article will give you the four-part prompt structure Christian uses and the check he recommends for each output, from testing a formula against a known total to back-testing a forecast on months whose actuals you already have. We’ve also included the webinar Q&A and the full transcript at the end.

61:07

Know the risks of machine learning and generative AI before you start

Before jumping into his demos, Christian offered his thoughts on the risks of using AI in finance and what he does to mitigate them.

Christian split AI into two flavors, generative AI and machine learning. Most of the session focused on generative AI, the technology behind ChatGPT, Gemini, and Claude, and Christian named three risks to manage before using it.

  • Data security: Christian said tools like ChatGPT and Gemini can be configured so uploaded data stays on-premises and isn’t used for training. But you should confirm with your manager and IT that your company’s setup does that before uploading anything confidential.
  • Hallucination: Models confidently make things up, so Christian recommends two defenses. Use a reasoning model, which thinks more slowly and runs built-in checks that reduce (but do not completely eliminate) hallucination. Ask the model for its references and click through each one, because a model can invent a citation, complete with a plausible title and a convincing link, for a source that doesn’t exist.
  • Math: Language models often get basic counting and arithmetic wrong. Christian pointed to a classic example: asking how many R’s are in “strawberry,” which many models miss repeatedly.

Machine learning, which has been around for more than a decade, forecasts by finding trends in your historical results and projecting them forward. Christian compared it with judgment-based forecasting.

It's powerful for forecasting because its strengths and weaknesses are almost the mirror image of judgment-based forecasting: Human forecasts carry bias but capture nuance and context. Machine learning has no human bias, but it's entirely bound by the data you feed it. Quality issues or unusual one-offs in recent data get amplified rather than filtered out.
Christian WattigFounder, Inside FP&A

Because the two err in different ways, Christian said combining expert judgment with machine learning can be extremely powerful. Getting machine learning right also takes experimenting with its inputs. At a company Christian worked with, the machine learning models stopped performing well in mid-to-late 2020 and regained accuracy only after the team fed in economic data, unemployment figures, and shutdown-related data.

Prompt ChatGPT to write Excel formulas without giving it your data

In his first demo, Christian showed how to get working Excel formulas from ChatGPT without uploading a workbook. The method works without an enterprise-grade ChatGPT setup, and more importantly, your company data never leaves your hands.

He built the formulas for a weekly pacing model, which compares actual revenue with the monthly target to show whether the business is ahead or behind. The model needs two calculations that update when you change a date cell: month-to-date revenue and the percentage of the month elapsed.

Christian described the workbook’s layout in his prompt, including which rows held revenue and dates, and structured the rest using his favorite framework for ChatGPT for finance prompts. It comes from OpenAI’s Greg Brockman and runs in a fixed order: goal, return format, warnings, then context.

Christian said context needs the most effort, because the model is like a well-read intern that has absorbed the internet but knows nothing about your business. The finance teams he trains most often skimp on context and get back generic answers that are hard to act on, a problem Drivetrain’s fourteen ChatGPT prompts for finance addresses by building that context into prompts for common FP&A tasks.

His pacing prompt covered all four parts.

  • Goal: “I’m a finance manager at a software company building a weekly pacing report in Excel to track actuals against monthly forecast. I need help writing the Excel formula.”
  • Return format: “Provide formulas I can paste directly into Excel, with short explanations of each component.”
  • Warnings: “Keep formulas as simple as possible for auditability, and verify they truly do what’s intended.”
  • Context: “This is a high-visibility project for my CFO, someone I don’t work with often, and I want it to help me stand out.”

Christian also explained why a bad formula costs little.

[Formulas are] actually a good example of a low-hallucination-risk use case. If the formula it returns doesn’t work, nothing breaks; there’s no factual claim at stake. I just paste the error back and ask it to try again. And you’ll find that iterative back-and-forth gets you a working result even when the first attempt fails.
Christian WattigFounder, Inside FP&A

In his demo, ChatGPT’s first formula, a SUMIFS that totals revenue up to the selected date, checked out against total revenue and recalculated correctly when Christian changed the date cell. The percent-of-month formula ChatGPT returned was long and complex, so he checked its logic by hand: On day 15 of a 31-day month, the answer should be 15 divided by 31, or about 48%. The math held up, so he then asked ChatGPT for a simpler version and used that one.

Christian also recommended reading the explanation ChatGPT returns with each formula, because it teaches the Excel behind the answer.

Prompt Copilot’s agent mode to draft a three-year P&L from a handful of assumptions

Christian’s second demo showed how to get a working first draft of a financial model from one prompt, using Copilot’s agent mode, which builds an Excel model from the instructions you give it.

Agent mode goes beyond the Copilot features Carl Seidman, FP&A advisor and fractional CFO at Seidman Financial, demonstrated in his Drivetrain session, How to Master Excel Copilot for FP&A in 60 Minutes. At the time of Christian’s October 2025 session, agent mode was an experimental feature released only a few weeks earlier. It required a Microsoft 365 or paid Copilot subscription and ran through Add-ins > Excel Labs, with desktop Excel support expected later.

Christian’s prompt asked for a three-year P&L for a new product launch, a pro forma income statement built from assumptions written like meeting notes. The assumptions included a $500 target price, a cost of goods sold (COGS) at 14% of price, marketing spend, sales targets, headcount and associated costs, and general and administrative (G&A) expenses at 5% of revenue.

After about a minute of reasoning, agent mode returned a model that calculated revenue and gross margin, listed its assumptions, and linked the summary to the underlying calculations. Revenue, operating income, and the chart it created all appeared to be formula-driven, with nothing hard-coded.

At first glance it did a solid job, though before using this for anything real, I’d absolutely do the same diligence you’d apply to a model handed to you by an analyst: check every cell, verify the logic, because mistakes are still possible.
Christian WattigFounder, Inside FP&A

Prompt ChatGPT to write Python for an ARIMA forecast, then run it in Excel

In his third demo, Christian built a machine learning forecast without asking ChatGPT to do any math. He had ChatGPT write the forecasting code and ran it with Excel’s built-in Python. He called it a fairly advanced use case, one that builds on the ChatGPT techniques in Nicolas Boucher’s ChatGPT for FP&A masterclass.

The dataset for this demo held eight years of monthly retail deposits for a bank, with actuals through April 2025. Christian asked ChatGPT for a forecast of May through December 2025 using ARIMA (autoregressive integrated moving average), a model that accounts for seasonality and adjusts based on its own past errors. His prompt asked for the point forecast plus confidence bounds, the range where the actual outcome is likely to fall, and told ChatGPT to double-check that the code runs.

Christian selected a cell, chose Insert Python on Excel’s Formulas tab, and pasted the code into it. He said he isn’t a developer and can’t verify the code himself, so he recommends scanning it or asking another AI to explain what it does before running it.

Christian didn’t upload the dataset to ChatGPT. It stayed in Excel, where Excel’s Python engine ran the code on it with no AI involved.

During the demo, the code failed three times. The first attempt stalled without returning a result, and the next two returned errors. Christian said it usually takes two or three rounds of troubleshooting, feeding errors back into ChatGPT to get a successful result. So, in the interests of time, he stopped his demo there.

Later in the session, Christian reported that the revised code ChatGPT wrote in response to the third error ran successfully. The output showed the forecast with its 80% and 95% confidence bounds, the range where the actual outcome is likely to fall. He said the next step would be back-testing it against actuals already on hand.

Back-test the forecast against the actuals you already have

Back-testing shows how well a model would have forecast a period for which you already know the results. Christian’s method is to set aside your most recent actuals, for example, the last eight months, have the model forecast those months from the earlier data, and check whether the real results fall inside the forecast’s confidence bounds. When he tested the working code on his demo dataset before the session, he said the results were surprisingly accurate, though forecasts on more irregular data will vary.

Christian described running the back-test in a fresh ChatGPT chat with no history, using the shortened dataset. That step puts company data in ChatGPT, which the live demo avoided, so clear it with IT first.

Work around AI’s limitations with connected data

Christian pointed out that even when you successfully build a workflow with AI, there are other limitations you need to keep in mind. General-purpose AI tools still require manual data uploads, don’t support real collaboration, and don’t connect to live data, so reforecasting remains tedious.

Those limits get harder to work around as a company adds data sources, entities, and stakeholders to coordinate, which Christian said is the point at which AI-native FP&A platforms start to make sense. Christian asked Jaco how a purpose-built FP&A platform would handle these problems. And Jaco answered him with a demo of Drivetrain.

Refresh, reforecast, and run scenarios in one connected model

Jaco’s platform demo addressed two of the limitations Christian described: manual uploads and live data. He also named coordinating with stakeholders like the CMO or head of sales as a common planning challenge, and walked through what happens at month-end and when one of them asks for an alternative plan.

  • At setup: Jaco connected two Xero instances, Google Sheets, and Salesforce, sources a team would otherwise export and upload by hand. He said Drivetrain offers more than 800 native connectors.
  • During the month: Each connection automatically refreshes the data daily, weekly, or monthly on a schedule the team sets. Analysts can also refresh their data on demand with a manual sync, so the forecast runs on current numbers.
  • When the books close: An analyst clicks Update on the closed month, and Drivetrain replaces its forecast with actuals. The months after it keep forecasting as before.
  • When an assumption needs rethinking: An analyst can change how a line is forecast. The sales and marketing forecast, for example, starts from the company’s connected accounting data, and Jaco said it can be tied to a driver such as 20% of revenue.
  • When the head of sales asks “what if”: An analyst adds an “aggressive case” scenario that starts from the budget assumptions and layers on changes such as a 20% revenue lift or updated R&D averages, all inside the same model.

Ground Claude's answers in your Drivetrain data with the MCP server

Christian mentioned another limitation in using AI for finance, which is that it doesn’t know your business context. As a result, any insights it provides are generic.

Jaco showed how his Drivetrain account was connected with Claude through Drivetrain’s MCP (Model Context Protocol) server. Connecting Claude via MCP gives it direct access to your data in Drivetrain so that responses are grounded in your real data, and every number is traceable back to the metric it came from. Jaco added that with the MCP server connection to Claude, access control carries through: Users who can't view the data inside Drivetrain can't see it through Claude either.

Jaco then asked Claude for revenue per full-time employee (FTE). He explained that Claude can either fetch the metric directly from Drivetrain or, if it isn’t already defined, calculate it from revenue and headcount.

Claude returned almost the revenue per FTE result along with the recent trend. To save time, Jaco had run two follow-up prompts before the session and, during the demo, showed what came back. Claude had searched the web for benchmarks from similarly sized companies, compared his revenue per FTE against them in a table, and drafted a playbook for improving the metric based on those benchmarks and his Drivetrain data. Jaco said the exchange took him from a single data point to a full strategy in just a couple of minutes.

To ask questions like these of your own numbers, book a personalized demo.

Summary

Most of this session focused on the different ways Christian uses ChatGPT and Excel’s Copilot as AI copilots for Excel financial workflows:

  • Christian prompted ChatGPT to write the pacing formulas by describing the workbook’s layout, so he never had to upload the file. Then he pasted the formulas into Excel and tested them against figures he could verify.
  • He gave Copilot’s agent mode a prompt listing assumptions such as a $500 price and cost of goods sold (COGS) at 14% of price, and the agent returned a formula-driven three-year P&L. Christian added that he’d check every cell and its logic before using it for anything real.
  • Because language models are unreliable for arithmetic calculations, in his forecasting demo, Christian had ChatGPT write Python code for the forecast and pasted it into Excel to run on Excel’s built-in Python. It wasn’t successful on the first try, so he fed each error back to ChatGPT until the code ran properly.
  • Christian recommended back-testing an AI forecast by removing your most recent actuals, asking the AI in a fresh chat to forecast those months, and then checking whether the real results fall inside the forecast’s confidence bounds.
  • Christian said general-purpose AI tools still require manual data uploads, don’t support real collaboration, and don’t connect to live data, which keeps reforecasting tedious.
  • Jaco demoed how Drivetrain, an AI-native FP&A platform, replaces uploads with direct connections to source systems, keeps data current with scheduled refreshes, lets analysts build scenarios for business partners inside one model, and, through its MCP server, gives Claude business context with access to the company’s real data. Access controls in Drivetrain carry through.
Summarize with AI:

Questions from the webinar

What should FP&A professionals focus on once AI handles the repetitive work?

Christian said the repetitive tasks are less enjoyable anyway, and finance is paid to help leaders make better decisions through business partnering and deeper analysis. Earlier in the session, he named challenging assumptions as the part of finance work AI will be slowest to replace, because AI answers questions well but isn't yet a good challenger.

How can I use Excel Copilot without enterprise access?

Christian said Excel Copilot is built on some version of ChatGPT, though Microsoft doesn't disclose which. Without enterprise-grade access, use it for brainstorming and formula help, and keep confidential data out of it. His pacing demo shows the method in ChatGPT: he described the sheet's layout without uploading the workbook.

Which finance tasks can I hand to ChatGPT with the least risk of a hallucinated answer?

Tasks where a wrong answer shows up immediately and costs nothing carry the least risk, Christian said. A formula is his example: if it fails, you paste the error back and ask again, and no factual claim is at stake. Answers built on facts or citations carry more risk, so click through every reference before using one.

How are finance teams using generative AI at work today?

Attendees listed reviewing memos, advanced Excel and PowerPoint functions, contract analysis summaries, meeting minutes, and first drafts of communications. Christian drafts his own emails first, then has AI fix grammar and tone, flag anything missing, and tighten the language. He said most companies now give employees access, yet the tools go underused because people struggle to find concrete use cases, a pattern Drivetrain's State of AI in FP&A report examines across finance teams.

Which AI-powered formulas and functions in Excel did Christian use for finance work?

Christian had ChatGPT write standard Excel functions, such as a SUMIFS with a date condition for revenue to date, and tested each one against figures he could verify. For heavier math such as an ARIMA forecast, he had ChatGPT write code for Excel’s built-in Python, which ran the calculation with no AI involved. He also had it write a formula for the percentage of the month elapsed, which he checked by hand and then asked ChatGPT to simplify.

How does Drivetrain keep financial data secure when Claude is connected?

Jaco said access control carries through the MCP server, so anyone without permission to view data inside Drivetrain can't see it through Claude either. Drivetrain is SOC 1 Type 2 and SOC 2 Type 2 certified.

Speaker spotlight

Christian Wattig

Founder, Inside FP&A

Christian Wattig is an FP&A expert with 14+ years of experience in MNCs (P&G, Unilever) and high-growth tech startups (Squarespace). After earning his MBA from NYU Stern, he helped take Squarespace public. Now an entrepreneur, Christian directs the FP&A certificate program at Wharton and runs his firm, FP&A Prep, offering corporate training, online programs, and sharing insights through LinkedIn, newsletters, and his 'FP&A Tuesday' series.

Jaco Schoeman

Associate Director of Strategic Finance, Drivetrain

Jaco Schoeman is Associate Director of Strategic Finance with over a decade of hands-on finance experience in financial planning, budgeting, and FP&A system implementation. He spent over four years at Vena Solutions as Senior Services Solutions Manager, where he partnered with sales and services teams to scope projects, position FP&A solutions, and guide clients through software implementations. Before Vena, Jaco held finance and planning roles at Predator Ridge Resort, Caron Business Solutions, and BASF, where he managed commercial planning and forecasting cycles as Company Controller. At Drivetrain, Jaco writes about financial planning and budgeting best practices, FP&A software implementation strategies, and how finance teams can optimize their planning processes.

Ready to start your journey?

Fewer spreadsheets, faster planning, better decisions.

Book a Demo