1. Refer to the data file Catalog Marketing.xlsx (Example 2.7 in the 4th Edition). a) First, using lookup tables, recode the Age values to Younger, Middle-aged, Older; Close values to LiveClose and LiveFar; and OwnHome values to Rent and Own. With these names, pivot tables are much easier to interpret. Paste a copy of the first three rows of the 3 recoded columns in your answer document. b) We are interested in the following question: ?How does a customer?s home ownership status affect the relationship between age distribution and how close customers live to a shopping area that sells similar merchandise to Hytex?s?? Create a pivot table to display the percentage breakdown of recoded Close (columns) by recoded Age (rows). Column totals should display 100%. In addition, we?d like to be able to view these percentages for either value of OwnHome. Put this recoded variable in the Report Filter field. Paste the 2 pictures showing the percentage breakdowns (for the Own and Rent segments) in your answer document. (The Snipping tool is ideal for this. See the copying document in Course Work if you have a Mac). c) Write a brief answer to the question posed in (b). 2. (This is a modified version of problem 3.28 in the 4th edition). File P02.11.xlsx contains data on 148 houses that were recently sold in a suburban community. The data set includes the selling price of each house, along with its appraised value, square footage, number of bedrooms, and number of bathrooms. a) Create a table of correlations between all of the variables using the Correlation module of Summary Statistics in StatTools. (Disregard the covariance). Which of the variables square footage, #bedrooms and #bathrooms is most highly correlated with selling price and what is that correlation? Which two variables in the correlation matrix have the lowest correlation and what is that correlation? (Note that high correlation means a correlation coefficient close to either +1 or -1, and a low correlation is a value close to zero.) b) Create 4 scatterplots (with selling price on the vertical axis) to show how the other four variables are related to selling price. Are these in line with the correlations in part (a)? (Note that high correlation is reflected by a scatter plot which is well represented by a straight line.) c) You might think of the difference, (selling price minus appraised value) as an ?error? in the appraised value, in the sense that this difference is how much more or less the house sold for than the appraiser expected. Create two columns for Error and Absolute Error and compute the correlation between each of these new variables and selling price. If either of these correlations is reasonably large, carefully explain what this is telling us? 3. Go to finance.yahoo.com and download the complete Nasdaq historical weekly prices (all 2087 of them) from February 5, 1971 to August 31, 2013 into an Excel worksheet. Just click ?weekly? and the first few dates and adjusted closing values should be: Feb 5 1971 (100), Feb 8 (102.05), Feb 16 (100.7), etc. after sorting the file in ascending date order. Save the file as an Excel workbook. From the adjusted closing values of the index in the last column, compute a new column of weekly returns. Note that the first return value will be in the new column cell in the February 8, 1971 row, since it measures the return for the first (partial) week. The formula for computing that first return value is: We would like to see how well the empirical rule describes the actual return distribution for the Nasdaq index. a) Construct a labeled time series graph of the weekly closing index values from February 5, 1971 until August 31, 2013. b) Construct a histogram of weekly closing prices (using the StatTools default settings). c) For the period Feb 8 (first return observation) until August 31, 2013, construct a histogram of weekly returns with proper labels. (Hint: A StatTools dataset requires having a label in the row immediately above the first observation, so copy the Return column to a new location (with label) and define this as a new Return dataset. d) Compute the mean and standard deviation of returns and construct a frequency table showing the frequency of return values in the categories shown in the template below. Use the frequency array function to fill in the table frequencies. (You can right click on the template and open the underlying Excel file. Be sure to save the opened worksheet as a new file before closing the open worksheet in Word). e) Finally, compute the percentage of returns falling within one, two and three standard deviations of the mean return and compare them with the empirical rule?s percentages. f) Paste a copy of your finished worksheet in your document and comment on the differences.

