Weighted scores

[FryGirl]

Hoping someone may have a suggestion on how to achieve this weighted score. As you can see in the data below, there are 4 categories. Each category is weighted by the percentages in the second table and can be seen by the Sumproduct formula used in row 2. After developing the weighted score in row 2, row 13 now shows the risk factors from the third table.

Here's the challenge, at least for me. In row 21 - 23, I need to now weight by the weights in row 8. This is where I do not have a formula. I'm actually looking for either identifying on overall score based on the Low, Med, or High, but also maybe like a grade level score card, A, B, C, D, and F.

I'm open to any suggestions as I'm struggling with how to proceed. Thanks

Weighted.xlsx
	A	B	C	D	E
1		Cat 1	Cat 2	Cat 3	Cat 4
2	Sample Data	81%	69.00	89.63%	24.38%
3			72.00	89.9%	17.9%
4			68.00	88.8%	36.4%
5					45.7%
6
7		Cat 1	Cat 2	Cat 3	Cat 4
8	Weights	50%	30%	10%	10%
9			25%	75%	70%
10			75%	25%	20%
11					10%
12
13		High Risk	Low Risk	Med Risk	Med Risk
14
15	Low Risk	90% – 100%	< 81	90% – 100%	< 15%
16	Med Risk	85% – 89%	81 – 110	85% – 89%	15% – 25%
17	High Risk	< 85%	> 110	< 85%	> 25%
18
19
20
21	Low Risk	9.0 – 10.0		A
22	Med Risk	8.5 – 8.9		B
23	High Risk	< 8.5		C
24				D
25				F
Sheet1

Cell Formulas
Range	Formula
C2:D2	C2	=SUMPRODUCT(C3:C4,C9:C10)
E2	E2	=SUMPRODUCT(E3:E5,E9:E11)
B13,D13	B13	=IF(B2<0.85,$A17,IF(B2<0.9,$A16,$A15))
C13	C13	=IF(C2>110,$A17,IF(C2>81,$A16,$A15))
E13	E13	=IF(E2>0.25,$A17,IF(E2>0.15,$A16,$A15))

 

[ValuationMan]

I follow everything until the final step weighting. Based on your data, what answer are you expecting?

 

[FryGirl]

That's just it. I don't have an answer. The bosses are trying to fit the answer within the Low, Med, or High of B21, B22, and B23

I just don't see how it can be forced into that box. As of right now, of the 4 categories, there is one High, one Low, and two Meds, but each one of those based on the percentage in row 8, it will somehow make up one overall risk. This is one reason why I was thinking the A, B, C grading might fit better.

 

[ValuationMan]

Hopefully this is helpful. I was recently involved in a project where we were putting all of our clients into tiers. There were four main categories with three subcategories for each categories. All of the subcategories would be scored on a scale of 1-5. For instance, days payable less than 30 days would be a 5, <45 would be a 4, etc. Once each category was scored, it was weighted to get an overall 1-5 score. I have a number of solutions for that, but it seems like your first step is to figure out your scale. Does that help at all? Happy to give more insights from our project.

 

[FryGirl]

I guess I'm still lost here. They did establish the scale as seen in the third table.

 

[ValuationMan]

Let me try again. This what I understand from your problem
Step1: you get a score in row 2.
Step2: Using to the score in row 2, you are able to establish the risk factor (as found in 13) based on the reference table 15:17
Step3: Now that you have that risk score, you want to use the weighting (row 8) to come up with an overall score.

What I don't understand is how to you translate the risk factor and weighting into a scale of 1-10 (so you can use the table in 21-23). For obvious reasons, multiplying row 13 by row 8 won't work.

 

[FryGirl]

Ok, thanks. This has nudged me in a hopefully good direction. Since this is for somebody else, I'll have to float it by them.

Basically, I will weight the low risk to 10, med risk to 5, and then high to 1. Again, not sure if this will work, but surely something I thing I can experiment with.

Thanks for hanging in with me.