TechnologyAdvanced · ~45 minWorking draft · 13 sheets · 1123 formulas

Telecom operator model: mobile and fibre

Subscribers, churn, ARPU, fibre build, EBITDAaL, spectrum. Build an integrated telecom operator from its customers and its networks: postpaid and prepaid subscribers rolled forward by gross additions and churn, ARPU by segment, handset revenue kept apart from service revenue, a fibre network from homes passed to connections as penetration ramps, costs to EBITDA and then to EBITDAaL after leases, capital spending split between maintenance and the fibre build, a spectrum auction amortised over its licence, then leverage, equity free cash flow, dividend cover and a DCF checked against a peer multiple.

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

On this subject: telecom operator model: mobile and fibre.Updated 11 November 2026

Who builds it, and for whatThe model an equity research analyst covering the listed telecom operators keeps for each of them, and that a credit analyst or a leveraged finance team rebuilds before a bond issue or an infrastructure fund’s bid for a fibre network. The analyst uses it to say what a point of churn or a price cut does to EBITDAaL and whether the dividend is covered once the fibre build and the next spectrum auction are paid for; the lender reads the same lines for leverage on a lease-consistent basis and for how long the company can fund a dividend its cash flow does not cover.

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 workbook
All checks read zero100%
Every value, every sheet, as the workbook holds it.
ABCDEFGHIJK
1Assumptions
2€m unless stated. Subscribers, homes and connections in thousands; ARPU in euros a month. All figures are illustrative.
4YearUnitFY0FY1FY2FY3FY4FY5FY6Notes
5Year number#0123456FY0 = 0. Drives price and cost growth and discounting.
7Scenario
8Scenario#11 = base, 2 = price war, 3 = accelerated fibre build. Selects a row of each case on the Scenarios sheet, from FY1.
10Mobile subscribers
11Postpaid subscribers, start of FY0'0008,850.0Contract customers billed monthly.
12Prepaid subscribers, start of FY0'0006,150.0Active SIMs topped up in advance.
13Postpaid gross additions, % of opening base%14.0%14.0%14.0%14.0%13.5%13.5%13.0%New contracts signed in the year, competitors’ customers and first-time buyers.
14Postpaid churn, % of opening base, base case%12.0%12.0%12.0%12.0%12.0%12.0%12.0%Annual: 1.0% a month. The scenario adds to it.
15Prepaid gross additions, % of opening base%33.0%33.0%32.0%32.0%31.0%31.0%30.0%
16Prepaid churn, % of opening base, base case%36.0%36.0%36.0%36.0%36.0%36.0%36.0%Annual: 3.0% a month. Prepaid SIMs lapse far faster than contracts end.
18ARPU and handsets
19Postpaid ARPU, FY0€ a month22.00Service revenue per average subscriber a month, handsets excluded.
20Postpaid ARPU growth, base case%1.5%1.5%1.5%1.5%1.5%1.5%Price rises and larger data plans. The scenario adds to it.
21Prepaid ARPU, FY0€ a month7.00
22Prepaid ARPU growth, base case%1.0%1.0%1.0%1.0%1.0%1.0%
23Postpaid gross additions taking a handset%60.0%The rest bring their own phone.
24Opening postpaid base upgrading its handset%18.0%Existing customers renewing with a new phone.
25Average handset price, FY0€450Net of any subsidy.
26Handset price growth%2.0%Annual, from FY1.
27Margin on handsets%5.0%Sold close to cost to win and keep the contract.
29Fibre network
30Homes passed, start of FY0'0005,000.0Homes the fibre network can serve without further street works.
31Fibre connections, start of FY0'0001,750.0
32Homes passed in the year, base case'000500.0600.0650.0650.0550.0400.0250.0The build plan. The scenario adds to it.
33Penetration of the homes passed before FY0%36.0%36.5%37.0%37.5%38.0%38.0%38.0%Connections / homes passed on the mature footprint, at year end.
34Penetration of homes passed, end of the year of the build%6.0%Customers sign up over years, not on the day the street is dug.
35Penetration 1 year after the build%14.0%
36Penetration 2 years after the build%21.0%
37Penetration 3 years after the build%27.0%
38Penetration 4 years after the build%31.0%
39Penetration 5 or more years after the build%33.0%
40Broadband ARPU, FY0€ a month40.00
41Broadband ARPU growth, base case%2.0%2.0%2.0%2.0%2.0%2.0%The scenario adds to it.
42Business and wholesale revenue, FY0€m650.0Fixed services to companies and capacity sold to other operators.
43Business and wholesale revenue growth%0.5%Annual, from FY1.
44Fibre build cost per home passed€600Civil works, ducts and fibre to the street.
45Fibre connection cost per new connection€350The drop to the home, installation and the router.
47Operating costs and leases
48Direct costs, % of service revenue%13.0%Interconnection, roaming and content paid to others.
49Mobile network costs, FY0€m520.0Energy, maintenance and backhaul on the mast sites. Fixed whatever the base does.
50Fixed network costs per home passed, FY0€ a year25On average homes passed.
51Acquisition cost per postpaid gross addition, FY0€120Dealer commission and marketing. Expensed as incurred.
52Acquisition cost per prepaid gross addition, FY0€15
53Acquisition cost per new fibre connection, FY0€90
54Customer care and billing per customer, FY0€ a month2.00On average mobile subscribers and fibre connections.
55Overhead, FY0€m560.0Staff, IT, property and head office.
56Cost inflation%2.5%Annual, from FY1, on network, acquisition, care and overhead costs.
57Lease costs, FY0€m330.0Depreciation of right-of-use assets and interest on lease liabilities: mast sites, shops and leased fibre.
58Lease cost growth%3.0%Annual, from FY1: indexation and new sites.
59Lease liabilities, multiple of the year’s lease costs, memox6.0xMemo item: for the lease-adjusted leverage only.
61Capital spending and depreciation
62Maintenance capital spending, % of service revenue%11.0%Radio equipment, the core network, IT and customer equipment replaced as it wears out.
63Useful life of maintenance spendingyears8
64Fibre, ducts and buildings, net book value FY0€m4,200.0
65Their remaining life at FY0years20
66Useful life of new fibreyears25
68Spectrum
69Spectrum auction payment€m-900.0----Paid in full in the year of the auction.
70Licence termyears20Amortised straight line from the year after the auction.
71Existing licences, net book value FY0€m1,800.0
72Their remaining term at FY0years12
74Working capital and tax
75Net working capital, % of revenue%4.0%Handset and service receivables and inventory less payables.
76Tax rate%25.0%On profit before tax; nothing in a loss year.
78Debt and dividends
79Bonds, FY0€m5,000.0Refinanced as they mature, so the balance is held.
80Share of the auction payment funded by a new bond%60.0%Issued in the auction year and held.
81Interest rate%4.5%On opening balances of the bonds and the revolver.
82Cash, FY0€m600.0
83Minimum cash€m400.0The revolver is drawn to hold it and repaid from any surplus.
84Dividend per share, FY0€ per share0.40
85Dividend per share growth%2.0%Annual, from FY1. Paid whatever the year’s cash flow.
86Shares outstandingm shares1,000.0
87Leverage policy ceiling, net debt / EBITDAaLx3.0xThe level the board and the rating agencies hold the company to.
89Valuation
90Discount rate (WACC)%7.5%
91Terminal growth rate%1.0%
92Peer multiple, EV / FY1 EBITDAaLx6.5xFor the cross-check: where listed operators trade.

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

