Forecasting Ticket Volume Simply, With One Spreadsheet

Wednesday, 11:00, at Ashgrove Housing. Leon runs a desk of five for a housing association of six hundred staff. The finance director has sent him the e-mail that arrives every autumn: "For the budget round, how many tickets do you expect next year, and how many people will you need?" Leon has three years of monthly ticket counts in the system. That is more data than most people who answer that question have ever had. He has no idea how to turn it into a sentence the finance director will accept. His draft reply says "about the same as this year, maybe a bit more". He knows how that will go. He has watched it go that way twice.

A volume forecast is a reasoned estimate of how many tickets will arrive in a future period. It is built from the counts of past periods and from what the organisation already knows is going to happen. "Simply" means four ingredients and one spreadsheet: a base, a seasonal multiplier, a growth line and a list of known events. Each is on its own row so that anyone who disagrees can point at the row they disagree with. No models, no software beyond the spreadsheet, and no number without a range around it. This article is part of our Ticket Volume Analysis series. It is the one the rest of the series has been building towards. Counting properly gives you the base. The trend and season reading gives you the multiplier and the growth line. This article puts them together and turns the result into hours and people.

It covers what a forecast is for and what it is not. Then come the four ingredients in turn: the base, the seasonal multiplier, the growth line and known events. Putting them together for twelve months follows, with a worked example from a fictional desk. Next come turning tickets into hours and hours into people, and checking the forecast against reality every month. A short war story and the forecast worksheet to copy finish the article, in that order.

What the Forecast Is For

A forecast has three uses on a small desk, and they need different horizons. The rota needs next month: which weeks will be heavy, when to refuse leave, when to schedule the desk's own project work. The budget needs next year: how many tickets, how many hours, how many people, and whether any of those is changing. The business case needs a what-if: what happens to the desk if the new system goes live in March, or if the self-service reset project removes forty tickets a month. One spreadsheet serves all three, because the ingredients are the same and only the horizon changes.

A forecast is not a prediction, and the difference is the range. "Next September will bring 768 tickets" is a guess wearing a decimal point. "Next September will bring somewhere between about 690 and 850 tickets, most likely around 770" is a forecast. It is the honest one, because the past Septembers it was built from varied by that much themselves. Report the range every time, say what it rests on, and let the reader pick the point inside it that suits their appetite for risk. That is their job. Yours is to make sure the range is true.

Ingredient One: The Base

The base is the average monthly count of created tickets over the last twelve months, which is the twelve-month rolling average from Trends and Seasonality taken at today's date. It uses twelve months so that every season is in it once and the yearly shape cancels out. It uses created rather than closed because a forecast is about demand. The counting rules for created, including what to do about duplicates, spam and definition changes, are in Counting Tickets Properly. At Ashgrove, the last twelve months summed to 7,320 created tickets, so the base is 610 a month.

Check the twelve months for anything that should not be in a base. Look for an outage month, a migration month, the month the desk started logging phone calls, or a month with a mail loop that created three hundred junk tickets. Each inflates or deflates the base by something that will not recur. Either take the odd month out and replace it with the average of its neighbours, saying so on the sheet, or leave it in and widen the range. Do not quietly leave it in and quote the base to the nearest ticket. The base is the ingredient every other number is multiplied by, and a base that is a twentieth too high makes every month of the forecast a twentieth too high.

Where the organisation's size is changing, carry a second base alongside the first: tickets per hundred staff per month. Ashgrove's six hundred staff and 610 tickets a month give about a hundred per hundred staff. That is a convenient coincidence and not a benchmark; your figure will be whatever it is. The per-head base is the one that survives a merger, an office opening or a redundancy round. You can multiply it by the new headcount and have a forecast when the raw base has become meaningless. Keep both. The raw base is the one people ask for; the per-head base is the one that is still right when they ask again. If you run QuerySolv, the created-per-month figures come straight out of the standard volume report. The forecast itself lives in a spreadsheet, as it does for every tool I have used. The tool knows your tickets and not your plans.

Ingredient Two: The Seasonal Multiplier

The seasonal multiplier for a month is how much busier or quieter that month usually is than an average month, expressed as a number around one. A September that averages 740 tickets in an organisation whose average month is 612 has a multiplier of 740 divided by 612. That is about 1.21: September is a fifth busier than average. An August that averages 480 has a multiplier of about 0.78. In plain spreadsheet words: for each calendar month, take the average of that month's counts across the years you have. Divide it by the average of all months across the same years. The twelve multipliers should add up to about twelve; if they do not, a month is missing or double-counted.

