Revenue Model Example – Tops-Down Forecasting

You’ll learn the 3 major methodologies you can use to project revenue in this lesson, and then you’ll use a tops-down approach to estimate Yahoo’s Search Advertising revenue based on Comscore data and market size and share expectations.

Sample Operating Model Video – Search Advertising Revenue Model Transcript

In this lesson we’re going to begin with Yahoo’s revenue model. And as you can see right here, we’re going to start off with the search advertising which is one of their major components of revenue. As you can see on the screen right in front of you right now, search advertising refers to those ads that pop up, when you’re searching for something on Yahoo, Google, MSN, other sites like that.

And as you search for something, you also get sponsored results. If you click on those, then Yahoo, or Google, or whoever is displaying those ads will get paid. And, whenever you estimate revenue for a company you have three options for how to do it.

First, you can do a tops-down model, where you estimate the total market size, the segment that Yahoo or another company owns (i.e. its market share), and then how quickly that segment is growing. So, maybe it’s a $10 billion market, they own 5% market share, and it’s growing at 10% per year.

Another method is to use what’s called a bottoms-up methodology where instead of looking at the total market size, instead you look at how many widgets they’re producing, the price of each of those widgets, and what their growth is going to look like at future years.

For Aspiring Investment Bankers Only:

Revenue Model Example - Tops-Down Forecasting

Claim Your FREE Discounted Cash Flow (DCF) Video Tutorial Series

Master Financial Modeling As It Is Performed In Real Life, With Our Simple 3-Step Method



So, maybe they’re selling 5,000 widgets at $100,000 each, and then next year they’re going to be selling 6,000 widgets at $105,000 each. And then the last way is to make a simple annual percentage growth estimate. So, rather than looking at the total market size, or the unit sales of individual products, you simply estimate and say 5%, 10%, 15%, or something else like that.

For Yahoo’s search advertising we’re going to be using the first method, the tops-down estimate. And, the way we’re going to be doing this is, we’re going to start by estimating the total addressable market for search advertising in other words, how many total searches there are each year.

Then, we’re going to split that into individual companies and look at Yahoo’s share of that market. So, maybe they own 10% or 20% of that market. Then we’re going to estimate based on that how many searches they actually get each year, and then how will they monetize each one of those searches.


And again, just to recap what we said in the beginning. The search advertising, when you search for something on Yahoo, for example, financial services or GMAT practice. Search advertising just means those ads that show up on the side of the page, or sometimes above the search results. When you click, on those Yahoo gets revenue from that, and it’s one of their major sources of revenue. Sometimes, people are skeptical when I go over this and they say, “Well, does anyone actually click on those ads, because I don’t click on those ads?”

Now, if you’ve very tech savvy, then maybe you’ve never clicked on an ad like this, because you know they’re ads, but hundreds of millions of people out there are not tech savvy. They don’t know how advertising on the Internet works. They don’t know the difference between sponsored results and organic results.

And to them, if they see an appealing ad, then they’re going to click on it. So, different strokes for different folks, but I can assure you that hundreds of millions of people, actually do click on these ads, because these companies have revenue in the billions, or tens of billions of dollars.


So, with that done and the search advertising overview explained, now let’s jump into Excel, and I’m going to show you how to actually, project Yahoo’s search advertising revenue, based on information from other companies such CommScore, equity research, and their investor presentation.

Okay, so as I just mentioned, in this lesson we’re going to be projecting Yahoo’s search advertising revenue using a tops-down methodology. And, you can see that I’ve already sketched out some of the numbers here, so you can get a sense for how exactly we’re going to be doing this.

But, essentially, we’re going to be starting at the top with the total number of global searches, worldwide. Ideally, we would like to get it from the years ’05 to ’07, so we get three years of historical data. Unfortunately, firms like CommScore, which tracks search engines, and search engine market share did not really do it on a global basis before ’07.

