Performance targets

So how do you calculate the 50,75,90

'E.g. if after a year, if 100% of jobs are completed within 50% then they want to be able to change the targets to say, 20, 30, 40 to reflect reality

is this the number of jobs completed "On Time"


How does your table in V5 to W11 actually populate

need a fuller explanation for me to see how to help

Instead of ON TIME column, could use the other columns , BUT if users want to be able to change the % , then need to work out how that % is calculated

OK, so you are manually entering all the results into the sheet JOBS
Column V W X

so instead of counting the ON TIME column - you want to count the V column for the 50% results
etc

so
50%
=IF(C4="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(B4),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")*(Jobs!$V$5:$V$100=1)))

75%
=IF(C4="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(B4),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")*(Jobs!$W$5:$W$100=1)))

90%
=IF(C4="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(B4),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")*(Jobs!$X$5:$X$100=1)))

Again, if C4 returns a blank as no jobs that month , then the cells are also blank

i did ask

So how do you calculate the 50,75,90

So how do you calculate the % in the JOBS V,X,W columns

50% is calculated based on WHICH Columns in the JOBS sheet

Just seen this

More info in the SS.

Better to explain in both the Post as well as on the sheet

ok,
SO Column Jobs!T - is the % that you want to base things on
and use the % put into the RESULTS!I3 cell

SO if it has 50% in RESULTS!I3 , then we would look down COLUMN JOBS!T
and anything that matched 50% or less , would be counted for that MONTH

SO for JAN
JOBS! I5 to I12
is the count 8 , and
JOBS! T5 to T12
has the following %
AND Those 50% or Less would be counted
13%. = YES
50%. = YES
90% = NO
67%. =NO
75%. =NO
45%. = YES
100%.= NO

so thats a count of 3 - regardless of the ONTIME FLAG in S -

Lets look at FEB - T13 to T19
Count = 7

Below or = 50%
50% = Y
43% =Y
32% =Y
129% =N
89% =N
100% =N
196% =N

So a count of 3 for FEB
Thats
JAN =3
FEB = 3
if RESULTS! I3 cell was set to 50%

anywhere near

NOT a full explanation , with a few examples , please take the time to do the same

OK, so still dont understand , and i put the possible results in my post , was hoping you would say if the Y or N are correct

BUT guessing
I have put a few ideas in the spreadsheet

So it uses whatever % is entered into the
RESULTS! sheet Cells I J & K as you seem to what 3 columns
and if you change any of the % in those cells - it will recalulate for that %

In Addition for illustration , I have also added a column VWX - with a formula , which outs a 1 into the cell , again based on the 3 rowes entries in
I3 & J3 and K3

just so we can sum the number up and check the results, BUT on JOBS sheet, I dont use the V W X columns for the calc

IN RESULTS column I
=IF(C4="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(B4),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")*(Jobs!$T$5:$T$100<=I$3)))

you are welcome , see my previous post , explains what i have dine and a sample sheet
Posted the sheet again here, as we crossed post

you are welcome
The solution will very much depend on whats required to calculate/manipulate and show
Often Pivot tables are a good way to good for a lot of data analysis , BUT not always the solution.

Just a further update , often thinking about how the data may expand would also be useful.
In your case, for example in the other post
https://www.excelforum.com/excel-gen...ml#post5474409
We started with the Months entered as TEXT

I have used to find the months using your text entries of JAN , FEB etc
as they are text this made it a little more difficult and had to use a lookup to change the text into a month number, 1 to 12

This also means if you try to add Jan 2022 into the bottom of your list , excel wont know the year.

Having now seen the complete setup, it would have been better if the months column on the results page , had used real dates 1st jan, 1st feb etc
and then you can format the cell to just show the Month , but as its a real date , then we would have used a different simple formula
COUNTIFS()
rather than a find and array formula

Then if you add 2022 to the sheet excel will know to use a different year
you could also display as JAN-2021 and JAN-2022

Pivot table
Again looking at the data, and some of the extra columns that have been added , it would have been possible to use a Pivot Table
But the headers would have needed setting out in 1 row perhaps

Anyway, as you asked , I thought i would mention. And it maybe you do need to add next year into the results , then it will need to be changed to use countifs() Also i think the performance of the calculation would be improved , as your not creating numerous lookups and arrays being processed

i think to move forward on the countifs() version, a new thread should be opened and linked here
I added a extra set of data for Jan 2022


