Introduction
Competency analysis is a significant area of study and analysis for any manpower planner and this page introduces you to a competency analysis model we have created by using Excel 2007.
This page describes how our competency analysis model allows you to create a database of employees by listing their name, work team, department and competency score. Moreover, the model takes your database and with no further inputs from the user once the database and links have been established, will produce the analyses and charts that follow here.
Database
Whether you generate your employee competency database from scratch or by copying and pasting from elsewhere doesn’t matter. You should expect to create something like this, however:
Of course, if you want to add further details, you are perfectly free to do so! You may also, for example, change the column headed year to being a test or quiz number or any other marker you like. Similarly you might only have departments and no teams, so leave the team blank or delete it completely.
In the competency model being discussed here, you will see that we create
- some instant, graphics free, formula based charts
- a Pivot Table
- a Pivot Chart
- a population pyramid style chart
- an XY (Scatter) chart
What follows now are screenshots of all of the above analyses together with a brief description of them.
Some Instant, Graphics Free, Formula Based Charts
We have programmed two complementary but different analyses here:
As you can see from cells G5 and G17, all you have to do to generate analyses here is to either type in your choices, top section or chose your selections by using drop down boxes, bottom section. If you make an invalid selection, you can see a warning in the error message column.
A Pivot Table and a Pivot Chart
The following screenshot shows a Pivot Table and Pivot Chart that has been generated from the database we have created.
Because we are dealing with Pivot Tables and Pivot Charts here, things could hardly be simpler to change. If you change your database, simply tell the Pivot Table icons what you have done and it will update your Pivot Table and your Pivot Chart automatically.
You can make changes to the Pivot Table directly, too: for example, the following Table and Chart relate only to Tony but are created almost effortlessly from the above primary Table and Chart:
We won’t demonstrate it here but rather than Tony, we could have chosen any team or any department or any combination of team and department ... we can also prepare an individual Table and Chart from every one of our selections.
A Population Pyramid Style Chart
The following population pyramid style charts are not built into Excel 2007 but they are a very effective way of presenting certain kinds of data. In this example what we have done is to ask Excel 2007 to generate the overall average score for the organisation and then show each employee’s score in relation to the average. We show whether an employee’s score is higher or lower than average.
An XY (Scatter) chart
Finally, we show a more conventional XY (Scatter) chart that Excel 2007 can do without further programming:
