None-Linear Regression - Log
Transformation
This
example shows uers how to perform log transformation on the data,
construct the linear and none-linear regression models, and
forecast. On the none-linear regression model, the dependent
variable (Y) is transformed into natural log value.
The linear regression model is as followed: Y = a + bX
Three
Excel functions are used for this purpose. Intercept() function yields
the constant term, a, also as know as alpha. The Slope() function
yields the slope, b, also as know as beta. RSQ() is the R2 of the
equation - measures how 'fit' the regression model is. The R2 is
about 93%. The original row data has 11 observations. After
the value of intercept and slope are derived, forecast is made on the
12th and the 13th period. The formula for predicting Y for period
12 and period 13 are (-8.673) + 5.127*12 and (-8.673) + 5.127*13,
respectively.
Here
is the chart that displays the relationship between the row data
(presented by dot) and the predicted linear line (presented by linear
line).
The
none-linear regression model is as followed: Y = a + bExp(X)
The
row data of the dependent variable (Y) is transformed by taking the
natural log (LN) of each of the observation values. This time, R2
yields about 98%. This shows that the none-linear model is
superior than the linear model in this case. The predicted value
is then converted back to the original state by using the anti-log,
EXP(). (Note that EXP(LN(X)) = X.)
|