using countifs()
Total Calls
=IF(COUNTIFS(Jobs!$I$5:$I$100,">="&$B4,Jobs!$I$5:$I$100,"<="&EOMONTH($B4,0))=0,"",COUNTIFS(Jobs!$I$5:$I$100,">="&$B4,Jobs!$I$5:$I$100,"<="&EOMONTH($B4,0)))

Ontime
=IF($C4="","",COUNTIFS(Jobs!$I$5:$I$100,">="&$B4,Jobs!$I$5:$I$100,"<="&EOMONTH($B4,0),Jobs!$S$5:$S$100,1))

A pivot table is also possible, BUT the header should be added into 1 row - you can use (ALT+Enter on Windows) OR (Option+Enter on a Mac) to get the text onto a new line

Hi etaf. That sound great.
I was concerned that the users would keep adding jobs and eventually I’ll get an email saying “it’s not working!”
This SS will be used by the Workshop Engineer he will complete the data and the Workshop Manager will monitor results and present these at monthly meetings, neither has much idea about how to use Excel so I need to make it as idiot-proof and as easy as possible for them to use and protect all formula from their keyboard bashing, cell deleting fingers.

I guess there are many ways to go about this but the only options I can think of are;
1. 1 jobs tab with a continuously expanding table and separate results tabs for each year, or
2. New jobs & results tabs (copy & paste) every year – not sure if this will mess up the calcs/formula

Currently trying to teach myself how to create dashboards so the results look better but only just started learning.
Did some work on this last night so have attached a copy but it’s very basic at this stage.

Having now seen the complete setup, it would have been better if the months column on the results page , had used real dates 1st jan, 1st feb etc. Happy to make whatever changes you think will work, I’m on the limits of Excel knowledge, so, grateful for any and all the advice I can get.

it would have been possible to use a Pivot Table I have tried to use Pivot Tables many times, but can’t get them show anything useful, so the problem there is me, not the PT.

hopefully the mods, will not mind continue here as its all part of the performance target displays

Having done this sort of thing over the decades , my approach would be (Note its been a while since i was involved in performance management for corporations - lte 80's till 2010 - so things have moved on a lot with the later versions of excel.

1) change the formulas to use the countifs() as previously attached, and months to real dates
2) Setup the data sheet, slightly differently - i would make all the columns where they enter data to be together , then have the formulas on the right hand side away from there input
3) Have a look at using TABLES - these allow the data to be entered , but all the formula in the data will automatically copy down.
4) Change the data sheet headers to 1 row - not going to be displayed , so does not matter, then pivot tables and charts can easily be added and updated with the push of the refresh button.
5) Dashboards have moved a lot since my last consultancy , when preparing performance data for board meetings, so you can get some great solutions now , with conditional formatting icons

just as a taster, I have created the JOBS as a table, amnd in ROW 5 added headers . as needed by Pivot table - and you can see the formulas are entered
And also a simple pivot table off that table

Just an idea, but appreciate you have a job to get the results out for review

Absolutely fine to continue here for this.

Ok,
thanks for all the answers

verall SS management.
What do you think is the best way to manage adding new jobs and creating new result as the years go by. 1 jobs table and 1 Results tab or multiple results tabs?

Very much depends on what the SS Management need to see and how, although if anything like my experience this will be continuously changing.

1a) I’ll need a hand with countifs. The months to real dates I think I know

I can help with that , assuming this is your latest version of the spreadsheet here, i have modify to use the countifs() and months. Also the range change row 3 to 300 using JOBS(2) and formulas should now been copied down the table.

2a) We could do that, however they asked me keep the layout of the data table similar to the current SS they are using, plus they use this table not just for entering the info about each job but as a tool to monitor progress and prioritise the work so will need a logical flow across the table. I can hide U,V,W and should be able to protect the formulae.

As they use the data sheet as part of the workflow, then best to leave it alone, and not stress the organisation with unnecessary changes.

I can hide U,V,W and should be able to protect the formulae.

Not necessary , with the countifs() , unless you want it as a checking , reconciliation view , with the way I set up the countif it used the % directly with the % columns, so you don't need to have the UVW Columns, once you change over to the countifs()

3) I think I changed it to a table in Jobs (2), or are you thinking of something else. Not sure how they auto copy down, is that highlight row, small +, then drag down?

Just had a look , and yep, thats it, BUT i think if SSMang are using the data sheet in the workflow, this may confuse them , But the formulas columns can be copied down and will not work, until data is entered

