[Archon]

Hi all,

Inspired by smartcat's effort earlier this year, I have tried to put together an excel sheet (attached) where you can make your own adjustments for preferences as well as override default figures to get dynamic rankings (separate tab)

Reply back for comments suggestions.

Thanks to smartcat for putting together the data and giving the criteria! He really did a lot of hard work there!!

[Archon]

I see 95 views and no comments. Is it that difficult to understand? or people are not finding utility in it?

[vivekz]

Nice attempt Archon. Though I have not got a chance to play with the settings too much, will do it later. But really appreciate the effort you made developing the excel model with so many variables. I hope people find it useful.

[xingamazon]

The tool is great Archon, thanks for your help.
Will try playing around with the sheet. But the details are what I need thanks to you and Smartcat.

[aargee]

I tried in the morning, but the results doesn't seem to be sorted out. No matter what I select in the Settings tab & couldn't understand what to expect the outcome. For instance, I changed Performance to 45, Driveability to 15, Running cost, Price, Safety & Equipment to 10 & Practicality to 0. But couldn't find any difference in ranking or Total points.

What should I expect? Just based on the rank on sheet 1, what is that I should be looking at on other sheets?

[Archon]

Thanks guys; any effort on my part will only be rewarded if people find utility with this sheet.

Originally Posted by aargee

I tried in the morning, but the results doesn't seem to be sorted out. No matter what I select in the Settings tab & couldn't understand what to expect the outcome. For instance, I changed Performance to 45, Driveability to 15, Running cost, Price, Safety & Equipment to 10 & Practicality to 0. But couldn't find any difference in ranking or Total points.

What should I expect? Just based on the rank on sheet 1, what is that I should be looking at on other sheets?

First of all, in my hurry to upload the sheet I forgot to put in any instructions..so my bad.

But first, if you feel the rankings are not updating just look at Tata Nano when you're changing price weightage. Cars like i20 are difficult to displace from the top!

Bug Update: There is a problem if two or more cars have the same rank in which only one shows up. Will fix this and upload shortly.

[Archon]

Uploading an updated version of the sheet with a fix to take care of cars with same rank. Also here's what I should've put back in the opening post:

Instructions & Methodology for the rankings

Instructions
STEP 1
In Settings tab/sheet there will be lots of places where you will find long blue bars which act as "breakers" between categories. These are the sum of all weightages in a category and have to total to 100(%). So in your example aargee, this condition is satisfied.

You will not be allowed to enter any weightages which make the total exceed 100, so you will need to reduce a parameter's weightage before you can increase another. Say for example, if I only want a ranking on Price, I would first have to make all other parameters zero, and then jack up Price to 100.

STEP 2
Ensure that the Petrol/Diesel Prices on the same tab/sheet are not blank or zero and have correct values for your city/town.

STEP 3
If you're including Safety as a parameter make sure the Airbag points are as per your requirements on the Settings sheet.

The rankings update on the Rankings sheet while you're making all these changes.

STEP 4
What is most fun though is if you tinker with the hard data that Smartcat accumulated from various magazines. I know lots of people do not agree with that data, so you can edit that data in the Base Data sheet directly, and watch rankings change. I would especially recommend updating the prices for the cars you are wanting to compare since the default figures are probably dated.

PS Unless you do this its quite difficult to remove the smug Hyundai i20 Diesel from its perch

METHODOLOGY
1. For each parameter, a central value (median) is found which is assumed as a rating of 0
2. Each car is rated on this parameter in % terms. So for example, if cost per km of using an i10 1.2 is Rs 5 and the media cost per km is Rs 10, then the i10 1.2 is given 50 points for being 50% better than the median. Similarly if another car had a cost/km of Rs. 20 then it would have got -100 because its 100% worse than the median.
3. Weighted average based on weights defined in Settings tab is taken to get the final points as seen in the Total Points sheet. Rankings are based on these points.

[mohandas]

great effort, Man!. any plan for sedan as well?

[CliffHanger]

Archon,

Though I've not used the excel sheet yet, I could see the amount of hard work you have put in. Kudos to that.

[oxyzen]

One of the best made Excel sheet ever.
Awsome man.

How about another sheet for sedans in the same file.

[oxyzen]

One thing missed out. Ride and Handling.
Might wanna add that as well.

Wanna know why Cars like i20 are difficult to displace from the top. Because the price weightage generally used is around 20%.
We try to concentrate more on performance and running cost etc. IMHO one should set the price weightage of a car to atleast 50% to get the perfect picture.

[Archon]

Originally Posted by mohandas

great effort, Man!. any plan for sedan as well?

Originally Posted by CliffHanger

Archon,

Though I've not used the excel sheet yet, I could see the amount of hard work you have put in. Kudos to that.

Originally Posted by oxyzen

One of the best made Excel sheet ever.
Awsome man.

How about another sheet for sedans in the same file.

Thanks! Honestly, my effort was pretty minimal as compared to Smartcat's. If I can
1. finalize the methodology/parameters in this version
2. Get required data for the sedans

Then why not! Of course I will need some time

Originally Posted by oxyzen

One thing missed out. Ride and Handling.
Might wanna add that as well.

Wanna know why Cars like i20 are difficult to displace from the top. Because the price weightage generally used is around 20%.
We try to concentrate more on performance and running cost etc. IMHO one should set the price weightage of a car to atleast 50% to get the perfect picture.

If I can get unbiased hard data on ride/handling (which I doubt exists), I could add that as a parameter too.
As far as weightages are concerned, playing with them is what the workbook is all about! For example I just changed the price weightage to 50% as you asked (I think) and kept the rest about equal (except for safety/equipment which got 5% each) and ended up with 800 and Nano topping the charts!

[Archon]

Forgot to add.

I guess the idea here is to tinker with the parameter weightages according to your requirements, and keep looking at the rankings i.e. which cars stay consistently around the top.

Since a score of zero in each category is equal to the middle (median) score of all 36 cars, the Total Score maybe (not totally sure about this) interpreted as how well a particular car fares in % terms as compared to the median.

For example, in oxyzen's scenario of 50% price weightage, the 800 came out to be 11.5% better than median and the Jazz 24% worse than the median. The closest car which came to approximating the median (i.e. which was the "average" of all cars) by these weightages was the Ritz 1.3 Diesel.

[SkyWalker]

Archon - excellent effort.

This will help the new car buyers a lot! If its not too much to ask, can you also put a similar sheet for Sedans?

[aargee]

@Archon - Yes, this time there's a good difference between what was posted earlier & this updated one. However with moving times, there's one more hatch that needs to be added - The Vista 90 that was missing during the time when Smartcat listed out the ready reckoner.

Also, I feel something better can be done on this. Not that its no good, its excellent, however, something needs to be done to make the choices much easier. That's what I feel.

May be the layout, adding some buttons to recalculate or stressing the importance etc. The advantage of the button to recalculate is that, Excel allows the values to increase in the data entry cell to any limit even when the totals exceed 100, but on clicking the button, it should display an appropriate message. Pls do this at your discreetion & time, as its only a nice to have feature.

Excellent work @Archon. You deserve a loud