Core modellingCore · ~40 minWorking draft · 11 sheets · 885 formulas

Multi-store retail model

Store estate, sales density, online, markdowns, IFRS 16. Build a retailer from its stores: roll the estate forward, trade each cohort of new stores up to the mature sales density, add online sales net of returns, take markdowns off the gross margin, work store four-wall EBITDA and central costs to EBITDA on both IFRS 16 bases, and carry it through stock days, capital spending, the payback on a new store, free cash flow and a valuation.

By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.

On this subject: multi-store retail model.Updated 5 November 2026

Who builds it, and for whatThe model an equity research analyst covering a clothing or general merchandise retailer builds, and the one a consumer team at a bank or a private equity fund rebuilds before advising on or buying one. It turns the questions every retail results call is about into numbers: how many stores open and close, how fast new space matures, what like-for-like sales did, how much online demand is sent back, how deep the markdowns ran and how much stock is left. Lenders and rating agencies read the same model for the leases, which under IFRS 16 sit on the balance sheet as debt-like liabilities.

Every sheet below, in your browser, with its outputs and checks

Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads

Inspect the workbookMulti-store retail model · Working draft · 11 sheets · 885 formulas

All checks read zero100%

Every value, every sheet, as the workbook holds it.

	A	B	C	D	E	F	G	H	I	J
1	Assumptions
2	£m unless stated. Space in thousands of square feet; sales density, rent and rates in £ per square foot.
4	Year	Unit		FY0	FY1	FY2	FY3	FY4	FY5	Notes
5	Year number	#		0	1	2	3	4	5	FY0 = 0. Drives growth and inflation.
7	Scenario
8	Scenario	#	1	1 = base, 2 = downturn, 3 = upside. Sets like-for-like growth, the markdown rate, online growth and stock days from FY1.
10	Store estate
11	Mature stores, end of FY0	stores	440	Stores in at least their fourth year of trading.
12	Stores opened in FY-2	stores	10	In their third year in FY0; mature from FY1.
13	Stores opened in FY-1	stores	12
14	Store openings	stores		12	14	16	16	15	14	Opened evenly through the year.
15	Store closures	stores		8	8	7	6	6	5	Mature stores closed at the end of their lease, evenly through the year.
16	Selling space per store	'000 sq ft	22.00	The same for new and mature stores.
18	Sales density and like-for-like growth
19	Sales per sq ft, mature stores, FY0	£ per sq ft	330
20	New store sales density, first year, % of mature	%	65.0%
21	New store sales density, second year, % of mature	%	80.0%
22	New store sales density, third year, % of mature	%	90.0%	Mature, at 100%, from the fourth year.
23	Like-for-like sales growth, base	%	3.0%	Annual growth in sales per sq ft of the mature estate, from FY1.
24	Like-for-like sales growth, downturn	%	-1.5%
25	Like-for-like sales growth, upside	%	5.0%
26	Like-for-like sales growth, selected scenario	%	3.0%
28	Online
29	Online demand, FY0	£m	2,600.0	Orders at selling price before returns.
30	Online demand growth, base	%	6.0%
31	Online demand growth, downturn	%	2.0%
32	Online demand growth, upside	%	9.0%
33	Online demand growth, selected scenario	%	6.0%
34	Returns, % of online demand	%	32.0%	Clothing bought online is returned far more often than in a store.
35	Fulfilment, delivery and online marketing, % of net online sales	%	20.0%
36	Cost of handling returns, % of value returned	%	10.0%	Collection, inspection and putting the item back into stock.
38	Intake margin and markdowns
39	Intake margin	%	58.0%	The margin the buying team plans at full price: 1 - cost / full-price value.
40	Markdowns, % of full-price value, FY0	%	7.0%
41	Markdowns, % of full-price value, base	%	7.0%	From FY1. Value given away in sales and clearance.
42	Markdowns, % of full-price value, downturn	%	10.0%
43	Markdowns, % of full-price value, upside	%	6.0%
44	Markdowns, % of full-price value, selected scenario	%	7.0%
46	Store and central costs
47	Staff cost per store, FY0	£m a year	0.95
48	Store wage inflation, annual	%	4.0%
49	Rent per sq ft, FY0	£ per sq ft	28.00
50	Rent growth, annual	%	2.5%	Upward-only reviews and indexation, applied to the whole estate and to new leases.
51	Business rates and service charges per sq ft, FY0	£ per sq ft	14.00
52	Business rates and service charge growth, annual	%	2.5%
53	Store running costs, % of store sales	%	3.0%	Card fees, utilities, consumables and repairs.
54	Central costs, FY0	£m	500.0	Head office, design and buying, IT and distribution. Fixed.
55	Central cost growth, annual	%	3.0%
57	Leases
58	Lease term	years	10	Every store lease, new or renewed. Rent paid annually.
59	Incremental borrowing rate	%	6.0%	Discounts the rent to the lease liability.
61	Capital spending and depreciation
62	Fit-out cost per new store	£m per store	3.20
63	Maintenance capital spending per trading store	£m a year	0.18	Refits and equipment. Fixed in money terms.
64	Online and central capital spending, % of net online sales	%	3.0%	Warehouse automation, the website and systems.
65	Net PP&E, FY0	£m	1,100.0
66	Depreciation, % of opening net PP&E	%	12.0%
68	Working capital, tax and cash
69	Stock days, on cost of goods sold, FY0	days	95
70	Stock days, base	days	95	From FY1.
71	Stock days, downturn	days	110	Stock bought for sales that did not come.
72	Stock days, upside	days	90
73	Stock days, selected scenario	days	95
74	Receivable days, on sales	days	5	Card settlements and trade accounts.
75	Payable days, on cost of goods sold	days	60
76	Days in the year	days	365
77	Weeks in the year	weeks	52
78	Tax rate	%	25.0%
79	Cash, end of FY0	£m	400.0	No borrowings. Interest is earned on the opening balance.
80	Interest earned on cash	%	3.0%
81	Dividends, % of net income	%	50.0%	Paid in the year earned.
83	Valuation
84	Discount rate (WACC)	%	9.0%	Valued at the end of FY0.
85	Terminal growth	%	2.0%
86	Shares in issue	m shares	120.00

Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.

