Elementary Mathematical Models
with Spreadsheet Applications

This brief tutorial on spreadsheets is designed to cover just enough basics to get you up and running as quickly as possible. The instructions on the mechanics of working with a spreadsheet (opening, closing, moving the pointer, clicking, etc.) are peculiar to the Microsoft Excel version 5.0, but the basic principles should be the same for other versions of Microsoft Excel or even other brand names. It is not intended to be a user’s guide nor a substitute for the documentation that accompanies the software.

What is a spreadsheet?

A spreadsheet is a computer software package that allows one to enter, manipulate and maintain data on a grid-like sheet. Two of the advantages in setting up a spreadsheet rather than entering data onto a table on a sheet of paper are

• if one changes any one piece of data in a spreadsheet, the other data that bear a relation to it (established by the user) will change accordingly. This makes the spreadsheet a "living" document, rather than a static one.

• many repetitive calculations that would be impractical by hand (such as summing columns of numbers over and over again) can be done quickly and easily through proper operation of the spreadsheet.

Why are we using spreadsheets?

The performance and review of iterative calculations can yield valuable insight into some of the mathematical principles of finance and data analysis. Often times, these calculations are so boring and time-consuming to do by hand that by the time the student is done, he has little energy nor inclination to think about what was revealed. A spreadsheet can take much of the drudgery out of these calculations and transform them into effective learning techniques. Also, the spreadsheet is a powerful real-world tool that can be put to immediate use at home or in a variety of business settings. Don’t be surprised if a working knowledge of spreadsheets gives you a competitive edge in the job market!

What do I have to know about spreadsheets?

To start, you only need to know where one is and how to activate it on a computer that you can use regularly throughout the semester (your instructor will discuss computer access). In order to get through the spreadsheet assignments in this class, you will eventually need to know how to:

• open, close, save and print a spreadsheet
• enter data and text
• set up basic relations between data entries such as addition and multiplication
• cut (remove and save), paste (put) and copy cells (data entries) to other cells

How do I get started?

The ideal way to begin with spreadsheets is to sit down at a computer with someone who has used one and do some basic operations together. Whether you can get help or not, put yourself through the following tutorial then for more information, consult the help menu that accompanies your spreadsheet software.

Begin Tutorial

Setting up a spreadsheet that compounds money in an account

The Problem: We wish to create a spreadsheet that will give us the monthly balance and interest for a fictitious bank account over a two-year period. Before we open a new spreadsheet, we should think a bit about what type of data the spreadsheet should show.

Let's assume that the initial deposit is $10,000 and that the interest rate being paid by the bank is 8.5%, compounded monthly. We will need a column that shows the month (numerals 1 through 24), one that shows the balance at the beginning of the month, another for the interest earned, and one for the balance at the end of the month, which is the sum of the starting balance and the interest earned. The monthly interest, I, will be computed by the simple interest formula, I = Prt, where t is one month (1/12 of a year) and r is the interest rate in decimal form, .085. In all, we will have four columns.

Step 0:

Start the spreadsheet software (the procedure for this depends on the computer you are working on - hopefully there is an icon for it and you can just double click on it with the mouse). By default, this action should open a new spreadsheet for you. If not, you will have to open one using the menus provided. You should see the blank grid, as below. Note that the cell A1 in the upper left-hand corner has a heavy border around it around it - the cell is said to be highlighted. Note that the highlighter moves around the screen when you use the arrow keys (up, down, left right) and that the (mouse) cursor moves freely around the screen when you move the mouse on the mouse pad. You can highlight a cell by first putting the cursor over the cell (move the mouse on the mouse pad), then clicking the mouse button once but you may find that you have more control if you move the highlighter with the arrow keys. Practice moving the highlighter to a cell then entering text and typing on the keyboard. To delete the text, highlight it, then press the delete key.

Figure 1: A blank spreadsheet

Clear whatever text you might have entered on the grid sheet and go to Step 1.

Step 1:

Highlight cell A1 and type, Compounding. Note that the text spills over into the adjacent cell.

Step 2:

