personnel performance scorecard

[dougers1]

Hey Guy's im currently trying to set up a score card for each employee so that the end of each month they can have a one to one with a manager who will go over there performance.

im just trying to come up with a template that will work,and was wondering if you can maybe give me some idea's

would like to be able to record errors in some sort of way that i can bring up the details of the error,

pleas se attachement for what ive got so far ,it's not much but its a start

cheers Dougers1

[Tsjallie]

Can you be more specific about the kind of errors you want to record?
If you mean deviations from the set targets then there is a challenge, because achieved and target are (I guess) for the whole year, whilst the recording is monthly.
And I would add a column "prospected" which shows a prediction of achievement by the end of the year based on results sofar.

[dougers1]

ERROR details would be for instance hospital , ward , date, start and end time,and reason

[Tsjallie]

ERROR details would be for instance hospital , ward , date, start and end time,and reason

Puzzles me. I believe you're operating some kind of call center? What kind of business are dealing with?
Having an idea of that would help me.

Will look at the sheet in the meantime and come up with some ideas. Hope I want shock you too much

[dougers1]

I work for a health service contact centre that deals with booking nurses into wards at short notice
Deal with cancellation and new shifts for ward sickness of there own stafff

[Tsjallie]

Here's a workbook with some suggestions.
Still not sure if I'm getting anywhere near to what you intend with the "errors". Just gave it shot.

[newdoverman]

I suggest using a table like the enclosed that you can fill in on a regular basis whether daily, weekly or monthly and this will allow you to filter for the employee by name and date. You can also do group analysis by filtering on any of the columns to get statistics for that particular subject. I have included a TOTALS row at the bottom of the table. You can change what kind of calculation is made for each column. The totals are adjusted according to what filter has been applied. If you select one employee, the totals are for that employee.

[dougers1]

i have attached new sheet with the data that will be plugged from stats,im want to find out how i can pull each staff member data up for daily and monthly and somehow match it up with the fill rates.
any ideas

Dougers1

[newdoverman]

This is quite confusing.

Does this workbook represent one person? If it represents more than one person, where are the statistics for each individual?

The organization of the data doesn't appear to be optimal for extracting data on any one individual if this is the way the data is recorded per individual.

[dougers1]

it would be for around 20 staff, the person by month and day stats are would be individual, the fill rates are the out come of the what was required and what was filled,

i was wondering if it would be possible to have some sort of drop down lists that i can bring employee name up and show the stats for month /day against the fill rates.....i know its quite confusing sorry

[Tsjallie]

Here's an example of how things could be linked together. Used the data you supplied. That helped a lot!
Haven't been looking at the "one-to-one" sheet yet.
The sheet "Fill rates per day" contains a box with some questions.

Serious issue is how to deal with summarizing months and employees.
Pivot tables like Newdoverman suggested would be an option to deal with that.
I'm not familiar with pivot tables (never use them), but I'm sure Newdoverman would be happy to help you with that. Would be a chance for me to learn
On the other hand you should realize that at the end you will be to one to maintain and change the workbook if necessary.
That means that for that you must understand pivot tables.

If however, you're not feeling comfortable with pivot tables we can discuss other solutions.
Guess at this point I need your answer to the questions and your view on how to move forward.

[newdoverman]

I think that something like this (red tabs) would be a better way to list your data and use a Pivot table to get the summaries.

[dougers1]

