Help creating a formula

Hi,

I just opened a used car dealership and I've found that I still buy cars that I like rather than the ones that have the most potential for making a profit. I've determined what I need to look at to find the "money-makers", but I have no idea how to put it into a formula that I can use in an Excel spreadsheet.

My example is a 2002 Windstar Mini-Van with 95K Miles and a Condition Score of 35. I'd like the Year, Mileage & Condition scores that I've assigned to have equal weight in a possible Overall score of 100.

33.3% Current Year minus Year of Car (zero being the highest)=11
33.3% Actual Mileage (zero being the highest)=95
33.4% Condition Mechanical 10, interior 10, exterior 10, tires 10 (40 being the highest)=35

I'd appreciate any help you can give me. I've been trying to figure this out for a couple of days and I just don't know how to do it. Please let me know if you need more information or if my example doesn't make sense, or for that matter if my idea is just plain dumb.

Thanks in advance,
Teresa

tacattack said:

Hi,

I just opened a used car dealership and I've found that I still buy cars that I like rather than the ones that have the most potential for making a profit. I've determined what I need to look at to find the "money-makers", but I have no idea how to put it into a formula that I can use in an Excel spreadsheet.

My example is a 2002 Windstar Mini-Van with 95K Miles and a Condition Score of 35. I'd like the Year, Mileage & Condition scores that I've assigned to have equal weight in a possible Overall score of 100.

33.3% Current Year minus Year of Car (zero being the highest)=11
33.3% Actual Mileage (zero being the highest)=95
33.4% Condition Mechanical 10, interior 10, exterior 10, tires 10 (40 being the highest)=35

I'd appreciate any help you can give me. I've been trying to figure this out for a couple of days and I just don't know how to do it. Please let me know if you need more information or if my example doesn't make sense, or for that matter if my idea is just plain dumb.

Thanks in advance,
Teresa

Click to expand...

You understand of course that all these values are arbitrary. Because you have arbitrarily decided that three criteria are going to get equal weight, use a value that is divisible by 3. And one of those criteria is going to be sub-divided into, arbitrarily, four sub-criteria of equal weight, so a value that would be divisible by 4 sounds good. So first off, I'd use a maximum value that is divisible by 12 = 3 * 4. Let's use 120 as our maximum possible score rather than 100.

Now you also have to decide what triggers a score of zero. Making this stuff up, let's say 200 thousand miles or more gets a score of zero on the mileage criterion, and 20 years old or older gets a score of of zero on the age criterion.

Your condition scores run from 0 to 10, in four sub-categories. In your example, those scores sum to 35.

Age Score = the higher of zero or 40 - 2(Current Year - Model Year). So in your example of a 2002 car in 2013 the score would be
40 - 2 * (2013 - 2002) = 40 - 2 * 11 = 40 - 22 = 18.

Mileage Score = the higher of zero or 0.2 * (200K - actual miles in thousands). So in your example, the score would be
0.2 * (200 - 95) = 0.2 * 105 = 21.

Your total score would be 21 + 18 + 35 = 74 out of a possible 120.

If you want to turn that into a percentage 74 / 120 = 61.7%.

Oh, you want to know where the 2 and the 0.2 came from. 20 years into a score of 40 is 2, and 200 thousand miles into a score of 40 is 0.2.

Perfect! Thank you so much for the help. This will work perfectly.