How to Create a CFO Dashboard Using AI: Excel and Power BI Guide
Build a CFO dashboard with AI using reconciled financial data, clear KPI formulas, Excel or Power BI, six reusable prompts and a practical validation checklist.
FREE PRACTICE PACK · SYNTHETIC DATATry it yourself: CFO Dashboard
Build and test a six-month dashboard from a fictional business dataset.
Every workbook sheet and offline demonstration includes the AI for All logo. The dataset is supplied as Excel to retain its branding. No macros, client records or automatic posting.
1. Open the sample
Open Monthly data. The six months run from April to September 2026. Amounts are INR, revenue and costs exclude GST, and blue cells are editable.
What to check: Baseline: revenue INR 75,00,000; EBITDA INR 13,65,000; EBITDA margin 18.2%.
A useful CFO dashboard answers a few demanding questions: Are we making money? Can we meet upcoming payments? Where is cash tied up? What changed against plan, and who needs to act? AI can help build the dashboard faster, but the quality of its answers starts with accounting definitions and reconciled data.
Use AI to propose structure, draft formulas and explain reviewed results. Use the accounting system for source records, deterministic formulas for calculations and an accountable finance reviewer for sign-off.
1. Decide what the dashboard must help you do
Start with the audience and decision. A daily treasury view is different from a monthly partner review or a bank-facing management report. Specify the entity, reporting period, currency, unit, accounting basis, comparison and refresh frequency.
Choose a small first version: an executive overview and four supporting views. Add sector-specific measures only when their definitions and sources are available. There is no universal ideal gross margin, current ratio or cash balance for every business.
2. Assemble the right data
| Source | What it supports | Key check |
|---|---|---|
| Monthly P&L / general ledger | Revenue, margins, expense trends. | Map accounts consistently and reconcile to the approved accounts. |
| Balance sheets and opening balances | Liquidity, working capital and average balances. | Verify the reporting date and opening/closing reconciliation. |
| Bank and cash records | Cash position and cash-movement analysis. | Reconcile balances; identify restricted balances and overdrafts separately. |
| Receivable and payable documents | Ageing, collection priorities and payment schedules. | Check due dates, credit notes, disputes and settlements. |
| Inventory records | Stock movement and inventory days. | Reconcile quantities and valuation to the accounts. |
| Budget and prior-period data | Variances, targets and comparable trends. | Match account, department, calendar and unit definitions. |
A P&L alone cannot establish operating cash flow, bank liquidity, DSO, inventory days or working capital. Mark those measures “Data required” instead of fabricating balance-sheet or cash inputs. For Indian reporting, keep GST collected, recoverable tax and revenue classifications consistent; do not accidentally treat all bank receipts as sales.
3. Build a clean model before asking AI for charts
Use one row per clearly defined record: a voucher line, an invoice, a daily bank balance or a monthly account balance. Do not append a monthly total to its underlying detailed transactions and then sum both. Keep actuals and budgets distinguishable.
A practical model has a date table, account mapping, entity/department dimensions and separate fact tables for ledger activity, budgets, receivables and payables. Check relationship cardinality so a join does not multiply amounts. Retain source-row IDs and an exception sheet.
For a smaller Excel build, use these worksheets:
| Sheet | Purpose |
|---|---|
| Read_Me | Scope, period, units, definitions and refresh instructions. |
| Raw_Data | Untouched source extracts, or clearly separated source tables. |
| Mapping | Approved account groups, department codes and sign rules. |
| Calculations | Transparent formulas and reconciliations. |
| Dashboard | Management view with filters, charts and action indicators. |
| Checks | Control totals, unmapped accounts and unresolved exceptions. |
4. Define the KPIs explicitly
These are common management definitions, not a substitute for the entity's accounting policies. Keep numerator and denominator periods and classifications consistent.
| KPI | Formula / definition | Frequency and use |
|---|---|---|
| Revenue growth | (Current comparable revenue − comparison revenue) ÷ comparison revenue × 100. | Monthly: track growth; flag a zero or negative comparison base for interpretation. |
| Gross profit / margin | Revenue − cost of sales; gross profit ÷ revenue × 100. | Monthly: examine pricing, mix and direct costs. |
| EBITDA / margin | EBIT + depreciation + amortisation; EBITDA ÷ revenue × 100. | Monthly: assess operating performance. Define adjustments separately; this is not cash flow. |
| Available cash | Reconciled cash and bank balances available for use; show restricted cash and borrowing separately. | Daily/weekly: assess near-term liquidity. |
| Operating working capital | Trade receivables + inventory − trade payables, under a documented operating definition. | Monthly: identify cash tied up in trading. Distinguish from total current assets less current liabilities. |
| DSO | Average trade receivables ÷ net credit sales × days in the period. | Monthly: monitor collection efficiency; use a consistent tax basis. |
| Current ratio | Current assets ÷ current liabilities. | Monthly: assess short-term balance-sheet liquidity; inspect asset quality. |
| Budget variance | Actual − budget; percentage variance = (actual − budget) ÷ budget × 100. | Monthly: investigate deviations. Higher cost is usually adverse, while higher revenue is usually favourable. |
Use an average balance definition suited to the business; opening/closing averages can hide intra-month swings. Handle zero denominators and missing inputs explicitly. Never give a ratio a value just to fill a card.
5. Design the dashboard around decisions
| View | Recommended content | Action supported |
|---|---|---|
| Executive overview | Six KPI cards, trend, budget comparison, exceptions and an action list. | Prioritise the next management discussion. |
| Profitability | Revenue trend, gross/EBITDA margin and expense variance bars. | Investigate pricing, mix and cost movements. |
| Cash and liquidity | Reconciled bank position, receipts/payments and a separately labelled forecast. | Plan payments and funding needs. |
| Working capital | Receivable ageing, overdue customers, inventory days and payable schedule. | Assign collection, purchasing and stock actions. |
| Budget and performance | Actual/budget/prior-year comparison by department and account. | Agree corrective actions and ownership. |
At the top, show company, period, unit, source cut-off and refresh time. Use line charts for trends, bars for category comparisons and a cash bridge only when movements reconcile. Use consistent colours and accessible labels. Show blank or unavailable periods honestly rather than connecting them with invented values.
6. Work through an illustrative example
Assume a monthly business dataset in ₹ lakh: revenue 20, cost of sales 12, and operating expenses of 5 excluding depreciation, amortisation and finance costs. Assume no other operating items. Revenue budget is 18. Reconciled available cash is 4.
Gross profit = 20 − 12 = ₹8 lakh. Under these stated classifications, EBITDA = 8 − 5 = ₹3 lakh. Revenue exceeds budget by ₹2 lakh, or 11.11%. None of these figures establishes operating cash flow, receivable days or a cash forecast. The example is fictional and does not describe a client.
7. Choose Excel or Power BI
Excel: useful for a controlled first version
- Load sources into named tables, preferably through repeatable imports.
- Use Power Query or tested transformations to standardise fields.
- Create approved formulas and PivotTables.
- Add slicers, trend charts and KPI cards linked to calculation cells.
- Protect formula areas appropriately, retain raw data and document refresh steps.
Microsoft documents natural-language analysis and Copilot assistance in Excel. Clear table structures and named columns help. Confirm feature availability in your account and review generated formulas and charts before use. Excel analysis guide ↗ · Copilot insights ↗
Power BI: useful for a governed, shared model
Build and test relationships and measures before asking Copilot to suggest report pages. Document financial definitions and AI instructions so the model does not have to guess what “revenue” or “margin” means. Microsoft describes Copilot-assisted report creation and model preparation; access depends on the applicable capacity, permissions and administrative settings. A Microsoft 365 subscription alone should not be assumed to include every Power BI Copilot capability. Report creation ↗ · AI instructions ↗ · Requirements ↗
8. Use these six prompts in sequence
1. Define the dashboard
Act as a CFO dashboard designer. The entity is [INDUSTRY], reporting in [CURRENCY/UNIT] for [PERIOD], on [ACCRUAL/CASH BASIS]. Available sources are [LIST]. Define six decision-relevant KPIs with formulas, source fields, frequency and owner. Separate available metrics from those requiring more data. Do not invent targets, opening balances or cash flows.
2. Inspect and map the source
Inspect these anonymised headers and sample rows. Identify each table's grain, unique key, date field, unit, sign convention and control total. Propose a mapping from ledger accounts to revenue, cost of sales, operating expenses, finance costs, depreciation and other relevant groups. Put uncertain classifications in an exception register; do not silently classify them.
3. Create tested formulas
Using these actual table and column names, draft Excel formulas or DAX measures for the approved KPI definitions. Explain date filters, signs, denominator handling and blank values. Give expected outputs for small test cases, including zero revenue and missing budget. Do not replace unavailable inputs with zero.
4. Design the pages
Design an executive overview plus profitability, cash, working-capital and budget views. For each visual give its business question, measure, dimension, filter and drill-through. Keep source timestamps, currency units and data-quality warnings visible. Avoid charts that imply a trend when only one period is supplied.
5. Draft the CFO commentary
Use only these reconciled dashboard results. Produce three findings, three questions needing investigation and three actions with proposed owners. Show amounts, dates and source measures. Separate facts from hypotheses. Do not claim that revenue growth caused cash growth or invent reasons for a variance.
6. Validate the dashboard
Review the KPI definitions, source-to-report control totals and sample outputs. Check for duplicate joins, mixed periods, unit errors, zero-filled missing data, reversed expense signs and formula inconsistencies. Explain each unresolved exception. Do not declare the dashboard validated without the source evidence.
9. Validate before the management meeting
- Tie revenue, costs and profit to approved accounting reports.
- Reconcile cash to bank and cash records; identify restrictions separately.
- Check ageing totals against control accounts and review credit balances.
- Test filters, relationships, signs, units and formulas with known examples.
- Keep actuals, budgets, estimates and forecasts clearly labelled.
- Show the impact of missing data and unresolved classifications.
- Have a finance reviewer sign off the definitions and outputs.
10. Add forecasting after the actuals are dependable
A cash forecast needs a separate schedule of opening cash, expected receipts, payment timing, payroll, taxes, debt service and other relevant movements. Document the assumptions and build base, downside and upside cases. Avoid simply applying a growth percentage to EBITDA and calling the result cash.
Maintain a monthly action register: issue, supporting measure, proposed action, owner, due date and outcome. AI can draft commentary; managers must confirm the cause and the action.
Protect financial information
Use an approved AI environment and remove unnecessary client identifiers. Keep credentials, bank-account details and payroll data out of public prompts. Apply access controls to the finished dashboard, and share only the level of financial detail appropriate for the audience.
Official resources
Continue with Tally productivity using AI, AI for Excel and AI-assisted cash-flow forecasting.