Productivity Targets

[cmcn45]

I am building a document that shows a productivity target for almost 200 positions. My data is trailing 12 month actual productivity for each position by week. Currently I have a formula that counts the weeks if they are greater than the current productivity target, divided by the number of weeks (ie. a position hits a target of 7.5 covers per labor hour 31 weeks out of the year, or 60% of the time). What I am trying to build is a formula that will tell me what each positions actual weekly productivity target needs to be to be able to hit that target 75% of the time. Now, I can back into the 75% manually with a Countifs for weeks that are greater than a hard-coded productivity divided by the number of weeks, and just keep changing that productivity manually until I get the % to where I need it to be. But that is not a practical solution to update for almost 200 positions every week. This is driving me insane trying to figure out so any help would be appreciated.

 

[Gerald Higgins]

Hi, welcome to the board.

This is probably doable, but it's difficult to give a precise answer without understanding more about how your data is laid out.

Can you clarify please ?

Don't worry too much about explaining the real world application of this problem, that may not be too important.
Focus on describing exactly what your data looks like, and what exactly you want to do with it.

Can you give us sample data for maybe 2 or 3 different positions, each with different levels of performance, and say what the results should be for each of them, and why ?

 

[cmcn45]

I have the positions going down column A, with their target productivity, weeks with productivity (# of weeks where productivity is > 0), and % of weeks hitting productivity going down columns B through F. My historical productivities by week are in the same row as their respective position, in columns G through BF. It looks something like this:

Column
A B C D E F G:BF
Position Target Productivity Weeks with Productivity % of Weeks Hitting Prod Revised Productivity Revised % of Weeks | Historical Data
Cook 7.5 52 60% ? 75% 7.39 8.92 6.44 ...
Bartender 8.3 48 55% ? 75% 7.30 9.91 10.05 ...

Column E is where I am trying to write the formula. I know that, for example, the revised productivity for Cook should be 7.2 based on the formula I described in my first post, where I can manually back into it. This revised target should change slightly every week though as my rolling 52 week data changes. I also have backup data tabs that are pulling hours worked by position by day, and covers by day, which is what is driving columns G:BF (sum of hours worked / sum of covers).

 

[Gerald Higgins]

OK, I think I understand how your data is laid out, thanks.

And you're trying to calculate the number in column E.

Why should the figure for cook be 7.2 exactly ?
And what should the figure for bartender be ?

 

[cmcn45]

7.2 is the productivity that Cooks would be able to meet in 75% of weeks, based on their 52 week historical productivities (or, to put it another way, 75% of Cooks 52 week historical productivities are greater than 7.2). Which is also why the 7.2 target would likely change slightly every week as I update this file, as my 52 week data rolls to the next week, and why I need a formula to calculate the 7.2 for me. The purpose is to create a "moving target" for productivity that will update with their historical data, so that as actual productivity gets better (or worse) the target productivity will change, and then I can add different weights to different dates for things like seasonality. I will put the current 52 week data for Cooks at the bottom if you really want to see what I'm talking about.

The figure for bartender would be 8.3, based on their current 52 week productivities.

Cook:
7.39 8.92 6.44 7.44 5.21 8.72 7.88 6.99 6.78 8.57 7.24 7.21 6.73 8.21 7.19 6.31 8.48 8.61 9.26 6.52 7.88 7.23 8.23 7.09 7.60 8.85 6.79 7.92 8.00 8.20 7.95 7.57 8.16 6.80 7.53 8.04 7.46 8.38 7.45 7.45 7.71 8.51 8.35 8.40 8.70 8.38 8.40 8.39 8.17 6.36 7.75 6.10

Bartender:
7.30 9.91 10.05 7.72 2.06 - 9.68 8.33 5.98 13.22 7.05 8.33 10.56 11.72 10.78 5.03 11.33 7.24 11.76 8.37 11.97 4.94 11.15 4.86 6.47 14.41 8.40 7.25 6.61 13.96 6.26 5.68 10.29 5.62 11.93 9.81 10.44 14.08 11.71 11.96 8.81 8.83 8.53 13.19 8.86 13.50 14.81 13.90 10.99 9.88 16.73 12.17

 

[Gerald Higgins]

Yeah ok, I think I get it, thanks. I'll go away and think about it.

In the meantime, if any gurus want to come back with a quicker answer, feel free !

 

[Dryver14]

I think if I was tackling this problem and thought the best that could be achieved was 75% then I would set that figure to 100% A. It makes the numbers look better 2. The maths would be far easier