How to combine franchise P&L and department budget files into one rollup

Why P&Ls don't stack neatly

Franchisees and departments each build their own spreadsheet. One calls it “Wages”, another “Staff costs”, a third splits it into “Kitchen staff” and “Front of house”. Some put months across the columns, others list them down. A Consolidate by label or a simple copy-paste can't add these together, because the labels and layouts don't match.

Step 1: get every file into a long format

Turn each P&L into rows of Entity, Local line, Period, Amount. If months run across the columns, Power Query's Unpivot Other Columns (select the Line column, then Transform → Unpivot Other Columns) turns them into Period and Amount rows. Remove subtotal and total rows such as “Gross profit” and “Net profit” at this point; they'll be recalculated, and leaving them in double counts everything.

Use one sign convention: either costs are negative everywhere or a Type column says whether a line is income or cost. Mixed conventions are the most common reason a rollup is wildly wrong.

Step 2: build the mapping table

The mapping table lists every Entity + Local line pair and the shared reporting line it belongs to. It's small, it's maintained by finance, and it's what makes the rollup repeatable:

Map on Entity + Local line, not Local line alone, because the same label can mean different things in different files (one franchise's “Other” might be income, another's a cost). That's a two-column lookup, as covered in joining on two columns at once.

=XLOOKUP(A2&"|"&B2, Map!A2:A200&"|"&Map!B2:B200, Map!C2:C200, "UNMAPPED")

Filter for “UNMAPPED” after every load. A new local line appearing next month should stop the rollup until someone maps it, not vanish silently.

Step 3: roll up and check

Each entity's contribution must equal the sum of its local lines: Leeds 82,000 − 27,500 − 9,000 = 45,500. If a franchise's own P&L shows a different bottom line, either a line is unmapped, a subtotal slipped in, or signs differ. A pivot with Reporting line in rows and Entity in columns produces this table directly from the long data.

SELECT m.reporting_line, p.entity, SUM(p.amount) AS amount
FROM pl_lines p
LEFT JOIN mapping m
  ON m.entity = p.entity AND m.local_line = p.local_line
GROUP BY ALL
ORDER BY m.reporting_line NULLS FIRST, p.entity;

NULLS FIRST puts any unmapped lines at the top where you'll see them. Analistable can run this when you load the P&L files and the mapping sheet and ask for “the group P&L by reporting line and franchise”; it reads the files in your browser and doesn't alter them.

Department budgets: the same pattern, plus versions

Department budgets combine the same way: unpivot months, map each department's cost lines to group budget lines, then sum. Two extra columns help: Version (Budget, Reforecast 1…) and Owner. Keep every version in the long table and filter rather than overwriting, so you can compare. Once budgets and actuals share the same reporting lines, a variance report is a simple join; see comparing budget vs actuals.

• Different fiscal calendars: a franchise on a 4-4-5 calendar won't line up with calendar months. Map periods as well as accounts.
• Intercompany charges: a management fee paid by a franchise to the group is income for one and cost for the other. Tag and eliminate it in the group view.
• Different currencies: convert before adding, with a stated rate.

More options, including the built-in Consolidate command, are in the consolidate data in Excel guide.

Keeping the mapping maintainable

The mapping table is where most of the long-term effort goes, so treat it as a controlled list. Keep it in one place, give each row an owner and a date added, and never map directly in the rollup formulas. When a franchisee renames a line, add a new mapping row rather than editing the old one, so last year's files still roll up.

A useful monthly report is a list of mapped amounts by entity and reporting line next to each entity's own bottom line, with the difference. When that column is zero for every entity, the rollup is complete. When it isn't, the UNMAPPED filter usually shows why within a minute.

Frequently asked questions

How often should the mapping table change?

Only when an entity adds or renames a line. Add new rows rather than editing old ones so historical files still map correctly.

How do I consolidate P&Ls with different account names?

Create a mapping table from each entity's local account lines to a shared set of reporting lines, look the reporting line up for every row, then sum by reporting line.

Can Excel's Consolidate command handle different line names?

Only if the labels match exactly. It adds values by matching row and column labels, so mismatched names stay separate rows.

Should I include subtotal rows when combining P&Ls?

No. Remove subtotals and totals before combining and recalculate them, otherwise amounts are counted twice.

How do I combine department budgets into one spreadsheet?

Unpivot each budget to Department, Line, Month, Amount, map lines to group budget lines, stack them, then pivot by line and month.

Related guides

• How to build a consolidation worksheet template in Excel
• How to compare budget vs actuals in Excel
• How to consolidate data in Excel
• How to combine monthly bank statements into one spreadsheet
• How to combine monthly expense claims into one spreadsheet