Sub-Weighted Scorecard

[lorr91]

Hi there,

I'm looking to create a supplier score card which involves weighting & sub weighting - please see example of my score card below. As you can see Logistics Ordering & Packaging have been further broken down in to sub-criteria.

I took this template from a website and their way of describing the method of calculation is:

Each of individual criteria will be scored from 1 to 100 based on its performance fromthe supplier. The evaluation basis is clearly indicated for different metric. The score ofeach criteria category is separately calculated by the sum of the multiply from submetrics'point with its contribution percentage. The overall score of a supplier is acombination of 5 criteria group's contribution percentage multiplying with its score.

not sure how to put this in a formula though? any help would be amazing!

[TABLE="width: 1043"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Strategic Priorities[/TD]
[TD]Weighting[/TD]
[TD]Measures (1st tier)[/TD]
[TD]Weighting[/TD]
[TD]Score (1-5)[/TD]
[TD]Measures (2nd tier)[/TD]
[TD]Weighting[/TD]
[TD]Score (1-5)[/TD]
[/TR]
[TR]
[TD]Quality[/TD]
[TD]34%[/TD]
[TD]Defect rate[/TD]
[TD]50%[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]% customer returns[/TD]
[TD]20%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer complaint rate[/TD]
[TD]30%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Price[/TD]
[TD]30%[/TD]
[TD]Price level[/TD]
[TD]50%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Invoice accuracy[/TD]
[TD]40%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Responsiveness to discrepancies[/TD]
[TD]10%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]20%[/TD]
[TD]On-time delivery[/TD]
[TD]40%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Delivery accuracy[/TD]
[TD]30%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ordering[/TD]
[TD]15%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD]Prompt order process[/TD]
[TD]40%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Lead time [/TD]
[TD]30%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Flexibility[/TD]
[TD]30%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Packaging[/TD]
[TD]10%[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD]Protectiveness[/TD]
[TD]70%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Unpackaging easiness[/TD]
[TD]30%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adequate delivery documentation[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Relationship[/TD]
[TD]8%[/TD]
[TD]Responsiveness[/TD]
[TD]35%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Complaint handling[/TD]
[TD]30%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sharing information[/TD]
[TD]25%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]15%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Continuous improvement[/TD]
[TD]8%[/TD]
[TD]Environment[/TD]
[TD]60%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Product/Service improvement[/TD]
[TD]20%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Proactive innovation[/TD]
[TD]20%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]