Skip to content

How to Create a Telecom Financial Model

This tutorial shows how to create a Telecom Financial Model in Microsoft Excel.

It starts with instructions on projecting users’ growth and estimating revenues and operating expenses related to servicing the customers. Then, it explores forecasting headcount and staff costs, followed by general expenditures. Finally, it explains how to calculate network and other infrastructure build costs in the telecom business.

Download my Telecom Financial Model Template to follow the examples used in this tutorial.

You can also watch the video version of the tutorial at the end of this post.

Step 1. Start with the Main Assumptions of a Telecom Cash Flow Forecast

To calculate investment return on a telecom project, first create a cash flow forecast. Split the main financial contributors, such as revenue and cost, and their main drivers.

Start with showing the number of properties passed over time. The number of properties passed represents the addressable market, i.e. the units to which a telecom business can provide services.

Then, assume the market reach, i.e. the number of properties a telecom or other infrastructure business plans to connect. For example, if there are 1,000 properties passed and the market reach target is 35%, then the final number of connections will equal 350 (i.e. 35% x 1,000 = 350). Finally, show how those connections will phase over time.

My Telecom Financial Model Template uses flags to indicate build and growth periods. The spreadsheet uses a monthly timeline to show the exact number of properties passed and connected at a given time. Consequently, the numbers will become the main drivers behind user revenue, service provision, and building cost calculations.

telecom cash flow growth forecast excel template

Step 1. An example of growth projections in a Telecom Cash Flow Forecast. The model calculates the number of units connected as a multiplication of the addressable market (i.e. properties passed) and a market reach assumption. You can download the entire Excel file here.

Notice that the model also makes assumptions about churn, i.e. the number of customers leaving the service.

Learn More:  Visit my tutorial on How to Create a Subscription Model with Churn Calculation for more examples of customer-based models.

Step 2. Calculate User Revenues in a Telecom Cash Flow Forecast

Calculate revenues using the users’ growth projections from the earlier part of the telecom model. Split the cash flow forecast into service streams to show how they grow or decline over time.

For example, my Telecom Financial Model Template assumes that a business will generate revenue from the following services:

  • Landline – with steady income but declining uptake,
  • Broadband – increasing usage, with low operating cost to the provider,
  • Mobile – provided by a third-party mobile operator,
  • TV / Other – content provided by a third party based on a revenue-sharing model.

To calculate the service revenues, multiply the number of connected users by the service uptake % assumption. Then, multiply the number by a monthly service fee. For instance, if there are 100 connected users, 80% of them using broadband, and a monthly fee in a given period is $40, the broadband monthly revenue will equal $3,200 (i.e. 100 users x 80% x $40 = $3,200).

telecom revenue forecast excel template

Step 2. An example of projecting revenue in a Telecom Cash Flow Model. The spreadsheet breaks down the users and revenue by the type of service provided. You can download the complete Excel template here.

Note that the model allows for an increase in pricing and changes the blend of service uptake over time, affecting the average revenue per user.

Learn More:  Visit my tutorial on How to Build a Cash Flow Forecast in Microsoft Excel for more examples of using growth projections to model revenues.

Step 3. Calculate Direct Operating Costs in a Telecom Financial Model

Direct operating costs are expenses related to providing users with the service. Forecast the direct cost by linking payment per unit assumptions to the connections growth forecast.

My Telecom Financial Model Template divides the direct costs into the following three categories:

  • Service Provision Costs: direct cost to the operator, for example, an average cost of providing a user with a mobile service,
  • Network Operating Costs: the cost of maintaining the network, calculated based on homes connected or overall network build cost,
  • Customer Acquisition Costs: the cash needed to acquire a projected number of users, for example, through an advertising campaign.
telecom cash flow direct opex forecast

Step 3. An example of Direct Opex forecast in a Telecom Cash Flow model. The template calculates the service provision expenses as a multiplication of a user number by an average monthly cost per user or as a revenue share. The marketing cost is linked to a cost-per-acquisition assumption and the number of new users. You can download the entire Excel file here.

Note that the model uses a mixed approach to calculate telecom direct costs. For example, the template multiplies the number of connected homes by an average price to calculate the broadband and landline expenses. On the other hand, the spreadsheet calculates the mobile and TV costs as a percentage of the revenue generated from the service.

Learn More: Visit my tutorial on How to Create a Marketing Investment Plan for more examples of projecting direct costs based on the number of customers.

Step 4. Estimate Headcount and General Operating Expenses

