Manpower Planning Example

Introduction

The purpose of this example is to enable you to analyse your Employee Database/Payroll records by using the fully working model to be found in an Excel 2007 work book. This page describes what that work book contains and how the modules within it work.

 

You will find the analysis contained in this Work Book very helpful for basic data analysis as well as for such issues as succession planning, retirement planning, the need to monitor and even manage the health and smoking habits of your employees and so on. A very recent report on the UK’s National Health Service touched on this very issue: obesity of staff, smoking habits, sick leave (see here: http://www.guardian.co.uk/society/2009/aug/19/nhs-sick-leave)

With this work book you will be able to carry out a detailed

• age analysis of your staff
• competency analysis of your staff
• analysis of someone’s job level together with length of service by department
• smoker analysis
• overweight analysis
• smoker and overweight analysis
• analysis of the number of courses attended by age and job level

 

As part of this analysis you will be using Pivot Tables: if this is new to you, you can learn all about them by buying Excel 2007 by Excel Master.

 

There is a video to accompany this page ... see the link at the end of this page.

What is in the Work Book

Firstly, the work book is very flexible because you can set it up in any way you like: the good news is that all you need to provide is a list of data. Just copy and paste your information from your ledgers, your payroll or wherever you like. In the work book you will get when you buy it, you will find the following variables already there ... you might want to keep some or all of them ... delete what you don’t want and add the other things you do want.

• Payroll Number
• First Name
• Second Name
• Competency Score
• Performance Score
• Male/Female
• Department
• Date Joined
• Service (Yrs)
• Date of Birth
• Age (Years) Today
• Age >= 60 on any date
• Age >= 60 on any date
• Overweight
• Smoker
• Job Level
• No of Courses Attended
• Appraisal Score

All you have to do is to copy and paste your data into the Data Work Sheet in the work book and you are then automatically presented with a wide variety of different analyses. If you add or remove variables, there will be some tweaking to do ... nothing too complicated though and as part of our after sales service we can help!

The Database

Here are some screenshots of what you will see together with a brief description of them.

Here is part of the input sheet: it’s just a list of people under the headings mentioned above: maintenance of this list is so simple!

 

Click on the thumbnal below to see the full sized version of it

 

Age Analysis

Here is part of the age analysis of the workforce based on the input work sheet: we have kept it very simple so that no one needs to strain their eyes to read it and everything is shown step by step. If you like, you can split these outputs over several work sheets or drag and drop things around this work sheet ... it’s entirely up to you!

 

Click on the thumbnal below to see the full sized version of it

 

Competency Score

The screenshot below shows the analysis of ONE employee’s competency score together with a summary of the competency scores of all of your employees ... instantly you can change/choose the employee to select and instantly you can compare each employee with your averages.

 

Click on the thumbnal below to see the full sized version of it

 

Age Profile

Have you ever seen the age profile analysis by job levels of your staff? Here it is!

 

Click on the thumbnal below to see the full sized version of it