Yahoo, Google, Baidu, similar companies operate globally. So, ideally we want that on a global basis. In this case we don’t have it, so we’re only going to look at ’07 here, at least for the more detailed information.


In any case, we’re going to start with the total number of global searches. Then, look at Yahoo’s market share again, based on CommScore data, which is the firm that tracks this. We can use that to work into our number of search queries, right here.

We know what their search revenue is, or at least we have an estimate, based on the Morgan Stanley numbers from equity research, right here. So, based on that, we can get to their revenue per search year.

Now, the number search queries will be in millions, as you can see. And then the search revenue, of course, is also in millions as indicated by the label, up here. So, basically, everything is in millions, and so as a result, the revenue per search year when we divide these numbers, will be in dollars, as stated.

So, what are the key variables that we have to look at? Well, first off, we have to get to our total number of global searches, the year-over-year growth rate. This would be the total addressable market’s growth rate. In this case, we’re not measuring it in dollars.

Instead, we’re measuring it in search queries. That is perfectly acceptable. That’s fine, and in this case, it’s actually better than measuring it in dollars, because different companies will monetize this, differently.

So, Google, for example, is notoriously good at monetizing search, and is much better than companies like Yahoo and Microsoft. So, as a result, even if they have the same number of search queries, they’re probably going to be making more money than Yahoo or Microsoft. So, in this case, it’s actually more appropriate to look at the total number of global searches.

Once we have that, then we can get to Yahoo’s market share, which we can estimate from various sources, by using filings, and investor presentations and then we can figure out what their revenue per search growth is.

So, once we’ve backed into our number here, for revenue per search we can estimate what their growth rate going forward is, again based on filings, equity research, investor presentations, and other sources.

So, first, let’s start with these two numbers, up here. I’m going to explain exactly where I got them. You can look at the footnotes here by hovering, your mouse over them, and you can see that, basically, I am using CommScore data.


Which CommScore data, specifically? Well, if you pull up the handouts right below this course, if you pull up the one labeled “August 2007″ and go to the second page here, they’re giving an estimate of the total number of searches, worldwide.

They break it out by Google, Yahoo, Baidu, Microsoft, and so on and all these other sites. If you do the math here, I’m not going to show this right now, but if you do the math, you take Yahoo’s number and divide by the worldwide searches it comes out to around 14%.

In other words, as of August 2007, Yahoo had around 14% of the market share here and there are an aggregate total of 61,036 million searches worldwide, which really means in this case, 61 billion, because these units are in millions already; 61 thousand million’s equals 61 billion. So, we have 61 billion searches in just August, right here. So, we have that. Unfortunately, CommScore doesn’t give us a whole lot of data way back in 2007.


So, the only other report we can use here is one from December of 2007, as well. And here they’re giving the total number of searches worldwide for December 2007. So, you have 66 billion worldwide. Yahoo has 8.5 billion. If you do the math here, it comes out to around 12.8% or 13% market share.

So, Yahoo’s market share had certainly dropped from August to December 2007, but the number of searches worldwide has grown greatly on a monthly basis. It’s up by around 5 billion over the August or July numbers. So, based on that, how can we get to the total number of global searches? Well, what I’ve done here if you look at the formula.
I have just assumed an increase here, and increase, assuming that each month on average it goes up by around 1 billion searches, extra per month. So, I’ve kind of backed into the numbers. I took the 61 or 62 billion number from August, I incremented that between August in each month, until December by 1 billion in each month; September, October, November, December, and I got to the number like that, I’m rounding a little bit here, because this is imprecise to begin with.


And then, I’m assuming that way back in January, we started at 55 billion. Again, it would be ideal if we had the real data, and the actual numbers from each month, here. But we don’t have it, so the best we can do is estimate this, and assume a constant growth rate on a month-over-month basis, here. That’s where I’m getting this number from.

