Cash flow forecast in Power BI: inputs and checks

A cash flow forecast in Power BI compares planned receipts, payments and closing cash with actual cash movements by period. Prepare and reconcile the planning export first, then use the report to show timing, forecast differences and headroom. This workflow uses finance-owned spreadsheet calculations so the report has an independently checked source.

Start with the cash question and a frozen forecast

Decide which decision the report supports: whether collections are behind plan, when cash approaches a buffer, or which payment category explains a change. Those questions require different measures. A chart of receipts cannot establish closing cash unless payments and opening cash are also present.

Use the cash flow forecast guide to build the underlying receipts and payments. Keep the approved forecast unchanged when actual results arrive. A replacement forecast can be useful for planning, but comparing actuals with a version updated after the period ended does not measure the original forecast's performance.

Export one row per reporting month for the worked workflow below. If the workbook contains entities, accounts or scenarios, keep their identifiers and prepare separate selections before importing. Repeat the opening cash balance only at the level at which it is valid; attaching the company balance to every customer row would multiply it.

Calculate the measures in the workbook

An illustrative US distributor opens January with $100,000. Management's monthly minimum cash buffer is $60,000. Receipts and payments are positive amounts, with payments subtracted in the calculation. The example uses whole USD; no dollar rounding is required.

Net cash flow = Receipts - Payments

Closing cash = Opening cash + Net cash flow

Headroom = Closing cash - Cash buffer

Put the following forecast table in columns A:H, with headers in row 1.

The exact Excel formulas are:

E2=C2-D2
F2=B2+E2
H2=F2-G2
B3=F2

Fill E, F and H down through row 4. Link B4 to F3. Preserve B2 as the starting cash input rather than overwriting it with a period total.

Place actual receipts in I, actual payments in J, actual net flow in K, actual closing cash in L, receipts variance in M and closing cash variance in N. Enter and fill down:

K2=I2-J2
L2=$B$2+SUM($K$2:K2)
M2=I2-C2
N2=L2-F2

The expanding SUM range accumulates actual net cash movements from the same opening balance. Microsoft's SUM documentation explains how the function adds cell ranges. Check that imported amounts are numeric, because text amounts can be omitted from the sum.

Variance means actual minus forecast throughout. February actual headroom is $60,000 - $60,000 = $0. That result identifies the period to inspect more closely, even though quarter-end actual cash recovers to $90,000.

Load the prepared table and choose the visuals

In Power BI Desktop, select Home, Get Data, then Excel. Open the workbook, select the prepared table in Navigator and select Load. This is the worksheet-loading route described in Microsoft's getting-started guide. Confirm the period field sorts chronologically and amount columns are numeric before designing the page.

Build the report around these views:

• Receipts versus payments by month. Use columns to expose the periods when outflows exceed inflows. Add clear forecast and actual labels rather than relying only on color.
• Closing cash versus the buffer. Use dated closing balances and a buffer reference so the audience can see February's pressure. Keep both on the same USD scale.
• Actual versus forecast receipts. Show the signed difference beside the two amounts, then identify the customer or category behind the difference in the supporting export.

Microsoft documents a combo chart combining a line and columns. That can present a closing balance alongside cash movements, provided the labels distinguish the balance from the flows. Avoid a second scale that makes the buffer comparison visually ambiguous.

For a broader reporting layout, see the neighboring Power BI cash flow dashboard. For chart selection beyond this workflow, use the cash flow forecast chart examples.

Keep a historical projection separate from the approved plan

Microsoft's Analytics pane documentation describes forecasting on supported line charts using historical data. If you use that option, label the result separately from the finance planning case.

A historical pattern alone does not document next month's loan payment, a specific delayed invoice or an equipment deposit. The business workflow here reports the supplied cash plan and its actual outcomes. Finance remains responsible for those dated assumptions and for choosing the comparison version.

Covirage is not affiliated with Microsoft, the maker of Power BI and Excel.

Reconcile the report to its source

Forecast quarterly receipts are $180,000 + $220,000 + $240,000 = $640,000. Payments are $200,000 + $210,000 + $230,000 = $640,000. Therefore quarter-end forecast cash is $100,000 + $640,000 - $640,000 = $100,000.

Actual cash reconciles as $100,000 + $630,000 - $640,000 = $90,000. The $10,000 closing shortfall is exactly the $10,000 receipts shortfall because total payments match plan. Receipts variance divided by forecast receipts is -10,000 / 640,000 = -1.5625%, displayed as -1.6% after rounding to one decimal place.

Check the report's final-period balance against F4 and L4. Summing forecast closing balances gives $80,000 + $90,000 + $100,000 = $270,000, which is not quarter-end cash. Set the report summary to show the ending balance for the selected period, and verify that behavior with a single-month selection and the full quarter. Use the separate forecast accuracy in Power BI guide when the question moves from cash availability to forecast performance measures.

Where it goes wrong

• Adding balance columns. Sum receipts and payments across months; show cash at the selected ending date.
• Mixing forecast versions. Compare actuals with the stated approval-date forecast, then show a revised outlook separately.
• Confusing missing actuals with zero. Mark future periods as unavailable rather than showing that no cash moved.
• Using invoice dates as receipt dates. Revenue and receivables do not establish the day money reaches the bank.
• Reading month-end headroom as daily coverage. Expand tight months into dated receipts and payments before concluding the buffer held throughout.

Investigate your cash export in Covirage

Bring your forecast workbook and actual cash export, with period dates and version labels, to Covirage. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. This gives finance a file-based way to investigate the figures behind its reporting.

Create a free Covirage account and bring a sample of your own data.

See FP&A reporting with Covirage for the supporting business context.