Download

Multi-store retail model: the workbook

An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.

Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
Free accountWork with every model online: change the inputs, switch the case, see every formula. And every starter workbook, with its key schedules left blank to build.
L3VLUP Plus$4.99/monthEverything in Free, and one worked model each billing month as an Excel file: every schedule built, formulas live, checks at zero, yours to keep.
L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.

The worked model: 11 sheets and 885 live formulas, all 9 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves.
The starter: The same inputs and layout with the store estate roll-forward and cohorts left blank for you to build, each blank cell marked. The worked model is its answer.

Checking your access Sign in for the starter workbook

What the base case says

EBIT margin, FY5: 17.5%
Value per share (£): 80.24
Sales, FY5: $6,620.0m
Stores, end of FY5: 517
Online share of sales, FY5: 35.7%
Gross margin, FY5: 54.8%
Four-wall EBITDA margin, FY5: 25.3%
EBITDA before IFRS 16, FY5: $1,210.4m
EBITDA under IFRS 16, FY5: $1,567.6m
New-store payback (years): 3.11
Cash conversion cycle, FY5 (days): 40
Free cash flow after lease payments, FY5: $747.6m
Lease liability, FY5: $1,449.0m
Enterprise value: $9,228.7m

Read from the workbook as served, every input at its default. Periods: FY0, FY1, FY2, FY3, FY4, FY5. The figures are invented and move with whatever you type in.

What this model is

A five-year model (FY1 to FY5, from an FY0 base year) of an invented retailer of clothing and homeware with a chain of stores and a website.

Store sales are built from the estate: stores opened and closed each year, average trading stores on the half-year convention, and sales per square foot by the age of the store. A new store reaches the mature sales density over three years, and like-for-like growth is measured on the mature estate alone.

Online is its own channel: demand less returns is net online sales, with fulfilment, delivery and the cost of handling returns against it. Both channels share one range, bought at an intake margin and sold with markdowns.

Store four-wall EBITDA, online contribution and central costs give EBITDA, shown before IFRS 16 (rent as a cost) and under it (rent replaced by depreciation of the right-of-use asset and interest on the lease liability). Stock days, payable days and the cash conversion cycle give working capital; capital spending per new store gives the payback on a new store.

