Using Spreadsheets in Algebra and Pre-Algebra

Cheryl Fallis, Karen Levin, Dan Lotesto, Dale Nowlin,

Susan Thurmond, Kathryn Trier, and Grace Williams

Introduction

In this paper we will consider ways of incorporating spreadsheets into the algebra and pre-algebra curricula as a problem solving tool. The intent is to focus on problem solving and mathematical investigations. The spreadsheet is merely a tool in that endeavor; whereas other tools like calculators, paper and pencil, or a variety of computer resources could be used, it is important that students see the mathematical power available to them through the use of spreadsheets. Spreadsheets also offer an excellent means for the introduction and usage of variables in an applied context.

The problems included in this paper are intended to be interspersed throughout the year and for that reason offer a variety of topics focusing on the mathematics of change. The actual mechanics of setting up a spreadsheet will not be included, as those instructions are software specific. The spreadsheets or portions of spreadsheets provided with each problem have been generated on Microsoft Excel. These are offered only as one possible format for each spreadsheet and are not meant to stifle any student or teacher creativity. The formulas shown on the sample spreadsheets would be similar to those that could be used with another spreadsheet program although the actual mechanics of entering formulas and generating graphs will differ.

One feature that may be incorporated into many spreadsheets is a control panel. A control panel makes use of spreadsheet cells, usually located at the top or side of the spreadsheet, to list all parameters involved in the problem and gives initial or constant values to each one. Other cells are then entered as formulas with variables referring to these parameters by their cell location. This lends itself to further investigations by allowing students to easily and efficiently vary the parameter values once the spreadsheet is set up. In doing so, students will begin to see the real mathematical power available to them in a spreadsheet.

Captain Vid-e-o

A Captain Vid-e-o store charges $2 for a one-day rental of a video cassette. On the average, 200 cassettes are rented from the store each day. If a survey indicates that the store's rentals will decrease by an average of 5 per day for each 10 cents increase in rental charge, what should the store charge to maximize its income?

The cost of renting one video cassette and the number of video cassette rentals are related inversely. That is, as the cost of the rentals increases the number of rentals decreases. The amount of revenue is represented by the product of the cost of renting a video and the number of videos rented.

The students will need to calculate enough values to see the increasing and decreasing behavior of the revenue figure. The students can examine the revenue column to find the maximum revenue. The one-day rental cost that will maximize the revenue is the corresponding value in the "cost per rental" column. The maximum revenue is $450 when the daily cost is $3.00. The students can also generate enough values to locate the maximum daily charge at which the revenue is $0.

The first figure shows the spreadsheet formulas used to generate the table of values. The second figure shows the table of values. The final chart is a graph of the data presented for a visual analysis. Using the graph the students can locate the maximum revenue at the highest point on the revenue curve.

The teacher can also have different students enter different initial values and then compare the results. The formulas remain the same because of the way the formulas are defined.

The Medication Problem

A patient begins taking 16 ml of a certain medication every four hours for a long period of time. The patient's body eliminates 25% of the drug every four hours. Will the amount of medication in the patient's body eventually level off and if so at what level will it do so?

After investigating the problem in small groups, a whole group discussion could center on student strategies for solving the problem and predictions for the solution. In preparing to use a spreadsheet as a tool for solving the problem students should discuss possible spreadsheet formats and make a list of parameters that could be included in a control panel. A possible spreadsheet format is given showing formulas and values.

As students complete the spreadsheet an informal discussion of asymptotic behavior would be appropriate. The level of medication in the patient's body does not reach a fixed amount but it does level off as it approaches a limit. This can be illustrated on a graph within the spreadsheet as shown below. The students could decide how close to that limit the level of medication must become for them to identify it as leveled off from a practical perspective.

This problem could be further investigated by considering one or more of the following extensions.

1. Investigate the effect on the amount of medicine in the body if the patient forgets to take one dose at some point in time after stabilization. How long does it take to restabilize?

2. Doctors often give patients an injection with a high level of medication for the initial dose and then prescribe a more moderate amount to be taken over a long period of time. Investigate the effect of increasing the initial amount to 100 ml while leaving all other parameters unchanged.

