I'm a lumberjack and it's OK,cuz I got my Excel Spreadsheet.

Assignment 12: Modeling fxn's in Microsoft Excel Spreadsheets

MS excel is a powerful spreadsheet program with the ability to be used in a variety of careers. Just to name a few, Teachers can use this program to manage grades, attendance, and trends in his/her classroom. Engineers can use this program to analyze experimental data, find trends, validate theories, and predict outcomes of experiments. In this obeservation, Excel will be used to analyze data from the lumber industry, given the number of board feet per tree in a forest with a known age, a function can be interpolated and used to predict the number of board feet of lumber per tree for later ages.
To start, the following tree data is considered ->
Age of Tree 100s of Board Feet
20 1
40 6
80 33
100 56
120 88
160 182
200 320
There are two sets of data: Age of the Tree, and the number of board feet per tree. From these two sets of data we would like to create a relation of the two in order to find a potential significant trend in the data. To start, we can rearrange this data as a set of points in the Cartesian Plane. The 100s of Board feet will be set dependent to the age of the tree. The points will look like the following graph:
Click on the chart for a larger view.
Ideally the sets of data would have a direct relation where the equation of the relation would be a linear equation. To determine what type of relationship there is between these sets of points, a trendline must be added to the data set. As such:
Again, click on the chart for a larger view.
When finding the trendline for the given data, if you have no prior mathematical savy, a trial and error approach can be made to find the best-fit line for the given data. Otherwise, upon visual inspection of the given data, this data series would be best represented by an exponential function. In this case Excel computes the following equation:
y = 0.0006* x^2.4926
This equation can then be used to predict the missing data points and the number of board feet per tree for older aged trees.

Age of Tree Calc # boards Age of Tree Calc # boards
20 1 220 414
40 6 240 514
60 16 260 628
80 33 280 755
100 58 300 897
120 91 320 1053
140 134 340 1225
160 187 360 1413
180 251 380 1616
200 326 400 1837
*The Rows highlighted Yellow indicate the number of boards that were not included in the given data, but calculated using the equation found from the trendline.
*The Rows highlighted Light blue indicate the number of boards that were calculated for the later tree ages. For a comparison of calculated values and given values click here->

Return to Assignment Matrix:
Questions? E-mail: gt0353d@arches.uga.edu