Create headcount and salary forecast in a Telecom Cash Flow Model. Divide the headcount projections into variable and fixed parts so you can clearly show the portion of the salary expenses that are directly related to customer growth.

For example, my Telecom Financial Model Template splits the forecast of employee numbers into the following parts:

  • Central Headcount: includes roles such as CEO, CFO, Finance, etc., and is less sensitive to changes in customer growth,
  • Operating Headcount: staff related to customer service and network maintenance, which is more dependent on the changes in the customer numbers.
  • Construction Headcount: the staff needed to support the network infrastructure building program. Notice that the model places the cost in the CAPEX section of the cash flow forecast.

Multiply the forecast headcount number by an average employee cost to forecast the overall staff cost. Remember to include an option to increase an average salary to calculate how the staff cost changes over time.

salaries opex cash flow forecast excel template

Step 4. An example of forecasting Salaries and General Operating Expenses in a Telecom Financial Model. The staff cost is directly linked with the headcount projection and the average monthly salary. The cost assumptions increase in line with the inflation assumption. You can download the entire spreadsheet here.

Finally, add other central expenses, such as office expenses and additional support costs, to calculate the overall general operating cost.

Learn More:  Visit my tutorial on How to Create a Merger and Acquisition Model for another example of estimating headcount and staff expenses based on projected revenue growth.

Step 5. Forecast Capital Investment in a Telecom Financial Model

Use the Capital Expenditure portion of a Cash Flow Forecast to calculate the network infrastructure building cost. The spending on the core infrastructure will form a significant part of an investment in a telecom project.

My Telecom Financial Model Template splits the build cost into the following categories:

  • Core Network: the network infrastructure which allows a telecom business to provide services to its customers,
  • Network Connections: cost of connecting a customer’s property to the core network,
  • Equipment: equipment needed to provide service to a connected customer.

The model estimates the Cost of Core Network Build as the number of newly passed properties multiplied by an average build cost per property. It also uses the number to forecast the number of construction employees.

In addition, the spreadsheet uses the number of newly connected properties and multiples it by an average unit cost to calculate the Network Connections and Equipment cost.

infrastructure cash flow capex forecast excel template

Step 5. An example of a Telecom Cash Flow Model with a forecast of network build and other capital investment. The core network cost is linked with the number of properties passed, while the active capex is related to the number of connections. You can download the complete Excel file here.

Note that the model template applies different depreciation assumptions to the telecom network build and equipment. It also has the option to forecast upgrades or replace the equipment, resulting in additional capital expenditure in the future.

Learn More: Visit my tutorial on How to Create a Project Finance Model and How to Create a Capital Investment Plan for more examples of forecasting capital expenses in an infrastructure project.

Step 6. Analyse the Customer Lifetime Value of a Telecom User

Use the customer churn assumptions, telecom revenue, and cost forecast to calculate the customer lifetime value. In addition, show how users contribute to the overall cash flow forecast and returns on investment.

My Telecom Financial Model Template contains two sheets that visualise the contribution of an average user to revenues and cost estimates. They also help explain how an average investment per user is paid back over time.

The Customer Lifetime Calculator uses churn assumptions to estimate the average length of a user’s lifecycle. It then takes average revenues and cost inputs to calculate an overall cash flow contribution over a user’s lifetime.

customer lifetime value excel calculator

Step 6a. An example of a Telecom Customer Lifetime Value Calculator. The spreadsheet estimates the lifetime values based on user lifespan and the model’s revenue and cost assumptions. You can download the entire Excel template here.

Additionally, the User Cohort Forecast shows the annual financials of a group of customers over ten years from their sign-up. It breaks down the revenue and cost by a service stream and uses the numbers to calculate overall cash flow contributions.

users cohort cash flow analysis graph

Step 6b. The graph analyses a cohort of users and their net contribution to overall cash flow over time. The breakdown depends on the service uptake assumptions over time. You can download the complete Excel file here.

The graph also includes a cumulative cash flow line, which visualises the payback period and the breakeven point of an infrastructure project investment.

Learn More: Visit my tutorial on How to Create a Subscription Model with Churn Calculation for another example of calculating Customer Churn and User Lifetime Value.

Step 7. Add Debt Financing to a Telecom Investment Forecast

The capital expenditure in a telecom financial model will result in a significant investment requirement. Include debt calculations to estimate the level of the capital requirement financed by loans. Then, take the remainder of the cash flow deficit to forecast the required level of equity.

My Telecom Financial Model Template calculates the amount of debt as a percentage of annual core network cost.