Three scenarios (base, downturn and upside) and two board-style sensitivity tables: FY5 EBIT margin by like-for-like growth and markdown rate, and value per share by discount rate and terminal growth.

Seats: Equity research and hedge funds, Investment banking, Private equity.

Sheets in the workbook

Cover. How to read it, the conventions, the sheet map.
Assumptions. Scenario, the store estate, sales density and its ramp, like-for-like growth, online demand and returns, intake margin and markdowns, store and central costs, leases, capital spending, working capital, tax and the valuation.
Stores. Stores at the start and end of each year, openings and closures, average trading stores, selling space, and the estate by age: stores in their first, second and third years and the mature base.
Sales. Sales per square foot by cohort, like-for-like growth, store sales by cohort, online demand, returns and net online sales, group sales, markdowns and gross margin.
Store Economics. Store four-wall EBITDA, online contribution, central costs, EBITDA before and under IFRS 16, and the payback on a new store.
Leases. Leases signed on new stores and renewals, the lease liability and the right-of-use asset, and the IFRS 16 lease cost against the rent paid.
Profit. EBIT, interest and tax under IFRS 16 as reported, and the same year before IFRS 16 with the difference between them.
Cash Flow. PP&E, stock, receivables and payables with the cash conversion cycle, free cash flow on both bases and the cash balance.
Valuation. A discounted cash flow on unlevered free cash flow after rent, the value per share, and the implied multiples on both EBITDA bases.
Sensitivity. FY5 EBIT margin by like-for-like growth and markdown rate; value per share by discount rate and terminal growth, with the workings beneath.
Checks. Store roll-forward, the estate by age, sales by cohort, gross margin, EBITDA on both bases, PP&E, the lease liability and right-of-use asset, free cash flow on both bases, cash and the grid centres. Each should read zero.

How the schedules connect

Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.

Store estate roll-forward and cohorts

Openings, closures, average trading stores, store age

Assumptions, rows 11–16 · Stores, rows 6–11 · Stores, rows 14–21

Retail sales: density, like-for-like and online

Sales per sq ft by cohort, LFL growth, online returns, markdowns

Assumptions, rows 19–26 · Assumptions, rows 29–36 · Assumptions, rows 39–44 · Sales, rows 6–10 · Sales, rows 13–19 · Sales, rows 22–25 · Sales, rows 28–30 · Sales, rows 33–43

Four-wall EBITDA and new-store payback

Store profit, online contribution, central costs, payback

Assumptions, rows 47–55 · Store Economics, rows 6–16 · Store Economics, rows 19–23 · Store Economics, rows 26–32 · Store Economics, rows 35–48

Store leases under IFRS 16

Right-of-use assets, lease liabilities, EBITDA on both bases

Assumptions, rows 58–59 · Leases, rows 6–9 · Leases, rows 12–16 · Leases, rows 19–24 · Leases, rows 27–30 · Leases, rows 33–36 · Profit, rows 6–16 · Profit, rows 19–24

Capex and depreciation (PP&E roll-forward)

Opening, plus capex, less depreciation, closing

Assumptions, rows 62–66 · Cash Flow, rows 6–11

Working capital schedule

Receivables, inventory and payables in days

Assumptions, rows 69–81 · Cash Flow, rows 14–24

Cash flow statement

Indirect method, closing on balance-sheet cash

Cash Flow, rows 27–40

Discounting and terminal value

Discount factors, two terminal methods, reconciled

Assumptions, rows 84–86 · Cash Flow, row 43 · Valuation, rows 5–13 · Valuation, rows 16–20

Sensitivity tables

Two assumptions at once, without a data table

Sensitivity, rows 5–8 · Sensitivity, rows 11–15 · Sensitivity, rows 18–23 · Sensitivity, rows 26–33

What you should be able to explain

