+ Reply to Thread
Results 1 to 41 of 41

Performance targets

  1. #1
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Performance targets

    I’m getting close to completing this project, hopefully, but need help pls.

    Not sure how best to achieve this and end user has little to offer accept they want to be able to show how the workshop is performing and present the data at meetings, “so, it needs to look good”.

    So that’s my brief and knowing nothing about pivot tables or dashboards the following is all I can think of. Any other ideas very welcome.

    My idea is to show in the Results tab, the number of jobs completed each month against a selectable performance target and of course blank when 0 or no data.
    Full notes in the SS
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: 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
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Not sure how else to put it.
    The boxes in the jobs tab are (just a manual count for illustration) from the T col.
    more notes added to the SS
    Attached Files Attached Files

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    I’m sorry etaf, my explanation is rubbish.
    I was hoping not to have to use any manual inputs.
    More info in the SS.
    Attached Files Attached Files

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  8. #8
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Sorry, I have made a mess of this request

    So how do you calculate the 50,75,90. I wasn't doing a calc, they are targets that are set by the user. Like a sales target each month so need it to just count how many jobs match that target
    So how do you calculate the % in the JOBS V,X,W columns. I wasn't doing a calc just a manual illustartion. Pls ignor everything I put in vxw, doing that was not helpful and I should have left it blank as it plays no part in what I was looking for.

    50% is calculated based on WHICH Columns in the JOBS sheet. The % in Results I,J,K 3 are not calcs, just reference targets for lookups to match.

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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)))
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Quote Originally Posted by etaf View Post
    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 - Yes that's correct

    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 Yes that's correct
    Thats
    JAN =3
    FEB = 3
    if RESULTS! I3 cell was set to 50%

    anywhere near Yes that's correct. And more of the same for the other targets

    NOT a full explanation , with a few examples , please take the time to do the same
    So sorry, but I could not think of a better way to explain it. I should have typed it out as you have just done. Thanks so much for your help.

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    That is brilliant. Thank you so much for all your help.
    And thanks for being patient with my rubbish explanations.
    I suspect my employer will ask me for more SS in the months to come.
    So, if I have to create more like this in the future, is it possible/better to use pivot tables or some other such function or are they not suited to this kind of solution?

  13. #13
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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.

  14. #14
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files
    Last edited by etaf; 02-19-2021 at 04:59 AM.

  15. #15
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    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.
    Attached Files Attached Files
    Last edited by Sandy737; 02-19-2021 at 09:48 AM.

  16. #16
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Performance targets

    Absolutely fine to continue here for this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  18. #18
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf. That all sound good.

    Overall 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?

    Reply to your points
    1) change the formulas to use the countifs() as previously attached, and months to real dates
    1a) I’ll need a hand with countifs. The months to real dates I think I know.

    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
    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.

    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.
    3a) 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?

    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.
    4a) happy with making headers 1 row. Jobs table will get a lot of use, it will be open on his desktop most of every day so headers and associated notes used frequently. PTs & charts, that’s a problem for me. I have tried for months on other jobs to get them to show more than just basic info. 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.

    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
    5a) Not too bad at conditional formatting, it’s just PTs I can not understand.

    Would be great to get some help on this I you can

  19. #19
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  20. #20
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    forgot the spreadsheet

  21. #21
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf. Thanks for that.

    General management of the data
    Still don’t know how they should manage this SS over the coming months and years. This workshop averages 300 jobs per year but they plan to increase this. They currently have 1 tab for current jobs and 1 for completed but if I did that, wouldn’t it mess up the results?
    So, 1 data table (Jobs) for all new data entry? If so, do we copy all rows down for thousands?

    Results
    Can 1 PT handle all results, or is it better to have multiple results tabs?
    If so, not sure how 1 PT could handle expanding as jobs are added over months and years.
    Is all that even possible for a PT?
    I need help with this, first to decide the best way to organise/mange data then to construct.

    Fiscal year
    Yes, very odd, financial year starts 1 Sep.

  22. #22
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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.

  23. #23
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf. Thanks for that.

    This is a small Co (30 odd staff). They have their own network system, backed up every night. IT is handled by Wessex IT, external Co.

    Understood most of that but don’t know where to begin with making any of it happen.
    Not sure how much I can call upon your assistance before it gets to a point of being too much work. You will need to let me know how much help/time you are prepared to give to helping me create this SS. I’m conscious of how much you have already helped and don’t want to seem greedy, but I’ll take as much as I can get. I have a loose deadline of end of Feb to show the Mgr a working version.

  24. #24
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  25. #25
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  26. #26
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    Sorry forgot to add the sheet

  27. #27
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    latest version
    Attached Files Attached Files

  28. #28
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files

  29. #29
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

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

  30. #30
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf

    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.

    Main data table (Jobs)
    Thinking ahead to the possible Q I may get “where’s the jobs current & jobs done tabs”?
    In case I’m asked, 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.
    I still think the 1 data sheet is the way to go but just trying to anticipate this request.

  31. #31
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files
    Last edited by etaf; 02-22-2021 at 05:45 PM.

  32. #32
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  33. #33
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    That’s looks good.

  34. #34
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

  35. #35
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf

    Thanks. All looks good. Some more comments in the attached.

  36. #36
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Attached Files Attached Files
    Last edited by etaf; 02-25-2021 at 10:34 AM.

  37. #37
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf
    Sorry, poor explanation.
    I have made a few more notes, hopefully a little more clear.
    Attached Files Attached Files

  38. #38
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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
    Last edited by etaf; 02-25-2021 at 02:44 PM.

  39. #39
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

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

  40. #40
    Registered User
    Join Date
    04-21-2020
    Location
    Horsham, W Sussex, UK
    MS-Off Ver
    2016
    Posts
    63

    Re: Performance targets

    Hi etaf. Good thanks. Once again, a massive thank you for all your help, this would not have been possible without it.
    I had to scale it back (make it a tad more simple for them)
    After I did that, they loved it.
    My new concern is cut and paste.
    When they copy & past the actual customer data from their old SS into the new it obviously messes up the formatting.
    Understandably, some of the formula won’t work as they are looking at text (or whatever) instead of dates.
    So, the only solution I can think of is to prevent both cut and paste but allow copy. (they need to copy out data so they can add it to, say, a word doc).
    But don’t know how to do that, shame it’s not on the list of protect options.
    Attached Files Attached Files

  41. #41
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Performance targets

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Performance: Tips for optimizing performance obstructions
    By dangelor in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-06-2019, 06:33 PM
  2. current moth MTD performance with last year same month same days performance
    By satyanarayana in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2015, 03:36 AM
  3. [SOLVED] If formula for more that 300 targets
    By winwall in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-13-2014, 09:09 AM
  4. Targets
    By Vokean in forum Excel General
    Replies: 7
    Last Post: 09-16-2012, 08:53 PM
  5. Formula to evaluate performance vs targets
    By OZManager in forum Excel General
    Replies: 1
    Last Post: 07-09-2012, 07:05 AM
  6. Conditional Targets
    By serovajoe in forum Excel General
    Replies: 1
    Last Post: 11-24-2010, 04:17 PM
  7. Sum To Work Out Targets
    By Mikey2005 in forum Excel General
    Replies: 1
    Last Post: 02-23-2006, 04:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1