For example, suppose the capital investment forecast in a given year equals $10m, and the debt leverage assumption is 60%. In that case, the model will inject $6m of debt at the beginning of that year (i.e. 60% x $10m = $6m).

Lastly, the model estimates the payments required to repay the debt over time. The spreadsheet also makes assumptions about other terms of the debt, such as the number of draws, arrangement fees, interest percentage and interest-only period.

infrastructure debt model cash flow excel template

Step 7. An example of adding a Debt Model to a Telecom Cash Flow Forecast. The amount of debt raised is linked to the core capex projection and a debt leverage assumption. You can download the entire Excel template here.

Note that the model links the debt calculations to the rest of the forecast. You can use the cumulative post-debt financing cash flow to estimate the amount of the required equity investment to close the remaining capital deficit.

Learn More:  Visit my tutorials on How to Create a Financial Model with Debt Financing for other examples of cash flow forecasts with debt financing.

Step 8. Present Telecom Forecast as a Cash Flow Statement

Aggregate the output of the telecom cash flow forecast so it’s easier to summarise and spot revenue and cost trends. Also, group the cost labels into broader categories to explain the projections and the use of invested funds.

My Telecom Financial Model Template splits the cash flow into the following standards categories:

  • Operating Activities: revenue and costs related to day-to-day business, such as revenues from provided services and direct and operating costs,
  • Investment Activities: in the case of a telecom model, those numbers primarily consist of the infrastructure spending, such as the core network build, network connections and equipment,
  • Financing Activities: this part explains how the debt and equity will finance the cash requirement resulting from the telecom plan.
telecom cash flow statement excel

Step 8. An example of a Telecom Cash Flow Statement in Microsoft Excel. The operating cash flow is linked to the recurring revenues and expenses, while the investment cash flow reflects the capex spending. You can download the complete spreadsheet here.

Notice that the model links the numbers from the cash flow statement to other spreadsheet parts, such as the Balance Sheet or Profit and Loss Statement. Therefore, remember to interconnect the calculation outputs so a change in one cell automatically affects the others.

Learn More:  Visit my tutorial on How to Create a Capital Investment Plan for another example of an infrastructure cash flow forecast template.

Step 9. How to Calculate Returns on Investment in a Telecom Model

Use the cash flow forecast to calculate returns on investment in telecom or any other infrastructure model. Estimate the level of equity required using the deficit in the post-debt financing cash flow balance. Then, compute the terminal value to calculate the returns on investment.

My Telecom Financial Model Template uses EBITDA and its multiplier to calculate the terminal value of a project. For example, if the EBITDA at the final exit year equals $1m and the EBITDA multiplier is 10.0x, the terminal value will be $10m ($1m x 10 = $10m).

For examples of EBITDA multipliers and other valuation techniques in the telecom industry, visit this page [external link].

To forecast the needed equity injections, take the cash from the operating and investing activities and add debt financing. The resulting annual cash deficits will reflect the additional equity investment.

Then, calculate a telecom project’s Internal Rate of Return and Net Present Value using Microsoft Excel’s IRR and NPV formulas.

telecom equity investment return calculator

Step 9. An example of calculating Returns on Equity Investment in a Telecom project. The exit proceeds are calculated as a function of EBITDA and its multiplier. The template uses Microsoft Excel’s IRR and NPV formulas to calculate the return on investment. You can download the entire Excel template here.

Lastly, complete the return on investment calculations with cash flow breakeven month and payback period. The former illustrates when an infrastructure project becomes profitable. On the other hand, the latter shows how long it will take for an investor to get their money back.

Learn More:  Visit my tutorials on How to Create a Project Finance Model for more examples of calculating return on equity investment in infrastructure projects.

Step 10. Stress Test the Assumptions and Cash Flow Model

When building a telecom cash flow forecast, place the main assumptions next to the annual summary. That way, you can quickly test how a change in input affects the overall output of the model.

As with most infrastructure projects, a telecom’s cash flow forecast will heavily depend on an initial investment funding the construction and deployment costs. Accordingly, the investors recoup their financing from customers’ revenue collected over time. As a result, the returns on equity will be sensitive to changes in significant build and pricing assumptions. 

My Telecom Financial Model  Template uses Microsoft Excel’s data tables to summarise how a change in a set of assumptions affects the return on investment. For example, you can test the impact of build cost and length on the payback period.

excel model stress test data table

Step 10. An example of using data tables to stress test a Telecom Investment Model. Scenario #1 (left) shows the impact of the Debt Interest and Debt Leverage assumptions on the overall Equity Investment requirement. Scenario #2 (right) analyses the relationship between Market Reach and Build Cost and their impact on the Equity Payback Period. You can download the entire Excel file here.