Now, for the market share, so according to CommScore they’re saying 14% in August, and 12.8% in December, I’m going to assume a discount to that, the reason being that CommScore is a US company. In many cases, the data from US companies is biased toward US and Western Europe sometimes, the search market share, there.

So, I’m assuming that CommScore is over counting it by a little bit, and I’m going to say that this is 12.5% instead. Again, we’re not being 100% scientific here. It would be better if we got Yahoo’s own internal numbers, but we don’t have that. All we can do is estimate, based on other public sources of information.


So, not a scientific estimate, but it is not going to matter all that much, because all that really matters here is how this changes over time, and how the revenue per search changes over time. These initial percentages and numbers, they’re fine.

But what we ultimately care about is how they’re going to change in future years. So, don’t get too hung up on this. These are very imprecise estimates, but all that’s going to matter is how they change, and how much they’re really growing in future years.

So, to get the total number of search queries, what we’re going to do here is take the market share (which is a percentage), and multiply it by the number of global searches. So, we get to 90 billion queries for Yahoo, in 2007; that’s our estimate. Now for the revenue per search calculation.

So, we’re going to take the $1.2 billion search revenue, and then divide by the number of search queries. We get to around 1.3 cents for revenue per search.

It may not look like a lot to you, but keep in mind that we’re dealing with billions; tens of billions of searches, here. So, you can see how even though it’s only around 1 cent per search, it can quickly add up to very large numbers, such as a $1.2 billion.


So, now we’re going to start by estimating the number of global searches worldwide, and how fast that is growing. You can see right here, that if you want to look at the footnotes, you can get a preview of where I’m going to get these numbers from. What you want to do is turn your attention to Yahoo’s investor presentation, once again, the one that I went through in the overview video.

And specifically, to save time here, what I want you to do is to go to management expectations of revenue, right here. I’ve pulled out a few select pages from this presentation, just to save us some time. So, we know that in the management case search revenue should be growing by around $1.4 billion from ’07 to 2010.

We also know on the second page here, the second page is dealing with search growth. The fourth page is dealing with display advertising growth. So, let’s look at these numbers in more detail. The one of the left side tells us the volume, in other words, how much the search queries are actually growing, and then the one on the right is telling us how much the revenue per search is growing by.


So, Yahoo is saying that, if you read the footnotes here, the US plus EU total search query growth was 16%, year-over-year from ’06 to ’07 here. We know that their US web search grew by around 13%. Based on that I am just estimating, and calling this at 15%, because they’re saying that, US plus EU is 16%, and US only was 13%.
If you throw in emerging and developing markets, as well, it’s probably going to be somewhere below the US plus EU growth, but slightly above the US growth. So, I’m saying 15%, here as an estimate.

Now, Yahoo is saying that their global total queries are going to grow between 10%, are going to grow actually at 10% for them, between ’08 and 2010. They’re saying that the market as a whole is going to grow between 10% and 14%.

In this case, we’re concerned with the market figures, because we’re starting with the market figures and then looking at Yahoo’s market share and backing into our numbers, like that. So, in this case, we’re going to go with the 10% to 14% numbers right here, for the growth of the global market as a whole, between 2008 and 2010.


For the two years beyond that, we are just going to estimate based on the direction, and the trajectory of these numbers. So, let’s go in and fill in these numbers, now. So, for the management case, this one as you can see right here, we’re going to start it at 14%, and then decline by 0.5% each year. This one is going to correspond to the upper end of the 10-14% range given in their presentation, so 14%, 13.5%, 13%, 12.5%, and 12%.

Then for our base case right here, we’re going to assume, as I say in the footnote, we’re going to take the middle of the range, instead. So, we’re going to start it at 12%, and then decrement it by 1% each year, until we get down to 8% at the end.

And then, for the conservative case, we’re going to start at the bottom of the range that they give here, of 10%, and we’re going to say 10%, 9%, 8%, 7%, and 6%. So, we have that in place. And again, a footnote in that, in case you want to come back to this and check it later, and remember where all these numbers are actually coming from.