ok im not used to pivot table so would need to read up and learn about them, i will try and answer your questions and see if this helps(fingers crossed)
measure overall activity- i would manually enter data from other admin duties like emails ect and would go down as a number,
Day sheets re-used--each month....ideally yes re used each month but would like to store them to compare with past months,
workbook for all staff or individually---either way would be fine as it would be for each person on a one to one basis,
Daily and monthly Data could be integrated ,as-long its there to see and compare.
the shift sheets in other thread are Brilliant and exactly what im looking for,(also like the way you did the kpi example with deviation and tolerance columns

[Tsjallie]

OK. Thx for the answers. Anxious to hear if you want to jump into pivot tables
Will look into other solutions in the meantime.

[dougers1]

im up for what is best and easiest to control, if pivot tables are easy to teach then i would consider this as an option,but as i said im kind of new to this

[newdoverman]

If you want to learn about Pivot tables and a host of other Excel things this is a very good site.

http://contextures.com/

[dougers1]

thanks i have added to my bookmarks

[newdoverman]

There are also many instructional videos on YouTube. Just search for excel and you will be presented with a lot of choices. I have found that ExcellsFun has over 1000 videos starting with the very basic and works up from there. These videos have spread sheets for download to follow along with the lessons.

https://www.youtube.com/user/ExcelIsFun

[Tsjallie]

Here's a redesigned workbook with the separate sheets merged into one personal sheet.
Still working on it. Just for you to see if that would be working for you.

[Tsjallie]

Here's the redesigned worbook again. Totals are working now.
Not sure what you want to put in the one-to-one section.
Added sheet protection to prevent formulas become overwritten. No password though.
Green zones are input zones.

[dougers1]

this is good, is there a way to consolidate so that i can have all the staff names on filter so if i choose tam for example itt will show me his stats ect

[Tsjallie]

A filter only applies to sheet it's on. So, you cannot jump to an employee's sheet thru the filter.
Alternatively you could design an overall sheet with a summary for all employees. From there you could jump to an individual employee's sheet thru a hyperlink.
Guess you will want to have an overall overview anyway. So that would match perfectly.

[dougers1]

hi i have added two new rows target calls and target performance but when i select month by column it does not show up ?

please see atttached

[Tsjallie]

how do i get this to work
if i enter data in cells in one sheet to automatically copy the data to the same cells in other sheets.as some of my data i collect will be the same for each staff member,,,save on typing time

You can select multiple sheets by clicking on their tabs while holding down [Ctrl]-key. Then everything you type in the sheet you're viewing will automatically by copied to the other selected sheets, like a carbon copy. Goes also for formatting, inserted/deleting rows and columns etc.
Nice feature, but make sure that the sheets have exactly the same layout.

A good strategy would be to have one sheet as a template and than after you're satisfied with it make a copy of the template for all employees. Then you know for sure they're all the same.

BTW, as you're inserting new rows, it's a good thing to add using names. This makes your sheets more flexible.
Normally Excel automatically adjusts cell references in formula when rows and/or columnd are inserted or deleted. In the Overview sheet there are cell references done by the indirect function.
In that case the reference is just a string and those are not adjusted. By replacing these references with names these references also become flexible.
As these names will be in the employee sheets, it is best when they are defined in the template sheet. When you then create employee sheets from the template these names are automatically copied along.
Saves a lot of clicking and typing again

[vinothsm]

Hi
I have mutiple sheet in a work book from Jan'14 - Dec'14.
In all these sheets i have My team members KPI is measured for every month. I need to share the consolidated view by sum the value for each Team member KPI in Final Sheet to explain the Year to Date Performance.

[dougers1]

this has thrown a spanner in the works for me,,i like what you have done and now wondering how i could get this to match up with what i have,


how did you do that dashboard. the links are great.is this easy to learn

[vinothsm]

I have done this, only thing is trying to Link them to show the consolidated view for my team Year to Date performance score

how to do that?

[Tsjallie]

@Vinothsm
if you think you can add to this thread, please be welcome. If you questions of your own, please start you're own thread to avoid confusion. See forum rules #2
Fancy sheet though

@Dougers1
The dashboard is done with SmartArt. If you get tired of these boring formulas have some fun with that. Will cheer you up

[dougers1]

i have just downloaded smartart,is it easy to use and create, i would like some like the dashboard for mine lol

[vinothsm]

Noted Tsjallie...Anyone can help me to get the output

[Tsjallie]

Gave it a try with a pivot.
Cannot say I'm convinced yet.
It's an awful lot of work making a datasheet fit for use with a pivot table. See sheet "Data".
I think that format will not make data entry any easier.
Created a pivot table on sheet "Pivot" showing the result so far.

Then again, like I mentioned before, pivot tables are not my cup-of-tea, so probably I haven't discovered all the features yet or (also likely) I'm using a wrong approach.
See what you find of it.

[dougers1]

decided to try it this way , need some help with formula for total talk time colmn i can get the traffic light system to work in the ist cell but not the rest of the cells in column ?

also want to be able to do it on new sheet for month

for example
jan feb ect
name 1
name 2
ecct

[Tsjallie]

For the traffic lights the conditional format can only handle absolute references (e.g. $K$3).
You probably created the conditional format for the 1st row and then copied the format to the other rows.
With relative references (e.g. K3) Excel would automatically adjust the reference, but not so with absolute references.
That makes that the conditional format of he 2nd row etc keeps looking at the first row (i.c $K$3) and so the 1st row seems to work and the others don't.

To solve this I inserted an additional column holding the formula of the conditional format, but it's only showing the trafic light. That's an option you can set.

Added some additional comments in the attached sheet.

Will get back to you about to other things.

[Tsjallie]

also want to be able to do it on new sheet for month

for example
jan feb ect
name 1
name 2
ecct

Isn't this similar to what I posted earlier (see post #25)? That layout could of course also be with months in the colums and rows for the employees.
Attaching another example based on your last post.
Maybe I'm way off of what you intend. Of so, could make up a sample of how you would like the sheet to look? No need for formulas or other technicalaties.

[dougers1]

this is exactly what im looking for ,i have added some new column's can you give it a run over and check my formulas please.
Thanks Again

[Tsjallie]

Ok, look at your formulas.
Some comments/tips:
- At some point in time you will probably want to represent time as time instead of decimal values. That's where the formulas need adjustment.
But as long as you keep showing evething as decimal it will be ok as is.
In the attached sheet I show you what will happen and how you can adjust the formulas

- Also I would recommend the use of absolute and relative referencing when applicable. Always using relative references can have Excel change formulas inadvertedly and above all unexpectedly.

- Finally I introduced the use of names which make your formulas more readable. I think this is one of the most powerful features of Excel.
The attached sheet also contains examples hereof.

[dougers1]

thanks again, can it be sorted so that the cell changes colour depending on the result

[Tsjallie]

You can sort on the cell pictogram, but you have to do that manually.

[dougers1]

Web Booking E-Mail Time Keeping Annual Leave Sickness
numbers yes/no number Days late hours number days off sick

these are the data i would like included please...i have also noticed that the log on total does not add up ?

i have looked at the formula's with the evalute formula trick......this has helped me to understand some of the basic ones....but need help to understand the more advanced ones please

[Tsjallie]

Here's a new version with the adjustments required.
Need to look into the traffic lights yet.

Now working on some decent documentation, so you can handle any further changes yourself. Like adding the other employees ...
Will still help you out if you get stuck, of course.

Edit: documentation ppt (zipped) attached.