In cell A3, type the column heading, month. In cell B3, type bal-start , standing for starting balance [Note: Use the column width option in the format menu to accommodate a longer title] In cell C3, type interest and in cell D3, type bal-end. So far, your spreadsheet should look like this:

Figure 2: The column headings have been entered

Step 3:

We would like to have the numerals 1 through 24 (one for each month) running down column A underneath the heading "month". In cell A4, type the number 1. You could type the numeral 2 in cell A5, the numeral 3 in A6 and so on, but we can get the spreadsheet to do this for us (this is a technique we will need later anyway). Highlight cell A5 and type =A4+1. Note that as you type this, the formula is entered in the formula bar just above the grid. Now hit ENTER (or return, as it may be labeled on your keyboard). You should see that the numeral 2 in cell A5 and that the highlighter has jumped to the cell below.

Step 4:

We are going to copy the formula in cell A5 to cell A6. Highlight cell A5 and press ctrl-C (that is, press the keys control and C at the same time). The cell border should be twinkling, indicating that a copy has been made. Highlight cell A6 and, while keeping the shift key pressed, press the down arrow (¯ ) key repeatedly until the highlighter moves all the way down to cell A27. As you do this, you should see all the cells highlighted except the first one. Release the shift key and the down arrow and press ctrl-V (that’s the control key and the V key at the same time). The command crtl-V pastes the copy to cell A6. Hit ENTER to finalize the copying and note that the numerals 1 through 24 occur down column A. (Move the arrow key to release the highlighter). In effect, we have told the spreadsheet to make each cell in this column equal to the value of the cell above it plus one.

Step 5:

5. In cell B4, type 10,000 and hit ENTER. This is our starting balance.

Step 6:

In cell C4, type the formula =B4*(0.085/12) and hit ENTER. Don’t forget the equal sign before the "B4" or else this will just be entered as text. This computes the interest in cell C4 according to the formula, I = Prt. The * symbol stands for multiplication and the / stands for division.

Figure 3: The starting balance and the interest for the first month have been calculated

Note: The numbers you get may be formatted to display a different number of decimal places than shown here. This can be changed for any number of highlighted cells (e.g. an entire column) by using the cells options under the format menu.

Step 7:

The ending balance (in cell D4) should be the sum of the two cells to the left, B4 and C4. So, in cell D4, type =C4+B4 and hit ENTER. The result in cell D4 should be 10070.83.

Step 8:

In cell B5, type the formula =D4. This will insure that the balance from month 1 is carried over to month 2.

Figure 4: The first month's records are complete

Step 9:

In cell C5, type =B5*(.085/12) and hit ENTER. Note that the formula for cell C5 is identical to the one for C4 except that it involves B5 instead of B4.

Step 10:

In cell D5, type =B5+C5 and hit ENTER. This formula is the same as the one for the cell above it, D4, except that it involves B5 and C5 instead of B4 and C4.

Figure 5: The formulas for rows 4 and 5 have been entered

Look at the pattern we wish to create for subsequent rows (don’t enter these yet):

B6 = D5 C6 = B6*(.085/12) D6 = B6 + C6

B7 = D6 C7 = B7*(.085/12) D7 = B7 + C7

B8 = D7 C8 = B8*(.085/12) D8 = B8 + C8

and so on.

We could enter all these by hand but that would be very time consuming. Instead we will simply copy row 5 to all of the rows below it. The spreadsheet will not copy the actual numbers in row 5 but rather it will copy the relations within a row.

Step 11:

Highlight cells B5, C5 and D5 all at the same time. To do this, first highlight B5, then hold the shift key down and hit the right arrow (® ) key twice before you let go of the shift key. The first cell may not appear to be highlighted, but it is.

Step 12:

Copy these cells by pressing ctrl-C.

Step 13:

Highlight cells B6, C6 and D6 all at the same time (hold down shift, ® ® ) then move the highlighter down the page with repeated down arrow keys (shift, ¯ ¯ ¯ ¯ …) Remember to keep the shift key held down. Highlight all the way through and including row 27 (the one that is labeled month 24).

Step 14:

Paste, by pressing ctrl-V (remember, that’s both keys at once). You should have the same numbers as below.

Figure 6: All 24 months are complete

Step 15:

In cell B29, type total int:

