Summarising Course Attendance Data For A whole Month

While reporting on course attendance is not considred to be critical reporting it is still important to track the data to have an idea of how much perceived value people are getting from training (is this course worth attending?) and how much costs are being expended on non-attendance trainees. Recently I wanted to create a simple and quick way for us to understand how much people attend all our training courses on a monthly basis, how many people booked did not attend and what is the average costs (this was calculated taking into consideration, trainer costs, venue costs, stationary provision, any food or refreshments and administrative time) of non-attendance per month.

We currently put all monthly course booking data on Excel in such a way that each course has a sheet which shows, amount of people booked on the course and those that attended. To summarise the data I inserted a new sheet at the beginning of the workbook containing information for all the month’s courses and named it Training Attendance Summary.

Next in each training sheet I wrote formilas in two cells, one which counted the amount of people booked to attend the course and the second to count the amount of people that actually attended the course.

Then in the summary sheet I wrote two formulas to sum the total amount of people booked on all the courses and the other to calculate the total amount of people that actually attended all course. From this data i was able to:

  • Calculate the amount of non-attendance for the whole month
  • Calculate total cost of non-attendance
  • Create a column chart comparing total of booked people, total of those that attended and total of those who didnt attend
  • Create a Pie chart that compares percentages of those who attended vs. those that did not attend.

The images below show the calculated data and associated charts (all data has been changed for confidentiality purposes).

This images shows the data calculated by summarising training booked and training attendance data summarised from 19 other worksheets.

analytics-1-calculated-data

Here is the column chart created from the data shown above.

analytics-1-column-chart

And here is the pie chart created from the data.

analytics-1-pie-chart

So how can this data be used?

Instantly this gives us a big picture of what our training attendance is like and how much money we may be spending on people who are not attending courses. It will help us to start to ask other questions which might need further data analysis such as:

  • If non-attendance is high, why is this happening?
  • Is there a pattern to this. If we compare this with last year’s data what relationship will emerge?
  • Are there any particular teams or people displaying consistent patterns in non-attendance?
  • Are there particular courses where non-attendance is high? If so can we correlate this with other data such as smile sheet feedback (yes I know), distance between workplace and venue or even other performance data.

This is a simple analysis which can reduce the time used to track training attendance data and also yield some interesting ansuprising insights.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s