Telecom operator model: mobile and fibre: the workbook

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

  1. Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
  2. 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.
  3. 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.
  4. L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
The worked model
13 sheets and 1,123 live formulas, all 11 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 mobile subscriber roll-forward: gross adds, churn, net adds left blank for you to build, each blank cell marked. The worked model is its answer.

What the base case says

Equity value per share (€)
8.29
Enterprise value
$12,689.1m
EBITDAaL, FY1
$1,777.6m
EBITDAaL margin, FY1
31.7%
EBITDA, FY1
$2,117.5m
Service revenue, FY1
$4,515.5m
Mobile subscribers, FY1 (thousand)
$14,994.1m
Fibre connections, FY6 (thousand)
$2,826.5m
Capex intensity, FY1
15.9%
Equity free cash flow, FY1
$475.5m
Dividend cover, FY1
1.2x
Dividend cover, lowest year
1.1x
Net debt / EBITDAaL, peak
2.8x
Enterprise value / FY1 EBITDAaL
7.1x

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

What this model is

A six-year model (FY1 to FY6, from an FY0 base year) of an invented integrated telecom operator: a mobile business of about 15 million postpaid and prepaid subscribers and a fibre network passing 5.5 million homes and still being built.

The mobile base rolls forward by segment: opening subscribers, gross additions, churn and net additions. Service revenue is average subscribers x ARPU x 12, and handsets are sold beside it at a thin margin, so equipment revenue is kept apart. Fibre revenue follows connections, which follow homes passed with a lag as each year’s build ramps to its mature penetration.

