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.)