3. After a long period of time the patient stops taking the drug. Use the spreadsheet to examine the level of the drug that remains in her body over time.

4. Consider the parameters and then predict the effect that varying them would have on the level at which the drug stabilizes and the time it takes to stabilize. Use the spreadsheet to verify or disprove those predictions.

Gradebook Activity

Students are often interested in how a teacher computes averages for the grading period. Questions asked include:

What do I need to make on the last test to make an A? a C? a passing grade?

What would I have had if I had done all of my homework?

Would my grade change if the teacher dropped the lowest test grade?

What was the class average for the six weeks?

These questions, and more, can be answered with a spreadsheet. It is suggested that the instructor use actual data for the class with names changed and alphabetized. Or, perhaps, the teacher would prefer to use data from another class. The structure of the spread-sheet should be similar to the way the teacher actually computes his or her grades.

The sample spreadsheet is based on Professor Cal Q. Lator's grades for his 3rd Period Algebra I class. The table below contains the data from his gradebook. "Hwrk" is the homework average and "Proj" is the 6-weeks project grade. Professor Lator computes his averages by the formula: 60% Tests; 20% Quizzes; 15% Homework; 5% Project. The students are asked to construct a spreadsheet containing the gradebook data. Working in pairs or small groups, they are then asked to complete the spreadsheet so that it will show a Test Average, Quiz Average, and Six Weeks Average. If there is only one computer in the classroom, this can be a teacher-led activity.

On the next page is one example of a completed spreadsheet. In order to "test" a student's spreadsheet, the following questions could be asked. The questions should be changed to reflect the teacher's classroom situation.

What is S. Brown's Test Average?

What is M. Jordan's Quiz Average?

What is M. Raines' 6-Weeks Average?

The next spreadsheet copy shows the formulas that were used in columns J and K. Row 17 containing the Averages was computed the same as J17 and K17. Column I was computed the same as column J. All numbers were rounded to the nearest whole number.

An exploration of this activity could include the following questions:

1. What is R. Parbhu's final average?

2. A. de la Fuente made a 0 on Quiz 2 because he never took it. How would his grade have been affected if he had taken it and received a 50 on it? an 80 on it?

3. How would M. Raines' grade have changed if she had received a 100 homework grade?

4. How would the class average be changed if J. Lo had made a 100 and Jordan had made a 60 on Test 1? Can you explain your answer?

5. How could you modify your spreadsheet so that tests count 50% and quizzes count 30%? Would you expect this to raise or lower the average of the final averages? Why? Test your answer by making the appropriate changes in your spreadsheet.

6. Averages can be computed by using =AVERAGE(first cell:last cell). If Professor Lator decides to give a third test and drop the lowest grade, show how to adjust the spreadsheet so that it will compute the correct final average.

The Cost of a Loan

Mary Cordero decides to buy a good stereo system. The one that she chooses costs $1200. Since she only has $200 to spend right now, she will have to take out a loan for the balance of the cost. The stereo store offers her the following:

Cost of stereo: $1200

Down payment: $200

Amount of loan: $1000

Interest rate: 18% per year

Monthly payments: $50

If Mary decides to accept this offer, how long will it take her to pay off the loan, and what will be the total cost of the stereo system? Remember that the total cost includes the down payment, the monthly payments, and the final loan balance.

The following is part of a sample spreadsheet set-up which can be used to solve this problem:

Fill down in the spreadsheet until the amount in column D, the loan balance, becomes negative, say at month n. It will therefore take n-1 months to pay off the loan. The total cost of the stereo system can then be found by summing B3 (the down payment), 50*(n-2) (the monthly payments), and the final loan balance given for month n-1.

The following shows the spreadsheet with the values calculated:

It will therefore take Mary 23 months to pay off the loan, and the total cost of the stereo system can be calculated to be:

200 + 50*(22) +47.12 = 1347.12

EXTENSION: Modify the given spreadsheet to answer the following questions.

1. Mary decides that she can make a down payment of $300, rather than $200. How long will it take her to pay off the loan and how much will she save by making a larger down payment?