Now, how do we select which one we’re using in the given case and make sure that our global searches here are correct? Well, we’re going to use the OFFSET function in Excel, which you should know if you’ve been through the Excel course as part of this program. So, for the reference we want the cell right above it. For the number of rows to go down, we want the case number, and then for columns, I’m going to say 0, and copy this across.

So, of course, if we go in and change this to the conservative or management case, it’s going to change to 10% or 14%. Let’s go back here just to test this out. We can see a change of 14%, now. In the conservative case, it changed to 10%, so we know that our formula is working correctly. So, we have the total number of searches now for Yahoo’s search market share. We don’t really have a good sense at this, and it’s not really addressed in their presentation, at all.


So, what we’re going to do instead, since we don’t have any data here, and since the only data point we have is the CommScore information, from August and December 2007 we’re going to take a different approach. And what we’re going to do is assume that in the management case, I say here we’re going to optimistically, assume a rise to correspond with Yahoo’s estimated query growth.

Remember that back in Yahoo management and investor presentation, right here, so they’re saying that their growth is going to be 10%. The market as a whole is going to 10% to 14%. Actually, thinking about it, thinking about these numbers, their market share may actually still be declining, or staying about the same since they’re not growing faster in the market, here. In fact, they may be growing a little bit slower than the market.

But nevertheless, we’re still going to be optimistic here, and assume that their actual market share grows year-over-year. I’m going to start by saying 12.5%, then 13%, 13.5%, 14%, and 14%. Now, in the base case, we’re going to assume a slight decline here. And this one actually is probably, going to be more accurate, and closer to what they have in their management presentation.


Because, remember again, they’re saying that they’re growing by 10%, but the market as a whole is growing about the same or slightly faster, which implies that their market share may actually be declining slightly, here. So, for this one, we’re going to say 12%, then 11.5%, 11%, 11%, and 10.5%.

And then finally, for the conservative case, as you can see from the footnote, we’re going to assume a steeper decline than in the base case, here. So, I’m going to say 11%, 10%, 10%, 9%, and 9%. We have those numbers in place, once again I’m going to go up, use the same OFFSET formula, and we can see that this is pulling from the base case now, which is exactly what we want.

Now, for the final step, we have to look at how their revenue per search is going to stack up, and grow over time. Let’s go back to the investor presentation once again, and let’s go to the right side of this page.


So, back in 2007, back in 2008, in this timeframe Yahoo has this major initiative called “Panama.” That was supposed to improve their revenue per search and make them on par, or at least close to what Google was getting; Google, being notoriously better at monetizing search.

So, they’re claiming their US web search, the revenue per search, grew by 15% in 2007. They’re estimating that it’s going to keep growing by 15%, in 2008 to 2010, which of course is widely optimistic. And they think that the market as a whole is going to grow by 8% to 10%.

So, in other words, they’re basically saying that they estimate they’re going to grow faster than even Google here, from 2008 to 2010. That’s not going to happen. It’s completely out of line with expectations. So, we’re not even going to pay close attention to that estimate here.

If you go in and look at the footnotes here, you can see that the US plus EU total query revenue per search was 11% year-over-year from ’06 to ’07. Eleven percent for US plus EU versus 15% for just the US, this is also telling us that the US will probably, well actually definitely did grow more quickly than the world as a whole.


So, we’re going to be assuming a discount to this 15%, because this is only the US, we want worldwide numbers here, and Yahoo is not giving us that. They’re assuming that worldwide it’s going to grow at 15%, even though in the US plus EU it only grew at 11% in ’07. So, we’re going to have to assume discounts to many of the numbers, here.
So, what we’re going to say is in the management case, we’re going to actually sort of agree with their estimates, in that we’re going to go with the 10% to 15% range. We’re still not going to be at exactly 15%, as they project, but we’re going to assume something in that range, to at least start with.

