Activity 4.1

Blood Alcohol Levels and Credit Cards: Working with More Than Two Variables

10 points

Due at the beginning of class, Wednesday, February 11, 2009

In this activity, you will look at examples in which there is a need to graph or analyze expressions and functions that involve the interactions of more than two variables. In the first part of this activity, you will work with "blood alcohol level" as the response variable, and in the second part you will explore how some variables affect the balance on a credit card.

Blood alcohol level tables are given in Exploration 5 of Topic 4 and are contained in the file EA4.1.1 Blood Alcohol Level.xls. You will compare the blood alcohol level of 120-pound males and 120-pound females based on the number of drinks consumed. To do so, you will create a SINGLE graph that shows two scatterplots (one relates the number of drinks consumed with the blood alcohol level of a 120-pound male, the other relates the same variables for a 120-pound female) on the same axes.

1. Open the file EA4.1.1 Blood Alcohol Level.xls. Using these tables and the Copy and Paste functions of Excel, create a three-column table in the same worksheet. The first column should be "Number of Drinks," and the second and third columns should be "Blood Alcohol Level of a 120-lb Male" and "Blood Alcohol Level of 120-lb Female," respectively.

2. Create and label appropriate scatterplots (in a single graph) using the table you created. Explain what your scatterplots show about the blood alcohol levels of 120-lb. males and females.  Include this graph in your Word document.

3. What variables are involved in the graph, and what other variables might you want to include in order to understand the issue more fully?

How fast the balance on your credit card decreases depends on the original amount charged, any additional purchases charged to the card, the monthly interest rate, and how much you pay off each month. Suppose you have charged $1000 on a particular credit card and do not charge anything additional on the card. Suppose also that the annual interest charge on this card is 18.0 percent. Then the monthly charge, as a percent, is 18.0 / 12 = 1.5. The credit card company requires that you pay off a minimum balance of $20 each month. Then you can calculate the next month's balance using the formula:

balance next month = balance this month + 0.015 * balance this month - 20

4. Explain what each part of this formula represents.

5. Set up a spreadsheet to use this formula to calculate how much you will owe after one month, after six months, and after one year, if you pay off only the minimum amount of $20 each month and don't charge anything else on your card. The instructions are below to set up the spreadsheet. Using the spreadsheet, you will be able to answer the questions that follow. Paste the cells of the spreadsheet (first 12 months only) into your Word document.

Note that the interest rate in the instructions below is different.  Be sure to use the rate mentioned above.

6. How much progress did you make in paying off your debt in month 1?

7. What is the "balance next month" for month 2?

8. How much will you owe after six months? After one year?

9. If you continue to pay $20 each month, and don't charge anything else on your card, how long will it take to pay off the whole amount? (To answer this question, highlight cells A13, B13, C13 and auto-fill.)

For the following questions, you may modify your spreadsheet table as needed to help determine the answer, but you do not need to paste the table into your Word document.

10. Suppose you only want to pay off $15 each month. If the initial balance is $1000, you don't charge anything else on your card, and the interest is 18.0 percent per year, find how much you owe after first month, after six months, and after one year.

11. What happens if you owe $1000 and you can only pay $14 per month?

12. What conclusions can you draw from the previous computations?

Summary
In this activity, you learned how to enter a formula in Excel and drag down to do a series of calculations. You also learned how to use Audit mode in Excel to check formulas. You looked at explanatory variables that affect blood alcohol levels. You also looked at various scenarios and explored how the original amount charged on a credit card and how much is paid off each month affect the balance remaining on the card after a period of time.