FP&A consolidation: combine department plans that reconcile

FP&A consolidation combines approved department or entity plans into a company total that can be rebuilt from the submissions. Define the reporting basis, list the submissions you expect and reconcile every imported amount before discussing the resulting plan. A total that adds correctly can still contain the wrong versions or omit a department.

This guide covers management planning submissions. The consolidation glossary provides the broader meaning; statutory accounting treatment, intercompany elimination judgments and published financial statements require their own accounting review.

Set a common basis for every submission

Begin with the department budget exports the business already uses. Ask each owner to identify the entity, department, fiscal period, currency, unit scale, approved version and approver. Keep the source filename with the register so a reviewer can return to the accepted submission.

Choose the grain before checking duplicates. A summary export may have one row per entity, department, period, version and currency. An account-level export needs the account in that identifying key. Employee or project detail may require additional identifiers. A repeated department is normal at a detailed grain and suspicious at a department-total grain.

Make the period convention explicit. A calendar month and a fiscal period with a similar label need not cover the same dates. Likewise, a year-to-date amount must not be added to individual monthly amounts. Establish whether expenses are positive and whether figures are whole dollars or another stated unit.

Keep original amounts alongside any conversion. If the plan includes foreign currencies, finance must approve the rate basis and its effective period before the rollup. The worked example avoids that judgment by using USD throughout. The wider role of this review sits within financial planning and analysis.

Create the expected register before importing files

A US business expects these monthly expense submissions. The approved register was signed off before the imports were combined. B1 is the approved version label in this example, not a spreadsheet address.

The company expects four submissions because the register contains four required keys. Do not derive that expectation from the imported file: a missing department would then disappear from both sides of the check.

The source register also records an owner and approval evidence for each row. A file arriving in a shared folder establishes receipt; it does not establish approval. Agree the handoff with department owners using the collaborative FP&A process.

Match versions and calculate differences in the spreadsheet

Put the combined department summary export on a sheet called Submissions, with Entity in A, Department in B, Period in C, Version in D, Currency in E and Amount_USD in F. This example has four imported rows, occupying rows 2 through 5.

On a separate report sheet, use H through L for Entity, Department, Period, Approved version and Approved total. Calculate imported amount in M2:

=SUMIFS(Submissions!$F$2:$F$5,Submissions!$A$2:$A$5,H2,Submissions!$B$2:$B$5,I2,Submissions!$C$2:$C$5,J2,Submissions!$D$2:$D$5,K2,Submissions!$E$2:$E$5,"USD")

Microsoft's SUMIFS documentation explains that it sums values meeting multiple criteria. The version and currency criteria protect the comparison basis. Expand every range consistently when the export contains more rows.

In N2, subtract the approved amount:

=M2-L2

In O2, count matching source rows:

=COUNTIFS(Submissions!$A$2:$A$5,H2,Submissions!$B$2:$B$5,I2,Submissions!$C$2:$C$5,J2,Submissions!$D$2:$D$5,K2,Submissions!$E$2:$E$5,"USD")

The COUNTIFS function counts rows meeting all specified criteria. At this summary grain, the expected count is one. A count of zero means the approved key is absent; a count above one requires a duplicate or grain investigation even if the dollar total happens to agree.

Combine the checks in P2 and fill M2:P2 down through row 5:

=IF(AND(O2=1,N2=0),"PASS","REVIEW")

Do a reverse check as well. Every imported key must match an approved register entry or appear on an exception list. A register-to-import check catches missing submissions but can overlook an extra, unauthorized department left outside the report.

Repair the source difference and prove the company total

North Operations imported at $265,000 against the approved $270,000. The owner confirms that an older export was used and supplies the accepted file. Replace that submission under the agreed version control process, preserving the review record.

The repair adds $5,000 to the imported total: $835,000 plus $5,000 equals $840,000. North now totals $180,000 plus $270,000, or $450,000. South totals $150,000 plus $240,000, or $390,000. The entity totals therefore prove the same $840,000 company amount.

Do not insert a balancing entry into the largest department to make the total agree. A reconciliation difference is evidence to investigate. A legitimate management adjustment needs its own labeled row, owner, rationale and approval, with the pre-adjustment total still visible.

Once the imported company total and every department agree, release the report with its accepted version list. Future changes should create an identifiable revision. Use FP&A goals to track whether reconciliation exceptions are being resolved, and FP&A modeling for the separate work of constructing plan assumptions.

Where planning rollups go wrong

• Summing every received file. An original and a revision can both look valid. Select approved versions before adding amounts.
• Checking only the company total. Department overstatements and omissions can cancel. Require agreement by expected submission key.
• Treating missing as zero. A missing plan is an unresolved submission, even when a formula returns zero dollars.
• Counting both costs and their allocations. Decide how shared costs are represented in the management view and reconcile that treatment explicitly.
• Changing the grain silently. A department summary and account detail for the same plan must not both contribute to the rollup.

Covirage is not affiliated with Microsoft.

Reconcile your own planning submissions in Covirage

Bring your approved-submission register and combined department or entity planning file to Covirage. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See the supporting FP&A reporting page.