# Predicting Your Helpline Call Answer Rate

One role of helpline managers is to manage their workers so that they can answer the most calls possible within the available resources. Even helplines that run 24-hours and have 100% coverage can’t answer 100% of the calls that come in if they have more callers calling in than workers available.

Using a system like Chronicall can give you real-time information on the calls that you answer and don’t and prepare more detailed results (for instance, noting where calls are not answered because the worker is already on a call.)

Given a series of values that are related to each other, regression allows us to predict values where we either don’t have the data or where we want to know the “average” of a piece of data.

For this task, we assume all you have is the data about how many hours your helpline is covered (either in hours or percentages) and the percentage of calls that you answer.

 Hours Covered (out of 24) Call Answer Percentage 24 80 24 78 24 82 24 76 24 79 22 75 22 85 22 76 20 82 20 80 19 70 18 74

While we can use the regression formulas by-hand, Excel provides simple techniques for deducing the formula. The first step (for the purpose of this article) was to do the calculations by hand to demonstrate. You can see the regression article for full details on how to do this.

## Regression By Hand

 Hours Covered (out of 24) [X] Call Answer Percentage [Y] X2 Y2 XY 24 80 576 6400 1920 24 78 576 6084 1872 24 82 576 6724 1968 24 76 576 5776 1824 24 79 576 6241 1896 22 75 484 5625 1650 22 85 484 7225 1870 22 76 484 5776 1672 20 82 400 6724 1640 20 80 400 6400 1600 19 70 361 4900 1330 18 74 324 5476 1332 263 937 5817 73351 20574

b = (12*20574 – 263*937) / 12*5817 – 263^2
b = 0.71969

a = 937 / 12 – 0.71969 * (263/12)
a = 62.3101

So our final equation is:

Y’ = a + bX
Y’ = 62.3101 + (0.71969)X

## Using Excel

We can use Excel to simplify this calculation. Starting with an Excel spreadsheet containing our X and Y values:

Next, we use Excel’s LINEST function. This requires you to select TWO cells at once. The first required value (called an “argument” in Excel) is the known Y values. In this case, it is C2 through C13. The next value is the known X values (B2 through B13.)

The third argument is whether to set b to zero, or to calculate it normally. Since we’re using the equation Y’ = a + bX and not the equation Y = mx + b, we’ll set it to TRUE. The final argument asks whether we want additional statistical information included, so we set this to FALSE.

So our final equation is:

=LINEST(C2:C13;B2:B13;TRUE;FALSE)

After we’re done typing this, instead of hitting enter like normal, we hit Ctrl-Shift-Enter. This is very important! If we neglect to do this, Excel will only give us part of the information we need. If we’ve done this correctly, Excel will put brackets around the formula, like this:

And you’ll notice that both cells you selected are filled in. The first cell holds the b value and the second cell holds the a value. Putting them into the formula, we have:

Y’ = 62.31024 + (0.719685)X

So, if we want to calculate what our answer percentage will be if we have 21 hours of coverage:

Y = 62.31024 + (0.719685)21 = 77.42

This falls right in line with our expected values, and this technique can be used with any other data where you need to predict values in a linear fashion.

Cite this article as: MacDonald, D.K., (2015), "Predicting Your Helpline Call Answer Rate," retrieved on November 17, 2017 from http://dustinkmacdonald.com/predicting-your-helpline-call-answer-rate/.

by

# Least-Squares Regression

Regression is a technique used to predict future values based on known values. For instance, linear regression allows us to predict what an unknown Y value will be, given a series of known X and Y’s, and a given X value.

Given the following, it’s easy to see the pattern. But assuming no obvious pattern exists, regression can help us determine what the Y value will be given our known X values.

 X Y 2 3 4 6 6 9 8 12 10 15 12 14

The X value is known as the independent variable, the “predictor variable”, while the Y value is the value you’re being predicted.

The linear regression (or “least squares regression”) equation is Y’ = a + bX

• Y’ (Y-prime) is the predicted Y value for the X value
• a is the estimated value of Y when X is 0
• b is the slope (the average change in Y’ for each change in X)
• X is any value of the independent variable

There are additional formulas for both a and b.

Let’s take a look at the following data-set, that compares the number of calls made for a product against the number of sales:

 Calls (X) Sales (Y) 20 30 40 60 20 40 30 60 10 30 10 40 20 40 20 50 20 30 30 70 220 450

First we need to calculate the sum of X-squared, Y-squared and X*Y:

 Calls (X) Sales (Y) X2 Y2 XY 20 30 400 900 600 40 60 1600 3600 2400 20 40 400 1600 800 30 60 900 3600 1800 10 30 100 900 300 10 40 100 1600 400 20 40 400 1600 800 20 50 400 2500 1000 20 30 400 900 600 30 70 900 4900 2100 Total 220 450 5600 22100 10800

Returning to our formula, let’s start with b first:

The top of the equation looks like this: b = 10(10800) – 220 * 450 / n(∑X2)-(∑X)2. We’ve simply filled in the values from our chart.

b = 10(10800) – 220 * 450
b = 108,000 – 99,000
b = 9,000 / n(∑X2)-( ∑X)2

Now we have to do the bottom half of the equation:

n(∑X2)-(∑X)2

=10(5600)-(220) 2
=56,000 – 48,400
=7,600

Returning to our equation:

b = 9,000 / 7,600
b = 1.1842

Now let’s move on to a:

a = 450 / 10 – 1.1842 * (220 / 10)
a = 45 – (1.1842 * 22)
a = 45 – 26.0524
a = 18.9476

So, going back to our original regression equation, Y’ = a + bX and plugging our numbers, we get:

Y’ = 18.9476 + (1.1842)X

To use this equation, we now put our desired value in for X. With an estimated 20 calls:

Y’ = 18.9476 + (1.1842)*20
Y’ = 18.9476 + 23.684
Y’ = 42.63

So, a salesperson who makes 20 calls will expect to make 42 sales.

Cite this article as: MacDonald, D.K., (2015), "Least-Squares Regression," retrieved on November 17, 2017 from http://dustinkmacdonald.com/least-squares-regression/.

by