previous article
Starting Situation
The controlling division of a telecommunications business is to be supported regarding the forecasting of the monthly development of gross adds figures. "Gross adds" is the key figure which reports the gross new customer growth within a defined period, where the number of lost customers is not taken into account. The key figure "gross adds" is primarily used in the telecommunications industry and reflects the number of newly concluded contracts (postpaid and prepaid).
The forecast shall, in particular, serve two purposes:
1. Supporting the budget planning: by means of more realistic forecasting values, both marketing and sales forecasting can be simplified. Thus, the probability increases that the targets can be reached and the budgeted funds are sufficient.
2. Evaluating promotion campaigns: as the forecasting values are based on past, mean results, they should reflect a "normal" monthly value. If a successful promotion has taken place in a month, the difference between forecasting value and actual value gives an indication for the success of the promotion.
The following central question resulted from this starting situation: "How many gross adds are to be expected in March, if February had x?".
The approach should not only be pragmatic and robust but also usable for the controlling employees in Excel. In this context, a separate differentiation according to the acquisition channel of the new contract was desired, as well.
Solution Approach
In order to meet the customer's requirements, b.telligent has decided to develop a time series analysis. Accordingly, the forecasting value consists of both a trend factor and a seasonality factor and an error term. (However, this is neglected under the assumption that it is very low).
In this process, the trend factor is detected by a linear regression, whereas the seasonality factor is detected by an average value based on the previous months.
b.telligent assumed that special effects, such as e.g. one-off promotions, balance out by including a long past - in this case more than five years. In addition, the influence of holidays and vacation periods is to be considered.
Approach
In a first step, b.telligent defined a time period for the overall view of the time series analysis. To this end, the monthly "gross adds" values of the last five years were available. As the number of weekends and the number of holidays frequently vary per month and accordingly per year, b.telligent decided to use the monthly working days as basis for the forecast. At a value of 40,000 gross adds and a month of twenty working days, the value to be forecasted would be 2,000 gross adds per working day. By means of this approach, months of different length are taken to one level.
Next, b.telligent made two outlier tests of the input data and adjusted it accordingly.
Subsequently, the holidays were considered, thus, for example, Easter can be in March one year and in April the next year. In this process, b.telligent determined that due to the choice of the forecasting value, i.e. gross adds per working day, no separate holiday consideration is required.
After the preparation and quality assurance of the data series, the calculation of the trends was made by a linear regression and twelve seasonality values.
For the first run of the forecast, b.telligent used the last full three years as data basis and the current year as validation sample. Short excursion into statistics: the correlation coefficient amounts to more than 0.8, its square - the coefficient of determination - thus explains 65% of the variance.
Here, b.telligent determined that the regular marketing effects, such as the annual iPhone launch in September, are already included in the forecast. Thus, by means of the forecasting value of September, it can only be evaluated whether this year's launch effect is better or worse than that of recent years. However, it cannot be measured how efficient the effect is in total. This also means that the forecasted value is too high for a September without iPhone launch.
Analogous to this, forecasts for individual acquisition channels such as e.g. shops, online shops etc. can be created. In this process, it needs to be considered in each case that the history has to be long enough and the case numbers may not be too low.
In order to be able to use the forecast, one factor per month is determined for the next year based on the detected trend and seasonality values, and entered into an Excel table. Thus, only the number of gross adds per working day of the previous month has to be entered, whereupon this is automatically multiplied by the factor. The result is the forecasting value for the gross adds per working day in the current month!
Outlooks
It turns out that a simple time series with surprisingly good forecasting quality (for the statisticians: correlation coefficient of 0.8) is also possible without an analysis tool and can be created by means of a database, if necessary even completely in Excel. Provided that the input data is available in adjusted form.
However, b.telligent recommends to check the parameters on a regular basis - at least once a year- and to only carry out forecasts one month or at most two months in advance.
b.telligent's next task is to create a forecast on a weekly basis, where completely new and different challenges arise!
