How to Create a Telecom Financial Model in Excel

This tutorial shows how to create a Telecom Financial Model in 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 in Excel 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’s Excel 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 build cost calculations.

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’s Excel 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).

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 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’s Excel 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.

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 Subscription Model with Churn Calculation 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’s Excel 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.

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 Real Estate Investment Model in Excel 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’s Excel 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.

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 in Excel 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’s Excel 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.

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.

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’s Excel 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 level of payments required to pay off 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.

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 an Excel Financial Model with Debt Financing and How to Create a Project Finance Model in Excel 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’s Excel 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.

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 Real Estate Investment Model in Excel 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’s Excel 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 Excel’s IRR and NPV formulas.

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 in Excel and How to Create a Real Estate Investment Model in Excel 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’s Excel Template uses 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.

To illustrate the impact of assumptions change on a return on investment, use metrics such as IRR, NPV, Investment 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 Project Finance Model in Excel and How to Create a Real Estate Investment Model in Excel for other examples of stress testing in infrastructure financial models.

Summary: How to Create a Telecom Financial Model in Excel

This tutorial has shown you how to create a Telecom Investment Model in 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 Excel Template

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

The 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 Excel 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].

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 Excel, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about Telecom and other Infrastructure Financial Models in particular or financial analysis in general, don’t hesitate to get in touch.

Click here to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you need help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

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 in Excel – this tutorial is another example of a cash flow forecast template for a capital-intensive financial model.

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

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

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

Monthly Budget and Forecast in Excel – an example of converting actual revenues and costs into a short- and long-term plan.

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

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

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

How to Create a Subscription Model in Excel – read more about user churn and its impact on the customer lifetime value.

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