To forecast call volume, take the total for the day, then apply the share of calls that arrive in each half hour. That gives you the 120 calls the Erlang sheet needs, in the right interval, instead of a flat average that is wrong everywhere. It takes four weeks of history and one spreadsheet column.
This is the missing piece in the series that started with my Erlang C calculator in Excel. The calculator is only as good as the volume you type into B1, and so far I have treated that number as a given. It is not. It is a forecast, and forecasts go wrong.
How do you forecast call volume by half hour?
Two steps. First forecast the day. Then split the day into intervals using a profile built from history.
Here is an example with made-up numbers. Suppose your last four Mondays brought 4,120, 3,980, 4,260 and 4,040 calls. Add them up and you get 16,400, so the average Monday is 4,100 calls. That is the day forecast.
Next, look at the 9:00 to 9:30 slot on those same four Mondays and work out what share of each day it was. If it averages 2.9% of the day, then 2.9% of 4,100 is about 119 calls. Call it 120. That is the number that goes in B1 of the calculator, and the sheet tells you to staff 25 agents for it.
Repeat for every half hour, and you have a staffing requirement for the whole day. The profile is the shape of your day. The day total is its size. You forecast them separately because they change for different reasons.
Why not just use last week’s volume?
Because one week is one sample. If last Monday had a billing run, a news story or an outage, you copied that spike into next Monday’s plan. Four weeks smooths out the odd one, and you can drop a week you know was abnormal.
The usual rule is to compare like with like. Mondays with Mondays. The first Tuesday of the month with the first Tuesday of other months if your business has a billing cycle. A flat daily average across the week will be too high on Saturday and too low on Monday, every week.
I would rather be clearly approximate than precisely wrong. A forecast that says “about 4,100, plus or minus 5%” is an honest statement. It also tells you how much buffer to hold.
What breaks the forecast?
Anything that makes the future different from the past. History cannot tell you about what you have not seen yet.
In December 2008, the UK government cut the standard rate of VAT from 17.5% to 15%, temporarily, starting on 1 December. The Register’s coverage of the pre-Budget report looked at what that meant for smaller businesses, which had to deal with the change at short notice. Any team supporting a retailer, a billing system or an online shop had a month where the last four weeks of history said very little about what was coming.
I am not claiming a particular volume went up or down because of the VAT change. I am saying that when something like that lands, your history is out of date, and the right response is to widen your forecast range and watch it daily rather than trust last month’s profile.
- Planned events. Promotions, price changes, mailings, launches, billing runs, a tax rate change. Ask the business for a calendar and add it to your forecast by hand.
- Seasonal effects. Christmas, school holidays, tax deadlines, the end of the financial year. You need a full year of history to see them.
- Unplanned events. An outage, a news story, a competitor’s mistake. You cannot forecast these, so you plan a reserve and an escalation route instead.
How do you know the forecast is any good?
Measure the error. Take forecast and actual for each half hour of the last four weeks, subtract, and divide the difference by the actual. Average the absolute values and you have a forecast error figure.
If your day total misses by 5% and your intervals miss by 15%, the day forecast is fine and the profile is what needs work. If the intervals are close and the total is out, you have a planning-events problem. Each failure has a different fix, and you only see which one you have by keeping score.
I covered a related idea in my post on leading indicators for support teams: the earlier you can see a change coming, the less you have to guess. A website visit spike, a mailing going out, a billing run starting, all of these predict calls before the calls arrive.
What about the intraday correction?
Even a good forecast misses on the day. So build in a morning check. At 10:30, compare the first three half hours’ actual volume with forecast. If you are running 15% over, ask whether it is a one-off or a day-long shift, and move people accordingly: bring forward a training session, cancel a meeting, call in the reserve.
I wrote about this pressure in intraday staffing after big news. The lesson is the same here. The forecast is a plan. The day is where you find out if the plan was right.
What does the spreadsheet look like?
Keep it simple. One tab holds raw history, with a row per half hour per day: date, weekday, interval start, calls offered. A second tab holds the profile.
- In the history tab, add a column for the day total using
SUMIFon the date, so every row knows how big its day was. - Add a column for the interval share: calls in the interval divided by the day total.
- On the profile tab, use
AVERAGEIFS(orSUMIFSdivided by a count, if you are still on an older version) to average that share for each weekday and interval across the last four weeks. - On the forecast tab, multiply your day forecast by each interval’s share. That column feeds B1 in the Erlang sheet, one interval at a time.
It takes an afternoon to set up. After that it takes ten minutes a week to refresh, and it replaces the number you used to pull out of the air on Friday afternoon.
How much history is enough?
Four weeks for the profile, if your business is stable. Thirteen weeks if you have a pattern that shifts with the calendar, and a full year if you want to see seasons at all. More is not automatically better. A profile from last Christmas will not describe next March, and one from before you changed your opening hours will not describe anything.
The failure I see most often is a team that keeps eighteen months of history and averages all of it. They are forecasting a business that no longer exists. Pick a window, write down why you picked it, and revisit it twice a year.
How does the forecast feed the rota?
One step at a time. The forecast gives you calls per half hour. The calculator turns calls into agents on the phones, 25 for the 120-call interval. Then you divide by one minus your shrinkage to get people on the rota, which is 36 at 30%. The three steps are separate for a reason: forecast error, model error and shrinkage error each need their own fix, and if you blend them into one number you cannot tell which is hurting you.
If you want a second view on the Erlang side, the Wikipedia overview of queueing theory gives the wider picture, and my own post on scheduling software compares the tools people use to do all of this at scale.
Where should you start?
Whichever way you build it, keep the old forecasts. Save each week’s forecast next to what actually happened, and after a quarter you will have a record of exactly where you are consistently wrong. It might be the first Monday after a long weekend, or the day after a mailing. Once you can see it in the data, adjusting for it takes one line.
Pull four weeks of half-hourly call volume for one weekday. Calculate the day total, the profile, and the 9:00 interval. Type the result into B1 of your calculator, and compare it with what the phones actually did next week.
It will be wrong. That is not the problem. The problem is not knowing by how much, and not knowing why. A forecast you check and correct each week gets better. One that you set once and trust gets worse.
Hutch Morzaria is a Director-level CX and Support Leadership professional with 19 years of experience building global support organizations across SaaS, Fintech, and enterprise technology. He has hired dozens of support and CX leaders across his career and holds ITIL Expert certification across V3 and V4.
