Assignment 12: Spreedsheet Explorations

by

Mike Cotton

In this assignment I will explore the use of a spreadsheet to analyze tree data from the lumber industry.
The tree data can be seen in Figure 1 below.


Figure 1

MS Excel will be used for this assignment because it has the ability to graph data as well as create best fit functions for the data.
After entering the data into Excel, I created a scatter plot. See figure 2 below.


Figure 2
The spread of the data suggests either a power or exponential relationship between the age of the tree and the amount of lumber that can be obtained from the tree. Figures 3 and 4 below are charts where MS Excel was used to create a exponential trendline and a power trendline respectively.


Figure 3



Figure 4

The correlation for each of the trendlines (exponential & power) is given on its chart. The correlation for the exponential trendline is 0.8986 and 0.9999 for the power trendline. The correlation can range from 0 to 1, where a correlation of 0 means there is no correlation between the age of the tree and the amount of the lumber, and a correlation of 1 suggests a very high correlation. The exponential is less than the power correlation, therefore the power trendline is the best fit.
To be able to predict the amount of lumber per tree for a given year would be useful. One way would be to use the trendline on the chart to make prediction. Another way is that Excel can calculate an equation that can be used for prediction. For the power trendline the equation is 5.863799E-4 * x2.4926086. Figure 5 below is a chart that uses this equation to predict board feet of lumber based on the age of the tree.

Figure 5

It is now possible to predict the amount of lumber per tree based on any age of a tree.

Assignment 11        Return to main Page        Final Assignment