Math 1020, Mathematics for the Liberal Arts, Fall 2015

Activity #05: Introduction to Mathematical Modeling, Friday, Sept. 11

Learning Outcomes:

• Introduction to sets of paired data

• Deciphering and computing with complex formulas which include Σ-notation

• Generating a least-squares linear equation from paired data

• Data ranges and introduction to interpolation and extrapolatio

NAME(s)______

As you’ve read, mathematical modeling is the process of finding a mathematical framework to describe some real-world phenomenon. In addition to building some mathematical models, in the next section of the course we’ll be addressing the two key issues raised in this week’s assigned reading:

A. How well does our model fit the information we’re trying to describe? In particular, could we use it to make reliable predictions? We’ll be modeling using the process of “regression” which was outlined in the online reading. This means we’ll be trying to find a straight-line equation that matches the data points as well as possible.

B. What is the “scope” of the model? That is, how well can it be expected to predict currently unknown values within our data range (this is called interpolation), and how well can it be expected to predict values outside our data range (this is extrapolation)?

Our first model will involve the water volume of Bush Lake, just west of campus. We’ll be looking at the relationship between the level of the surface of the lake (in feet above sea level) and the estimated volume of the lake (in millions of cubic meters) (approximated by looking at contour maps of the lake bed and doing some fancy calculations, whose details we won’t worry about).

The data we’ll be using come from the Nine Mile Creek Watershed District’s publication called the “Bush Lake Use Attainability Analysis.” To see the data (you need to be logged into the Normandale student network):

From the Start menu, select “Computer”. Double-click on “ShrDirs(Y:)”, then “Dept,” “MATHCSI,” Dunlop,” “Math1020.” Then double-click on the file called “OpenMeFirst.xls.” This will open the file in EXCEL®.

Before doing anything else, SAVE A NEW COPY OF THE FILE, either to your “H” drive or to a personal flash drive. You cannot modify the master copy.

Now fill in the following, by finding the answers in the table:

Question 1. The smallest lake level in the data is______feet above mean sea level

Question 2. The largest lake level in the data is______feet above msl

Question 3. The smallest lake volume in the data is ______million cubic meters

Question 4. The largest lake volume in the data is ______million cubic meters.

Question 5. So lake levels that are within our data range are between ______and

______feet above sea level. (Use two of your answers from the preceding questions.)

The first thing we’re going to do is to develop a linear equation model for the relationship between the lake level (x) and the lake volume (y). Most people would probably consider it obvious that, as the lake level gets higher, the volume of the lake increases. But that doesn’t give us enough information to predict how much water will be in the lake for a given lake level. For that, we need a formula of some kind. We’re going to use the “linear regression” process outlined in the assigned online reading for this week. We’ll go through it slowly, step-by-step. At the end of it, we’ll have an equation:

y = mx + b

relating the lake volume, y, to the lake level, x. Once we have this equation, we can use it to predict the lake volume (which is hard to measure) from the observed lake surface elevation (which is relatively easy to measure).

In the reading, you saw that there are formulas for the numerical values of m and b (you may remember these as slope and y-intercept, respectively), which use the actual data values in the table. The formulas used the “Σ” notation for sums, and it went like this:

m =

b = .

(Wait, WHAT?????) Don’t panic, these are just numbers to plug in. In fact, the EXCEL® table already has and calculated. Find them (hint: what does “Σ” stand for?):

Question 6. = ______ = ______

n is just the number of data pairs in the table, so count them:

Question 7. n = ______.

All we need to find now are the sums of all the products xy, and the sum of all the squares of the x – values. You’ll follow a similar process to the interactive part of the Web reading. In the spreadsheet, you’ll see a column for “xy” and another one for “x^2” (which means , but I don’t know how do to exponents in EXCEL®).

In the first “xy” entry: Type an = sign, then click cell B2 (which has the first value of x); type *, then click cell C2 (which has the first value of y). Then hit the Enter key in the lower right corner of your keyboard. The calculated value in cell D2 should be 1488.562838 (which is 831.46×1.7903).

Now we use the magic of spreadsheets. Place your cursor over the lower right corner of cell D2, until it becomes a solid “plus” sign. Then left-click the mouse and, holding the mouse button down, drag down to the last row which contains data (row 11). Let go of the mouse button, and all of the column fills in with the value of xy for that row. The sum is automatically calculated in row 13, so you can fill in (do not round):

Question 8. = ______

There’s only one more piece missing from the puzzle that is the formula for m; that’s . We’ll use EXCEL® again to fill in column E.

Place your cursor in cell E2, type =, then click cell B2 and type ^2 (your cell should read “B2^2”.) Hit the Enter key as before, then click and drag from the lower right corner to fill in the column. You now can fill in the last piece:

Question 9. = ______

OK! All the pieces are in place! (Fill in the pieces, and do the calculations, on the rest of this page. Clearly circle or box your final answer!)

Question 10. m = =


And once we have a value for m, we have all of the pieces of the puzzle for b:

Question 11. b = =

So: Our “best-fit” equation for the lake volume, y, in terms of lake level, x, is:

Question 12. y = mx + b = ______x + ______(fill in the numbers you found above!).

This is our first attempt at a mathematical model for the relationship described verbally as: “the relationship between the level of the surface of the lake (in feet above sea level) and the estimated volume of the lake (in millions of cubic meters).” Using the “regression equation” guarantees that this is the most accurate straight-line-graph equation for the relationship.

The equation in Question 12 is important; we’ll be working with it on Monday and Wednesday.