MBA 646 Professor Carter Assignment 2 Please do the following three problems, neatly and comprehensively, and submit as a Word document, listing the assignment number and problems, and stapled at the top left. 1. Refer to the data file Catalog Marketing.xlsx (Example 2.7 in the 4th Edition). a) First, using lookup tables, recode the Age values to Younger, Middle-aged, Older; Close values to LiveClose and LiveFar; and OwnHome values to Rent and Own. With these names, pivot tables are much easier to interpret. Paste a copy of the first three rows of the 3 recoded columns in your answer document. b) We are interested in the following question: \"How does a customer's home ownership status affect the relationship between age distribution and how close customers live to a shopping area that sells similar merchandise to Hytex's?\" Create a pivot table to display the percentage breakdown of recoded Close (columns) by recoded Age (rows). Column totals should display 100%. In addition, we'd like to be able to view these percentages for either value of OwnHome. Put this recoded variable in the Report Filter field. Paste the 2 pictures showing the percentage breakdowns (for the Own and Rent segments) in your answer document. (The Snipping tool is ideal for this. See the copying document in Course Work if you have a Mac). c) Write a brief answer to the question posed in (b). 2. (This is a modified version of problem 3.28 in the 4th edition). File P02.11.xlsx contains data on 148 houses that were recently sold in a suburban community. The data set includes the selling price of each house, along with its appraised value, square footage, number of bedrooms, and number of bathrooms. a) Create a table of correlations between all of the variables using the Correlation module of Summary Statistics in StatTools. (Disregard the covariance). Which of the variables square footage, #bedrooms and #bathrooms is most highly correlated with selling price and what is that correlation? Which two variables in the correlation matrix have the lowest correlation and what is that correlation? (Note that high correlation means a correlation coefficient close to either +1 or -1, and a low correlation is a value close to zero.) b) Create 4 scatterplots (with selling price on the vertical axis) to show how the other four variables are related to selling price. Are these in line with the correlations in part (a)? (Note that high correlation is reflected by a scatter plot which is well represented by a straight line.) c) You might think of the difference, (selling price minus appraised value) as an \"error\" in the appraised value, in the sense that this difference is how much more or less the house sold for than the appraiser expected. Create two columns for Error and Absolute Error and compute the correlation between each of these new variables and selling price. If either of these correlations is reasonably large, carefully explain what this is telling us? 3. Go to finance.yahoo.com and download the complete Nasdaq historical weekly prices (all 2087 of them) from February 5, 1971 to August 31, 2013 into an Excel worksheet. Just click \"weekly\" and the first few dates and adjusted closing values should be: Feb 5 1971 (100), Feb 8 (102.05), Feb 16 (100.7), etc. after sorting the file in ascending date order. Save the MBA 646 Professor Carter Assignment 2 file as an Excel workbook. From the adjusted closing values of the index in the last column, compute a new column of weekly returns. Note that the first return value will be in the new column cell in the February 8, 1971 row, since it measures the return for the first (partial) week. The formula for computing that first return value is: We would like to see how well the empirical rule describes the actual return distribution for the Nasdaq index. a) Construct a labeled time series graph of the weekly closing index values from February 5, 1971 until August 31, 2013. b) Construct a histogram of weekly closing prices (using the StatTools default settings). c) For the period Feb 8 (first return observation) until August 31, 2013, construct a histogram of weekly returns with proper labels. (Hint: A StatTools dataset requires having a label in the row immediately above the first observation, so copy the Return column to a new location (with label) and define this as a new Return dataset. d) Compute the mean and standard deviation of returns and construct a frequency table showing the frequency of return values in the categories shown in the template below. Use the frequency array function to fill in the table frequencies. (You can right click on the template and open the underlying Excel file. Be sure to save the opened worksheet as a new file before closing the open worksheet in Word). e) Finally, compute the percentage of returns falling within one, two and three standard deviations of the mean return and compare them with the empirical rule's percentages. f) Paste a copy of your finished worksheet in your document and comment on the differences