Hi All,

Ive been tasked with completing some forecasts for the next 12, 36 and 60 months. As this is something ive never completed before ive no idea where to start. the only call data i have available is from 2009. the management team are not after daily or weekly just an overall monthly figure. as im newish into the forecasting arena, i only currently forecast for calls in the next month broken down to daily. so where do i start, any help in excel would be a massive.


richard

Hi Richard:

If you have sales projections or anticipate taking on a certain amount of new business then you can scale the 2009 data accordingly. Some of the contracts might have a projected start date so any ramp-up should be timed appropriately. If there are projections to close outlets or trim back product lines then staffing would be scaled down in a similar fashion. You may also wish interview your department managers to solicit their growth/shrinkage projections.

Let’s say you have been provided with no such projections and you've been given the task of extrapolating purely based on past data. Conventional wisdom tells you that this exercise requires more that one year of data. Each year has seasonality patterns that move up and down. For this reason, you can't reasonably use any portion of the year to extrapolate into future years. For example, you can't say December was 50% more active than November so I'm going to extrapolate on a 90 degree slope. It is not reasonable to do so because December may just be seasonally 50% higher than November.

When you do have multiple years of data, long term forecasting is not rocket science. You use the seasonal pattern of the past year and gross the future years up by the annual growth rate. If the Growth rate looks linear then the math is pretty simple. If the growth is accelerating or decelerating then you can use a variety of approaches to extrapolate on a curve.

Now if you are lacking historical call volumes but have historical staffing figures, you can use the staffing levels as a proxy for past demand. If you don't have historical staffing levels, you can use historical payroll costs as a proxy for staffing levels. This can make you look pretty smart in front of those who have set you up with what might have been seen as an unsolvable puzzle.

Looking smart is nice but if you want to look brilliant, you need to recognize that whether you use payroll, staffing levels or call counts & durations, all of these measures are what can be called capacity based measurements. They will only measure the work that you are doing and have no potential to measure the work that should have been done. For example if you sent 500 agents worth of work to a call centre that had only 400 agents then the handled call data would never indicate more than 400 agents worth of demand. This is a fundamentally flawed basis for forecasting.

If you want to plan to maximize revenue and minimize customer churn then you need a super capacity forecast. Feel free to contact me if you are interested in the subject of super capacity forecasting.

Kind Regards
Paul. Kasanda@L3Prime.com

Often I have been in simlar situation when I did not have more than a years data to forecast for more than 12 months.

2009 data may not be very useful since it was economic downturn and it may give you wrong trending.

However what you can do is analyse month on month % changes from Jan 2009 to say March 2010 and use those % over lastest actual volumes i.e March'10. So in a way you are not forecasting the volumes but predicting the % changes. You can treat Jan'10, Feb'10 and Mar'10 variance as a benchmark and tweak % for rest of the year accordingly.

You can overlay new/lost business inputs on this forecast if available. and apply YoY volume change to subsequent years.

This will only give you directional forecast and you will have to continue updating the forecast every month.

Let me know if you need more clarifications.