Blog · Board and management reporting · Finance and FP&A teams
Prepare cost center plans and ledger actuals for FP&A reporting in Power BI. Work through expense variances, compare a separate forecast version and prove that the report agrees with the source files.
Power BI financial planning and analysis reporting starts with a comparable budget, dated forecast and set of ledger actuals. Match their periods, cost centers, accounts and expense signs before displaying variances. A report is ready for management when its totals reconcile and each owner can identify the rows behind a movement.
For a finance team working from exports, the main job is defining the comparison. The dashboard then presents that agreed view consistently through the monthly review.
Begin with one closed month. Export the approved budget for that month, the forecast version finance wants to evaluate and the ledger actuals after the agreed close cutoff. Keep the version date and approval status beside each planning file.
Use entity, period, cost center and account as the comparison key. Map ledger accounts to reporting lines once and inspect every unmapped account. A cost center renamed during the year needs a stable mapping; matching on a similar-looking description can combine departments that have different owners.
For this expense report, positive amounts mean expense and positive actual-minus-budget variance means overspend. Revenue needs its own interpretation of favorable performance. Do not assign one favorable label to a mixed list of revenue and expense amounts.
Keep the budget and forecast definitions distinct. The original budget remains the baseline even when a later forecast becomes the better guide to expected spending. A report that silently replaces one with the other loses the ability to explain what changed.
A fictional US distributor reviews September 2026 operating expenses. All figures below are USD, use the same account mapping and exclude internal allocations for this example. Budget is the approved original plan; forecast is the separately dated outlook prepared before September began.
Put cost center in A, account in B, budget in C, actual in D and forecast in E. Rows 2 through 5 contain the detail; do not include a total row in the imported detail table.
| Cost center | Expense account | Budget (USD) | Actual (USD) | Forecast (USD) | Actual minus budget (USD) | Actual minus forecast (USD) |
|---|---|---|---|---|---|---|
| Operations | Labor | 120,000 | 126,000 | 124,000 | 6,000 | 2,000 |
| Operations | Freight | 30,000 | 27,000 | 28,000 | -3,000 | -1,000 |
| Sales | Payroll | 80,000 | 82,000 | 83,000 | 2,000 | -1,000 |
| Sales | Travel | 20,000 | 15,000 | 18,000 | -5,000 | -3,000 |
| Total | 250,000 | 250,000 | 253,000 | 0 | -3,000 |
Calculate F2 and G2, then fill down:
F2 = D2-C2
G2 = D2-E2
Actual spending is exactly on the original budget but $3,000 below the latest forecast. Those statements can both be true. The forecast anticipated higher total spending, while the budget comparison shows offsetting movements within the business.
If the ledger export is on a sheet named Ledger, with cost center in A, account in B, period in C and expense amount in D, use the following formula in D2. H1 holds the agreed period label.
=SUMIFS(Ledger!$D$2:$D$10000,Ledger!$A$2:$A$10000,A2,Ledger!$B$2:$B$10000,B2,Ledger!$C$2:$C$10000,$H$1)
Use the complete source range and add entity criteria if the file includes several entities. Microsoft's SUMIFS documentation describes summing amounts that meet multiple criteria. Check unmatched keys separately: a formula returning zero does not establish that the source is complete.
Sum the account amounts within each cost center. Calculate each percentage from that center's budget, then calculate the company percentage from the company amounts.
| Cost center | Budget (USD) | Actual (USD) | Variance (USD) | Variance / budget |
|---|---|---|---|---|
| Operations | 150,000 | 153,000 | 3,000 | 2.0% |
| Sales | 100,000 | 97,000 | -3,000 | -3.0% |
| Total | 250,000 | 250,000 | 0 | 0.0% |
The percentages are shown to one decimal place. Operations is over budget; Sales is under budget. The two signed dollar amounts cancel, but each needs its own explanation.
For the detailed table's company percentage, use:
=IF(SUM(C2:C5)=0,"",SUM(F2:F5)/SUM(C2:C5))
The proof is budget plus variance equals actual: $250,000 plus $0 equals $250,000. The forecast proof is $253,000 minus $3,000 equals $250,000. Absolute account movements total $16,000, calculated as 6,000 + 3,000 + 2,000 + 5,000; this describes the size of movements being investigated, not extra company spending.
Reconcile the actual total to the corresponding ledger scope and the budget total to the approval file. If allocations are included in your real report, reconcile their receiving and sending entries and document the chosen scope before interpreting departmental results.
Save a recalculated workbook with consistent headers and amount types. In Power BI Desktop, Get Data > Excel is the route for a simple worksheet, as distinguished in Microsoft's Excel import guidance. Select the detail table rather than the worksheet containing the printed management pack.
Create a matrix with cost center followed by account in Rows. Put budget, actual, forecast and both dollar variances in Values, using sums. Microsoft's matrix visual documentation explains how rows, columns and values organize the display.
Confirm that the unfiltered report reproduces the detailed table and that Operations reproduces its subtotal. Add period and entity filters only after those checks pass. Keep ratio totals out until the report owner confirms that each divides the filtered variance by the filtered budget, with undefined results for zero budgets.
Show the budget version, forecast version and actual close cutoff in the report description. A user selecting a period should be able to tell which expectation is being evaluated. Use FP&A best practices for the wider review discipline and FP&A transformation for changes to ownership and reporting routines.
Covirage is not affiliated with Microsoft, the maker of Power BI and Excel.
Have each owner describe the account movement, its supporting records and whether it affects the remaining forecast. The Power BI alternatives comparison addresses the broader reporting-tool choice; it does not change these reconciliation requirements.
Bring a sample of your own ledger, approved budget and dated forecast as files or a Google Sheet. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account and bring the exports behind your next cost center review.
See FP&A reporting for the supporting management reporting context.
Start with the approved budget, a dated forecast and closed-period ledger actuals. Each needs a period, entity, cost center, account and amount. Add an account mapping when source labels differ, and retain source record references so reconciliation differences can be investigated.
Yes, when each comparison is labeled. The budget records the approved baseline, while a dated forecast records a later expectation. Reporting actuals against both helps distinguish delivery against the original commitment from performance against the most recent outlook.
Expense increases in one department can offset savings in another. A company total therefore cannot tell you whether each owner met the plan. Inspect cost center and account variances, then confirm that their signed amounts add back to the company result.