• Out of 100 points.
• Turn in one pdf file. Other file formats will not be accepted.
• Write your name (Chinese and English) and the date.
• Answer all questions inside this document and in the same order. Questions answered out of order will not be graded. All questions should appear in the homework.
• No late submissions will be accepted.
• Copy&Paste Excel tables/graphs below each question. You will need to delete blank lines as you do this. Be careful not to delete any questions or change the format of the number list.
• Questions that must be answered by hand (indicated in the question) must have a picture pasted into the document.
• There is a penalty for up to 10 points for not following any of the instructions above.

Dataset (45 points)

Dataset. Download the following file from Canvas:

HW1_Data_Mutual Funds.csv

This is a dataset for a sample of 45 mutual funds. There are three Fund Types (DE=domestic equity, IE=international equity, FI=fixed income). Net Asset Value ($) = Equity per share. Expense Ratio (%) = expenses as a percentage of assets. The remaining variables are self-explanatory.

Instructions for Tables and Graphs: ? Each must have a descriptive title. ? Axes in graphs and rows/columns in tables must be properly labeled. ? Answer the question associated to each Graph/ Table to get full credit.

1.How many variables are in the dataset?

• How many observations are in the dataset?
• Which variables are categorical and which are quantitative?
• Use Excel to find the number of observations, mean, median, 1^st and 3^rd quartiles, sample standard deviation, minimum and maximum for variables: Expense Ratio, 5-year Average Returns and Net Asset value.
• Fund Type
• Fund Type and Expense Ratio
• Expense Ratio and Average Returns. An interesting question is whether funds that spend more perform better (i.e., produce higher returns).
• Draw a histogram for Net Asset Value using a PivotTable. Group Settings: min= 0, max=80, by=10.

	Formula in Excel	Expense Ratio	5-year Avg Return	Net Asset Value
mean	=AVERAGE(data)
median	=MEDIAN(data)
sample std. dev.	=STDEV.S(data)
1st Quartile	=QUARTILE.INC(data, 1)
3rd Quartile	=QUARTILE.INC(data,3)
minimum	=MIN(data) =QUARTILE.INC(data, 0)
maximum	=MAX(data) =QUARTILE.INC(data, 4)
No. of obs.	=COUNT(data)

–Using a PivotTable: Prepare the percentage frequency distribution of Fund Type. No need to copy and paste the table to the homework. But you need it for the next step.

–Prepare a Pie Chart of the Fund Type. Copy and Paste it here.

–Which is the most frequent fund type in the dataset?

–Using a PivotTable: Prepare a cross tabulation of Fund Type (rows) and Expense Ratio (columns). Group Settings: min= 0.25, max=1.49, by=0.50. No need to copy and paste the table to the homework. But you need it for the next step.

–Prepare a 100% Stacked Bar Chart of Fund Type (horizontal axis category) and Expense Ratio (stacked categories). Copy and Paste it here.

–Which Fund Type spends the most?

–Do a Scatter Plot of Expense Ratio (horizontal axis) and 5-Year Average Return (vertical axis). Add a linear trend line. Copy and Paste it here.

–Calculate the correlation between the Expense Ratio and 5-year Average Returns?

–Comment on the relationship. Is it positive or negative? Strong or weak? Do better performing funds spend more?

–Comment on the shape of the histogram. Is it symmetric or skewed left/right?

Mean and standard Deviation (25 point)

• Go to the textbook to find the formula for the mean. Write the formula here; by hand. (Take a picture of your handwritten formula and paste a copy in this document.)
• Go to the textbook to find the formula for the sample standard deviation. Write the formula here; by hand. (Take a picture of your handwritten formula and paste a copy in this document.)
• Calculate, by hand, the mean and sample-standard-deviation if , and, , , and . (5p)
• Use the command below to generate 3 random numbers. Calculate their mean and sample-standard-deviation in (i) Excel and (ii) by hand. (10p)
  • Excel copy and paste (or type) this function into Excel to generate a random number between 1 and 20, do this in three cells to get three random numbers (Note: Excel will generate new random numbers each time you do a change in any cell. You can type the numbers yourself in new cells to use random numbers which not change.)
• Go to the text and look up the formula for the sample covariance. Write the formula here; by hand. (Take a picture of your handwritten formula and paste a copy in this document.)
• Go to the text and look up the formula for the sample correlation coefficient. Write the formula here; by hand. (Take a picture of your handwritten formula and paste a copy in this document.)
• What are the largest and smallest possible values for the sample correlation coefficient? This is true for any correlation coefficient and has nothing to do with actual data. (5p)
• Calculate, by hand, the sample-covariance and sample-correlation-coefficient: , and, , , and , and, , , and . This is best done by filling the following table to stay organized. (5p)
• Input the numbers into Excel and confirm your answer using the function below. (No need to answer anything here, just make sure you got the right answer.)
• Draw a scatterplot of the variables X and Y. Add a fitted line (linear trend line). (5p)
• Use the correlation coefficient and/or scatterplot to comment, in a complete sentence, about the relationship between X and Y. Is it positive/negative and strong/weak? (5p)

=RANDBETWEEN(1,20)

=AVERAGE(data)

=STDEV.S(data)

Covariance and Correlation (30 points)

0	5
2	3
4	4
Sum

=CORREL(data1, data2)