Telecom financial model: subscribers, costs and cash

A telecom financial model links subscriber movements and monthly fees to revenue, operating costs and cash. Start with the subscriber and billing exports you already have, then add the cost ledger, collection records and approved capital-spending schedule. The useful result explains which assumptions missed plan and whether those differences changed available cash.

Start with a defined subscriber movement

Choose a month, a paid service unit and a consistent cancellation rule. A regional US connectivity provider might count paid connections; an enterprise carrier might need separate schedules for circuits and customer accounts. Do not combine those units in one average price.

Keep opening subscribers, gross additions, cancellations, transfers and closing subscribers in a movement schedule. Give each row a service identifier, effective date and event type. Preserve the approved budget separately from actual events so a revised expectation cannot overwrite the original comparison.

Closing subscribers = Opening subscribers + Gross additions - Cancellations

Estimated average subscribers = (Opening subscribers + Closing subscribers) / 2

Recurring revenue = Average billable subscribers × Net monthly fee

The midpoint average assumes additions and cancellations are spread evenly through the month. Use subscriber days and actual prorated billing when timing is uneven. Exclude sales taxes collected for others from the net service fee, and reconcile credits and equipment revenue separately.

For the broader planning process, see AI for FP&A. A startup financial model covers a different planning question: funding an early business rather than explaining an established subscriber base.

Build the monthly spreadsheet

The fictional provider below sells one service in April 2026. All assumptions are supplied by its operating team, rather than external benchmarks. The midpoint convention is used for both columns so the comparison is consistent.

Put this table in A1:C17, with the headings in row 1. Dollar amounts are USD; subscriber counts are connections. Amounts are shown to whole dollars and churn to one decimal place, with calculations kept unrounded.

Enter these Excel formulas in B5, B6, B8, B10, B12, B14, B16 and B17, respectively, then copy across to column C:

=B2+B3-B4
=(B2+B5)/2
=B6*B7
=B6*B9
=B6*B11
=B3*B13
=B8-B10-B12-B14-B15
=B4/B2

For multiple service tiers, calculate average billable subscribers and net fees separately. Microsoft's SUMPRODUCT documentation describes multiplying corresponding ranges and summing the results. If average subscribers are in E2:E10 and net fees in F2:F10, total recurring revenue is:

=SUMPRODUCT(E2:E10,F2:F10)

Rebuild actual revenue from billing lines rather than assuming every subscriber pays the list price. If Billing contains period in column A, service category in B and net revenue in C, the following totals recurring revenue for the period in H1:

=SUMIFS(Billing!C2:C10000,Billing!A2:A10000,H1,Billing!B2:B10000,"Recurring")

Use a consistent period label in both places. Microsoft documents SUMIFS as summing values that meet multiple criteria. Covirage is not affiliated with Microsoft.

Explain the variance with an exact bridge

Actual revenue is $25,700 below plan. Split that gap in a fixed order: change subscriber volume at the planned fee, then change the fee on actual volume.

The proof is $482,000 - $15,200 = $466,800. Lower acquisition expense partly offsets weaker revenue, but it also accompanies fewer additions. Finance should ask commercial operations about pricing and additions, and customer operations about cancellations, before treating the savings as a lasting improvement.

Capacity remains a separate constraint. Use capacity planning that reconciles to budget when installation or service resources limit how many additions can actually be delivered.

Reconcile operating performance to cash

The operating surplus is not cash flow. Assume cash collections are $1,050,000 against actual revenue of $1,090,800, increasing receivables by $40,800. Actual network and support expenses total $303,000, but supplier payments are $315,000 because prior payables fall by $12,000. All other operating expenses are paid in the month.

The direct check is $300,000 + $1,050,000 - $636,000 - $250,000 = $464,000. Operating payments are $315,000 to suppliers, $81,000 for acquisition, and $240,000 of fixed expenses. This illustration excludes taxes, financing, equipment sales and other working-capital movements; add their own rows when they exist.

Where it goes wrong

• Mixing accounts and connections. Match the subscriber count to the billed unit before multiplying by a fee.
• Using closing subscribers for a full month. Late additions did not earn a full month's revenue. Use billing records or an explicit timing assumption.
• Calling transfers churn. Internal migrations should reconcile between products without creating company-wide customer losses.
• Treating spending as one expense line. Separate recurring network costs, capital purchases and payment timing so cash and operating results remain explainable.
• Overwriting approved assumptions. Keep the original plan, current expectations and closed actuals identifiable in every comparison.

Compare your own telecom plan in Covirage

Bring your subscriber movement export, billing totals, expense ledger and prepared budget assumptions in a file or Google Sheet. Covirage maps the columns, and its tools compute and check the supplied comparisons; 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 telecoms for the supporting industry context.