CSC 239: Personal Computing for Science
Fifth Assignment (Due 11:30 Monday October 31, 2005)
Assignment
Scatterplots
Gordon Moore, one of the founders of Intel corporation, predicted in 1965 that the
number of transistors on an integrated circuit chip would double every 18
months. This is "Moore's law", one way to measure the revolution
in computing. Here are the data on the dates and number of transistors for Intel
microprocessors.
| Processor |
Date |
Transistors |
| 4004 |
1971 |
2250 |
| 8008 |
1972 |
2500 |
| 8080 |
1974 |
5000 |
| 8086 |
1978 |
29000 |
| 286 |
1982 |
120000 |
| 386 |
1985 |
275000 |
| 486DX |
1989 |
1180000 |
| Pentium |
1993 |
3100000 |
| Pentium II |
1997 |
7500000 |
| Pentium III |
1999 |
24000000 |
| Pentium 4 |
2000 |
42000000 |
- Import the data into a worksheet named IntelData. (You can find the data also in
moore.txt.) Make sure the columns are labelled Processor for the processor,
Date for the years and Transistors for the number of transistors.
- Create a scatter plot of the number of transistors against the date to represent
the rate of growth of the number of transistors over time.
- Do you think that the relationship between number of transistors and time is
linear or non-linear?
- Compute the logarithmic transformation for the number of transistors. (The
logarithmic transformation is defined used the function "=LN(value)"
where value can be a cell address - If Transistors is column
C, define the new variables in column D and label the column LogTrans.)
- Create a new scatter plot for the new variable LogTrans against Date. Describe the
pattern displayed in this plot.
Regression analysis
Investors ask about the relationship between
returns on investments in the United States and on investments overseas. The
data in file returns.txt gives the total returns on U.S.
and overseas common stocks over a 26-year period. (The total return is change in price
plus any dividends paid, converted into U.S. dollars. Both returns are averages over
many individual stocks.) The data set contains 26 observations: one for each year
from 1971 to 1996. The first column is the Date, the second column is
the Overseas Returns
and the third column is the US Returns.
In the regression analysis below, the variable US returns is the X variable and
the variable overseas returns is the Y variable.
- Create a worksheet called InvestorData and perform the following steps:
- Import the data. (Make sure you have
three columns labelled Date, Overseas Returns and US Returns.)
- Use the workbooks created in the lab to compute the summary worksheet for the data.
(Don't forget to name the overseas returns data yvalues and the US returns data xvalues.)
- Draw the scatter plot of the overseas returns vs. the US returns.
(Keep in mind that we want US returns on the x axis and overseas returns on the
y axis. Excel will automatically put the first column on the x axis and the
second column on the y axis, which is not what we want in this case.)
- Write down the correlation between US and overseas returns to three decimal places.
- Compute the regression line that fits the data. Once again, use three decimal places
for the slope and intercept.
- Create a worksheet called RegressionSummary and perform the following steps:
- Use the data analysis toolkit to draw the scatter plot of the overseas returns vs. US returns
where the regression line is displayed and also to draw the residual plot for the
regression line.
- In 1993, the return on US stocks was 10.1 %. Use the regression line to
compute the predicted return on overseas stocks and find the residual
value (observed y - predicted y). Write the formulas in cells to calculate these.
- Examine the residual plot and draw conclusions on the goodness of fit of the
regression line to represent the association between US and overseas
returns. Present your conclusions with a few sentences.
Additional challenges
- Create a scatterplot showing overseas returns plotted against year.
- Calculate the slope of the regression line for this scatterplot.
- What "conclusion" can you draw from the slope of the regression line? Do you trust this conclusion?
- Do the same three steps for US returns.
Deliverables
Submit your excel workbook using
Course On-Line.
Make sure that text answers are included in your workbook.