Step 16:

In cell C29, enter the formula =SUM(C4:C27) and hit ENTER. This tells the spreadsheet that C29 should be the sum of all cells from C4 to C27. The number 1845.95 should appear. This makes sense, since the total interest is the final balance, $11845.95 minus the initial deposit, $10,000.

Figure 7: The final product

The spreadsheet is complete!

Further Spreadsheet Information

To save your work

Save your work periodically

To preview your spreadhseet before printing

To print your spreadsheet

To change the look of your work

To change a column width

To insert a column or a row

To reverse a step

To open a new spreadsheet or open a previously created spreadsheet or to close the current spreadsheet

How to input a data set into the TI-81

Repeat Steps 4 & 5 until you enter in the rest of the data. [In our example, the next point is (1.05, 24), so enter x2 = 1.05 and y2 = 24, etc.]

Getting the Least-Squares Equation on the TI-81

The first number is the y-intercept of the least squares line (this is b in y = mx+b)
The second number is the slope of the least squares line (this is m in y = mx + b).
The third number is called the correlation coefficient., which we will study in the next section.
CAUTION!: The TI-81’s b is our slope m; the TI-81’s a is our y-intercept b!
Notice that the numbers in this example match with our answer worked out by hand in the previous section.

Graphing the least-squares line on the TI-81

Xmin=

Type in a number just a little less than the smallest x-coordinate in your data; then hit [ENTER]. [In the hot dog example, the smallest x-coordinate is 0.65, so enter in 0.64.]

Now the cursor will be blinking after

Xmax=

Similarly, type in a number slightly bigger than the largest x-coordinate in your data; then hit [ENTER]. [In the example, the biggest x-coordinate is 1.1, so enter 1.15]

Skip the next line (Xscl) by pressing the down arrow key once.

Similarly enter in minimum and maximum y-coordinates. [In the example, reasonable numbers are Ymin = 0 and Ymax=75.]

:Y1=

:Y2=

:Y3=

:Y4=

with the cursor blinking after the Y1= .

Hit the [VARS] key. On the top line you will see XY highlighted. Hit the right arrow twice to highlight LR. After you do this, below the top line you will see

1:a

2:b

3:r

4:RegEQ

Hit the down arrow once to highlight 2: and then hit [ENTER]. You will now be back at the previous screen and will see

:Y1=b

:Y2=

:Y3=

:Y4=

Now hit the key [X|T]. (An X should appear). Now hit [+]. (A plus sign should appear). Again hit the[VARS] key. On the top line you will see XY highlighted. Hit the right arrow twice to highlight LR. Now hit [ENTER]. You should see:

:Y1=bX+a

:Y2=

:Y3=

:Y4=

NOTE: If you have already analyzed one data set, you can skip Step 2, you only need to do it once.

To start all over on a new set of data you must first clear the memory of the old data set.

To do this: Hit the [STAT] key ([2nd] [MATRX]).

Move the cursor on the top line over to DATA and then select 2:ClrStat .

Hit [ENTER]; you will then see ClrStat displayed, Hit [ENTER] again. You are now ready to start all over.

How to input a data set into the TI-82

Tutorial