2. A bank offers Mary an interest rate of only 16% per year, but requires that she make monthly payments of $60. Assuming that Mary can afford the extra $10 per month, is this a better deal?

3. If Mary wants to pay off the loan in one year, what amount will she need to pay each month?

4. Mary decides to add another component to the stereo system, bringing the total cost up to $1500. How much longer will it take her to pay off the loan, and how much more will it cost her?

5. Another stereo store offers Mary what seems to be a better offer. They will charge her 12% interest for an 18 month loan if she can afford to make a down payment of 15% of the total cost of the stereo system and monthly payments of $100. Under these conditions, what is the total cost of the stereo system which she can buy? (Your answer should be correct to within $25.)

Saving for the Future

"Suppose that on the day you were born, your grandparents had deposited $1000 into a savings account in your name. On your eighteenth birthday, how much money would be in that account?" This type of question will usually arouse student interest. Of course, it should lead to further questions, such as what rate of interest the account earns, and how many times per year interest is compounded. To begin with a simple problem, first assume that interest is paid only once a year. The students will probably be disappointed with the total amount of only $2406.62. To improve the return, introduce them to compound interest and adjust the number of interest periods as well as the rate for that period.

Somewhere in the discussion the objection may be raised that "My grandparents didn't have $1000 to spend on me when I was born!" This will allow an investigation of how much money would be available if only a small fixed amount had been deposited at regular intervals. A modification such as this one will allow this investigation. To look only at the effect of compounding, set additional deposits equal to zero. For interest compounded annually just set the frequency of compounding equal to one. Both the formulas and a sample output are provided. Only a portion of the spreadsheet is displayed.

A variety of questions may now be considered.

1. In the annual interest problem, vary the interest rates and see how many years are required to double the initial investment. Then change to interest compounded quarterly and record the number of years required at the same rates to double the investment. According to the "rule of 72", students should discover that seventy-two divided by the integer value of the interest rate should approximately equal the number of years.

2. Using the compound interest spreadsheet, gradually increase the number of times a year that interest is compounded and compare the end results. Plot the final amounts on deposit against the number of times interest is compounded and discuss whether there seems to be a limit to the amount which can be earned, even if interest is compounded daily or hourly.

3. In the case where deposits are made at regular intervals, choose a desired amount to have available after eighteen years and try to see what the size of the monthly savings must be at different interest rates in order to obtain that amount.

4. For a shorter spreadsheet or a problem which may seem more relevant to the students, consider saving money for a specific item the student might want to purchase, such as the stereo system in an earlier problem. To be realistic, the students may want to figure inflation of the current cost. Then refer to the loan spreadsheet and compare the cost by saving for the stereo with the cost of buying it on the installment plan.

Oil Refinery Problem

This well-known optimization problem lends itself well to introducing spreadsheets. The spreadsheet solution assumes only knowledge of the Pythagorean Theorem.

Two oil rigs, R₁ and R₂, are located 0.5 mi. and 0.7 mi. from points A and B along the shoreline respectively. The distance from A to B is one mile. Where along the shore should a refinery be built so that a minimum length of pipeline will be needed to transport oil from both oil rigs, and what is the minimum pipe length? (Figure 1)

|^{________________} x ^{_____________}|^_____ 1-x^________|

|^{______________________}1mi.^{______________________}|

Figure 1

Spreadsheet Investigation (Figure 2):

On our spreadsheet the control panel gives the perpendicular distance from each rig to the shore, an initial position as to where to build the refinery (beginning at point A where x=0), and the increment (in this case x=0.1) for "moving" the refinery along the shore from A to B.

Cell A8 refers absolutely to F3 in the control panel, and D8=1-A8. Cell A9 is the sum of A8 and F4. This allows easy changes in investigating the problem starting at different points along the shore and using smaller increment sizes. Columns B and E give the lengths of the pipes from R₁ and R₂ to the refinery, respectively. These are found by using the Pythagorean Theorem (e.g.. B10=). Finally, column G adds columns B and E, giving the total length of pipe needed.