The multipliers need at least two years of counts, and three before they are trustworthy, because a single year's September includes whatever happened that particular September. Ashgrove had three years, and its twelve multipliers ran from 0.78 in August to 1.21 in September, with January at 1.14, October at 1.12 and December at 0.85. The remaining months sat within a few hundredths of one. Those seven ordinary months are the reason the forecast is simple: for most of the year, the season does almost nothing, and the base is the forecast. The multiplier earns its keep in the four or five months that are genuinely different, and those are the months the rota and the budget care about.

With only one year of data, use that year's multipliers. Say, in the sheet and in the report, that the season is based on a single year and may be off by a tenth either way. With less than a year, do not compute multipliers at all. Instead, ask the business when its busy periods are and write them in as high, normal or low. Use 1.2, 1.0 and 0.8 as the multipliers until the data exists. That is a guess, and a labelled guess in a forecast is fine. An unlabelled one is the thing this article is against.

Ingredient Three: The Growth Line

The growth line is how fast the base is rising or falling from year to year, taken from the last two twelve-month totals. Ashgrove's previous twelve months summed to 6,720 against this year's 7,320, a rise of six hundred, which is about nine in a hundred. Call it nine percent a year, and apply it gradually across the forecast year. A month six months out gets half of it and a month twelve months out gets all of it. In the spreadsheet, the growth factor for a month that is k months ahead is one plus the yearly growth times k divided by twelve. Nine percent, twelve months out, is a factor of 1.09; six months out, 1.045.

Growth has a cause, and the forecast should name it, because the cause decides whether to carry the growth forward at all. Ashgrove's staff had risen from about 560 to 600 over the year, which explains most of the nine percent. The rest was a new repairs app that generated tickets its predecessor had not. Growth from headcount continues if headcount continues to grow, and HR knows whether it will. Growth from a new system is a one-off step that should not be carried forward as a line. Growth from the desk logging more of its work is not growth at all. Ask where the six hundred extra tickets came from before drawing a line through them. If the answer is "more staff, and we are hiring again", carry it; if it is "the repairs app", add the step and set the growth line lower.

Never carry a growth line more than a year ahead without re-checking it. Never carry a steep one at all without asking whether its cause can continue. The war story below is about exactly this. Growth is the ingredient most often used to make a forecast say what somebody wants it to say, in either direction, and it is the one to be most boring about.

Ingredient Four: Known Events

Known events are the things the organisation already knows will happen that will change the count, and they go on the sheet as explicit lines, never hidden inside the growth. A new system going live produces a launch spike, a fall and a permanent settle, and the shape and its usual proportions are described in Volume by Category Over Time. An office opening or closing changes the headcount from a known date. A self-service project removes a known category from a known month. A change in the desk's own scope, taking on facilities requests, or starting to log phone calls, changes the definition and therefore the count. Each is a row with a month, a size and a range, and each is a thing somebody told you, which means somebody can be asked how sure they are.

Ashgrove had two. The new housing-management system was due to go live in March. From the quoting-system experience elsewhere in the library the desk estimated a launch bump of about 120 extra tickets in March, 60 in April and 40 in May. It estimated a settle of about 25 a month from June onward, as the permanent load the system would add. And the self-service password reset project was due to complete in May. The desk expected it to remove between 30 and 60 of its 80 monthly reset tickets from July, once people had enrolled. It carried 45 as the middle figure. Both estimates were written on the sheet with their source and their range. When March came and the bump was 140 rather than 120, the sheet could say by how much the estimate had been wrong. Nobody had to pretend it had not been an estimate.

Putting It Together

The forecast for any month is the base, times that month's seasonal multiplier, times the growth factor for how far ahead it is, plus or minus the known events landing in that month. Take next September at Ashgrove, nine months out from the January start of the forecast year. First, 610 times 1.21 is about 738. Times a growth factor of 1.0675, that is about 788. Plus the 25 settle from the new system and minus the 45 from the reset project, that is about 768. The range comes from two places: the year-to-year wobble in the past Septembers, which was about eight percent either side, and the ranges on the two events. Applied together they give roughly 690 to 850. That is the sentence Leon sent instead of "about the same, maybe a bit more", and it is the sentence a finance director can put in a budget paper.

The table below is the full twelve months at Ashgrove, every number a worked example from this fictional desk and none of them a benchmark for yours. Read the columns left to right as the ingredients: the multiplier, the base line after season and growth, the events, and the result as a range. The last two columns are the next section. Notice how little the forecast moves for seven of the twelve months, and how much it moves for the other five. The rota is built around the five, and the budget around the total at the bottom.