If I could get PTs to look like what I have created in Results (2) tab but auto update and expand with each job added to the Jobs table, then that would be the perfect solution. But that’s proving to be beyond me.
Should be able to do that with a refresh.
You show in order Sep - Aug , is that your fiscal year order ? and is this data just a dummy for the year 2020/21

Performance Targets, are quite fun, once you get into it a bit , be careful on statistics though, management can jump to conclusion that are not statistically sound, and just natural variation in data, but that a whole new subject on its own

good luck, happy to help out

forgot the spreadsheet

They currently have 1 tab for current jobs and 1 for completed but if I did that, wouldn’t it mess up the results?

Depends on what data is included , BUT I would suggest keep in 1 TAB , easier to report and may provide additional information, without having to duplicate across 2 tabs.

Date/time closed may be all thats needed in 1 tab , rather than duplicating all data in a different tab

Data Validation is something to look at , to keep some of the entries the same and avoid different spellings

Can 1 PT handle all results, or is it better to have multiple results tabs?

Would depend on what the Organisation wants to view , What are the Strategy & KPI's , at one time a balanced scorecard was all that was talked about
I had 1 Results tab with the 5/7 KPI measurements , the organisation(s) had in the Business Plan , so where possible all measurements linked to those KPI. I consulted for a few Financial, Telecoms & Tech companies where we implemented that type of linkage and then automated into the MIS (Management Information Systems)
I would also look at Management behaviour , when financial reward based on metrics , making up a substantial part of the pay, was involved. For example First Time Fix was introduced as a main driver for a tech company , So ALL possible parts shipped to engineer, and distribution/material inventory cost went through the roof.

Conditional formatting , can give a good indication for focus , Just a RAG Status (Red, Amber , Green) against targets.

Then on other tabs, you may drill down to different sets, depending on what's the flavour/focus for the month/Qtr or for that Department.

If so, not sure how 1 PT could handle expanding as jobs are added over months and years.

That would depend on how complex the calculations - but excel can handle a lot of records , and i have used spreadsheets with 1000's & 1000's of rows of data.

How many years are you expecting to keep - even at 1000 jobs a year , 10years is only 10,000 rows , and excel 2016 max rows is 1Million


Its the complexity of calculation that can slow the spreadsheet down.

Just something i have seen all to often , is data loss
How is this info backed up , if its the only data source ?
A PC crashing , or hard disk breaking, is all the data then lost to the organisation.

I need help with this, first to decide the best way to organise/mange data then to construct.

A lot will depend on the balance between the workflow usage and then the reporting need. You already mentioned the Organisation did not want to change the data entry layout .

Fiscal Year, worked for a few companies with that sort of Fiscal Year , also where the months they reported were not calendar and a quarter was 4 weeks, 4 weeks, 5 weeks - which was a bit of a nightmare
and the fiscal year ran from July.

Anyway a bit of a waffle, on a sunday morning, but i hope that has answered a few questions or given some direction for thoughts on layout and design.

Ok, happy to help out, at the moment in lockdown, so not going very far , other than gardening when weather good.

On Some specific points , it would be worth starting a new thread, so you get peer review and other people's input here , - but that maybe more on perhaps a specific point or formula , like the other thread , rather than generic performance KPI measurements. I follow quite a few threads here, and its useful to see how different ways of coming up with a solution, and often MUCH simpler visually or resource needed for excel to carry out. I usually learn something new most weeks.

Be careful what you share here, its a PUBLIC forum, and so always make sure you remove any company private data - and i would suggest use dummy data , not real data , and maybe false KPI's - just so then you know how to apply to your specific spreadsheet with all the "Real" data

I don't like doing anything offline , and the rules of the forum its not allowed
However, If the mods are OK, perhaps for a private to specific info a PM or two , maybe allowed for anything private , for example - Like
what is your role in the organisation?
whats the brief ?
Role of your manager & Why you have been tasked with the model?- you may want to PM that , as private
, and sure that should be OK

Open for peer review here by other members, if you are allowed to put onto a public forum

Working Version, is that what I would call a straw dog, OR is that a fully operational model that can be implement across the department.

What are the KPI's - Not the specific target values, but what areas
Who are the audience , groups of different people, like CEO, CFO , Managers , user
How is the data to be used , Daily , monitoring, Monthly for any Improvement change and actions, Presentation at formal review meetings, AD-Hoc use. Trends ,
Who would drive PT measurement.