EDIT CALC
1: Edit
2: SortA(
3: SortD(
4: ClrList

with edit highlighted. We want to edit the list of data.
Step 2: Press [ENTER], you will then see:

Tip: Clearing the list: You can enter new data over existing data or clear the existing data by pressing the [DEL] key.
Step 3: Type in the x-coordinate of the first data point and the hit [ENTER].
[In the hot dog example, the data points are: (1.10, 20), (1.05, 24), (1.00, 45), (0.95, 10), (0.90, 54), (0.85, 50), (0.75, 62), (0.70, 67), and (0.65, 71). So we enter the first point’s x-coordinate 1.10.]
Continue entering in all of the x-coordinates of your data points in the 1^st column.
Step 4: Enter in the y-coordinates of the data points into the second column. You will need to use the arrow keys to position the cursor.

Getting the Least-Squares Equation on the TI-82

We assume that a set of data points has been entered.

Tutorial

EDIT CALC

1: Edit
2: SortA(
3: SortD(
4: ClrList
with edit highlighted.
Step 2: Hit the right-arrow key once, now CALC should be highlighted and a new list of options will appear. Use the down-arrow key to select the option:

5: LinReg(ax+b),

The first number is the slope of the least squares line (the variable m in y = mx + b).
The second number is the y-intercept of the least squares line (the variable b in y = mx+b)
The third number is the correlation coefficient.
Notice that the numbers in this example match with our answer worked out by hand in the previous section.

Getting the Least-Squares Line on the TI-82

We assume that a set of data points has been entered and that the you have obtained the least-squares line (via the above procedure).

If you do not see your line on the display, then adjust the window scale (see below).
If the data points do not appear on the graph, then:
[2nd] [STAT PLOT] , [ENTER], highlight ON, [ENTER], [GRAPH].

Tutorial

WINDOW FORMAT

Use the down-arrow key to position the cursor on Xmin
Type in a number that is just a little less than the smallest x-coordinate in your data set; then hit [ENTER]. [In the hot dog example, the smallest x-coordinate is 0.65, so enter in 0.64.] Now the cursor will be blinking after

Xmax=

Similarly, type in a number slightly bigger than the largest x-coordinate in your data; then hit [ENTER]. [In the example, the biggest x-coordinate is 1.1, so enter 1.15]
Skip the next line (Xscl) by pressing the down arrow key once.
Similarly enter in minimum and maximum y-coordinates. [In the example, reasonable numbers are Ymin = 0 and Ymax=75.]
Step 2: Hit the y= key (right next to the [WINDOW] key). (We are telling the calculator to graph the least squares equation). You should see:
Y1=
Y2=
Y3=
Y4=
Y5=
Y6=
Y7=
Y8=

with the cursor blinking after the Y1= .

X/Y S EQ BOX PTS

Again hit the [VARS] key, select the statistics option, and use the right-arrow to highlight EQ. Now use the down-arrow to select 2:b and hit [ENTER]. You should see:
Y1=aX+b
Y2=
Y3=
Y4=
Step 3: You are now ready to view the graph of your line. Hit the [GRAPH] button.
Step 4: To see the scatter plot of the data: Hit the [STAT PLOT] key ([2nd] [y=] ). You will then see a screen entitled: STAT PLOTS. Select the first option (Plot1), hit [ENTER]. The cursor will now be blinking on On. Hit [ENTER].
Step 5: Now view the graph by hitting the [GRAPH] button.

How to input a data set into the TI-83

y-coordinates in the L₂ column, [2nd], [QUIT]

Tutorial

EDIT CALC TESTS

1: Edit

2: SortA(

3: SortD(

4: ClrList

5: SetUpEditor

with the "1: Edit" option highlighted.

Tip: Clearing the list: You can enter new data over existing data or clear the existing data by pressing the [DEL] key. Also, you can clear all lists in the calculator by pressing MEM (via [2^nd], [+] ) and then selecting 4: ClrAllLists and pressing [ENTER] (possibly twice).

(1.10, 20), (1.05, 24), (1.00, 45), (0.95, 10), (0.90, 54), (0.85, 50), (0.75, 62),

(0.70, 67), and (0.65, 71).

Enter the rest of the x-coordinates of your data points in the L₁ column.

Obtaining the Least Squares Equation on the TI-83

We assume that a set of data points has been entered.

Tutorial

EDIT CALC TESTS

1: Edit

2: SortA(

3: SortD(

4: ClrList

5: SetUpEditor

with 1: Edit highlighted.

Your calculator will output "LinReg(ax+b)". Press [ENTER] again. You should now see (using the hot dog example):

LinReg

y=ax+b

a=-118.1666667

b=149.1583333

So the equation of the least-squares line is

y = -118.1666667x + 149.1583333

The first number is the slope of the least squares line (the variable m in y = mx + b). The second number is the y-intercept of the least squares line (the variable b in y = mx+b). The TI-83 does not display the correlation coefficient.

Graphing the least-squares line on the TI-83

We assume that a set of data points has been entered and that the you have obtained the least- squares line (via the above procedure).

[2nd] [STAT PLOT] , [ENTER], highlight ON, [ENTER], [GRAPH].

Tutorial

WINDOW
Xmin=-10
Xmax=1
Xscl=1 Note: You may see numbers other than these default values.
Ymin=-10
Ymax=10
Yscl=1
Xres=1

After "Xmin=", enter a number that is a little less than the smallest x-coordinate in your data set; then hit [ENTER]. In the hot dog example, the smallest x-coordinate is 0.65, so enter in 0.64.

Similarly, after "Xmax=", type in a number slightly bigger than the largest x-coordinate in your data; then hit [ENTER]. [In the example, the biggest x-coordinate is 1.1, so enter 1.15]

Ignore the Xscl line. (it changes the tic marks on the x-axis).

Similarly, enter minimum and maximum y-coordinates. In the hot-dog example, reasonable numbers would be Ymin = 0 and Ymax=75.

Plot1 Plot2 Plot3
\Y1=
\Y2=
\Y3=
\Y4=
\Y5=
\Y6=
\Y7=

with the cursor blinking after the \Y1= .

Hit the [VARS] key. On the top line you will see VARS highlighted. Use the down-arrow to highlight 5: Statistics… ; Press [ENTER]. You will then see a screen with the following heading:

XY S EQ TEST PTS

Use the right-arrow to highlight EQ. Below the top line you will see

1:RegEQ
2:a
3:b
4:c
5:d
6:e
7¯
r

Be sure that 1:RegEQ is highlighted , then hit [ENTER].

You will then see a screen entitled: STAT PLOTS, select the first option (Plot1), hit [ENTER]. The cursor will now be blinking over the On option. Hit [ENTER].

How to input a data set into the TI-82

Tutorial

EDIT CALC
1: Edit
2: SortA(
3: SortD(
4: ClrList

with edit highlighted. We want to edit the list of data.
Step 2: Press [ENTER], you will then see:

Getting the Least-Squares Equation on the TI-82

We assume that a set of data points has been entered.

Tutorial

EDIT CALC

1: Edit
2: SortA(
3: SortD(
4: ClrList
with edit highlighted.
Step 2: Hit the right-arrow key once, now CALC should be highlighted and a new list of options will appear. Use the down-arrow key to select the option:

5: LinReg(ax+b),

The first number is the slope of the least squares line (the variable m in y = mx + b).
The second number is the y-intercept of the least squares line (the variable b in y = mx+b)
The third number is the correlation coefficient.
Notice that the numbers in this example match with our answer worked out by hand in the previous section.

Getting the Least-Squares Line on the TI-82

We assume that a set of data points has been entered and that the you have obtained the least-squares line (via the above procedure).

If you do not see your line on the display, then adjust the window scale (see below).
If the data points do not appear on the graph, then:
[2nd] [STAT PLOT] , [ENTER], highlight ON, [ENTER], [GRAPH].

Tutorial

WINDOW FORMAT

Use the down-arrow key to position the cursor on Xmin
Type in a number that is just a little less than the smallest x-coordinate in your data set; then hit [ENTER]. [In the hot dog example, the smallest x-coordinate is 0.65, so enter in 0.64.] Now the cursor will be blinking after

Xmax=

Similarly, type in a number slightly bigger than the largest x-coordinate in your data; then hit [ENTER]. [In the example, the biggest x-coordinate is 1.1, so enter 1.15]
Skip the next line (Xscl) by pressing the down arrow key once.
Similarly enter in minimum and maximum y-coordinates. [In the example, reasonable numbers are Ymin = 0 and Ymax=75.]
Step 2: Hit the y= key (right next to the [WINDOW] key). (We are telling the calculator to graph the least squares equation). You should see:
Y1=
Y2=
Y3=
Y4=
Y5=
Y6=
Y7=
Y8=

with the cursor blinking after the Y1= .

X/Y S EQ BOX PTS

Again hit the [VARS] key, select the statistics option, and use the right-arrow to highlight EQ. Now use the down-arrow to select 2:b and hit [ENTER]. You should see:
Y1=aX+b
Y2=
Y3=
Y4=
Step 3: You are now ready to view the graph of your line. Hit the [GRAPH] button.
Step 4: To see the scatter plot of the data: Hit the [STAT PLOT] key ([2nd] [y=] ). You will then see a screen entitled: STAT PLOTS. Select the first option (Plot1), hit [ENTER]. The cursor will now be blinking on On. Hit [ENTER].
Step 5: Now view the graph by hitting the [GRAPH] button.

How to input a data set into the TI-83

Tutorial

EDIT CALC TESTS

1: Edit

2: SortA(

3: SortD(

4: ClrList

5: SetUpEditor

with the "1: Edit" option highlighted.
Step 2: Press [ENTER], you will then see:

Tip: Clearing the list: You can enter new data over existing data or clear the existing data by pressing the [DEL] key. Also, you can clear all lists in the calculator by pressing MEM (via [2^nd], [+] ) and then selecting 4: ClrAllLists and pressing [ENTER] (possibly twice).

(1.10, 20), (1.05, 24), (1.00, 45), (0.95, 10), (0.90, 54), (0.85, 50), (0.75, 62),

(0.70, 67), and (0.65, 71).

Enter the rest of the x-coordinates of your data points in the L₁ column.

Obtaining the Correlation Coefficient on the TI-83

You will then see a screen with the following heading: XY S EQ TEST PTS. Use the right-arrow to select EQ. Below the top line you will see the following:

1:RegEQ

2:a

3:b

4:c

5:d

6:e

7¯ r

How to input a data set into a TI-85

Press the [F4] key to select "EDIT". The calculator will display:

LIST

Name=

with the cursor blinking after the equals sign. Press the [F1] key to edit the list named "xStat" and press [ENTER] You will now see:

LIST:xStat

e1=

with the cursor blinking after the equals sign.

Tip: To clear existing data, press [F2] repeatedly. This selects the DELi option in the current menu.

(1.10, 20), (1.05, 24), (1.00, 45), (0.95, 10), (0.90, 54),

(0.85, 50), (0.75, 62), (0.70, 67), and (0.65, 71).

Enter the rest of the x-coordinates. When you are finished, press [EXIT].

Obtaining the Least-Squares Equation on the TI-85

We assume that data points have been entered (see above).

Tutorial

Select CALC option by pressing the [F1] key; the calculator will display:

xlist Name=xStat

ylist Name=yStat

with the cursor blinking on the xStat. Press the [ENTER] key twice. (We are telling the calculator to use the list xStat for the x-coordinates, and the list yStat for the y-coordinates). At the bottom of the screen, you will see the following menu:

Select the LINR option by pressing the [F2] button. The calculator will display (using the hot-dog example data):

LinR

a=149.158333333

b=-118.166666667

corr=-.852178234542

n=9

The number a is the y-intercept of the least squares line (the variable b in y = mx+b)

The number b is the slope of the least squares line (the variable m in y = mx + b).

The third number is the correlation coefficient.

So, the equation of the least-squares line is y = -118.1666667x + 149.1583333

CAUTION!: The TI-85’s b is our slope m; the TI-85’s a is our y-intercept b!

Graphing the Least-Squares Line on the TI-83

If you do not see your line on the display, then adjust the window scale (see below).

Tutorial

Press [F2] to edit the range. The calculator will output:

RANGE

xMin=-10

xMax=10

xScl=1

yMin=-10

yMax=10

yScl=1

Type in a number that is just a little less than the smallest x-coordinate in your data set; then hit [ENTER]. In the hot dog example, the smallest x-coordinate is 0.65, so enter in 0.64. Now the cursor will be blinking after "xMax="

Similarly, type in a number slightly bigger than the largest x-coordinate in your data; then hit [ENTER]. In the example, the biggest x-coordinate is 1.1, so enter 1.15

Skip the next line (xScl) by pressing the down arrow key once (it changes the tic marks on the x-axis).

Similarly, enter minimum and maximum y-coordinates. In the example, reasonable numbers are yMin = 0 and yMax=75. Press [EXIT].

Press the [F3] key. A coordinate system will appear together with the following menu:

Note: You may need to clear the existing graph by selecting the option CLDRW ([F5]).

Press [F2], the calculator will display the scatterplot associated with the data points.

Press [F4] to draw the least-squares line.