Multi-location reporting usually fails the same way. Each site has a workbook that is almost right. Someone consolidates them into a master that is wrong by Tuesday. Leadership debates whose numbers to trust instead of what to do. The spreadsheet is not the villain by itself. The villain is no shared definitions, no timed refresh, and no owner for the rollup ritual.
Why the chase feels inevitable
Local managers optimize for speed. Central teams optimize for comparability. Without a designed middle, you get heroic Fridays: copy, paste, VLOOKUP, hope. Mid-market retailers, multi-site services, and facility groups know this pain well. SMB owners with a second location discover it suddenly. New multi-site operators can avoid it if they set definitions before location three.
Shared definitions first
Write what revenue includes. What labor hours count. How transfers between locations are treated. When a day closes. Publish those definitions where the report lives. Name an owner. If locations can invent local meanings, your rollup is fiction with charts.
Architecture that fits the org
Often the right pattern is operational systems at the edge, a curated warehouse or curated model in the middle, and a small executive pack on top. Jumping straight to thirty Power BI pages without cleaning location feeds just moves the mess into a prettier tool. Start with the weekly pack leadership already argues about. Replace that pack first.
Ritual and change management
Set a submit-by time only if you still need local inputs. Better: remove local inputs by integrating source systems. Hold a weekly rollup review with location leads where the pack is the only packet. Retire email workbooks on a date. Offer break-glass extracts with a log. People keep shadow books when they lose a capability; move the capability, then close the door.
Failure modes
- Definitions differ by site and nobody notices until planning season.
- Refresh timing makes Monday meetings use Sunday's partial truth.
- Local KPIs that cannot roll up without double counting.
- Central team blamed for ops issues they cannot see at grain.
- New location onboarded without the reporting checklist.
Onboarding checklist for a new location
Systems access, chart of accounts mapping, labor feed, sales feed, definition acknowledgment, training on the weekly pack, and a named local owner. Put it in the open playbook. Locations opened without that checklist become permanent exceptions.
What to do this week
Collect every workbook used in last week's leadership meeting. Circle fields that disagree. Those fields are your definition backlog. Pick one weekly pack to replace in four weeks. Assign definition owners. Schedule the retirement date for the email chase before you pick chart colors.
Replace the master workbook in layers
Layer one: definitions and owners. Layer two: automated extracts from each location's systems of record into a curated store. Layer three: a small certified pack for the weekly meeting. Layer four: controlled self-serve for trained managers. Teams that jump to layer four first recreate spreadsheet chaos inside BI tools. Earn each layer.
For labor and sales, prefer source feeds over typed submissions. Typed submissions drift under Friday pressure. If a location cannot provide a feed yet, time-box the manual path and put feed work on the integration backlog with a date.
Handling local exceptions without breaking the rollup
Some local metrics will never roll up cleanly. Isolate them in a local appendix instead of bending the enterprise definition. Document why. Review quarterly whether the exception still deserves oxygen. Endless exceptions are how rollups rot.
Change management across managers
Run a short academy for location leads: how to read the pack, how to dispute a number, how to request a definition change. Disputes without a path become shadow books. A path turns disputes into governance. Celebrate the first week the email chase does not happen. Make the absence of chaos visible so people know the new system won.
What to do this week
Pick the worst field conflict from last week's master workbook. Resolve the definition in writing with two location leads and finance. Wire that field from source systems for one metric end to end as a pilot. Use that win to fund the rest of the pack replacement. Big-bang consolidations sound brave and usually slip.
Executive pack blueprint
One page for company rollup: revenue, margin or contribution, labor, cash or AR risk as relevant. One page per location with the same definitions. One exceptions page: feeds late, definitions under dispute, actions open. That is enough for most weekly meetings. Extra pages must earn a decision slot or they are decoration.
Feed contract for each location
Document the sales feed, labor feed, and any inventory or pipeline feed: source system, timing, owner, and failure alert. Put the contract in the same place as definitions. New locations sign it during onboarding. When a feed breaks, you want a named human and a known SLA, not a scavenger hunt across personal inboxes.
Want more of this in your inbox? Subscribe to our newsletter.
If every location emails a workbook by Monday noon, we can replace that chase with a rollup people trust.
Book a Call