Restrictions you have in place , a) You already mentioned one restriction, The Users want to use the data sheet as it is , as a) thats what they are used to, and b) used as a tracking , monitoring tool. C) Version of excel in use across the users. you have 2016 in your profile. D) How much historic data to keep and review.

expectations for end of Feb, which is NOT long away only 5 working days

New thread. No problem.
KPIs – The Workshop Manager (WM) was told to show how many jobs were completed in 50% and 70% of the schedule. From experience these targets will change on a monthly basis hence the need for them no be selectable.


Restrictions;
A) The Users want to use the data sheet as it is. Yes, it’s similar to what they use now so I’m sure will be happy with how the main data (Jobs) looks.
A) used as a tracking, monitoring tool. They will use Jobs col L a lot to prioritise work and I can set Conditional Formatting (CF) to highlight when time is running out on a job.
C) Version of excel in use across the users. you have 2016 in your profile. We are all using 2016.

It will depend on how the info is update, keeping a large amount of historic data in the main data sheet may become an issue if used as a monitoring tool, depending on who is updating, if multiple users that could become a problem, and your then building more of an application.
As the call volume is small, maybe keeping a couple of years live so Year on year can be compared - then the Old calls could be moved to a closed archive sheet

However, visibility of open calls can be done with a pivot table , as I have added a open calls pivot , with ageing

But on 300 calls per year, 30/mth you have probably got a good starting point
Perhaps on the graphs - show a combo chart Bar & line chart performance against the target number - added a Pivot Chart, maybe over the top for the need to the results sheet.

You could have a results sheet show simple performance compare to previous month , maybe with Icons , up or down - but you only really have 1 metric - fixed on time.

Maybe something about on aging , length of time to complete a job Start to Finish - average time

A little more involved, but I had a Reason Analysis
A flag for Why it went overtime , maybe use a dropdown on the data showing, things like - Training/Parts/Resource/ , look into the reasons why over last few months identify the common problems that causes a delay to the job , and then use those as say 3 or 5 categories, then on a monthly bases you could maybe use a Pareto to show the main causes of delay to jobs - Again this may be over the TOP for 30 jobs a month - But that's what I wanted to know , where are we now, how many calls open each day by age, and why are we not meeting first time fix, and what's the reason and how to improve or change that metric

As you have to march 4th to play , I think you have a good starting point, and I'm sure this will evolve , probably more so as the quality Manager

Sorry forgot to add the sheet

i have added a couple of charts to the results , that you may want to consider
i may have not understood the % Targets - I thought % of number achieved on time, rather than changing the time allowed.
Interesting metric i guess
My Spreadsheet , showed performance against target, which was % of ON TIME CALLS / TOTAL CALLS
and also a chart showing difference to target, this was quite popular in my day to show visually issues , when compared month on month
with a different set of metrics cross the X-Axis and the data months - but may not apply here - so just the month along the X-Axis

some slight tweaks , and added conditional formatting icons , showing performance compared to last month RAG status ICONS

is it possible to create an additional tab to lookup/pick out current jobs so they can be viewed and/or printed off? Sometimes they like to upload that kind of thing to MS Teams to show others the current status.

this is an example where posting a new thread for this specific question may be best to extract the list.

As its 2016, you dont have the advantage of the FILTER() function which would do it reasonable easily , on 2016 you would need an array formula to extract the JOB Number and then lookup each job number across all fields needed.
And this will calculate every time data is entered into any cell on any sheet, (i think thats known as VOLATILE) , where as in the past years ago, i would have put a button to extract the list only when needed , but thats been 10-15 years since i played with VBA

But is the Job Number unique, I pulled off the OPEN calls , BUT since 1 job number 12372 , it appeared as open and closed , so then the lookups did not work, as its NOT unique.
If the job number is unique, then my formula should work and extract the open list
=IFERROR(INDEX(Jobs!$B$3:$B$1000,SMALL(IF((Jobs!$Z$3:$Z$1000)="open",MATCH(ROW(Jobs!$B$3:$B$1000),ROW(Jobs!$B$3:$B$1000))),ROW(B1))),"")
Entered using control+shift+enter to get {} around the formula
turorial is here
https://excel-bytes.com/how-to-extra...ters-in-excel/

You can just perhaps load a small sample of jobs, and then say something like - you want to extract all rows where the complete date column Q is blank, i added the open/closed status in Z as that was easier then blank , and would ignore cells that had NO DATA entered in the range table

you can see how the extraction works as a sample here - using a new field Status Z - OPEN to extract from

otherwise - you could filter on Z "OPEN" and copy

