How to Create a Telecom Financial Model in 10 Steps

This tutorial discusses creating a Telecom Financial Model. It starts by showing the model’s primary drivers, such as market share or the number of connections.

Then, it forecasts revenues, operating costs and capital expenditure. Finally, I explain how to calculate returns on investment and test the model.

Step 1. Show the Main Drivers of the?Model

Set up your spreadsheet in a way that clearly shows the main drivers of your plan.

For instance, I would use a metric such as addressable properties to estimate my addressable market. Then, I would use market reach (e.g., 35%) to calculate the number of connections and forecast revenue and costs.

(Right-click on the images to save or open them in a new tab in their original size)

Step 2. Forecast Monthly Telecom?Revenues

In my example, the number of active users or connections will drive the revenue calculations. I split the estimate by the service or product offered.

To calculate the telecom revenue, multiply the number of projected users by the average price or fee charged to a user in a given period.

Step 3. Estimate the Direct Operating Costs

Split the cost estimates into variable or direct and fixed operating costs. The former will differ depending on the number of users or the network size.

For example, the number of new users or connections can drive marketing or acquisition costs. You can also link maintenance costs to the number of homes connected or the overall network investment.

Finally, the service costs will likely relate to the number of customers or connections.

Step 4. Headcount and General Opex Projections

To calculate staff costs, start with the headcount projections. Divide your estimate by department or type, such as Central Office and Operations.

Then, multiply the numbers by the average employee cost in a given period. Remember to include an option to increase salaries over time.

Add office, admin, and other general costs to complete the opex forecast.

Step 5. Forecasting Capital Expenditure in a Telecom?Model

Spending on building the infrastructure will significantly impact the cash flow forecast.

You can divide the capital expenditure into three broad categories:

• Core Network: the central infrastructure allowing the provision of services to the potential customer base,
• Network Connections: the cost of connecting new customers to the core network,
• Equipment/Other: any additional expense needed to activate the service for a customer.

Depending on the expense type, link the calculations to one of the model’s drivers, such as the number of passed or connected properties.

Step 6. Calculate the Users’ Value and Revenue Contribution

Analyse how each new user contributes to the cash flow to understand the telecom model’s outcome better.

For instance, using churn and retention assumptions, calculate the lifespan of an average user.

Then, multiply the outcome by the average consumption of each service or product and the corresponding revenue to calculate the user’s lifetime value.

Step 7. Adding Debt Financing and?Leverage

A telecom investment model will often require debt financing to minimise the level of equity needed. You can take the capital expenditure forecast as the starting point of the calculation.

For example, multiply the core infrastructure spending by the debt leverage assumption (e.g. 60%) to calculate the amount of bank loan received.

Then, using Excel’s PMT formula, estimate the payments needed to service the debt, splitting principal and interest payments.

You can divide loans into tranches, i.e., draw debt annually based on the next 12 months’ expected capital expenditure.

Step 8. Presenting the Telecom Model as a Cash Flow Statement

Aggregate the outcome of the calculations and split them into the following cash flow categories:

• Operating Activities: related to day-to-day business, such as revenues from provided services and ongoing costs,
• Investment Activities: in the case of a telecom model, it will include infrastructure spending and equipment,
• Financing Activities: showing financing from debt and equity.

Step 9. Calculate the Return on Investment

Adjusting the cash flow from operations and investments for debt financing will indicate the required equity investment.

One method for estimating a telecom plan’s terminal value is to use the exit year’s EBITDA and a multiplier value.

Adjust the outcome for any liabilities and current assets to forecast the final proceeds to the investors.

You can use Microsoft Excel’s IRR and NPV formulas to calculate the returns.

Step 10. Stress Testing and Sensitivity Analysis

Test the impact of the main assumptions on the financial model’s outcome. Finding weak spots will help you address and mitigate any concerns about the plan’s feasibility.

For example, as the telecom model will heavily rely on infrastructure spending, test the impact of capex assumptions on the capital required and the investment returns.

Use Microsoft Excel’s data table to quickly build various scenarios with different inputs.

Download my Telecom Financial Model?Template

To learn more or to download the Excel template used in this tutorial, go to my blog at: https://www.challengejp.com/blog/telecom-financial-model-excel-tutorial/

*Use a coupon code LINKEDIN to get 20% off your first order.

About the?Author

Hi, my name is Jacek, and I love spreadsheets! Visit my website and let me know if you need help building a telecom cash flow forecast or want to learn more about financial modelling.

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