This is the fifth post in a series about online advertising forecasting. This post is on how to forecast ad revenue. Thus far we have covered:
- Why do you need to reconsider your forecasting models?
- What are the current methods used to forecast advertising revenues?
- What are the required data points, for what period and from which sources, needed for proper advertising revenue forecasting?
- How to forecast your website unique visitors and page views?
Where does this come from?
I used my forecasting tool for 3 years, constantly keeping it up to date; even though some numbers started varying from month to month where projected and actuals were concerned, when I looked at quarterly or the annual forecast it ended up bang on with the actual yearend figures.
Two posts back (require data points) I spelled out which data points you needed on a monthly basis, going as far back as you could. Here I will explain in detail how to set up your ad revenue forecasting tool. Here again are the data points you’ll need:
– Monthly actual (final and net) sales figure per sales team per website, or a clustered contextually similar bundle of sites.
– Monthly budget objective per sales team per website
How to forecast ad revenue
For this exercise, I’ve remained with the same website I used to manage as an example. You’ll see I’ve filled in on the horizontal months. They actually start at December 2005, but for the sake of clarity I’ve only shown here 2009 through to the end of 2014. In other words I’ve taken the last three years’ worth of data for forecast the next 2. Ideally, you should try to go as far back as you possibly can, even if it’s only one month here and there – it’ll give you more background on which to rely.
As I’ve stated in my initial post in this series, the websites I used to manage had sales coming in predominantly from 2 sources: a local sales team dedicated to a radio station and its website; and a national sales team that sold every website we managed and a few others. There were other sources, but for this demonstration we’ll keep it to those 2 sources.
Let us begin by plotting monthly actual local sales for as far back as you have the data up to the last completed month. Ignore the “Projected” line for the moment. You might want to add a “Budget” line to compare actual to budget, and eventually projected to budget to see if you’re likely to achieve it or not.
Next you’ll plot national sales the same way.
You’ll add as indicated a sum of both in the Total Sales “actual” line (i.e. =an13+an17). Let us skip the “initial projection of sales” and its index, along with the “Original UV projection,” we’ll come back to that in a minute.
You can click on the image below to see it full size.
Now you can calculate your Local RPMUV (Revenue Per Thousand Unique Viewer) and National RPMVU. Your total will simply be an addition of both of these. To calculate each, you’ll first take your actual local (or national) sales for that month, divided by your actual UV count for that month (divided by one thousand). The formula should look like this: =AN13/(AN3/1000).
You can also calculate your eCPM if you so desire. Here I calculated it only on page views, but you could further add a line in the traffic section of this spreadsheet for total ad impressions if you’d rather look at it that way.
Tracking your average RPMUV
In order to forecast revenue in the future, you’ll need to know the average RPMVU for a given month over the past few years. Looking at the chart above, we want to make this average in cell BX29 for local sales, and BC30 for national sales. In both you want to calculate the average RPMVU for January of every prior year you have data for. The formula should look like this for local, the same for national but on the right row: =average(BL29;AZ29;AN29;AB29;).
Forecasting Future Sales
In order to forecast what your local sales team, and what your national sales teams should achieve in sales performance for a given website, for any given month in the future, you will finally need the two pieces of information which we’ve been constructing until now:
– Forecasted monthly reach (UV) per website
– Average monthly RPMUV per website, per sales team
What we want to start filling in automatically based on our two pieces of collected and averages data starts in cell BX14 for the local sales team and BC18 for the national (again, these could be any two distinct sales teams that can bring in revenue to a particular website – they could be sitting next to each other or be in different markets, it doesn’t matter, but being in different teams with different realities affects each team’s performance enough that you cannot bundle them together). The formula for predicting what your sales team should achieve (if no major unforeseen event occurs) is by multiplying your projected reach (UV) figure for January 2013 in BX3 by your average RPMUV for the month of January up to 2012 in BL29, and so one for every month going forward. The formula should look like this: =BX3*BL29/1000.
Benchmarking your forecasts
I took the habit of benchmarking my forecasts for both reach and sales, up to 3 years out, to see down the road how accurate my initial predictions were vs actual, and how much they varied over time as new monthly actual traffic and sales figures became available. This is how you’ll know this tool works, and how you’ll convince others around you that your predictions are rock-solid.
That’s why you see line 24: Initial Sales Projection; and in line 26: Original UV projection. I recommend that once you’ve done this exercise, as I’ve shown above in image 2, cut & paste as figures (not formulas) your initial sales projections and UV projections for the coming 12 months or fiscal year.
Next add an index of that initial projection against your eventual “actual” figure to see how close you were. The index will rarely be 100%, but should be relatively close to it as an annual average – unless major events occur that you had not anticipated.
I personally went 1 step further. I tallied up in a separate sheet the annual projection next to the date, every time I made changes to this forecasting tool. Explained differently, every month when I added actual sales figures and UV numbers for every website and sales team under my responsibility (like I said before there were 4 distinct revenue sources and 14+ websites), I would in a separate sheet note the date and the new total for that year (summing the forecast for my fiscal period which followed the broadcast calendar). Every month new numbers would swing the annual total for this year, the next and the one after that a little up and down. Needless to say I did not keep those files when I left, but I can assure you that ultimately, the 3-years-out tracking swung up and down quite a bit as all manner of unforeseen events occurred and affected things, but the average remains fairly close to the initial prediction – which to me validated the whole effort.
Do you have a different and equally solid projection model? Please share your thoughts and comments.