Costs are direct costs on service revenue, two networks that cost the same whoever is connected, acquisition costs per gross addition, customer care and overhead. EBITDA is shown before lease costs and EBITDAaL after them; leverage, free cash flow and the valuation all rest on EBITDAaL.

Capital spending is split between maintenance and the fibre build, a spectrum auction is paid in FY2 and amortised over its 20-year licence, and the dividend per share is paid whatever the year brings, so equity free cash flow and dividend cover are the lines to watch.

Three scenarios (base, a price war and an accelerated fibre build) and two board-style sensitivity tables: FY1 EBITDAaL by postpaid churn and ARPU growth, and equity value per share by discount rate and terminal growth. An integrated operator only: tower companies and cable and media groups are not modelled here.

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

Careers that do this work: growth equity, venture capital, equity research, corp dev / m&a, bizops / strategy & ops.

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.

Mobile subscriber roll-forward: gross adds, churn, net adds

Postpaid and prepaid bases, gross adds, churn, net adds

Assumptions, rows 11–16 · Subscribers, rows 6–13 · Subscribers, rows 16–23 · Subscribers, rows 26–30

Revenue build

Where the top line comes from

Assumptions, rows 19–27 · Revenue, rows 6–11 · Revenue, rows 14–17 · Revenue, rows 20–26

Fibre build: homes passed, penetration and connections

Homes passed, a penetration ramp, capex per home

Assumptions, rows 30–45 · Fibre, rows 6–9 · Fibre, rows 12–19 · Fibre, rows 22–26

Operating costs and margins

Cost of sales, opex, EBITDA

Assumptions, rows 48–59 · Costs & EBITDAaL, rows 6–16 · Costs & EBITDAaL, rows 19–27

Capex and depreciation (PP&E roll-forward)

Opening, plus capex, less depreciation, closing

Assumptions, rows 62–66 · Capex & Spectrum, rows 6–11 · Capex & Spectrum, rows 14–20 · Capex & Spectrum, row 33

Spectrum licences: auction payments and amortisation

An auction paid in one year, amortised over the licence

Assumptions, rows 69–72 · Capex & Spectrum, rows 23–30

Working capital schedule

Receivables, inventory and payables in days

Assumptions, rows 75–76 · P&L & Cash Flow, rows 18–27

Debt schedule

Tranches, interest, amortisation and the sweep

Assumptions, rows 79–87 · Debt & Dividends, rows 6–9 · Debt & Dividends, rows 12–19 · Debt & Dividends, rows 22–26 · Debt & Dividends, rows 29–31

Discounting and terminal value

Discount factors, two terminal methods, reconciled

Assumptions, rows 90–92 · Valuation, rows 6–9 · Valuation, rows 12–25 · Valuation, rows 28–32 · Valuation, rows 40–45

Enterprise value to equity value

Debt off, cash on, per share

Valuation, rows 35–37

Sensitivity tables

Two assumptions at once, without a data table

Sensitivity, rows 5–8 · Sensitivity, rows 11–18 · Sensitivity, rows 21–26 · Sensitivity, rows 30–37

What you should be able to explain

  • Why gross additions, churn and net additions are three numbers, and why an operator can spend heavily on acquisition and still not grow.
  • Why service revenue and equipment revenue are reported apart, and what a handset sold near cost does to a margin on total revenue.
  • How IFRS 16 moves lease costs out of EBITDA, what EBITDAaL puts back, and why net debt and earnings must be on the same lease basis before leverage means anything.
  • Why fibre capital spending comes years before fibre revenue: homes are passed first and connected later, so a faster build lowers penetration before it raises cash flow.
  • What capex intensity is, how maintenance differs from build spending, and why a spectrum auction is kept out of both and still has to be paid for.
  • How dividend cover is read from equity free cash flow, and what an uncovered dividend does to leverage year by year.

