Software

Use Excel's Moving Average add-in to predict customer service call load

Monitor and predict call center volume using Excel's Moving Average add-in, which can help you spot trends that may warrant further attention, saving you headaches down the line.

UPDATE: You can download an Excel example of this tip here.

Your call center manager notices that the center has received an increased number of calls over the past five days; the majority of calls are about your company's newly released product. Before alerting the quality control department, you ask the manager to provide you with the total number of calls received each day for the past 10 days. The manager sends you an Excel spreadsheet with the calls for each day listed in fields A2:B11. To perform a Moving Average analysis of the data to see if this reflects a trend that will result in increased calls over the next week, follow these steps:

  1. Go to Tools | Options.
  2. Under the Error Checking tab, clear Formula Omits Cells in the Region check box.
  3. If this is the first time you have used an Excel add-in, go to Tools | Add-ins. Otherwise, click OK and skip to step 6.
  4. Click the Analysis ToolPak-VBA check box and click OK.
  5. Go to Tools | Data Analysis.
  6. Click Moving Average and click OK.
  7. Click in the Input Range text box, and then select the A2:A11.
  8. Click in the Interval box and enter 3.
  9. Click in the Output Range, select B2, and click OK.

If the number of calls listed in column B shows an increase, then you should alert engineering to correct the problem before it becomes worse.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

8 comments
bob.ellis
bob.ellis

The "moving average" model, as with other averaging models, is used to smooth data and, as such, may not offer the predictive capabilities desired by management. As with any forecasting techniques provided by Excel, and there are several good techniques available, the data should be graphed and reveiewed for pattern and trend. Highly visible trend and patterned data promote linear models with seasonality complement. The manager should consider the model for the needs of the forecast and not select a model only because the functions have been predefined by Excel.

EvilBelle
EvilBelle

We would really find this usefull in our call center so pls if you can give us an example

tbrittnacher
tbrittnacher

These examples are great, but providing sample data with them would be better.

Leee
Leee

See update.

senios
senios

when i put input range a2:b11 i get error saying that moving average can be a singel column or row the output range cant be b2

Leee
Leee

The step has been corrected. Thanks.

rapell
rapell

only be a single row or column.