If you’re wondering about the 10% number here, I’m saying that the US plus EU growth was 11%. I’m discounting it to around 10%, because in emerging markets, places like China, India, etc., the monetization is much tougher, because there’s less money to go around, consumer spending is not nearly as high, and so the revenue per search is going to be quite a bit lower. The growth is going to be lower in those regions, as well.


So, I’m discounting this from the 11% in the investor presentation, for the US plus EU, to 10% here. So, in future years, in the management case, I’m going to say 11%, 11%, 11%, then 10.5%, then 10%. For the base case here, I’m going to assume a slight decline each year from this initial 10% number that we’re estimating.

So, we’re going to start this one at 9%. You can look at my footnote here, and I’m saying that again, I’m assuming here that basically we’re going to be in line with the market, instead of what Yahoo is saying in the base case. Now, if you think about the way these estimates work, in the base case we’re going with what the market, what Wall Street thinks.

If the market as a whole is growing at 8 to 10%, that makes sense to use this range for our base case, so 9%, then 8.5%, 8%, 7.5%, and 7%. Then for the conservative case right here, we’re going to be assuming an even sharper discount, as I say in my footnote.


So, we’re going to be starting below that range of 8 to 10% given in the investor presentation. I’m going to say 7%, 7%, 6%, 5%, and 4%. Now, let’s get the OFFSET formulas once again, and then get to our total search advertising revenue, right here. So, we have all these numbers now, let’s zoom out a little bit, so we can get a handle on what our total revenue here is going to look like.

So, for the total number of global searches this is going to be a percentage growth based on our cases, right here. So, we’re going to take our existing number, multiply it by 1 plus the selected growth rate. For Yahoo’s market share, we’re going to link directly down to Yahoo’s search market share.

For the number of search queries we’re going to multiply the market share percentage times the total number of global searches. For the revenue per search we’re going to take their old number, and then multiply it by 1 plus the growth rate, right here.


And then, for the total search revenue we’re going to multiply our revenue per search, by the number of search queries. So, we get to $1.4 billion in the second year here, in 2008. Now, let’s copy this formula across. We’re just going to ignore this error for now; it’s giving an inconsistent formula error in Excel.

So, let’s copy this formula across, and see what we get. In the base case, it comes out to around $2.4 billion of search revenue in 2012. Let’s change it to the conservative case and see what happens. It comes out to about $1.6 or $1.7 billion. And then in the management case, it comes out to around $4 billion of search revenue.
Now, let’s quickly just check and see what Yahoo is saying for their own numbers in their management presentation, here. Go back to the page where they give their revenue bridge, so they’re saying that search is going to grow by around $1.4 billion. We’re looking at the 2008 to 2010 range.


We’re saying here, that it’s growing by around $1.4 billion from ’08 to 2012. It’s going from $1.2 to around $2.5 or $2.6 billion. That’s actually very close to the estimate they have there. So, we’re in line with their numbers in the management case.

Let’s see in the base case. Let’s just check this against the Morgan Stanley numbers quickly, and see what they have. So, they’re saying that sponsored search is going to grow to $1.5 billion in ’08 and then $1.8 or $1.9 billion in ’09, and our numbers are a little bit below theirs. We have $1.8 billion in 2010, instead.

But we’re roughly in the right range of the Morgan Stanley numbers, so at least we’re not widely off the mark, compared to what they have. So, that’s how we go in and estimate the search advertising revenue for Yahoo here, based on filings, the investor presentation, equity research, and other sources.


So, that concludes our explanation, and projection for Yahoo’s search advertising revenue. Coming up next, we’re going to jump into their display advertising, their second major source of revenue. This time around, we’re going to use a separate methodology called a bottoms-up projection, to estimate how much their display advertising revenue is going to grow over the next five years.

For more tutorials on Advanced Financial Modeling click here.