I have a project I'm working on and I need some advice. I have only a very basic knowledge of stats. Here goes...

Your company has a production department with three shifts and a total of 115 employees. There are four factors used to measure an employee's performance in this department - Quality (QA), Productivity (PROD), Attendance (ATT), Level of Work Difficulty (WkDIFF). You've been asked to find a way to use the data from the four aforementioned factors to create a single number that will assign an overall performance rating for each worker. The Quality measure is similar to a test score (points remaining out of possible 100), the Productivity measure is a ratio (Amount work completed : Hours worked), the Attendance measure is amount of points remaining (out of nine, employees are terminated upon accruing nine attendance pts), and the Work Difficulty is an average of work difficulty ratings assigned to certain tasks. Hopefully that makes sense.

The only way I know to fairly combine these scores (that are in different units of measurement) is to convert them to standard scores then sum each employee's scores for each variable which would give the final rating number.

QA + PROD + ATT + WkDIFF = Performance Rating.

Here's my questions:

1) The rating will be calculated for each shift and overall for the entire department, should each shift be treated as a sample of the population (entire department) or should each shift be treated as it's own separate population?

2) I'm not performing an experiment, just trying to give employees a fair and accurate rating of their overall performance. What other statistical analysis can be used to ensure the data is accurate and reliable.

3) How should I examine the distribution of the data - for each individual variable or for the final performance rating scores? What are the possible pitfalls if the data is not normally distributed?

Sorry to ask so many questions. I just need to know that my thinking on this calculation isn't flawed. I also just need ideas on how to find ways to make that data tell me more than just the performance rating numbers (if that's possible).

The other problem, I have to be able to figure all this out using only Excel.

Any suggestions or help is GREATLY appreciated.

By the way, nice website...I'm lucky to have found it.

JDC