I have a situation with a client. They have 4 sources of data and they are wanting to create a single metric out of these four values to gain a generalised insight into how the company is going overall.
The problem is that each source has a completely different scale and are not really comparable.
Source A has a scale in the millions where as Source B's scale is in the hundreds.
Further to this we wanted to weight each source as some provide more value than others.
We decided to scale all four between 0 and 1 using this formula
zi= xi− min(x) / max(x)−min(x)
and while its works I am confused as to what insight I can get out of the numbers.
Here is the google sheet I am preparing with
If you look at cell H14 and H15 can you say that March was 3 times worse than Feb because the March score was 1.1 and the Feb was 3.2?
Thanks in advance
If i will have to make it , my approuch more or less will be :
1.- Define with the client what does it mean to "company going overall" . what the client is expecting to see. i will suggest you to use a simple 0-100 scoring. Where 100 is the best scenario and 0 the worst and not start witlh over-performers.etc..
2.- As per your Xls. looks like turn-over?. if yes, You should have a 0-100 per segment. and you can asigne a pondarate overall score where max score will be 100 and min 0.
3.- If you are working just with sales funnels with an objective per segment ( funnel). take the objetives per segment if you can per week / month.
Hope it will help you
The weights distribution (0.8, 0.5 etc.) seems arbitrary. Should work better if distributed proportionately (and all totaling 100%).