Help Desk Performance in a Single Chart

One of the challenges in measuring the performance of any support team is to create a meaningful view of performance. Although it is not possible in a single chart to capture all of the facets of peformance, there is one chart that provides a great monthly peformance dashboard.

This chart, shown below, provides a look at the backlog of open support cases at the beginning of a month, the number of new cases received, the number of cases resolved during the month, and the average turnaround time for the resolved cases.



With the first bar, the backlog of open cases, you can see if your backlog is higher than expected. You can also see if what you are doing to reduce backlog is effective by looking for a downward trend in this measurement each month.

The second bar, new cases this month, shows the trend of your case volume, to see if you are experiencing heavier support request volume. You can use this to adjust by reassigning resources or changing processes to shift workload of certain types of cases.

The third bar, cases resolved, shows the actual cases resolved during the month. This provides an at-a-glance measurement to see if you are working through backlog, only completing new requests, or adding more to your backlog by completing less than your new requests.

The line represents case turn around time for the given month. With this, you can see if you are improving, staying the same, or getting worse at resolving cases in a timely manner. It provides enough information to see if you want to drill down deeper to isolate problem areas.

This chart requires a little bit of work to create the first time, but after you have created it, you can reuse it easily.

1. Using Microsoft® Excel, create a data table like the one below.



	A	B	C	D	E
1	Date	Backlog	New	Resolved	Turnaround
2	1/1/2006	500	800	820	5.5
3	2/1/2006	480	823	873	5
4	3/1/2006	430	750	779	4.9
5	4/1/2006	401	830	881	4.8
6	5/1/2006	350	1,144	944	5
7	6/1/2006	550	900	1,100	4.5
8	7/1/2006	350	810	840	4.5
9	8/1/2006	320	795	905	4.2
10	9/1/2006	210	750	780	3.7
11	10/1/2006	180	810	840	3.5
12	11/1/2006	150	880	875	3
13	12/1/2006	155	700	800	2.5



2. Select the values from A1 through E13.


3. Select Chart from the Insert menu.


4. Select a column chart, using the default column chart format, and click Finish.


5. Right click on one of the columns in the Turnaround series and select Format Data Series.


6. On the Axis tab, select the SecondaryAxis radio button and click Ok.


7. Right click once again on one of the columns in the Turnaround series and select Chart Type.


8. From the Standard Types tab, click on Line, using the default line chart format, and click Finish.


9. The mechanics of your chart are done. Now right click on the background of the chart and select Chart Options to add titles and change the format.

Now your chart is finished! This chart works well as a stand alone spreadsheet or as part of an Access database report.

-Steve McElwee