To illustrate the impact of assumptions change on a return on investment, use metrics such as IRRNPVInvestment Payback Period, and a Cash Flow Breakeven point. Also, pay attention to the margins to ensure the model doesn’t deviate from other industry peers. To read more about margins and other metrics in the telecom industry, click here [external link].

Learn More:  Visit my tutorials on How to Create a Real Estate Investment Model for other examples of stress testing in infrastructure financial models.

Summary: How to Create a Telecom Financial Model

This tutorial has shown you how to create a Telecom Investment Model in Microsoft Excel. Here is a summary of the steps to create a cash flow forecast and calculate and stress test the returns on investment:

  1. Start with the growth forecast. Show monthly phasing of passed and connected properties over time.
  2. Forecast telecom revenues by service stream. Link the average pricing assumptions to the growth estimates.
  3. Calculate the cost related to providing telecom service to users. Use % share of revenue or per unit values for your forecast.
  4. Create a headcount forecast based on the number of properties saved. Then, link average salary assumptions to calculate the staff cost.
  5. Estimate build and infrastructure costs based on the number of connected and passed properties. Split the capital expenditure into building the core network, network extensions and equipment.
  6. Illustrate the model’s profitability on a unit level with a cohort analysis or user lifetime value calculation.
  7. Add debt to the calculations to leverage the cash flow forecast showing principal repayments and interest expenses.
  8. Split the cash flow forecast by operating, investing and financing activities.
  9. Clearly show incremental equity injections. Also, Calculate the terminal value and use the numbers to calculate the returns on the telecom investment.
  10. Use the summary to stress test the model and investigate the sensitivity of the assumptions.

Download: Telecom Financial Model Template

Click the button below to download my Telcom Financial Model Template.

The Excel spreadsheet is an example of how to forecast revenues and costs for a telecom project. It also allows you to add debt to the projections. Finally, the template uses the resulting cash flow to calculate the return on investment and test the model’s assumptions.

The file contains the following tabs and sections:

  • Monthly Telecom Cash Flow Forecast with Debt Calculations:  monthly projections for a telecom project. The template splits the customer uptake into service streams and links it to customer revenue and cost calculations. It includes an estimate of passed and connected properties based on addressable market and market reach assumptions. Then, it provides headcount and salary estimates and a forecast of operational expenditures. Lastly, the spreadsheet forecasts the cost of building the core network and connections, including calculating depreciation.
  • Telecom Profit & Loss, Cash Flow Statement and Balance Sheet: an annual breakdown of operating, investing, and financing activities, profit and loss, and balance sheet calculations.
  • Equity, Terminal Value and Return on Investment Calculation: a projection of expected returns based on the equity investment forecast and terminal value estimate.
  • Customer Lifetime Value Calculator and Cohort Analysis: profitability estimate of the telecom model per an average customer and illustration of cash flow contribution of a cohort of users.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].


*Order risk-free with a 30-day Money Back Guarantee


44.00 – Download My Telecom Financial Model Template
*Order risk-free with a 30-day Money Back Guarantee

Video: How to Use the Telecom Financial Model Template

For more details and a step-by-step explanation of using and creating the Telecom Financial Model Template in Microsoft Excel, watch my video tutorial below:

 

Get in Touch

challengejp_data_analystHi, my name is Jacek, and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Telecom Financial Model in particular or financial analysis in general, don’t hesitate to get in touch.

Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.

Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

Real Estate Investment Model – this tutorial is another example of a cash flow forecast template for a capital-intensive financial model.

Project Finance Model – this post is an example of estimating revenue and cost and consolidating the cash flow forecast of multiple infrastructure projects.

Capital Investment Plan – read about combining capital investment and market reach to forecast revenues and fixed / variable costs.

Merger and Acquisition Model – this post explores combining two markets to increase customer reach, showing how to use debt to acquire other companies and their assets.

Monthly Budget Forecast – an example of converting actual revenues and costs into short- and long-term plans.

Marketing Investment Plan – convert a marketing budget into a user growth and cash flow forecast.

Cash Flow Forecast in Microsoft Excel – an example of projecting customer growth and linking it to the monthly revenue and cost calculations.

Financial Model with Debt Financing – explore how to add debt to a cash flow forecast using the present value of projected revenues.

Subscription Model with Churn Calculations – read more about user churn and its impact on the customer lifetime value.

Your First Steps in Microsoft Excel – Beginner’s Tutorial – refresh the basics of spreadsheets and the main formulas and functionalities.