Figure 2

Students should see that a minimum length of pipe will be needed if the refinery is located between 0.3 and 0.5 miles from point A. The solution can then be "refined" by changing the initial x to, say, 0.4 and incrementing by 0.01. This process can be repeated until the desired degree of accuracy is reached.

Other Solution Methods:

Transformational Geometry

Using the fact that the shortest total pipeline length will occur when the angles formed by the pipes and the shoreline are equal ( of incidence = of reflection ), the solution can be found by labeling line AR₁ as the y-axis, line AB as the x-axis, and then "reflecting" R₂ over the x-axis. The resulting pipe is a straight line whose equation can be found by first finding the slope of line R₁R₂'_{. The x-intercept of this line is the ideal location for the refinery (Figure 3).}

_{Figure 3}

TI-82 Solution:

The total length of the pipe is given by the function L=. Graphing this function and using the [2^nd] [CALC] key will allow the student to find the minimum on the curve. Also, a "spreadsheet" can be set up using [2^nd] [TblSet] with TblMin=0 and Tbl=0.1. The table can then be displayed using [2^nd] [TABLE]. Again, the student can "zoom in" by changing the values for TblMin and Tbl.

Calculus Solution:

Using

L=

as the function to minimize,

=.

Setting =0 and solving gives the solution x= as well as an extraneous solution.

Population Models

The number of fish in a pond increases at the rate of 12% per year. That is, at the end of each year there are 12% more fish than there were at the start of the year. Suppose the pond is stocked initially with 40 fish. Examine the growth of the fish population over 10 years.

To begin solving the problem, students create a control panel that contains the Initial Population and the Growth Rate Per Year. In the main body of the spreadsheet, students use a Fill command in the first column to list the Year from 0 to 10. Then for the Population of Year 0 they enter the formula for the absolute cell reference of the Initial Population (that is, use the $ code with both the row and the column referred to). Next they enter the Change in Population of year 0 by multiplying (Population of year 0) *(absolute cell reference of the Growth Rate per Year). For the Population in year 1, students add the Population of year 0 and the Change of year 0. To complete the setup, the formula for Change in Population of year 1 is entered.

This sample spreadsheet illustrates the formulas in the cells:

The remainder of the data is generated by the Fill or Copy command of the software. The teacher should mention that the Format command should be set for zero decimal places because population consists of individuals.

Graphing data in a spreadsheet is enlightening and is a computer activity that students especially enjoy. If students are left to their own choice of type of graph, the teacher will probably witness the gamut from line graphs to area graphs to pie charts because of the appealing colors and shapes. While not hindering the spirit of exploration, and not devaluing students' choices, the teacher should guide the class toward knowing which graph types are more appropriate to certain investigations. To model the fish population, the class should make a scatter plot of Population vs. Year.

Since the points do not lie in a line, the scatter plot provides an opportunity to review linear graphs. What characterizes a line? A line has constant slope. What is slope? Slope is the change in y per unit change in x. In terms of the spreadsheet, the slope of the line between two consecutive points is the increase in number of fish over that year. Students will see that the Change in Population varies; in fact the rate of change in population speeds up.

In the next phase of exploring the population model, naturally the students will go to the control panel and change the values of the Initial Population and the Growth Rate. How does the graph change? Students should observe the consequences of changing the Initial Population, on one hand, and changing the Growth Rate, on the other. They should also extend the table to show the population for at least another ten years.

This fish model presents an example of unrestrained growth. In the real world, population growth of any one type of living thing is usually kept under control by such factors as living space, available food, oxygen, stress, and relationships with other organisms. A spreadsheet model for such restrained growth could be carried out by beginning algebra students. However this will not be included here because the formulas involved are not intuitively satisfying to this level of mathematics course. (See "A Derivation of the Logistic Equation" elsewhere in this book.)

Woodrow Wilson Leadership Program in Mathematics lpt@www.woodrow.org
The Woodrow Wilson National Fellowship Foundation webmaster@woodrow.org
CN 5281, Princeton NJ 08543-5281 Tel:(609)452-7007 Fax:(609)452-0066