How to Use AI to Build a KPI Dashboard

What you are actually buying

Let us be clear about the pitch, because the usual one is wrong.

The FP&A Trends survey of 383 finance practitioners found that only 35% of finance and planning time goes to insight and action, while 45% goes to collecting and validating data.^[1] The same research found Excel is still the predominant planning application, used by 52% of organisations. The Association for Financial Professionals put it more bluntly, describing a “tyranny of spreadsheets”: 71% of teams own dedicated planning tools, yet spreadsheets are used alongside them (85%), to prepare data for them (82%), and to bypass them entirely (57%).^[2]

That 45% is what you are buying back. Not smarter analysis. Not machine intelligence. The tedious front half of the month, where somebody exports four systems into four tabs, reconciles them by hand, and rebuilds the same chart they built last month.

And you are not replacing a flawless process. Fifty real operational spreadsheets, 270,722 formulas, audited under a published protocol: 94% contained at least one error, and 86% contained an error that produced a wrong result.^[3] Across all the error instances they logged, the most common were hard-coded numbers where a formula should be (37.7%) and reference errors, meaning formulas pointing at the wrong cells (32.9%). Note that the sample was 50 workbooks chosen deliberately rather than at random, so read it as a warning about ordinary practice, not a population estimate. Gartner’s survey of 497 controllership professionals found 59% make several financial errors a month.^[4]

Which tool for which job

People choose tools by brand loyalty or by whatever their company bought. The useful question is much simpler: where does your data currently live? That single answer eliminates most of the options.

Two of those limits deserve calling out because they catch people mid-project.

The first is size. OpenAI’s own documentation puts a hard cap of 512MB per file, but notes that CSVs and spreadsheets cannot exceed roughly 50MB depending on row size.^[5] That is your real ceiling, not “millions of rows.” If your export is bigger, filter it before uploading rather than discovering the limit halfway through.

The second is that Tableau’s own FAQ is refreshingly honest about what its agent will not do: it “can’t pick a data source for you, do data modeling, build dashboards, suggest relevant content or answer data lineage questions,” and is “primarily aimed at helping novice users and those exploring ad-hoc data questions.”^[10] If you were expecting to type “build me a dashboard” into an enterprise BI tool, read that sentence twice.

If you are starting from a single messy file rather than a system, our guide to analysing a spreadsheet with AI covers the earlier stage of this workflow, and using ChatGPT for Excel covers the formula side.

The twenty minutes that decides everything

Here is the least glamorous section in this article and the one that will change your results most.

Microsoft says it plainly in the Power BI Copilot documentation: without this preparation, Copilot “can struggle to interpret data correctly” and produce “generic, inaccurate, or even misleading outputs.”^[9] That is the vendor telling you the accuracy of the tool is mostly a function of the quality of your labels.

Think about what your export actually looks like. A column called ProdID. Another called amt_2. A date column where half the rows are text. Three different spellings of the same region. A total row sitting in the middle of the data because someone added it for a meeting in March.

The model has no idea what any of that means. It will guess, confidently, and the guess will be plausible.

So before you write a single prompt:

• Rename every column to something a new starter would understand. ProdID becomes Product Name. amt_2 becomes Net Revenue GBP. Put the currency and the unit in the header. This is the single highest-return action in the whole process.
• Delete subtotal and total rows. They will be counted as data and they will double your numbers.
• Make one row equal one thing. No merged cells, no headers repeated partway down, no blank spacer rows.
• Fix the dates. One format, one column, actual date values rather than text.
• Standardise your categories. “UK”, “U.K.” and “United Kingdom” are three regions as far as any tool is concerned.
• Write down what the file is. One paragraph: what each row represents, what period it covers, where it came from, what is excluded. You will paste this into your first prompt and it will do more good than any clever phrasing.

Building the thing

With clean data and a written description, the build itself is short. The order matters.

Step one: upload and force it to orient itself. Before asking for anything, ask the tool to describe the file back to you. “Tell me how many rows and columns this file has, what each column contains, the date range covered, and anything that looks inconsistent or missing. Do not analyse anything yet.” You are checking that it read what you think you sent. This catches encoding problems, truncation and misread headers in about fifteen seconds.

Step two: agree the metric definitions in writing before any chart exists. This is where most dashboards go wrong and it has nothing to do with AI. Does “revenue” mean gross or net of refunds? Does a customer count as active at 30 days or 90? Is the month calendar or fiscal? Write the definitions out, paste them in, and ask the tool to restate them back in its own words. If the restatement is wrong, you have just avoided a fortnight of confusion.

Step three: ask for the calculations, one at a time, with the code shown. Not “build me a dashboard.” One metric per request, and always add “show me the code you used.” In ChatGPT and Claude you get Python you can read. Gemini in Sheets has a “Show code” option. Microsoft’s Analyst agent sits in Copilot Chat rather than in Excel itself.^[6] A number you cannot trace is a number you cannot defend in a meeting.

Step four: chart it, and be specific about the chart type. Left to itself the tool will reach for whatever is common. Ask for what you want: a line for anything over time, bars for comparison between categories, and a plain number for a single headline figure. Say so explicitly.

Step five: assemble, then rebuild in whatever your team actually uses. This is the step people skip. A dashboard living in a chat thread is not a dashboard, it is a conversation. Once the numbers are agreed and the logic is written down, rebuild it in Excel, Sheets or your BI tool so it can be refreshed next month without recreating the whole session. The AI’s job was to get you to a correct definition fast, not to become your reporting infrastructure.

For the narrative that goes around the numbers, our guide to writing a business report with AI picks up where this leaves off.

Six metrics, not twenty

