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

A1	Portfolio1	Portfolio2	Portfolio3	Portfolio4	Portfolio5	Portfolio6
# Clients	1	4	3	1	10	1
Hours	48171	26055	18961	11801	5429	43475
Break Fix	260	459	1658	296	819	294
Fix/Hours %	1%	2%	9%	3%	15%	1%

<tbody>
</tbody>

 

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