Excel Graphing

Goals:

· To model data using linear, exponential, and logarithmic regression equations.

· To use regression equations to make predictions.

· To gain experience working with matrices in Excel.

Procedure:

· Complete each problem on a separate spreadsheet.

· Enter answers to any questions directly on the spreadsheet.

· Print your project; ensure each spreadsheet fits on a single page.

· Ensure your name is on the front page of your packet

Problem 1: Costco Revenues. The annual revenues R (in millions of dollars) for the Costco Wholesale Corporation from 1996 to 2005 are shown in the table.

a. Create a scatter plot of the data. Let t represent the year, with t = 6 corresponding to 1996.

b. Use the Chart Menu and then choose Add Trendline. Select Exponential. Include the equation and the R-squared value on the chart. (R2 is called the coefficient of determination; it is a measure of how close the data are fitted to the regression line. R2 is always a number between 0 and 1. In general, the closer to 1, the better the model fits the data.)

c. Repeat step b above, this time using a logarithmic model for the data.

d. Use the two models to predict the revenue in 2007. It is projected that revenues in 2007 will be $64,500 million. Does your predicted revenue agree with the projected revenue?

e. Which model, exponential or logarithmic, is a better fit for the data?

Problem 2: Restaurants. The total sales y (in billions of dollars) for fast-food and full-service restaurants for the years 1999 to 2005 are shown in the table.

a. Create a scatter plot of the data for fast-food sales and create a trend line. Let x represent the year, with x = 9 corresponding to 1999. Include the equation and the R2 value on the chart.

b. Repeat the procedure for the data for full-service sales.

c. Assuming that the amounts for the given 7 years are representative of future years; will fast-food sales ever equal full-service sales? Explain.

Problem 3: Long-Distance Plans. You are choosing between two monthly long-distance phone plans offered by two different companies. Company A charges $0.05 per minute for in-state calls, $0.12 per minute for state-to-state calls, and $0.30 per minute for international calls. Company B charges $0.085 per minute for in-state calls, $0.10 per minute for state-to-state calls, and $0.25 per minute for international calls. In a month, you normally use 20 minutes on in-state calls, 60 minutes on state-to-state calls, and 30 minutes on international calls.

a. In Excel, create matrix C that represents the charges for each type of call by each company. Use headings to identify each row and column.

b. Create matrix T that represents the time spent on the phone for each type of call.

c. Find the product matrix CT.

· The command for matrix multiplication is MMULT

· On your spreadsheet, select a set of rows x columns that represents the size of the product matrix

· Enter: = MMULT(select data in matrix C, comma, select data in matrix T)

Example: MMULT(B3:D4,B7:B9)

· CTRL SHIFT ENTER

d. Which company should you choose? Explain.