The Nielsen Norman Group defines a dashboard as “a single-page view that imparts at-a-glance information on which users can act quickly,” and is explicit that “their goal is not to facilitate exploration.”^[11] That distinction is the whole discipline. A dashboard is a car dashboard. Speed, fuel, temperature, warning light. It is not a place to go digging.

AI makes this harder rather than easier, because producing another chart now costs nothing. When each visual took an hour, scarcity did your editing for you. It does not any more.

Two rules hold up well:

Pick metrics somebody can act on this week. For each candidate, ask: if this number moved badly, who would do what by Friday? If there is no answer, it is a reporting line, not a KPI. Put it in the appendix.

Encode quantities with length and position. The NN/g guidance on preattentive processing is that length and two-dimensional position are read almost instantly, while area-based encodings like pie, donut and tree map charts are judged far less accurately.^[11] In practice: bars and lines, not pies and donuts. If you must use a pie, it should have two or three slices at most.

Six metrics, each with a comparison (versus last period, versus target) so the number means something, on one screen with no scrolling. That is the whole specification. Everything else belongs in the detail pack behind it.

Code that runs is not analysis that is right

This is the section most articles on this topic leave out, and it is the reason I would not let a new analyst ship an AI-built dashboard unsupervised.

Researchers at the University of Washington built BLADE, a benchmark for data-driven scientific analysis, and the result is worth reading carefully. GPT-4o produced an executable analysis 96% of the time. Across the language models tested, though, coverage of expert ground-truth decisions stayed below 13% for choosing the conceptual variables in a statistical model and below 27% for operationalising them. The authors also show an example, from a different model, of plausible-looking code that referenced a column which did not exist in the data at all.^[12]

Sit with the distance between those two kinds of number. The code runs. The chart renders. The formatting is immaculate. Whether the analytical judgement underneath it is the one an expert would have made is an entirely separate question, and nothing on your screen tells you the answer.

The same pattern shows up in query generation. On Spider 2.0, a benchmark built from realistic enterprise database schemas, the authors report a code agent built on o1-preview solving just 21.3% of tasks, compared with 91.2% reported on the older and much simpler Spider 1.0.^[13] Your finance data, with forty columns of cryptic codes, three joins and a fiscal calendar, looks like Spider 2.0, not Spider 1.0.

Charts break too. On VisEval, a benchmark of 2,524 natural-language visualisation queries, GPT-4 with Matplotlib achieved a 75.27% pass rate with a quality score of 2.89 out of 5. Changing only the plotting library to Seaborn dropped the pass rate to 58.70%.^[14] Same model, same questions, different library, sixteen points of difference.

And the vendors say this themselves, in documentation nobody reads. Microsoft: “Copilot uses AI to generate suggestions and can sometimes make mistakes, misinterpret information, or produce inaccurate results. Avoid using Copilot for decisions in sensitive areas such as finance, legal, or medical topics.“^[6] Anthropic states that Claude for Excel is not recommended for final client deliverables without human review, audit-critical calculations without verification, or models containing regulated data without proper controls.^[7]

Two of the largest vendors in this space are telling you, in writing, not to trust the output in finance without checking it. That is not a reason to avoid the tools. It is a reason to build the check into your process rather than your intentions.

The verification ritual

Four checks. Five minutes. Every time, before anything leaves your desk.

1. Re-run one KPI by hand. Pick one, ideally the one you would be most embarrassed to get wrong, and calculate it yourself in a corner of the spreadsheet. If it matches, your pipeline is probably sound. If it does not, you have found the problem before your CFO did.

2. Check the row count and the date range. Against the numbers you wrote down before uploading. This catches truncation, dropped headers and stray filters.

3. Read the code, specifically the filters and the joins. You do not need to be a programmer. You need to check that it filtered on the right column, joined on the right key, and did not quietly exclude nulls that should have been zeros. Ask the tool to explain each filter in one sentence and see whether the explanation matches your intent.

4. Sanity-check the direction and the magnitude. Does revenue up 340% in a month match anything you know about your business? Order of magnitude errors are the easiest to spot and the most humiliating to miss.

If you are extending this to forward-looking numbers, our guides to financial analysis with ChatGPT and cash flow forecasting apply the same verification logic to forecasts, where the stakes are higher because nobody can check the answer against reality yet.

What goes wrong

Four patterns, in the order I see them.

Asking for the dashboard before agreeing the definitions. You get twelve charts in ninety seconds, they all look excellent, and three weeks later two departments discover they have been reading different definitions of “active customer” off the same screen. Definitions first, always, in writing.

Uploading the file with the total row still in it. Nothing in the output will flag this. Your annual figure will simply be roughly double and it will look completely normal.

Treating the chat as the dashboard. The session ends, the context goes, and next month someone starts from scratch and gets slightly different numbers. Rebuild it somewhere that refreshes on a schedule and that a colleague can open without you.

Trusting the confidence. This is the real one. The tone of an AI answer is uniform whether it is right or wrong, and the formatting is always good. Human analysts hedge, pause and say “that looks odd, let me check.” The model does not, unless you ask it to. So ask it to: “what assumptions did you make here, and what would make this analysis wrong?” is the most useful follow-up question in the entire workflow.

What I would take from all of this: the tools have got good enough that the bottleneck has moved. It is no longer building the chart. It is knowing whether the chart is true. Spend your saved hours there, on definitions and verification, rather than adding a fourteenth metric because it was free.

Sana Mian, Co-Founder of Future Factors AI

Sana is an AI educator and learning designer specialising in making complex ideas stick for non-technical professionals. She has trained 2,000+ learners across corporate teams, bootcamps, and keynote stages. Future Factors offers AI Bootcamps, Corporate Workshops, and Speaking & Consulting for businesses ready to adopt AI without the overwhelm.

More about Sana →