Weighting Question!

[Debstated]

Good Evening!

I am having trouble getting the last step of this worksheet done.

Basically I have 1 Budget and 3 projects types. I want to weight the projects by importance but maintain a balanced budget.

For example:

Budget: 1500

Project A: 5 Team Member
Project B: 4 Team Members
Project C: 6 Team Members

I used to just do 1500/15 (Total Team Members) and it was $100 per member on the team. Meaning Project A would be a $500 project, Project B would be a $400 project, Project C would be $600.

However now I want to prioritize, because the work on the different projects is much more complex than others but don't lend themselves to larger teams or some are a lot of low skill work so we have more people.

The number on the team matters so currently I am doing:

Budget 1500

High Complexity: (Budget/Members)*1.2
Medium Complexity: (Budget/Members)*1
Low Complexity: (Budget/Members)*0.8

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Complexity[/TD]
[TD]Members[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]High[/TD]
[TD]3[/TD]
[TD]360[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]High[/TD]
[TD]5[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Medium[/TD]
[TD]2[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Low[/TD]
[TD]5[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]1560[/TD]
[/TR]
</tbody>[/TABLE]


So now I'm over budget $60 so I'll adjust the multiplier but then I am under budget then over and then under never getting to equality Nirvana. Because the Members and the Budgets are changing monthly I need to figure out a way to make excel calculate what I should weight each project type (High, Medium, Low) so that the budget is always balanced.

Any ideas?

Thanks!

 

[jproffer]

Have you tried 0.76 instead of 0.8, leaving the others alone? It doesn't make sense at first glance, but try it and see if it holds true through several different budgets/priorities/etc.

 

[jproffer]

That didn't to it either.

But, I did get 1.15 / 1 / 0.76 to come out to 1500 with your example. Not sure if those numbers are acceptable, I'm sure there are other combinations that work also.

 

[Debstated]

I used 1500 in my example but I am actually working with about 700,000 (but fluctuates) as do teams.

I think I figured out how to spend my whole budget

I did the ((Total Budget*% of Budget)/# of Team Members of Complexity)*(Project Team Size)

So High Complexity Team Members are worth more than low complexity even if low has more members.

I am sure there is a more elegant way to do this so if anyone has any opinions, please let me know. Thanks y'all!

 

[joeu2004]

@Debstated.... Try:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Budget
[/TD]
[TD="align: right"]1500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Complexity
[/TD]
[TD]

Weights​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]High
[/TD]
[TD="align: right"]1.2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Med
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Low
[/TD]
[TD="align: right"]0.8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Total
[/TD]
[TD="align: right"]15.6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Project
[/TD]
[TD]Complexity
[/TD]
[TD]

#Members​

[/TD]
[TD]

Exact Cost​

[/TD]
[TD]

Rounded Cost​

[/TD]
[TD][/TD]
[TD]

Weight​

[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]A
[/TD]
[TD]High
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]346.153846
[/TD]
[TD="align: right"]346
[/TD]
[TD][/TD]
[TD="align: right"]1.2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]B
[/TD]
[TD]High
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]576.923077
[/TD]
[TD="align: right"]577
[/TD]
[TD][/TD]
[TD="align: right"]1.2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]C
[/TD]
[TD]Med
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]192.307692
[/TD]
[TD="align: right"]192
[/TD]
[TD][/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]D
[/TD]
[TD]Low
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]384.615385
[/TD]
[TD="align: right"]385
[/TD]
[TD][/TD]
[TD="align: right"]0.8
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]1500.000000
[/TD]
[TD="align: right"]1500
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
B7:  =SUMPRODUCT(C10:C13,G10:G13)
D10: =$B$1*C10*VLOOKUP(B10,$A$4:$B$6,2,0)/$B$7
D14: =SUM(D10:D13)
E10: =ROUND($B$1*C10*VLOOKUP(B10,$A$4:$B$6,2,0)/$B$7, 0)
E13: =B1-SUM(E10:E12)
E14: =SUM(E10:E13)
G10: =VLOOKUP(B10,$A$4:$B$6,2,0)
Copy D10 into D11:D13
Copy E10 into E11:E12
Copy G10 into G11:G13

In general, when we have weights w[1], w[2],..., w[n], the formula for each line "i" would be total*w / SUM(w, i=1,...,n). In your case, the weight for each line is m*w,
where m is the number of members; and the formula for each line is total*m*w / SUM(m*w, i=1,...,n).

(Note that those are mathematical formuls, not Excel formulas.)

The calculations in column D are unnecessary. They are included to demonstrate the point. The values displayed are rounded due to the cell format.

The calculation in E13 is needed to ensure that the total is the budgeted amount. Otherwise, the column total might differ slightly due to rounding. That are other ("better") ways to round each line in column E in order to minimize the "error" (nonproportional result) in E13, especially when there are many more projects.

The calculations in G10:G13 are a "bandaid" (work-around). The formula in B7 should be
=SUMPRODUCT(C10:C13,VLOOKUP(B10:B13,A4:B6,2,0)). But that does not work for some inexplicable reason (defect, IMHO).

 

[Debstated]

This was immensely helpful thank you!

 

[joeu2004]

The calculations in G10:G13 are a "bandaid" (work-around). The formula in B7 should be
=SUMPRODUCT(C10:C13,VLOOKUP(B10:B13,A4:B6,2,0)). But that does not work for some inexplicable reason (defect, IMHO).

Avoid the formulas in G10:G13 by using the following formula in B7, courtesy of Aladin Akyurek:

=SUMPRODUCT(C10:C13, SUMIF(A4:A6,B10:B13,B4:B6))