How the store estate rolls forward, and why average trading stores, not stores at the year end, drive a year’s sales when openings and closures fall through the year.
Why a new store trades below the mature sales density for its first years, and why like-for-like growth is measured on the mature estate alone.
Why online is modelled as its own channel: demand less returns, with fulfilment, delivery and the cost of handling each return against it.
How markdowns cut the selling price but not the cost, so that gross margin is 1 - (1 - intake margin) / (1 - markdown rate).
What four-wall EBITDA measures, and why central costs sit outside it.
Why IFRS 16 moves rent out of EBITDA into depreciation and interest, why the free cash flow is unchanged, and which enterprise value goes with which EBITDA.

What a reviewer looks for

Store sales on the year-end store count, so a store opened in December earns a full year.
New stores at the mature sales density from the day they open, so growth from new space is overstated and like-for-like growth is muddled with it.
Online sales at demand rather than net of returns, with the cost of handling returns left out.
Markdowns taken as a share of sales off a fixed gross margin, so deeper discounting never compresses the margin as it should.
An IFRS 16 EBITDA multiple applied to an enterprise value that leaves out the lease liability, or a pre-IFRS 16 multiple with the leases deducted, which counts the rent twice.

Conventions this workbook uses

Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.

Annual periods. FY0 is the base year, built from the same drivers as the forecast. Scenarios change like-for-like growth, the markdown rate and online growth from FY1 only.
Openings and closures fall evenly through the year: average trading stores are the average of the stores at the start and the end, and a store in its first year trades for half of it.
A new store trades at 65%, 80% and 90% of the mature sales density in its first three years and joins the mature estate at the start of its fourth. Like-for-like growth is the growth in sales per square foot of the mature estate; closures come from the mature estate.
Markdowns are a share of the full-price value of what is sold. Cost of goods sold is the full-price value x (1 - intake margin), so gross margin = 1 - (1 - intake margin) / (1 - markdown rate).
Four-wall EBITDA is a store’s gross profit less its staff, rent, business rates and service charges and running costs. Central costs are carried by neither channel.
IFRS 16: every store lease runs for the lease term with rent paid at the end of each year, discounted at the incremental borrowing rate. New stores and renewals add leases at present value; closures fall at lease end. The FY0 balances are those of a book of evenly spread leases, and the right-of-use asset is depreciated in a straight line, which for such a book is 2 / (term + 1) of the opening balance.
EBITDA is shown on both bases: before IFRS 16 rent is an operating cost; under it rent leaves EBITDA and returns as depreciation and interest. Tax is charged on profit before tax under IFRS 16, never below zero. Free cash flow after lease payments is the same on both bases.
Stock and payables are in days of cost of goods sold, receivables in days of sales. Dividends are a share of net income, paid in the year; there are no borrowings. Maintenance capital spending is per trading store and online capital spending a share of net online sales. Depreciation is a rate on opening net PP&E.
The valuation discounts unlevered free cash flow after rent, before IFRS 16, at the end of FY0, with a terminal value on FY5. Because rent is already deducted, the lease liability is not; the implied multiple under IFRS 16 adds the leases to enterprise value.
The sensitivity tables recompute FY5 from the model’s own lines; the centre of each equals the model.

What this model does not cover

Where its answer stops, so you know what to add before relying on it.

Individual stores, regions, formats and product categories. The estate is one store size and one range.
Seasonality, the trading weeks of each half and the weeks of cover through the year; the model is annual.
Lease events other than expiry: surrenders, break clauses, onerous leases, turnover rents and lease incentives.
Store impairments, pre-opening costs, and the cannibalisation of nearby stores and online sales by a new store.
Currency, sourcing costs and freight by country, and the hedging of a retailer that buys in dollars and sells in pounds.
Customer credit, which some clothing retailers run as a separate finance business.

Build it yourself

The starter workbook

The Stores sheet has been cleared: the store roll-forward, average trading stores, selling space and the estate by age. Rebuild it from the Assumptions sheet, then watch store sales, four-wall EBITDA, the leases and the valuation calculate again. The Checks sheet shows when the roll-forward and the estate by age tie.

Blanks: Store estate roll-forward and cohorts. Download it beside the worked model.

The path around this model

Understand it, drill it, read the build, then apply it to a real company.

Build · Lab · ~12 min

Unit Economics

Build an LTV:CAC you could defend under questioning, and answer payback as its own question.

Build · Lab · ~8 min

Three-Statement Linker

