Blog · Finance metrics and formulas · Telecommunications
Telecom financial analysis connects subscriber revenue with operating costs and capital spending. Work through a segment-level example, reproduce it in Excel and reconcile operating profit to the cash balance.
Telecom financial analysis connects subscriber revenue, operating costs and capital spending to explain which segments generate profit and cash. Use the same quarter and segment definitions across the subscriber file, ledger and capital-spending report. Subscriber growth alone cannot show whether the business has more cash available after network investment.
Start with service revenue by segment and month, average active billable subscriptions, operating expenses, receivables and payables, depreciation and cash capital expenditures. Include opening and closing cash plus financing payments. Keep equipment revenue separate from recurring service revenue.
Document whether the unit is a connection, a household account or a business account. A business customer with multiple connections cannot be counted as one unit in one quarter and several in the next. Use the billing team's consistent subscriber definition and a time-weighted average when the base changes materially within a month.
Map each source category once. Direct segment expense should include the costs management assigns to serving that segment; common corporate costs remain in a separate reconciliation. Preserve an unassigned category until finance resolves every unmatched account.
For the general review sequence, use financial analysis. The customer questions addressed by telecom analytics support this finance review, but subscriber behavior and financial performance need different measures.
This illustrative US connectivity provider has two segments for a three-month quarter. Subscriber averages are supplied inputs. Service ARPU means monthly average service revenue per subscriber; the example excludes equipment sales and pass-through taxes.
Use a Segments sheet with headers in row 1 and the segments in rows 2 and 3. Columns A through G follow the table. Revenue and costs are USD; percentages are rounded to one decimal, and formulas retain full precision.
Monthly service ARPU = Quarterly service revenue / Average subscribers / Months
Segment contribution = Service revenue - Direct operating costs
| Segment | Average subscribers | Monthly ARPU (USD) | Service revenue (USD) | Direct operating costs (USD) | Contribution (USD) | Margin |
|---|---|---|---|---|---|---|
| Consumer broadband | 20,000 | 60 | 3,600,000 | 2,400,000 | 1,200,000 | 33.3% |
| Business connectivity | 5,000 | 120 | 1,800,000 | 1,050,000 | 750,000 | 41.7% |
| Total | 25,000 | 72 | 5,400,000 | 3,450,000 | 1,950,000 | 36.1% |
When building the planning comparison, calculate D2, F2 and G2 as follows, then fill through row 3:
=B2*C2*3
=D2-E2
=IF(D2=0,NA(),F2/D2)
For actual results, take revenue from the ledger and calculate observed ARPU with =D2/B2/3. Compare that result with the planned ARPU instead of forcing actual revenue to equal the assumption.
In row 4, sum subscriber, revenue, cost and contribution columns. Calculate combined ARPU and margin from totals:
=SUM(D2:D3)/SUM(B2:B3)/3
=SUM(F2:F3)/SUM(D2:D3)
Microsoft's SUM guidance covers adding cell ranges; verify that imported amounts are numeric because SUM ignores text values.
The combined monthly ARPU is $72, and contribution margin is 36.1%. Neither is the simple average of the segment figures. These totals are meaningful here because both segments use comparable subscription units and the same period.
Direct operating costs in this example exclude common corporate expense, depreciation, interest and tax. They include expensed maintenance and service-delivery costs. Capital additions remain outside the contribution calculation.
| Reconciliation step | Amount (USD) | Calculation |
|---|---|---|
| Segment contribution | 1,950,000 | 1,200,000 + 750,000 |
| Less common corporate expense | 400,000 | Ledger expense |
| Less depreciation | 600,000 | Ledger expense |
| Operating profit | 950,000 | 1,950,000 - 400,000 - 600,000 |
The expense check is 3,450,000 + 400,000 + 600,000 + 950,000 = 5,400,000. That reproduces service revenue. Label contribution exactly as defined here; do not assume it matches another operator's published segment measure.
The SEC's financial-statement guide explains depreciation as allocating the cost of long-lived assets over their use. Depreciation affects operating profit even when this quarter's capital payment is much larger or smaller.
Keep operating cash and capital expenditure separate. Verizon's quarterly results, for example, report operating cash flow and capital spending separately in their financial review. Covirage is not affiliated with Microsoft or Verizon.
For the illustrative provider, increases in receivables and payables are signed below. Cash interest and tax are combined for this simplified bridge; there are no other operating adjustments. Create a separate Cash sheet with headers in row 1 and amounts in column B.
| Cash bridge | Signed amount (USD) |
|---|---|
| Operating profit | 950,000 |
| Add depreciation | 600,000 |
| Increase in receivables | -180,000 |
| Increase in operating payables | 80,000 |
| Cash interest and tax | -150,000 |
| Operating cash | 1,300,000 |
| Cash capital expenditures | -1,100,000 |
| Cash after capital expenditures | 200,000 |
| Debt principal repayments | -150,000 |
| Net cash movement | 50,000 |
| Opening cash | 500,000 |
| Closing cash | 550,000 |
Calculate B7 with =SUM(B2:B6), B9 with =SUM(B7:B8), B11 with =SUM(B9:B10) and B13 with =SUM(B11:B12). The final balance must match reconciled cash. Include lease payments and other financing movements according to the actual ledger classification when extending the example.
Capital spending is 1,100,000 / 5,400,000 = 20.4% of service revenue. If consumer monthly ARPU increases by $2 with the subscriber base, costs, collection timing and capital spending unchanged, quarterly revenue rises by 20,000 × 2 × 3 = $120,000. Cash after capital expenditures becomes $320,000 before any incremental tax. This isolates the price assumption rather than promising the same outcome after real-world cost changes.
Upload your segment workbook and subscriber, expense and capital-spending exports: Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See Covirage for telecom for the industry context.
Divide service revenue by the average subscriber base and the number of months in the period. Match the revenue scope to the subscriber definition. Exclude equipment sales when calculating service ARPU, and do not mix customer accounts with individual connections.
Capital spending purchases or improves assets used across periods. Depreciation allocates their cost to earnings over time. Cash paid for those assets therefore belongs in the cash review, while depreciation belongs in the profit reconciliation. Treating both as current operating expense counts the asset cost twice.
No. Additional subscribers can bring lower realized prices, higher support costs or greater capital requirements. Compare revenue and contribution per segment, then include collection timing and cash capital spending before concluding that subscriber growth improves available cash.