What a reviewer looks for

  • Service revenue grown at a rate rather than built from average subscribers and ARPU, so churn and price cannot move it.
  • Net additions used for acquisition costs, when the cost is paid on every gross addition, including those that only replace churn.
  • Handset revenue left inside service revenue, which flatters growth and dilutes the margin without saying so.
  • EBITDA before lease costs set against net debt without lease liabilities, so leverage looks a turn lower than it is.
  • Fibre revenue from homes passed at mature penetration from the year of the build, with no ramp.
  • A terminal value on a year with no spectrum payment and no allowance for renewing licences that expire.
  • A terminal value on a year the fibre build is still filling, so the cost of connecting homes is repeated for ever and the customers they have still to bring are never counted: a faster build then always looks like a loss.

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; the valuation date is the end of FY0. Every scenario changes FY1 onwards only.
  • Subscribers, homes and connections are in thousands. Gross additions and churn are rates on the opening base and fall evenly through the year, so revenue and care costs use the average base. Monthly churn is the annual rate / 12.
  • Service revenue = average subscribers x ARPU (euros a month) x 12 / 1,000. ARPU grows on last year’s at the base rate plus the selected scenario’s change. Equipment revenue is handsets sold x the average price and is not service revenue.
  • Fibre connections = the homes passed before FY0 x their penetration + each later year’s build x the penetration its age has reached, which stops rising five years after the build. Fixed churn is inside penetration. Build cost is per home passed; connection cost is per net connection and is spent only when a customer signs up.
  • EBITDA is before lease costs, as IFRS 16 reports it. EBITDAaL deducts lease costs (right-of-use depreciation and lease interest, taken as the year’s lease payments). Net debt holds no lease liabilities, leverage is net debt / EBITDAaL and free cash flow is after leases. The lease-adjusted basis is a memo on Debt & Dividends and is never mixed into the case.
  • Subscriber acquisition costs are expensed as incurred, per gross addition, so replacing churn costs as much as growing.
  • Maintenance capital spending is a share of service revenue, depreciated over eight years from the year after; before FY1 the networks were renewed at the FY0 rate. New fibre is depreciated over 25 years. Spectrum is paid in the auction year, kept out of capital spending and amortised over the licence from the year after.
  • Interest is charged on opening balances; bonds are refinanced as they mature, a new bond part-funds the auction and a revolver holds minimum cash. The dividend per share grows at a fixed rate and is paid whatever the year’s cash flow. Dividend cover is equity free cash flow (after leases, interest and tax, before spectrum) / dividends.
  • Enterprise value discounts FY1 to FY6 unlevered free cash flow and a terminal value at the end of FY6. The terminal value is a growing perpetuity on FY6 cash flow before spectrum, less a year of licence amortisation so the cost of renewing licences is in it, with connection costs at the steady state a build of FY6’s size brings (that build x mature penetration a year); plus the connections homes passed by FY6 add over the next five years as they fill, each valued as it arrives at its own after-tax cash flow less the cost of connecting it. The peer multiple is a cross-check, not the valuation.

What this model does not cover

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

  • Tower companies, which rent sites to operators and are modelled from tenancies per tower, and cable and media groups with content costs. This is an integrated mobile and fibre operator.
  • Tariffs and bundles. One ARPU stands for each segment; there is no split by plan, no fixed-mobile bundle discount and no migration from prepaid to postpaid.
  • Capitalised contract costs and handset financing. Acquisition costs are expensed as incurred and handsets are sold outright.
  • A separate fibre company, wholesale partners or co-investment in the build, and any sale of the towers. The operator builds and owns its network.
  • Lease liabilities built contract by contract. Lease costs are one line, and the liability is a multiple of it shown as a memo.
  • A real company. Orange, KPN and Telefónica are candidates for a case built from their reports; this model uses invented figures.

Build it yourself

The starter workbook

