Go Back   Team-BHP > BHP India > The Indian Car Scene


Reply
 
Thread Tools Search this Thread
Old 27th November 2010, 19:40   #1
BHPian
 
Join Date: Nov 2010
Location: Hyderabad
Posts: 38
Thanked: Once
Default Small Car Ranking Guidance

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!!
Attached Files
File Type: xlsx Smallcar Mod.xlsx (78.5 KB, 919 views)
Archon is offline   Reply With Quote
Old 28th November 2010, 11:36   #2
BHPian
 
Join Date: Nov 2010
Location: Hyderabad
Posts: 38
Thanked: Once
Default

I see 95 views and no comments. Is it that difficult to understand? or people are not finding utility in it?
Archon is offline   Reply With Quote
Old 28th November 2010, 14:33   #3
BHPian
 
Join Date: Aug 2010
Location: Mumbai
Posts: 161
Thanked: 114 Times
Default

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.
vivekz is offline   Reply With Quote
Old 28th November 2010, 15:15   #4
Senior - BHPian
 
xingamazon's Avatar
 
Join Date: Sep 2009
Location: Chennai
Posts: 1,399
Thanked: 111 Times
Default

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.
xingamazon is offline   Reply With Quote
Old 28th November 2010, 15:33   #5
Senior - BHPian
 
aargee's Avatar
 
Join Date: Jan 2010
Location: KATN
Posts: 4,697
Thanked: 2,143 Times
Default

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?
aargee is offline   Reply With Quote
Old 28th November 2010, 18:25   #6
BHPian
 
Join Date: Nov 2010
Location: Hyderabad
Posts: 38
Thanked: Once
Default

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

Quote:
Originally Posted by aargee View Post
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 is offline   Reply With Quote
Old 28th November 2010, 20:12   #7
BHPian
 
Join Date: Nov 2010
Location: Hyderabad
Posts: 38
Thanked: Once
Default

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.
Attached Files
File Type: xlsx Smallcar Mod.xlsx (76.3 KB, 413 views)
Archon is offline   Reply With Quote
Old 28th November 2010, 22:48   #8
BHPian
 
mohandas's Avatar
 
Join Date: Feb 2008
Location: Hyderabad
Posts: 266
Thanked: 27 Times
Default

great effort, Man!. any plan for sedan as well?
mohandas is offline   Reply With Quote
Old 28th November 2010, 22:57   #9
BHPian
 
Join Date: Feb 2009
Location: Chennai
Posts: 634
Thanked: 630 Times
Default

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.
CliffHanger is offline   Reply With Quote
Old 28th November 2010, 23:07   #10
Senior - BHPian
 
oxyzen's Avatar
 
Join Date: Feb 2010
Location: Not fixed.
Posts: 1,026
Thanked: 268 Times
Default

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

How about another sheet for sedans in the same file.

Last edited by oxyzen : 28th November 2010 at 23:15.
oxyzen is offline   Reply With Quote
Old 28th November 2010, 23:29   #11
Senior - BHPian
 
oxyzen's Avatar
 
Join Date: Feb 2010
Location: Not fixed.
Posts: 1,026
Thanked: 268 Times
Default

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.

Last edited by oxyzen : 28th November 2010 at 23:35.
oxyzen is offline   Reply With Quote
Old 29th November 2010, 04:16   #12
BHPian
 
Join Date: Nov 2010
Location: Hyderabad
Posts: 38
Thanked: Once
Default

Quote:
Originally Posted by mohandas View Post
great effort, Man!. any plan for sedan as well?
Quote:
Originally Posted by CliffHanger View Post
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.
Quote:
Originally Posted by oxyzen View Post
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

Quote:
Originally Posted by oxyzen View Post
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 is offline   Reply With Quote
Old 29th November 2010, 04:27   #13
BHPian
 
Join Date: Nov 2010
Location: Hyderabad
Posts: 38
Thanked: Once
Default

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.
Archon is offline   Reply With Quote
Old 29th November 2010, 08:21   #14
Senior - BHPian
 
SkyWalker's Avatar
 
Join Date: Jul 2006
Location: Bangalore
Posts: 1,531
Thanked: 122 Times
Thumbs up Great work

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?
SkyWalker is offline   Reply With Quote
Old 29th November 2010, 08:21   #15
Senior - BHPian
 
aargee's Avatar
 
Join Date: Jan 2010
Location: KATN
Posts: 4,697
Thanked: 2,143 Times
Default

@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
aargee is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ranking of hatchbacks in India Based on looks. vishnurp99 The Indian Car Scene 239 30th November 2016 02:33
Ranking of indian cities according to cars avishar Shifting gears 4 3rd December 2014 16:49
Sedans Under Rs. 12 Lacs - A Quantitative Ranking smartcat The Indian Car Scene 400 22nd October 2014 13:01
The Most Dependable Car Brands - Ranking Survey in US shenoytech The International Automotive Scene 15 22nd March 2011 15:35


All times are GMT +5.5. The time now is 10:29.

Copyright 2000 - 2018, Team-BHP.com
Proudly powered by E2E Networks