Blog · Board and management reporting · Finance and FP&A teams
Build a revenue forecast-versus-actual report from an approved plan and a revenue export. This business workflow covers the spreadsheet calculations, report layout and checks needed before a business unit explains its variance.
Power BI revenue forecasting works best as a comparison between an approved revenue plan and actual revenue measured on the same basis. Prepare the amounts by month and business unit, prove their totals, and then build the report around the exceptions that need a decision. Creating a forecast and displaying an approved forecast are separate jobs; this workflow addresses the business reporting job.
The finance director needs to know which unit missed its revenue plan, whether another unit offset that miss, and whether the next forecast needs to change. A company total answers only the middle question. A useful report preserves the unit and month detail behind that total.
Decide what revenue means before collecting files. Use the same accounting basis for forecast and actual: recognized revenue, net of the returns and adjustments included in the company's reporting policy. Bookings, invoices and collections can help explain a change, but substituting one of them for recognized revenue changes the question.
For constructing the underlying commercial plan, use the separate sales projection method. For a subscription business, SaaS revenue forecasting covers the different inputs behind recurring revenue. Neither should be replaced by a chart of historical revenue alone.
Bring the approved forecast export, the posted revenue export and a business-unit mapping maintained by finance. Keep the original files unchanged. In the reporting workbook, retain a forecast version, approval date, reporting currency and actuals cutoff alongside the working table.
The minimum comparison has one row per month and business unit. If the source exports contain invoice or journal detail, sum those rows into that grain first. Do not attach a full monthly forecast amount to every invoice row: that repeats the plan every time another invoice appears.
Microsoft's Excel connector documentation describes selecting an Excel workbook and choosing its tables or sheets. The practical preparation is to give the report one clearly named comparison table with real dates, numeric amounts and consistent unit names. Resolve duplicates and unmapped units before it becomes a reporting input.
An empty actual is not automatically zero. It might mean a unit has not closed its books. Keep a close-status field in the working file so finance can distinguish an incomplete period from a closed period with no revenue.
Consider a fictional US services company comparing its approved plan with closed January and February actuals. Columns A through D hold month, unit, forecast and actual. Columns E and F calculate the amount variance and variance percentage.
Revenue variance = Actual revenue − Forecast revenue
Variance percentage = Revenue variance / Forecast revenue
| Month | Business unit | Forecast revenue (USD) | Actual revenue (USD) | Variance (USD) | Variance |
|---|---|---|---|---|---|
| January | East | 120,000 | 118,000 | -2,000 | -1.7% |
| January | West | 80,000 | 86,000 | 6,000 | 7.5% |
| February | East | 100,000 | 92,000 | -8,000 | -8.0% |
| February | West | 50,000 | 55,000 | 5,000 | 10.0% |
| Total | 350,000 | 351,000 | 1,000 | 0.3% |
In E2 and F2, enter these formulas and fill down through row 5:
=D2-C2
=IF(C2=0,"",E2/C2)
The blank percentage flags a zero forecast for separate review. It does not remove the amount variance. In total row 6, enter these formulas in C6, D6, E6 and F6, respectively:
=SUM(C2:C5)
=SUM(D2:D5)
=D6-C6
=IF(C6=0,"",E6/C6)
Percentages are displayed to one decimal place; calculations retain the underlying amounts. The total is $351,000 minus $350,000, or $1,000 favorable. Dividing by the forecast gives 0.3% after rounding.
Use a matrix with business unit on rows, month on columns and forecast, actual and amount variance as values. Microsoft's matrix visual guide documents these row, column and value field areas. Have the report author use the prepared comparison table and verify that displayed amounts match it.
For this example, the East unit totals $220,000 forecast and $210,000 actual, a $10,000 shortfall. West totals $130,000 forecast and $141,000 actual, an $11,000 favorable variance. Their variances sum to the company's $1,000 favorable result.
Display forecast version and actuals cutoff beside the report. Keep units and currency visible. Review a selected unit and month against the workbook, then clear the selection and check the full total again. This catches a report that appears correct only under a filter left over from an earlier review.
Quantity planning needs different inputs; Power BI demand forecasting covers that neighboring question. Here, units are organizational reporting units, and the measured amount is revenue.
Three ties must hold: forecast total to the approved plan, actual total to the closed revenue ledger, and summed row variances to total actual minus total forecast. For the last check, this formula must return zero:
=SUM(E2:E5)-(SUM(D2:D5)-SUM(C2:C5))
The company result is close to plan, but the underlying misses are larger. Add absolute row deviation in G2 and fill down:
=ABS(E2)
The four absolute deviations are $2,000, $6,000, $8,000 and $5,000, totaling $21,000. Divide that total by $351,000 actual revenue to get 6.0%, rounded to one decimal place:
=SUM(G2:G5)/SUM(D2:D5)
This measure applies here because all actual revenue amounts are positive. It reveals offsetting misses that the net variance hides. Finance should ask East about delivery timing or lost revenue and West about the source and repeatability of its favorable result before changing the next plan.
Covirage is not affiliated with Microsoft or Power BI. For the broader choice of reporting approach, see the Power BI alternatives discussion.
Bring a sample of your own approved forecast and revenue export as a file or Google Sheet; Covirage maps the columns, and its tools compute and check the comparisons. The external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.
See financial planning and analysis reporting for the supporting business context.
Only when pipeline assumptions are part of the revenue plan being reviewed. A forecast-versus-actual report can start with an approved revenue schedule and posted actuals. Opportunity amounts alone need expected closing dates, delivery timing and the company's revenue rules before they become a revenue forecast.
Use forecast revenue when the question is how far actual performance differed from the plan. Use actual revenue for an explicitly labeled forecast-error measure. Keep the definition visible, handle a zero denominator separately and calculate the total from amounts rather than averaging row percentages.
Choose a review cycle that matches the business decision, and retain each approved version. A monthly update can reflect new demand and delivery information while the original version remains available for accountability. Never replace the earlier forecast and then describe the comparison as its original accuracy.