Training dashboard

[Corkey]

I have been asked to put together a dashboard to track training and costs. I'm fairly OK with using OFFSET to gather data for scrolling lists, etc and using the camera for graphic snapshots, but how on earth do I gather the data across 10 managers, each with up to 25 staff who may attend an unknown no. of courses to show that to my senior manager?


I was thinking of a worksheet per manager, and some of the courses will be common across the departments, but what's the best way to drag all of that together? I can see one sheet to aggregate the costs, but beyond that' I'm kind of struggling.


Any ideas most welcome

 

[chirayu]

You could use a macro that work on an if condition and only copies data for the manager you select - row wise onto a snapshot sheet - where you will already have your calculation set

Your data sheet would need to be something like

Manager Details | Staff Details | Course Details | Time/ Cost

 

[Somendra Misra]

Hi Corkey,


I would rather ask you a question? Who assigns training in your organisation for employees? Is it done through Human Resource Department or individual manager makes training plan based on employee need?

The idea to know the answer to this question is that if you had a yearly plan of training for each employees based on their individual training need identification than it is only the matter of putting it in an excel sheet with a status whether they attended that program or not and the cost involved in that program, which can be easily done with their respective managers and finance dept.

Regards,

 

[NARAYANK991]

Hi ,

I think this is a common mistake in dashboard development , that we first talk of how things are going to look viz. scrolling lists , camera tool ,... , instead of focusing on the data that is available or that can be made available.

Can you first list down all the possible data that can be gathered about training ?

1. Does each department have a training budget ?

If yes , then we can gather data for every department ; this will have data such as :

Department Name , Year ( or Period , if the budget is allocated in smaller chunks ) , Budget Amount

2. Is the training split up into categories ?

If yes , then we can gather data on how the training will be organized across categories :

For example , Personality Development may be allocated amount X , Technical Upskilling may be allocated Amount Y ...

3. Is the training planned / scheduled in advance , or is data available after the event ?

4. Regarding every training programme , what kind of data would be available ?

Name of the programme :
Conducted By :
Conducted where ? In-house or Away :
Period :
Type : Full-time / Part-time
Funding : Paid for by the organization / financed by the participant
Category : if applicable
Name of participant :
Participant's Department :
Measure of Success :

The last 3 fields can be a separate set of data.

I am sure that there will be much more data that is either already available or that can be gathered , depending on the kind of professionalism that is present in the organization.

First collect all of this data , and see how it can be segregated into various slots such as department-wise data , programme-wise data , participant-related data ,...

Once all of this has been done and agreed upon between you and your manager , get the actual working data together in one workbook , segregating it into different worksheets.

See if the segregation makes it easy to enter and validate data , especially if the data entry will be done by more than one person.

When everything is done , we can start discussing the dashboard !

Narayan