Blog · Territory, capacity and quota planning · Finance and FP&A teams
Reliable FP&A reporting starts with controlled plan versions, reconciled totals and assigned variance explanations. Follow a budget-to-actual workflow with spreadsheet formulas and a worked expense example.
The best practices for financial planning and analysis are to fix the reporting basis, prove the totals and assign each material explanation to someone who can support it. A report is ready when another analyst can reproduce the numbers and a department owner can explain the remaining difference. A polished presentation cannot supply either check.
These are operating practices for the monthly reporting cycle. For the wider responsibilities of the function, start with what FP&A does.
Begin with the approved budget export and the closed-period actuals export. Keep their original filenames, approval status, period and extraction dates in a reporting register. Agree whether the review covers operating expenses, revenue, cash or another defined measure before adding rows together.
Use a common set of department and account codes. A department rename needs an explicit mapping; it should not create a new business unit in the report. Keep unmapped accounts visible in an exception list until finance resolves them. A blank classification is unfinished work, not an instruction to exclude the amount.
State the sign convention in the packet. The example below treats expenses as positive amounts and defines variance as actual minus budget. Positive variance therefore means spending above plan. Revenue needs a separate interpretation even if its arithmetic uses the same subtraction.
Hold the original approved budget fixed. If management authorizes a revision, record that revision as a separate version and state which comparison each chart uses. Changes to the reporting process itself belong in the broader FP&A transformation discussion.
Suppose a US services company has an expense export on a sheet named Export. Columns A through F contain Period, Department, Account, Scenario, Version and Amount_USD. Each row is a recorded amount at that stated grain, with reversals carrying their original signs.
On the report sheet, put the selected period in J1 and the approved budget version in J2. Department names run down column A. Calculate the department budget in B2:
=SUMIFS(Export!$F$2:$F$1000,Export!$A$2:$A$1000,$J$1,Export!$B$2:$B$1000,A2,Export!$D$2:$D$1000,"Budget",Export!$E$2:$E$1000,$J$2)
Calculate actuals in C2, using Closed as the company's actuals version label:
=SUMIFS(Export!$F$2:$F$1000,Export!$A$2:$A$1000,$J$1,Export!$B$2:$B$1000,A2,Export!$D$2:$D$1000,"Actual",Export!$E$2:$E$1000,"Closed")
Microsoft documents SUMIFS as adding values that meet multiple criteria. Those criteria matter: omitting the version condition can add both an original plan and its replacement. Extend the ranges to cover the complete export, then fill the formulas down through all departments.
Check the summed budget against the approved expense schedule and summed actuals against the same-scope ledger control total. Neither total should depend on which departments happened to receive commentary.
For the selected month, the company obtains this report. Column E contains signed driver amounts accepted by the department owner after supporting schedules were reviewed.
| Department | Budget (USD) | Actual (USD) | Variance (USD) | Supported drivers (USD) | Residual (USD) |
|---|---|---|---|---|---|
| Sales | 200,000 | 214,000 | 14,000 | 12,000 | 2,000 |
| Marketing | 120,000 | 109,000 | -11,000 | -9,000 | -2,000 |
| Operations | 300,000 | 318,000 | 18,000 | 15,000 | 3,000 |
| Finance | 80,000 | 79,000 | -1,000 | -1,000 | 0 |
| Total | 700,000 | 720,000 | 20,000 | 17,000 | 3,000 |
With this table in A1:F6, enter the following in D2 and F2, then fill down through row 5:
=C2-B2
=D2-E2
The report must prove both identities: $720,000 minus $700,000 equals the $20,000 variance, and $17,000 of supported drivers plus $3,000 of net residual equals that variance. A narrative such as "higher activity" does not earn an explained amount until its driver schedule supports the dollars.
Do not let positive and negative residuals disappear into the total. Add absolute variance in G2 and absolute residual in H2:
=ABS(D2)
=ABS(F2)
Microsoft's ABS documentation defines absolute value as the number without its sign. Here the absolute variances sum to $44,000 and the absolute residuals to $7,000. Explanation coverage is:
=1-SUM(H2:H5)/SUM(G2:G5)
That is 1 minus 7,000 divided by 44,000, or 84.1%, rounded to one decimal place. If total absolute variance is zero, show coverage as not applicable rather than dividing by zero. Use this measure only after checking that explanations are relevant, signed consistently and free of duplicate drivers.
Sales should support its accepted driver with the relevant compensation or spending schedule. Marketing should identify whether its underspend reflects lower activity, a timing change or a canceled commitment. Operations should retain the detail behind its accepted amount and investigate the residual. The finance owner checks the bridge across departments.
For each open item, record the source reference, responsible owner, due date and next decision. An explanation may establish that a difference is temporary without establishing when it will reverse. Keep those statements separate. The follow-up forecast should reflect only the changes management has accepted.
Choose the reporting tool after defining these controls. The separate guide to FP&A variance-analysis approaches covers that choice, while reporting with Power BI for FP&A addresses a neighboring reporting workflow.
Covirage is not affiliated with Microsoft.
Bring a budget and actuals export with period, department, account and version columns to Covirage. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See how this fits FP&A reporting.
A reliable report identifies its period, scope, actuals cutoff and approved comparison version. Its totals reconcile to the underlying exports, and each material explanation has supporting evidence and an accountable business owner. A reader can reproduce the reported result without relying on an analyst's memory.
Set a dollar threshold and a business relevance test before reviewing results. Explain recurring problems, policy breaches and decision changing movements even when they fall below the threshold. Apply the same rule across departments, and show the remaining unexplained amount explicitly.
Keep the approved budget as the accountability comparison and label the latest forecast separately. The forecast answers what is now expected, while the budget records the original commitment. Replacing the budget silently makes performance appear to improve without any operating change.