The total at the bottom, about 7,800 against this year's 7,320, is the budget sentence: demand up about six or seven percent net, with the new system adding more than the reset project removes. The September row, at 768 against an August of 484, is the rota sentence. The busiest month needs half again the capacity of the quietest, which no permanent headcount can match. That is why the forecast's job is to make that gap visible in June rather than in September. The spreadsheet behind the table is twelve rows and eight columns. It took Leon an afternoon the first time and takes him twenty minutes a month now.

From Tickets to Hours to People

Tickets become hours by multiplying by the hands-on time per ticket, and hours become people by dividing by the productive hours one technician has in a month. Both figures come from your own desk and nowhere else. The hands-on time is measured from a sample of your own tickets: the sum of the time actually spent on each divided by the number of tickets. The traps in measuring it, including the difference between hands-on and fully loaded time, are the subject of Cost per Ticket and Its Traps. Ashgrove's sample of forty tickets gave a little under an hour each on average. The desk rounded that to 0.9 for the sheet and wrote down "0.8 to 1.0" for the range. Yours will be whatever your tickets are; a desk that handles mostly password resets will be a fraction of that, and one that handles mostly site visits will be a multiple.

Productive hours are the hours in a technician's month that are available for tickets, which is a good deal less than the hours they are paid for. Take the contracted hours, around 160 in many organisations, and subtract leave averaged across the year, public holidays, training, team meetings, the desk's own project work and the daily glance at the queue. A range of 100 to 120 productive hours a month is common and yours should be counted, not assumed; Ashgrove used 110. Then the September row reads 768 tickets, times 0.9 hours, is about 690 hours, divided by 110, is 6.3 people, against a desk of five. August reads 4.0. The average across the year is 5.3.

Those three numbers are the staffing implication, and they say three different things. The 5.3 average against a desk of five says the base demand has reached the desk's capacity. The growth line says it will pass it during the year. That is the beginning of a case for a sixth person. How to make that case with the data rather than with exhaustion is the subject of The Headcount Case. The 6.3 in September says that no permanent headcount will cover the peak without being idle in August. So September needs seasonal cover, a borrowed pair of hands, a temporary hire or a deferral of project work. The forecast gives the date to arrange it by. The 4.0 in August says when the desk's own projects, its training and most of its leave should happen. A forecast that stops at tickets has done half the job. Hours and people are the half the finance director was asking about.

Checking the Forecast Every Month

On the first working day of each month, write the actual created count for the month just ended next to its forecast, and look at the gap. Inside the range, the forecast is doing its job and nothing changes. Outside the range once, note it and the likely reason, an outage, a mailing, a holiday that moved, and carry on. Outside the range twice in a row, in the same direction, one of the ingredients is wrong. The sheet is built so you can tell which. If every month is high by about the same proportion, the base or the growth line is off. If one month is high and the others are fine, the multiplier for that month is off. If the gap opened in the month an event landed, the event's size was off. Correct the ingredient, not the forecast, and re-run the sheet from the current month forward.

Keep every version. The forecast from last October, with its gaps filled in month by month, is the document that proves the desk understands its own demand. It is far more persuasive to a sceptical reader than any single forecast can be. It shows the method being right most months and corrected honestly when it was not. It is also the evidence for next year's events: the sheet that recorded "launch bump estimated 120, actual 140" is how the next new system's bump gets estimated at 140. My own first forecast was a fifth too high for six months. The twelve months I built the base from included a migration month I had forgotten about. I did not find it because I had not written the base's twelve months down. I write them down now, which is why the worksheet has a line for them.

Present the forecast as part of the regular report rather than as a once-a-year event. That way, the finance director sees the gaps as they happen. The budget conversation in the autumn is a confirmation rather than a surprise. The one-page format that leadership will actually read, and where a forecast line fits in it, belongs to The Monthly Report They Will Read. The forecast's line in that report is short: last month's forecast, last month's actual, the gap, and next month's range. Four numbers, every month, and by the autumn nobody needs persuading of anything.

A Short War Story: The Straight Line at Pennywell Council

At Pennywell Council, a desk lead called Fiona was asked for a five-year staffing projection. She built it from the only two years of counts she had. Those happened to be the two years in which the council had rolled out a new case-management system to every department in turn. Volume had risen by a third each year, and she drew the line straight on. It said the desk of six would need eleven people by year five. The committee, alarmed, approved two posts at once. The rollout finished the following spring, and volume flattened within a quarter. By the autumn the desk of eight was visibly quieter than it had been at six, which the same committee noticed. The two posts were absorbed, slowly, as the organisation grew. But the next three budget rounds opened with a question about the projection. Fiona spent something like sixty hours across those rounds rebuilding her credibility one spreadsheet at a time. The desk's genuine need for a seventh person three years later was approved a year late. Growth has a cause, and the forecast must say what the cause is and when it stops. A line drawn through a rollout is a forecast of the rollout, not of the desk.

