I made a scattergraph (Car price Y; Km X). It is ok for comparison at
say a given level of Km, but as prices are scaled from $4000 to $7500
and 0 to 120 kliometers it is a little artificial in so far as an extra
$1000 might get a car that has done 40,000 fewer km .
I'm wondering what statistical/ mathematical measures I might use to
squeeze more out of the data.
You could use the "Exponential" trendline type in the chart to calculate
a dollars per mile marginal depreciation rate. The trendline equation
that is displayed looks like this
y = e^[some number]x
The number (which we'll call K) will probably be negative if the cars
are getting cheaper the more miles they have. The equation is another
way of saying
cost = exp(K times mileage)
or
ln(cost) = K times mileage
Copy the number K, including the negative sign (don't forget to get lots
of digits! the more the better) and paste it back into a cell in your
spreadsheet. K is the average marginal depreciation, in dollars per
mile, of the cars *by cost*.
dollars cheaper/extra mile = K times cost
Since that's a little awkward (you'd rather have the depreciation for a
car of miles travelled), you want to convert the cost into mileage using
the same equation as above
dollars cheaper/extra mile = K times exp(K times mileage)
It's okay for K to appear twice in that equation, that's not a mistake.
Now you should be able to look at a car of 100,000 miles, and guess how
many dollars value it would lose by running another 10,000 miles. Of
course, different models of car would have different depreciation rates.
If your database is big enough, you might like to calculate a different
depreciation for every model of car.
If you don't want to use the trendline, you can use LINEST() to get K
from a column of LN(cost) against a column of mileage. If you're not
sure you got the right number, you can check to see if LINEST and the
trendline are giving the same answers.
