Applying a rating scale to numerical measurements

[GeorgiaK]

I have two lists of data that i need to graph. An example is given below.

Project Ref Investment/Beneficiary in $ Efficiency Rating
1 87.3 2.3
2 156.6 3.8
3 185.8 3.8
4 8250.0 3.0
5 1164000.0 1.8
6 1500000.0 2.1
7 2812000.0 1.6
8 21462700.0 1.7

The Efficiency column represents the results of a survey on quality using a
rating scale of 1-4 with 1 being poor and 4 good for each project. We now
want to graph the relationship of efficiency with the investment per
beneficiary that each project received.Ideally we want our data in a matrix
with the Project references as data labels. This will help us identify
projects with lets say high Investmement and poor efficiency. Because the
scales are so different (refer to project 8 compared to the others) we
thought it would be easier to apply the same scale 1-4 for the
Investment/Beneficiary results. How do you apply a rating scale to a list of
numbers so that "proportional" representation is ensured? Is this the right
thing to do with the data above?

 

[Joel]

I think your problem has to do with the wide variances in the dollar coloum.
You may want to use a LOG plot and take the log of the dollars rather than
the dollar amounts.

There doesn't seem to be a good correlattion between the Investment and
effiency and don't see the value in make this comparison. Maybe you really
want is the return in the investment rather than the investment itself.

 

[GeorgiaK]

You see these are ruralwater projects boreholes etc. Value is assessed based
on how many beneficiaries you have reached and we are trying to assess the
performance of the organisations delivering. We tried to develope some KPI
such as cost per capita gross and net but dont see how you calculate return
on your investment for projects like this.

Is a matrix a more appropriate way of presenting it maybe? quadrants could
maybe help show some trend by clustering?

Your help greatly appreciated.

 

[Joel]

I don't know if quandrant are going to give better results because I don't
know how you would define quandrants. Is there a way to classify projects
into different categories? I do think clustering by projects by Investment
size (range of dollar value) will give betterresults than what you are
presently seeing. Try project less than 10,000; 10,000 - 1,000,000; over
1,000,000 (or similar groupings).