Financial Modeling Techniques: Revenue Forecasting

It's only fair to share...Share on Facebook0Share on LinkedIn0Share on Google+0Tweet about this on Twitter0

In our first of many compilations on financial modeling, we demystified the concept of building a financial model. We covered why we need models, who builds models and why it’s in your best interest to learn how to build financial models.

Great, so now that we’re all clear on why building financial models are important, and we understand we need to gather the relevant data, the next question is: how do we forecast and what exactly do we forecast? Do we start forecasting the balance sheet first or do we start with the income statement? And if we start with the income statement, is it more important to forecast accounting profits or operating profits?

Some companies have a more complex cost/business structure than others, and therefore, more difficult to model than others. Regardless of the differences, I can tell you that not every single line-item (whether you’re modeling the income statement, balance sheet or cash flow statement) will be necessary to forecast. However, it is important to have a thesis about the growth prospects for a company. For that, the most central component for forecasting growth (unless you’re valuing a bank or oil and gas company) is to start with forecasting revenue.

Why Start With Forecasting Revenue?

To get an idea of what expenses, EBITDA, and Net Income will look like, you need to start somewhere. The Revenue model is the first piece of the puzzle. Modeling revenue first is not only important for the 3-statement models we will be building in our financial modeling workshop, but other types of models such the Leverage Buyout Model and the M&A Models.

If you were doing a financial model on Microsoft, it might look a little something like this.

This model is based on Microsoft’s segment revenues (at least, their updated segments). There are a couple of different ways to model revenue, but we are going to focus on the main techniques analysis use for modeling a company in the industry.

Top-Down Approach

The Top-Down forecast revenue from a macro perceptive and is probably the most widely used method in the industry. So, if you were trying to forecast Microsoft’s Xbox revenue, you would look how large the market is for video game consoles.

Most analysis don’t have detailed financials about the cost structure of the firms they’re valuing or the unit sales of the products they sell, so we derive a growth thesis based on the addressable market and the market share. In our examples, we collected data on the addressable market using information from secondary sources; we’ve also collected sales based on product lines (which is readily available in Microsoft’s 10-K). Using this information, we calculate the market share (sales /addressable market) and the rate of growth in the market. From there, we can simply calculate future sales by calculator future market share growth, which is what we have done for Microsoft.

Assumptions:

  1. The market for video game consoles will growth 1.5% annually for the next 5 years.
  2. This growth results in Microsoft’s market growing from 35% – 38%.

Math Example: For Microsoft’s 2018 fiscal year, the addressable market will grow by 1.40%. The addressable market increases to $26.3 billion. The Microsoft’s market share increases to 36.10% and as a result, the Microsoft’s revenue grows to $9.3 billion in 2018.

In our examples, we collected data on the addressable market using information from secondary sources; we’ve also collected sales based on product lines (which is readily available in Microsoft’s 10-K). Using this information, we calculate the market share (sales /addressable market) and the rate of growth in the market. From there, we can simply calculate future sales by calculator future market share growth, which is what we have done for Microsoft.

Bottom-Up Approach

The Bottom-Up approach looks at revenue from a more micro perspective. It’s generally used by people who are industry experts/insiders, but there are also times when calculating (or finding) the addressable market is impossible.

Take Apple in this case, which operates in multiple industries in many different continents. It would be difficult to measure the addressable market for all of the products Apple sells.

However, we have some idea (based publicly available information) of the number of units sold for each product. With this information, we take the total amount of iPhone, iPad, Mac sales in a year, divide sales by a number of units sold for each product and arrive at an average selling price (ASP). From there, you forecast future ASP and unit sales growth to back into future revenue for each Apple product.

Assumptions:

  1. Fiscal year 2016: Apple revenue consist of $136 billion, $20.6 billion, and $22 billion for iPhones, iPads, and Macs respectively.
  2. Fiscal year 2016: Apple sold 211.8 million iPhones, 45.5 million iPads, and 18.4 million Macs
  3. ASP for iPhones, iPads, and Macs are $602.72, $445.49, and $1273.62 respectively
  4. ASP growth for iPhones, iPads and Macs will be 7%, -4% and -1% respectively in fiscal year 2017.
  5. Unit sales growth for iPhones, iPads and Macs will be 5%, -4%, 3% respectively in fiscal 2017.

Math Example:  We multiply the current year’s ASP by 1 plus the growth rates (7%, -4%, -1%) and arrive at an ASP for $638, $434, $1,321 for iPhones, iPads, and Macs. Based on the unit sales growth, Apple is predicted to sell 222 million iPhones, 43.7 million iPads, 19 million Macs next year. Multiply the ASP with the number of units, we predict $142 million, $19 million, and $25.1 million in iPhone, iPad and Mac sales.

Cyclical Trends

Cyclical trends involve how sensitivity businesses are to the changes in the business cycle. Companies that are known as “cyclical companies” tend to have longer periods of sustained growth during good times; followed by a period of decline during bad times. This decline is usually tied to the strength of some economic indicator, such as GDP growth, housing starts or inflation.

For example, if I were looking at AutoZone (an Auto Parts and Accessories retailer), I might tie its revenue to the growth in retail sales. Specifically, the Motor Vehicle and Auto Part sales growth

Revenue Model Summary

Most of all the information you need to build an assumption on where revenue is going will be in a company’s 10-K; guidance on future trends will most likely be found in the press releases. This is not to say that once you have started forecasting revenue that the rest will be easy. However, revenue modeling lays the groundwork for a lot of other assumptions you will be making about your case study, as it ties into working capital, EBIT, EBITDA and other important line-items.

1,432 total views, 2 views today

Andre

Andre is a senior at Pace University, studying Finance and Economics. His topics of interests include Equity Valuation, Short Ideas, Macro, Monetary Economics, ETFs, Forex and Fixed Income. His sectors of choice involve Technology, Consumer Goods, and Financials. Andre has completed a number of internships at leading investment banks domestically and abroad.