The Subscribers sheet has been cleared: for postpaid and for prepaid, the opening base, gross additions, the churn rate in the selected scenario, churn, net additions, the closing and average base and monthly churn, then the total base, its net additions and the postpaid share. Rebuild it from the Assumptions and Scenarios sheets, then switch to the price war and watch three points of churn work through the average base into service revenue and EBITDAaL.

Blanks: Mobile subscriber roll-forward: gross adds, churn, net adds. 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.

Vocabulary: ARPU (Average Revenue Per User), EBITDAaL, Homes Passed, Subscription Churn, Operating Leverage, Maintenance CapEx, Net Debt, Leverage Ratio (Debt/EBITDA), Terminal Value, Free Cash Flow (FCF).

Questions about this model

What is EBITDAaL, and why does the model use it?

EBITDAaL is EBITDA after leases. Under IFRS 16 the rent on mast sites, shops and leased fibre leaves operating costs and becomes depreciation and interest, which lifts EBITDA and adds a lease liability to debt. In FY1 the operator reports EBITDA of €2,117.5 million; deducting €339.9 million of lease costs gives EBITDAaL of €1,777.6 million, 31.7% of revenue and 39.4% of service revenue. Leverage is then read on one basis: net debt without lease liabilities is 2.4x EBITDAaL, and the memo with lease liabilities in the debt is 3.0x EBITDA. Net debt without leases over EBITDA before them would mix the two and flatter the ratio.

How do gross additions, churn and ARPU drive a mobile operator?

The postpaid base opens FY1 with 9,027 thousand subscribers, adds 1,264 thousand and loses 1,083 thousand to churn of 1.0% a month, for net additions of 181 thousand. Almost all the selling effort replaces customers who left, and it is paid for on gross additions: €155.4 million of acquisition costs in the year. Because the network costs the same either way, a point more annual churn costs €9.4 million of FY1 EBITDAaL in its first year, and more in each year after because the subscribers stay lost, while a point more ARPU growth is worth €20.9 million.

What happens in the price war?

Mobile ARPU growth is cut by four points and churn rises by three for two years, easing in the third, with broadband prices following part of the way. Service revenue shrinks while the networks, the leases and the cost of each new customer stay, so EBITDAaL falls from €1,730.8 million in FY0 to €1,477.4 million in FY3. The dividend is still paid: dividend cover falls to 0.49x, the revolver reaches €727.4 million, leverage peaks at 3.8x against a 3.0x ceiling, and the value per share falls from €8.29 to €3.95.

Does building fibre faster create value?

A little, and only if the new homes fill. The accelerated case passes 9,700 thousand homes by FY6 against 8,600 thousand and ends with 3,152 thousand connections against 2,827 thousand, but homes are passed years before they are connected: penetration in FY2 is 28.8% against 30.8%, capex intensity reaches 20.8%, cover falls to 0.48x and leverage touches 3.1x. On FY1 to FY6 alone the build looks like a loss, because homes are paid for years before they fill. The terminal value therefore counts the connections homes already passed have still to add, 299.5 thousand against 261.5 thousand, each worth €3,804 when it arrives, and charges connection costs only at the rate a steady build brings them. On that basis the extra homes earn a little more than the 7.5% discount rate: €8.47 a share against €8.29. The margin is modest. With penetration a tenth lower in every year’s build the lead halves, and at about €830 a home passed rather than €600 it is gone.

How is spectrum treated, and what is the valuation?

The FY2 auction costs €900 million, €540.0 million of it funded by a new bond. It is kept out of capital spending and out of the dividend cover operators report, then amortised over the 20-year licence, €45.0 million a year from FY3. Licences expire, so the terminal value deducts €195.0 million of licence amortisation from FY6 cash flow as the cost of renewing them. It also counts the 261.5 thousand connections homes passed by FY6 have still to add as they fill, worth €869.6 million at the end of FY6. At a 7.5% discount rate and 1.0% terminal growth that gives an enterprise value of €12,689.1 million, 7.1x FY1 EBITDAaL, and €8.29 per share, against €7.15 at the peer multiple of 6.5x. The FY1 dividend is covered 1.17x by equity free cash flow of €475.5 million.

Related models

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.