Exponential regression trendline does not match data

Question

I'm attempting to generate an exponential regression equation using Excel 2010. I plan to use the equation to predict the future condition of roads.

I've mocked up a simple chart in Excel using the data below.

AGE   CONDITION
0     10
1     10
2     10
3     10
4     10
5     10
6     9
7     9
8     9
9     9
10    8
11    8
12    7
13    7
14    6
15    6
16    5
17    4
18    3
19    2
20    1

---

The orange line is based on the data. The black line is a regression trendline that has been auto-generated by Excel.

Problem:

It's clear to me just by looking at the graph that the exponential trendline does not match the data. In comparison, all of the other types of trendlines that Excel produces match the data fairly well.

For example, this polynomial trendline seems to fit the data like a glove:

Why does the exponential trendline not match the data?

Answer 1

A function of the form $m_Y = \exp(a+bx)$ (where $m$ represents some conditional population coefficient of interest, like a mean, geometric mean, or a median, perhaps, depending on how your error term is set up) is convex, while your data are not:

The plot shows two such curves, one with a negative coefficient on $x$, and one with a positive coefficient on $x$. By changing parameters, you can move the curves left or right and you can make them steeper or shallower (in essence, changing the axis scale), but this basic shape is what you get with exponential curves.

There are other functions containing exponential terms that might fit something like your data more or less okay, but you should not expect a convex function to fit data that are clearly nothing like convex.

Answer 2

My concern with your model is that you may be ignoring important mathematical properties and get bad predictions. For example, I am assuming that roads cannot spontaneously improve themselves. Roads do not get better on their own. This matters.

It may be the case that no pre-built Excel model will be a good predictive fit. It seems like roads remain quite good for a long time, but once they begin deteriorating they begin to come apart faster and faster. Although this loosely implies a quadratic-like model, the maximum value should always be for a new road and it is not under your specification. The best road happens in year two.

Your model also does not consider changes in weather or the comparability of building materials from twenty years ago to today.

Finally, your dependent variable is probably ranked data. Is a road rated as a 10 actually 10 times better than one whose rating is a 1 and is one rated a 10 exactly 2 times better than one rated a 5?

I am a little concerned that your model may be fragile for the purposes of making a prediction. Excel really isn't designed with this in mind and doesn't have the functions for this. It doesn't really go past simple models. While you may be experiencing exponential decay, your rating system won't work with that.