Results 20-21
Love the chart, that’s exactly what I was trying to do, but failing😊
Really like col G, the icons are a great addition, will try and add that to the Q1-4 data as well.

Hopefuly, the examples allow you to re-create as needed with the metrics you want to focus on.

The colour of the bar -ve, i manually change the colour, not sure if new version of excel can conditional format , BUT i used a trick to separate the values , something like this
https://excel-example.com/charts/con...umn-bar-charts

i have changed the arrow icons, so now they work on
Red = Less than previous
Amber = same
Green = greater

using Sign() to return 1,0,-1 and use that for the RAG status

F12 forced to be the same % for illustration purposes

can you explain exactly whats needed for the RAG Arrows , Percent ?

The chart difference - that is using the First Complete % , so may not be giving what you want exactly - i would keep the calculations separate , then changes are easier , plus you can check the graph is OK, also a different layout may be able to give automatic colouring, did this a while back, but had to change the width of the Bar - need to review that
To keep the main sheet tidy, I would create a seperate reference sheet , which could be hidden

Quite a bit going on here
I have moved the data to ref sheet
I have shown how a Title , can refer to a cell, so that can change dynamically - you in future you could have a dropdown and choose which % target to use - Or show all 3 charts - but starts to get busy.

I have also on the differences section on ref sheet , set out a Plus and Negative calculation , so then the chart uses 2 bars one negative difference and 1 bar positive , then you automatically get a different colour bars

Just to add to the automation, if you change the 50% on the review sheet 20-21 from 50% to a different % then the charts should also change including the title -

You NEED to just need to check the maths are doing what you want - as i'm only really looking at the display , not the accuracy of any data, or the way i'm calculating which i suspect the management may be all over, mine rarely understood the concept of draft/straw dog, and got hung up on the actual content even though made up....
For example 20% is 1 out of 8 calls and NOT 20% of the total - so maybe you need to take the S,T,U numbers and recalculate the completion % and use that as actual ?

You should review the Actual for those charts , as i am using the % to completion on time.

Anyway
heres a draft, so thoughts or comments and answer to 1st question

Like the R.A.G icons.

Also like the number % shift for each month next to each arrow a per a previous version.
So, both would be good.

Its not showing a percent now, its just seeing if higher, same , lower - using 1,0,-1
that way the amber makes sense
Can be change to show percent , but what are the thresholds, can be difficult to set.
If you click on the cell, Conditional formatting , edit the icons, theres a checkbox to show value
The default for these icons with % is usually 33%, 67% thresholds.

This KPIs table should now contain all the data needed to generate the required charts
I can hide col U, X & AA at a later stage.

Ok, so those values can be calculated , calculated OFF sheet, as i have done based on the Header cell S3 to get the %
So i have deleted those repeated target column , and then the charts can be driven off the reference sheet , and keep the results sheet clean and not have a repeated number in all the rows

I have changed these to Bar (from column) as they better represent the table arrangements above.
However, it would be better if the chart months were in the same order as the tables above i.e. Sep & Q1 at the top and Aug & Q4 btm.

Format Axis, reverse the order - this webpage should help
https://www.extendoffice.com/documen...-order.html#a1

This one however is showing mixed data. The 50% target is ref to the number of jobs in any month that were completed on or within 50% of the customer allocated number of days. So, for Jan 3 of the 8 jobs were completed within the 50% target. Expressed as a % for Jan 38% of jobs were completed within the 50% target. So I need the 50% target line and Jan to show 38% and so on...

So i have now changed the Ref Sheet - It pulls the correct % now from the T,V,W on the results sheet - so now 50% does show 38% for Jan

I liked this chart (befor I messed it up by adding col to the KPIs table) but didn't fully understand what it was showing me

Now uses the Ref Table and shows the difference between the Target and the actual value , so now using T,V,W %'s
So as the target is 50% , then its positive for any Actual above 50% in V and negative below 50% in V
So Jan is 38% and thats 12% below the 50% target, so negative and so shows 12% below the zero line

if you want more graphs, we can add the other targets 2 charts for 20% and 2 charts for 100% - BUT it does start to get very busy on the sheet.

Other than changing the icons to a percent and what range you want , i think thats everything on the callouts

hope that helps, and all is now correct, BUT worth a good check
updated spreadsheet attached

How did that all go last week with your manager meeting ?

looked into and not sure how to do this, perhaps via VBA , dont know
But maybe worth starting a new thread/question in the Excel Programming / VBA / Macros forum