Walk any event through all three statements in the right order, with the right signs, and prove the balance sheet balances rather than asserting it.

Read · Guide · 7 min

Equity Research Interview Questions: The Coverage Mindset

Read · Guide · 15 min

How to Build a Three-Statement Model That Actually Balances

Read · Guide · 10 min

Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested

Apply · Skill

Company Profile

Build a banker-grade one-pager on any company, fast.

Apply · Skill

Model Audit

Find the errors in a financial model before someone senior does.

Vocabulary: Like-for-Like Sales, Four-Wall EBITDA, Cash Conversion Cycle, Gross Margin, Operating Lease, EBITDA, Free Cash Flow (FCF).

Questions about this model

How are store sales built?

From the estate and the sales density of each cohort. The retailer ends FY0 with 474 stores of 22,000 sq ft: 440 mature and 34 opened in the last three years. A mature store sells £330 per sq ft, or £7.26m a year; a new store trades at 65%, 80% and 90% of that in its first three years, and a store in its first year trades for half of it. That gives FY0 store sales of £3,387m from 472 average trading stores. Like-for-like growth of 3% a year lifts the mature density, and with net new space store sales reach £4,254m by FY5.

What do markdowns do to the margin?

Goods are bought to sell at a 58% intake margin. If 7% of their full-price value is given away in markdowns, the selling price falls but the cost does not, so gross margin is 1 - 42% / 93% = 54.8%. Each point of markdown costs about half a point of EBIT margin: at 9% rather than 7% the FY5 EBIT margin falls from 17.5% to 16.5%. The downturn case, with like-for-like sales falling 1.5% a year and markdowns at 10%, takes it to 9.6%.

Why does the model show EBITDA twice?

Because IFRS 16 changed what EBITDA means for a retailer. Before it, store rent was an operating cost inside EBITDA, which was £904m in FY0. Under it, each lease is a right-of-use asset and a lease liability, the rent leaves EBITDA and comes back below it as depreciation and interest, and EBITDA is £1,195m. The cash is the same either way. An enterprise value / EBITDA multiple must match its basis: £9,229m of enterprise value is 9.7x FY1 EBITDA before IFRS 16, and 8.4x FY1 EBITDA under it once the £1,279m lease liability is added.

How long does a new store take to pay back?

At FY1 prices a new store costs £3.2m to fit out and ties up £0.3m in stock net of payables. Its four-wall EBITDA, after its own staff, rent and rates, is £0.58m in its first full year, £1.17m in its second, £1.55m in its third and £1.94m once mature, so it pays back in 3.1 years and earns a 55% pre-tax return on the investment when mature.

Related models

Core modelling · Foundation

Integrated three-statement model

The operating model everything else sits on

Core modelling · Core

Discounted cash flow model

Unlevered free cash flow to a share price

Core modelling · Core

FP&A budget and variance model

Driver-based budget, rolling forecast, price-volume-mix

Questions about this model

Discussions open here soon.

All discussions

Share your result 📊

𝕏 in 💬🤖

Instagram and TikTok have no desktop share link, so copy the caption and paste it into the app.

Provided for information purposes only, not financial advice. The model, its inputs and the outputs quoted on this page are illustrative, produced from assumptions you control, and must not be relied on for any investment, financial or business decision. Read the full notice

Not advice, and not a recommendation. Everything L3VLUP publishes is provided for information purposes only. Nothing here is financial, investment, legal, tax or accounting advice, a personal recommendation, or an offer or solicitation to buy or sell any security or financial instrument. It takes no account of anyone’s objectives, financial situation or needs.

Not a regulated adviser. L3VLUP does not provide regulated financial services. Speak to a suitably qualified and regulated professional before acting on anything you read here.

Illustrative figures. Default values are plausible starting points chosen so a model has somewhere to begin. They are not data, not a view on any company, and not calibrated to anything. Outputs move with whatever you type in.

No warranty, and limited liability. This page is provided “as is” without warranty of any kind. To the fullest extent permitted by law, L3VLUP accepts no liability for any loss or damage arising from use of, or reliance on, anything on it. Nothing in this notice limits any liability that cannot lawfully be limited.

Full terms: Information Purposes and No Advice · Terms