Power BI dashboard for the CFO: profit and loss, cash flow and working capital

Which questions does a financial dashboard answer?

Before building a single chart, write down the questions the CFO and CEO ask every month. A good financial dashboard is designed to answer exactly these:

• How does this month's profit compare with budget and last year, and why did it change?
• Which product, customer or branch earns the most, and which loses money?
• Will we run short of cash in the next four to thirteen weeks?
• How much money is tied up in receivables and inventory, and how many days does it take to turn back into cash?
• Which costs are growing faster than sales?

Page one: management profit and loss

The statutory income statement is prepared for auditors and tax authorities; the management P&L is prepared for decisions. The main difference is structure: general ledger accounts are mapped to management lines (for example, all plant energy costs under one line) and profit is shown in layers: gross profit, contribution margin, operating profit and net profit.

In Power BI this page is usually a matrix with lines in rows and months in columns, KPI cards on top and a waterfall chart showing which items moved profit from last month to this month. Clicking any line opens the accounts behind it and even the individual entries.

• Gross margin = (net sales − cost of goods sold) ÷ net sales
• Operating margin = operating profit ÷ net sales
• General and administrative expenses as a share of sales
• Variance from budget by line

For profit by product and customer to be trustworthy, product cost has to be calculated correctly. The methods, with a worked example, are in how to calculate the cost of a manufactured product.

Page two: cash flow and a 13-week forecast

Profit is on paper; cash is in the bank. Many profitable companies get into trouble because they run short of cash. The cash flow page has two parts: the past (actual cash in and out, split into operating, investing and financing) and the future (a week-by-week forecast).

The 13-week forecast is built from three sources: committed receipts (cheques received and receivables due), committed payments (cheques issued, loan instalments, payroll and tax) and estimates (cash sales and expected purchases based on trend). The result is a chart of each week's closing cash, with weeks below the minimum balance turning red. You see a shortfall weeks in advance and have time to plan an extension, earlier collection or financing.

Page three: working capital and the cash conversion cycle

Working capital shows how much money is tied up in day-to-day operations. The three core KPIs on this page together make up the cash conversion cycle:

Suppose a company sells 365 million a year, one million a day, and its cash conversion cycle is 90 days. If collecting receivables faster and cutting slow-moving stock bring the cycle down to 70 days, about 20 million of cash is released without borrowing anything. The dashboard shows these KPIs by customer, product group and warehouse, so you can see where those 20 days can come from.

How the KPIs are built in Power BI

In Power BI each KPI is a DAX measure, defined once and correct on every page under any filter. Net sales, for example, is the sum of invoice amounts minus returns and discounts; gross margin divides gross profit by net sales with the DIVIDE function; and last year's sales for the same period come from time intelligence functions over a calendar table. When a manager changes the branch or product filter, every measure recalculates for that selection automatically.

One detail matters for companies with a non-calendar fiscal year or a local calendar, such as the Persian calendar: the built-in time intelligence functions assume the Gregorian calendar. A calendar table with fiscal year, quarter and month columns is built, and comparisons are defined on those columns, so "this May against last May" is calculated correctly.

From the accounting system to the dashboard: the build steps

• Extract: general ledger, sales and purchase invoices, receipts, payments and cheques from the accounting system or ERP (Excel export or a direct database connection).
• Map accounts: each account goes to its management line and cost centre.
• Reconcile: dashboard totals are checked against the trial balance and official statements until no difference remains.
• Build measures and pages: profit and loss, cash, working capital and expenses.
• Automate refresh and train: the dashboard updates itself when each month is closed.

Reconciliation is the most important step. If the CFO sees the dashboard disagree with the books, they will not trust it again. If your reports are in Excel today, also read moving Excel reports to Power BI.

Summary

A Power BI dashboard for the CFO does not replace accounting; it is built on the same data, and its purpose is to move the CFO's time from preparing reports to analysis and decisions. The profit and loss, cash and working capital pages are the base; budget, financial ratios or customer profitability pages can follow. Try the interactive CFO dashboard sample and see the financial and cash flow dashboard service for details. If the financial dashboard is part of a wider view for the CEO, read what a management dashboard is as well.

Frequently asked questions