Re-creating ranking table to allow scenario analysis

Hi all,

At work I have been asked to re-create a league table comparing employers, and build in the underlying scoring system so that we can estimate the impact of improving in one or more measures (assuming all other scores and employers remained the same).

The problem is I do not know the exact methodology of how the league table was produced. I know that the final score is produced from 4 equal-weighted scores, with the first score comprised of two sub-scores weighted 0.67 and 0.33 respectively.

The accompanying text also states that "Tables were based on four indicators: employee satisfaction, quality, weekly wage and retention. Theses were combined using a z-score transformation with equal weighting for the indicators and the totals were transformed to a scale with 100 for the top score."

So far I have tried to convert each of the known figures into a z-score (though 4 are percentages, and my research hasn't told me much about the validity of doing this).

I have then combined the 4 z-scores and ranked the employers based on this score. My ranking doesn't match that of the league table - I'm not sure where I'm going wrong. I've also tried creating a rank score for each and simply summing that rank score, that doesn't work either. Surely if all the weightings are equal a summed rank score should produce the same ranking as in the actual league table?

I have the following specific questions:

• Should I be creating z-scores for the actual raw indicator, it's rank, or its rank score?
• Some of the employers have missing data, how should this be handled?
• Where am I going wrong with replicating the final score?

I have attached a spreadsheet showing my steps and working, I'd be really grateful for any advice.

Kind regards,

Scoobing