Percentage of hours, based on size (weight?)

[ddander54]

I have a table of portfolio's that have varying number of clients and work hours. Each portfolio has 1-10 clients depending on the amount of work hours that are assigned, ie a portfolio with a large client with lots of work hours will only have 1 client, and a portfolio with clients with a small amount of hours may have 10 clients.

What I'm trying to do is to compare total work hours for a portfolio with the amount of repair hours and come up with a percentage (standard percentage), but then give 'credit' to the portfolio with 10 clients, vs the ones with only 1 client. I guess basically I'm saying that a 'small' percentage of repair is acceptable per client, but its gets skewed when we combine multiple clients into a portfolio, if that makes sense. so how do I 'level that out to be a more fair comparison in my formula?

Current % formula in B5 is '=B4/B3' (260/48171)

Hope my question makes sense....

Thanks,
Don

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]Portfolio1
[/TD]
[TD]Portfolio2
[/TD]
[TD]Portfolio3
[/TD]
[TD]Portfolio4
[/TD]
[TD]Portfolio5
[/TD]
[TD]Portfolio6
[/TD]
[/TR]
[TR]
[TD]# Clients
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Hours
[/TD]
[TD]48171
[/TD]
[TD]26055
[/TD]
[TD]18961
[/TD]
[TD]11801
[/TD]
[TD]5429
[/TD]
[TD]43475
[/TD]
[/TR]
[TR]
[TD]Break Fix
[/TD]
[TD]260
[/TD]
[TD]459
[/TD]
[TD]1658
[/TD]
[TD]296
[/TD]
[TD]819
[/TD]
[TD]294
[/TD]
[/TR]
[TR]
[TD]Fix/Hours %
[/TD]
[TD]1%
[/TD]
[TD]2%
[/TD]
[TD]9%
[/TD]
[TD]3%
[/TD]
[TD]15%
[/TD]
[TD]1%
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, I'm not really sure what exactly you're trying to do here.

Please can you tell us what the results should be, for each portfolio ?

Don't worry too much about trying to explain the real world application of your situation, that may not be important, just focus on describing how your data is laid out - which I think you've done - and what exactly you want to do with it - which I don't understand at all.

 

[ddander54]

Gerald,

We are an IT PMO office and have 6 portfolios that each manage a specific number of client projects. Each portfolio is a self contained unit and manages their own resources (Program Manager, PM's, AppDev, BA, QA, etc).
Now to the data:
# Clients = The number of clients each portfolio does development work for
Hours = The total number of hours of work that client has in 2018 for us to do
Break/Fix = The total number of hours in 2018 that the Service Desk has to do as support work for that client
Fix/Hours % = BreakFix/Hours

So if I think of it this way....My team does great work and delivers projects with 0 defects, then the Break/Fix hours would be zero (utopia), or My team missed something in their delivery and that caused xx hours of Break/Fix work.
But I say that with a grain of salt because there will be an acceptable (hopefully small) percentage of Break/Fix with every client that is needed for IT Support to turn switches on/off, add a new entry to a table, etc

That said...It seems like Portfolio5 gets penalized more because they have a small amount of work and a large amount of clients, therefore a larger percentage of Break/Fix. So just doing the straight percentage BreakFix/Hours doesn't seem like the right thing to do.

So my question is this...if I were a Statistician or Mathematician, how would I best reflect Portfolio5 vs the rest of the Portfolios? It seems like I could use the number of clients as the 'weight' to even out the percentage a little more (ie, compare apples to apples), but I would think that I also need to use hours as a 'weight' as well.

So this is somewhat of a philosophical question, but I'm also asking for input as to how to 'do the math' in Excel.

Hopefully that helps you understand what I'm looking for.

Thanks,
Don

 

[Gerald Higgins]

OK I'm sorry I can't help you with this one, I think first of all you need to work out for yourself the "philosophical question" of how to do this.

Once you've done that, I or someone else can probably help you work out the Excel maths.