Mathematics Explorations with
Microsoft Excel Spreadsheet
Assignment 12
By Gloria L. Jones
This
assignment will demonstrate how Microsoft Excel (spreadsheets) can be used to
manipulate data graphically, in particular, create mathematical models for data
to be used for regression analysis.
The
following data represents lumber output per tree (in 100s of board feet) based
on the age of the tree:
|
|
Tree
Data |
|
Age
of Tree |
100s
Board Feet |
|
|
|
|
20 |
1 |
|
40 |
6 |
|
60 |
|
|
80 |
33 |
|
100 |
56 |
|
120 |
88 |
|
140 |
|
|
160 |
182 |
|
180 |
|
|
200 |
320 |
Typically when one
first obtains data, a scatter plot should be generated to detect possible signs
of a mathematical relationship:
At
a glance one might guess that there exists a mathematical model to approximate
this data. The shape of the graph
hints at possibly an exponential or power function to better model the data.
Letšs
first attempt to fit an exponential curve to the data:
I
The
curve fits closely for the lower numbers but as the age of the tree increases,
the observed data does not increase at such a high rate (notice the outlier at
age 200). Note that the
R² value is 0.8968. The higher
this value is the better the prediction.
Also,
notice the residuals (difference between observed and predicted values) for
this model:
It
is clear that the exceptionally large residual at age 200 which contributes
strongly to an average sum of squares of 14306.15646.
Indicators
hint that a power regression model would probably serve the data better:
Notice
how exceptionally tight the trend line fits the data points with considerably
lower residual error. Most likely
this will serve as our best model for the data. The R² value of 0.9999 also serves as a quick
determinant of how close the fit is.
Note that the power function has a higher R².
The
mean sum of squares is a relatively low 11.71996878.
Being
careful not to extrapolate beyond our range of values, we can now use the power
model equation
y=0.0006x^(2.4926)
to
predict the lumber harvest for trees that are 60, 140, and 180 years old. The lumber out put in 100s of board
feet are 16.232, 134.15, and 250.983 respectively.