The Forecast Worksheet

The worksheet below is the spreadsheet as a form, one page, filled in once a year in full and brought up to date once a month in section F. Sections A to D are the four ingredients, each with the source of its numbers written beside them, because the source is what a reader will ask about. Section E is the twelve-month table, compressed to fit. Section F is the monthly check. The sheet is deliberately long on sources and short on arithmetic, because the arithmetic is four multiplications and the sources are where every forecast that went wrong went wrong.

VOLUME FORECAST WORKSHEET — ONE PAGE
Desk: ______________________   Forecast year: ______   Prepared by: ______________   Date: ________

A. THE BASE
   A1 Created tickets, last twelve months (list them): ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____
   A2 Odd months removed or adjusted, and why: ____________________________________________
   A3 Base = twelve-month total ____ divided by 12 = ____ per month
   A4 Staff supported ____  so per hundred staff per month = ____   (source and date: ____________)

B. THE SEASONAL MULTIPLIERS (years of data used: ____ ; if fewer than two, mark as business-calendar estimate)
   Jan ____  Feb ____  Mar ____  Apr ____  May ____  Jun ____  Jul ____  Aug ____  Sep ____  Oct ____  Nov ____  Dec ____
   Check: the twelve add up to about 12.0?  Total ____
   Each = that month's average across the years, divided by the average of all months

C. THE GROWTH LINE
   C1 Previous twelve-month total ____   Latest twelve-month total ____   Change ____  = ____ percent a year
   C2 Cause of the change (headcount / new system / scope or logging change / other): ____________________
   C3 Will the cause continue through the forecast year?  [ ] yes, carry ____ percent  [ ] no, carry ____ percent
   C4 Growth factor for a month k months ahead = 1 + (C3 x k / 12)

D. KNOWN EVENTS (one line each; size as a range; source named)
   Month ______  Event ______________________  Effect ____ to ____ tickets  Settles at ____ from ______  Source ________
   Month ______  Event ______________________  Effect ____ to ____ tickets  Settles at ____ from ______  Source ________
   Month ______  Event ______________________  Effect ____ to ____ tickets  Settles at ____ from ______  Source ________

E. THE TWELVE MONTHS   (line = base x multiplier x growth factor; range = line +/- ____ percent, plus event ranges)
   Month  Mult   Line    Events   Low    Mid    High   Hours (x ____ h)   People (/ ____ h)
   Jan    ____   _____   ______   ____   ____   ____   ______            ____
   Feb    ____   _____   ______   ____   ____   ____   ______            ____
   Mar    ____   _____   ______   ____   ____   ____   ______            ____
   Apr    ____   _____   ______   ____   ____   ____   ______            ____
   May    ____   _____   ______   ____   ____   ____   ______            ____
   Jun    ____   _____   ______   ____   ____   ____   ______            ____
   Jul    ____   _____   ______   ____   ____   ____   ______            ____
   Aug    ____   _____   ______   ____   ____   ____   ______            ____
   Sep    ____   _____   ______   ____   ____   ____   ______            ____
   Oct    ____   _____   ______   ____   ____   ____   ______            ____
   Nov    ____   _____   ______   ____   ____   ____   ______            ____
   Dec    ____   _____   ______   ____   ____   ____   ______            ____
   Year   12.0   _____   ______   ____   ____   ____   ______            ____ average; peak ____ in ______

   Hands-on hours per ticket ____ (from a sample of ____ tickets, taken ________)
   Productive hours per person per month ____ (contracted ____ minus leave, holidays, training, meetings, projects)
   Desk today: ____ people.  Months above capacity: ______________.  Cover arranged by: ________

F. MONTHLY CHECK (first working day; correct the ingredient, not the number)
   Month ______  Forecast ____ to ____   Actual ____   In range?  [ ] yes  [ ] no   Likely reason / ingredient: __________
   Month ______  Forecast ____ to ____   Actual ____   In range?  [ ] yes  [ ] no   Likely reason / ingredient: __________
   Month ______  Forecast ____ to ____   Actual ____   In range?  [ ] yes  [ ] no   Likely reason / ingredient: __________
   Two months outside the range in the same direction?  [ ] yes: ingredient corrected on ________

Leon sent the finance director the September range, the yearly total and the three staffing numbers, with the worksheet attached, on the Friday. The reply asked what the reset project's range rested on, which was the right question and had an answer on the sheet. The desk got its September cover arranged in June. It got its sixth person approved the following spring on the strength of twelve months of forecast-against-actual. Leon has not written the words "maybe a bit more" in a budget e-mail since. The forecast was wrong in four months of the twelve. It said so, each time, and that is what made it believed.

Frequently Asked Questions