Call volume forecasting for a help desk needs three things in a spreadsheet: a moving average of the same weekday over the last four weeks, a seasonal index that lifts or lowers it for the time of year, and an intraday curve that splits the day into half hours. That’s it. You can build all three in Excel in an afternoon, and the result will beat a clever model nobody on your team can explain.
The reason it matters is simple. Every staffing number downstream starts here. In my post on what a 10% forecast miss does to Erlang C the miss cost two agents in a single half hour, and that was a small miss. Get the forecast roughly right and everything after it has a chance.
Why forecast each weekday on its own?
Because Monday isn’t Thursday. Most desks I’ve seen run a clear weekly shape: Monday is the heaviest day, the week tails off, Friday afternoon goes quiet. If you average all the days together you flatten that shape out, and your Monday forecast ends up too low every single week while Friday ends up too high.
So forecast Mondays from Mondays. Put eight weeks of actual calls offered in a column, one row per Monday. Here’s an illustrative set:
| Week | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|---|
| Monday calls offered | 1,140 | 1,210 | 1,185 | 1,260 | 1,230 | 1,300 | 1,275 | 1,320 |
The four-week moving average for next Monday is the last four values averaged: (1,230 + 1,300 + 1,275 + 1,320) / 4 = 1,281. The eight-week average is 1,240. That gap of 41 calls is the trend you’d throw away by using too long a window. In Excel, with Mondays in B2:B9, the forecast is =AVERAGE(B6:B9), and as each new week lands you add a row and the range moves down with it.
Why four weeks and not eight? A short window reacts to a rising trend; a long one smooths out noise. Every moving average lags behind turning points in the data, and the longer the window, the later it notices. On a growing product four weeks is a fair trade. If your volumes are flat and noisy, go to six.
If you want the recent weeks to count for more, weight them. With weights of 10%, 20%, 30% and 40% from oldest to newest, the same four Mondays give 1,294. In Excel that’s =SUMPRODUCT(B6:B9,{0.1;0.2;0.3;0.4}). It’s a small change, and I’d only bother with it once the plain average is in place and being checked.
How do you add seasonality without a statistics degree?
A four-week average knows nothing about next month. If November is your busy season, it will walk straight into November at October’s volume, three weeks late on every rise.
The fix is a seasonal index built from last year’s numbers. Take the average weekly volume for each month last year and divide it by the average week across the whole year. As an illustration, if last November averaged 7,080 calls a week and the year averaged 6,000, November’s index is 1.18. Multiply your moving average by it: 1,281 x 1.18 gives a Monday forecast of 1,512 for a November Monday.
In Excel, keep a twelve-row table of month and index in, say, E2:F13, and the forecast becomes =AVERAGE(B6:B9)*VLOOKUP(MONTH(A10),$E$2:$F$13,2,FALSE), where A10 holds the date you’re forecasting. One year of history gives you a rough index. Two years and you can average them, which takes the edge off one odd month.
Be careful with the multiplication, though. If your four-week average already includes some of the busy season, multiplying by the full index counts the lift twice. The cleaner version is to divide each historic week by its own month’s index first, average those, then multiply back by the target month’s index. It’s one extra column and it stops the double count.
For a service desk behind an eCommerce platform, the calendar that matters is the retailers’ calendar, not ours. Their promotions and holiday trading weeks are what fill the queue, so that’s the history the index has to come from.
How do you turn a daily forecast into half hours?
Erlang C doesn’t take a daily number. It takes calls per interval. So the last piece is the intraday curve: what share of a typical Monday’s calls arrive in each half hour.
Pull the last four Mondays by half hour, add each interval up across the four weeks and divide by the four-week total. You get 20-odd percentages that sum to 100%. In a typical example the 10:00 to 10:30 slot might carry 9.4% of the day. Multiply: 1,281 x 9.4% is 120 calls in that half hour.
That 120 is the exact input in my Erlang C calculator in Excel, which needs 25 agents logged in to hit 80/20 at a 300-second handle time. Now the chain is complete: weekday history, seasonal lift, half-hour share, agents. Each step is a cell you can point at when somebody asks where the number came from.
Build a separate curve for each weekday. Monday mornings spike harder than Thursday mornings, and one shared curve will put your people in the wrong half hours even when the daily total is right.
What goes wrong with call volume forecasting?
The most common mistake is forecasting from the wrong number. Use calls offered, not calls handled. If 8% of last week’s callers hung up before anyone answered, a forecast built on handled calls bakes that 8% shortfall into next week’s staffing, which produces the same abandonment again. The forecast becomes a loop that keeps you short.
The second is letting a one-off event poison the average. An outage that doubled Tuesday’s volume will drag the next four Tuesdays up with it. Replace that day with a normal value in the history column and keep a note of what happened. Events you know about in advance get their own line in the plan, which is a different job, and I covered how I’d handle a known event like Inauguration Day separately.
The third is never checking. Every week, compare the forecast with what actually arrived and work out the mean absolute percentage error: the average of how far off you were, ignoring the direction. With forecasts in C2:C6 and actuals in D2:D6, =SUMPRODUCT(ABS(D2:D6-C2:C6)/D2:D6)/COUNT(D2:D6) does it without an array formula. In one illustrative week, daily misses of 0.8%, 3.4%, 7.6%, 8.2% and 0.8% average out to 4.2%. Watch the direction too. If you’re under every day, the method has a bias and the window or the index needs adjusting.
Why does a simple model beat a clever one?
Because someone has to defend it. When the Monday queue blows out and a director asks why, “the four-week average was 1,281, the November index is 1.18, and the 10 o’clock slot carries 9.4%” is an answer that gets you a decision. “The model said so” gets you a second meeting.
A clever model also tends to hide its mistakes. Regression with a dozen variables can fit last year beautifully and still miss next Monday, and when it does nobody can tell which input went wrong. With three pieces, a miss points straight at one of them. That’s the same argument I made about why most schedules fall apart in the gap between plan and floor: what you can see, you can fix.
Once the forecast is landing within about 5% most days, stop polishing the method and start recording the reasons behind the misses. A column of notes saying “billing run”, “email campaign” and “outage” is worth more to next year’s forecast than any extra decimal place.
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.
