Store-Level P&L Modelling

This use case explains how to build a store-level P&L for a retail business using Model Reef. It covers both single site and multi site structures.

You will use branches for stores, driver based revenue and COGS, and store specific Opex and staff variables. The result is a consolidated view plus clean store level performance.

When to use this pattern

Use this pattern when:

• You operate multiple stores and want P and L per location as well as a consolidated view.

• You want to understand which stores drive growth and which underperform.

• You need to evaluate store rollouts, closures or refits using a consistent structure.

For a group that owns multiple entities, combine this with the Multi Entity Group Model pattern.

Architecture overview

You will build three layers:

1. Branch structure

   • One branch per store or site.

   • Optional central or head office branch for shared costs.

2. Store variables

   • Revenue, COGS, store Opex and staff costs in each store branch.

   • Shared drivers from the Data Library where appropriate.

3. Consolidated outputs

   • P and L at store and group level.

   • Cashflow, Cash Waterfall and Balance Sheet at group level.

   • Store comparison dashboards.

Branches roll up automatically so the group view aggregates all stores.

1

Design the branch structure

Create a model with branches similar to:

• Group (root branch)

  • Store 01

  • Store 02

  • Store 03

  • Head Office (optional)

Guidelines:

• Use simple, human readable store names or codes.

• Align with how management already thinks about the store network.

• Keep branch names stable to avoid breaking mental maps and reporting.

If you only have one store today, still use a store branch under Group so future expansion is easy.

2

Define store-level revenue drivers

Retail revenue is usually driven by a small set of operational drivers, for example:

• Foot traffic or transactions.

• Average basket size or average transaction value.

• Mix of product categories or price points.

In the Data Library, create drivers such as:

• Driver - Store 01 - Transactions

• Driver - Store 01 - Average Basket

• Driver - Store 02 - Transactions

For each store branch, create Revenue variables like:

• Revenue - Store 01 - Core Sales

• Revenue - Store 01 - Other Income

Link them to drivers via formulas, for example:

• Revenue = Transactions × Average Basket

This lets you forecast revenue by adjusting operational inputs, not just top line percentages.

3

Model COGS by store

For each store, create COGS variables such as:

• COGS - Store 01 - Merchandise

• COGS - Store 01 - Freight and Handling

Driver options:

• Use a percentage of revenue for simplified modelling, for example:

  • COGS = Revenue × COGS Percentage per store.

• Or use separate unit cost drivers if you track volumes by category.

Make sure COGS variables are of type COGS so that gross margin, working capital behaviour and cash timing follow the standard rules.

For more detailed stock timing, see Inventory and Replenishment Planning.

4

Capture store Opex and local costs

Store level Opex usually includes:

• Rent and occupancy costs.

• Utilities.

• Local marketing.

• Repairs and maintenance.

• Store supplies and consumables.

In each store branch, create Opex variables, for example:

• Opex - Store 01 - Rent

• Opex - Store 01 - Utilities

• Opex - Store 01 - Local Marketing

Use timing settings for:

• Monthly or quarterly rent cycles.

• Utility delays if bills are paid a period after consumption.

Place head office level costs, such as central marketing or accounting, in the Head Office or Group branch, not in individual stores.

5

Add store workforce and roster costs

Store staffing is usually one of the largest controllable costs. In each store branch, create Staff variables for:

• Store managers.

• Full time staff.

• Casual or part time staff.

Use drivers such as:

• Number of full time equivalents per store.

• Average hourly rate and expected hours.

• Roster patterns from Retail Workforce and Roster Forecasting.

This lets you analyse store profitability both before and after staff costs.

6

Integrate central costs and allocations

Central costs such as:

• Head office salaries.

• Central marketing.

• Shared logistics or warehouse costs.

belong in a head office or group branch.

If you need to show store level results after central allocations, you can:

• Decide on an allocation key, for example revenue share or floor space.

• Create Data Library drivers for allocation keys.

• Define Opex variables in each store branch like Allocated HQ Opex - Store 01 that use a share of central cost.

For guidance, see Build Cross Branch Drivers and Dependencies.

7

Build store-level and consolidated P&L views

Use reports and dashboards to provide:

• P&L per store showing:

  • Revenue.

  • COGS and gross margin.

  • Store level Opex and staff.

  • Contribution margin before or after central allocations.

• Consolidated P&L at Group level.

• Store comparison views, for example:

  • Revenue per store.

  • Gross profit per store.

  • Contribution margin per store.

This structure makes it easy to see which stores are performing and which are dragging the group down.

Check your work

• Each store branch contains only store specific variables.

• Central costs are clearly separated from store costs.

• Store P&Ls roll up to the consolidated P&L without gaps or double counting.

• Drivers for revenue and staffing reflect the operational reality of each location.

Troubleshooting

Store results do not reconcile to accounting reports

Check that imports or manual entries for revenue and COGS per store align with the way the ledger is coded. You may need additional mapping if accounts are not split by store in the source system.

Allocations obscure true store performance

Provide both pre allocation and fully loaded views in your dashboards so stakeholders can see both raw and allocated profitability.

Branch structure becomes unwieldy as stores grow

Use consistent naming and consider grouping stores under regional branches if the network is large.

Related guides

• Build a Debt Schedule & Covenants Model

• Build a Dilution / Cap Table Impact Model

• Branch Level Permissions

• Data Library Viewer