ECON 201 Business Statistics QUAN 201 CHAPTER 3 GRADED ASSIGNMENT (50 points) POINTS: Name: _____________________________ PERCENT: At the beginning of 2009, the economic downturn resulted in the loss of jobs and an increase in delinquent loans for housing. The national unemployment rate was 6.5% and the percentage of delinquent loans was 6.12% (The Wall Street Journal, January 27, 2009). In projecting where the real estate market was headed in the coming year, economists studied the relationship between the jobless rate and the percentage of delinquent loans. The expectation was that if the jobless rate continued to increase, there would also be an increase in the percentage of delinquent loans. The data on the next tab ("Housing Data") show the jobless rate and the delinquent loan percentage Instructions: (formulas and Excel functions are below the instructions) 1. For Jobless Rate prepare a frequency table and a bar chart for the frequency table. You can choose the intervals for the table and the bar chart. Include a title for the chart and axis titles. Do this on the tab "Frequency & Bar Charts" (10 points) 2. For Delinquent Loans prepare a frequency table and a bar chart for the frequency table. You can choose the intervals for the table and the bar chart. Include a title for the chart and axis titles. Do this on the tab "Frequency & Bar Charts" (10 points) In the tab "Cross Tabulation" cross-tabulate the two data series; put Jobless Rate in Rows and Delinquent Loans in Columns and Values. Group Delinquent Loans between 3% and 13% by 2% and Jobless Rate between 5% and 10% by 1%. (When you group the data: for Delinquent Loans , highlight 3.2% in the line across the top; for Jobless Rate , highlight the 5.2% at the top of the column.) (10 points) 3. 4. For both data series, calculate the mean and the standard deviation; using these and the data values calculate z-scores for each data point. Using these z-scores, identify any "outliers" in team revenue and team value. (An "outlier" is a z-score that is greater than +3 or less than -3). Do this on the tab "z-score" (10 points) 5. Calculate the sample correlation coefficient between jobless rate and the percentage of delinquent loans -- in two ways: the "long way" and using the Excel function CORREL. Describe the relationship between jobless rate and perentage of delinquent loans, comment on the strength of this relationship based on the correlation coefficient you calculated. Do this on the tab "Correlation". (10 points) Formulas Sample Mean: Excel Function 𝑥= 𝑥𝑖 =AVERAGE(AA:BB) Formulas Excel Function 𝑥𝑖 Sample Mean: 𝑥= Sample Variance: 𝑠 2 = 𝑣𝑎𝑟 𝑥 = Sample Standard Deviation: =AVERAGE(AA:BB) 𝑛 𝑥 𝑖 −𝑥 2 𝑠 = 𝑠2 = Covariance(x,y): 𝑠𝑥𝑦 = 𝑐𝑜𝑣 𝑥, 𝑦 = Correlation(x,y) 𝑟𝑥𝑦 = z-score: 𝑧= 𝑥 1 −𝑥 𝑠 𝑐𝑜𝑣 (𝑥,𝑦) 𝑠𝑥 𝑠𝑦 = =VAR(AA:BB) 𝑛 −1 𝑣𝑎𝑟(𝑥) 𝑠𝑥𝑦 𝑠𝑥 𝑠𝑦 (𝑥 𝑖 −𝑥 )(𝑦 𝑖 −𝑦 ) 𝑛 −1 =STDEV(AA:BB) =COVARIANCE.S(AA:BB,CC:DD) =CORREL(AA:BB,CC:DD) 0 0% bs and an increase in nd the percentage of n projecting where the real ionship between the jobless e jobless rate continued to oans. The data on the next SCORE uency table. You can hart and axis e frequency table. You can hart and axis obless Rate in Rows and en 3% and 13% by 2% and Delinquent Loans , highlight top of the column.) (10 points) ng these and the data any "outliers" in team r less than -3). Do this on e percentage of delinquent Describe the relationship trength of this relationship elation". (10 points) unction GE(AA:BB) unction GE(AA:BB) A:BB) (AA:BB) RIANCE.S(AA:BB,CC:DD) EL(AA:BB,CC:DD) Metro Area Atlanta Boston Charlotte Chicago Dallas Denver Detroit Houston Jacksonville Las Vegas Los Angeles Miami Minneapolis Nashville New York Orange County Orlando Philadelphia Phoenix Portland Raleigh Sacramento St. Louis San Diego San Francisco Seattle Tampa Jobless Rate (%) Delinquent Loans (%) 7.1 5.2 7.8 7.8 5.8 5.8 9.3 5.7 7.3 7.6 8.2 7.1 6.3 6.6 6.2 6.3 7.0 6.2 5.5 6.5 6.0 8.3 7.5 7.1 6.8 5.5 7.5 7.02 5.31 5.38 5.40 5.00 4.07 6.53 5.57 6.99 11.12 7.56 12.11 4.39 4.78 5.78 6.08 10.05 4.75 7.22 3.79 3.62 9.24 4.40 6.91 5.57 3.87 8.42 Metro Area Atlanta Boston Charlotte Chicago Dallas Denver Detroit Houston Jacksonville Las Vegas Los Angeles Miami Minneapolis Nashville New York Orange County Orlando Philadelphia Phoenix Portland Raleigh Sacramento St. Louis San Diego San Francisco Seattle Tampa Jobless Rate (%) Delinquent Loans (%) 7.1 5.2 7.8 7.8 5.8 5.8 9.3 5.7 7.3 7.6 8.2 7.1 6.3 6.6 6.2 6.3 7.0 6.2 5.5 6.5 6.0 8.3 7.5 7.1 6.8 5.5 7.5 7.02 5.31 5.38 5.40 5.00 4.07 6.53 5.57 6.99 11.12 7.56 12.11 4.39 4.78 5.78 6.08 10.05 4.75 7.22 3.79 3.62 9.24 4.40 6.91 5.57 3.87 8.42 Jobless Rate: Frequency, Relative Frequency, Percent Fre Delinquent Loans: Bar Chart Delinquent Loans: Frequency, Relative Frequency, Percent Frequency Delinquent Loans: Bar Chart Metro Area Atlanta Boston Charlotte Chicago Dallas Denver Detroit Houston Jacksonville Las Vegas Los Angeles Miami Minneapolis Nashville New York Orange County Orlando Philadelphia Phoenix Portland Raleigh Sacramento St. Louis San Diego San Francisco Seattle Tampa Jobless Rate (%) Delinquent Loans (%) 7.1 5.2 7.8 7.8 5.8 5.8 9.3 5.7 7.3 7.6 8.2 7.1 6.3 6.6 6.2 6.3 7.0 6.2 5.5 6.5 6.0 8.3 7.5 7.1 6.8 5.5 7.5 7.02 5.31 5.38 5.40 5.00 4.07 6.53 5.57 6.99 11.12 7.56 12.11 4.39 4.78 5.78 6.08 10.05 4.75 7.22 3.79 3.62 9.24 4.40 6.91 5.57 3.87 8.42 Jobless Rate (%) Delinquent Loans (%) Atlanta 7.1 7.02 Boston 5.2 5.31 Charlotte Chicago Dallas Denver Detroit Houston Jacksonville Las Vegas Los Angeles Miami Minneapolis Nashville New York Orange County Orlando Philadelphia Phoenix Portland Raleigh Sacramento St. Louis San Diego San Francisco Seattle Tampa 7.8 7.8 5.8 5.8 9.3 5.7 7.3 7.6 8.2 7.1 6.3 6.6 6.2 6.3 7.0 6.2 5.5 6.5 6.0 8.3 7.5 7.1 6.8 5.5 7.5 5.38 5.40 5.00 4.07 6.53 5.57 6.99 11.12 7.56 12.11 4.39 4.78 5.78 6.08 10.05 4.75 7.22 3.79 3.62 9.24 4.40 6.91 5.57 3.87 8.42 Metro Area Jobless Rate Mean Standard Deviation Delinquen t Loans Jobless Rate Delinquent z-score Loans z-score Metro Area Atlanta Boston Charlotte Chicago Dallas Denver Detroit Houston Jacksonville Las Vegas Los Angeles Miami Minneapolis Nashville New York Orange County Orlando Philadelphia Phoenix Portland Raleigh Sacramento St. Louis San Diego San Francisco Seattle Tampa Mean Jobless Rate (%) (x ) Delinquent Loans (%) (y ) 7.1 5.2 7.8 7.8 5.8 5.8 9.3 5.7 7.3 7.6 8.2 7.1 6.3 6.6 6.2 6.3 7.0 6.2 5.5 6.5 6.0 8.3 7.5 7.1 6.8 5.5 7.5 7.02 5.31 5.38 5.40 5.00 4.07 6.53 5.57 6.99 11.12 7.56 12.11 4.39 4.78 5.78 6.08 10.05 4.75 7.22 3.79 3.62 9.24 4.40 6.91 5.57 3.87 8.42 𝒙𝒊 − ഥ 𝒙 ഥ) 𝒚𝒊 − 𝒚 Summation Standard deviation Covariance Correlation coefficient [A] Correlation coefficient [B using Excel function CORREL( : )] 𝒙𝒊 − ഥ 𝒙 ഥ 𝒚𝒊 − 𝒚 ഥ 𝒙𝒊 − ഥ 𝒙 𝒚𝒊 − 𝒚 𝒏−𝟏 𝒄𝒐𝒗𝒙𝒚 𝒄𝒐𝒗𝒙𝒚 𝒄𝒐𝒓𝒓𝒆𝒍𝒂𝒕𝒊𝒐𝒏 = 𝝆 = 𝒐𝒓 𝒓 = 𝝈𝒙 𝝈𝒚 𝒔𝒙 𝒔𝒚 𝒄𝒐𝒗𝒂𝒓𝒊𝒂𝒏𝒄𝒆 = ECON 201 Business Statistics ECON 201 CHAPTER 3 PRACTICE EXERCISES Gamblers and other fans of college footbal make predictions about the score in bowl games. The "Data" tab contains predictions of the point margins for several games as well as the actual point margins for those games. Instructions: (formulas and Excel functions are below the instructions) 1. For Predicted Point Margin prepare a frequency table and a bar chart for the frequency table. You can choose the intervals for the table and the bar chart. Include a title for the chart and axis titles. Do this on the tab "Frequency & Bar Charts" (10 points) 2. In the tab "Cross Tabulation" cross-tabulate the two data series; put Predicted Point Margin in Rows and Actual Point Margin in Columns and Values. Group Predicted Point Margin between between -5 and 15 by 5 points and Actual Point Margin between 0 and 30 by 5. (When you group the data: for Predicted Point Margin, highlight the -5--1; for Actual Point Margin, highlight 0-4 at the top of the column. (10 points) 3. For both data series, calculate the mean and the standard deviation; using these and the data values calculate z-scores for each data point. Using these z-scores, identify any "outliers" in team revenue and team value. (An "outlier" is a z-score that is greater than +3 or less than -3). Do this on the tab "z-score" (10 points) 4. Calculate the sample correlation coefficient between jobless rate and the percentage of delinquent loans -- in two ways: the "long way" and using the Excel function CORREL. Describe the relationship between jobless rate and perentage of delinquent loans, comment on the strength of this relationship based on the correlation coefficient you calculated. Do this on the tab "Correlation". (10 points) Formulas Excel Function 𝑥𝑖 Sample Mean: 𝑥= Sample Variance: 𝑠 2 = 𝑣𝑎𝑟 𝑥 = Sample Standard Deviation: Covariance(x,y): =AVERAGE(AA:BB) 𝑛 𝑥 𝑖 −𝑥 2 𝑠 = 𝑠2 = 𝑠𝑥𝑦 = 𝑐𝑜𝑣 𝑥, 𝑦 = 𝑐𝑜𝑣 (𝑥,𝑦) =VAR(AA:BB) 𝑛 −1 𝑠𝑥𝑦 𝑣𝑎𝑟(𝑥) (𝑥 𝑖 −𝑥 )(𝑦 𝑖 −𝑦 ) 𝑛 −1 =STDEV(AA:BB) =COVARIANCE.S(AA:BB,CC:DD) Sample Variance: 𝑥 𝑖 −𝑥 2 𝑠 2 = 𝑣𝑎𝑟 𝑥 = Sample Standard Deviation: 𝑠 = 𝑠2 = Covariance(x,y): 𝑠𝑥𝑦 = 𝑐𝑜𝑣 𝑥, 𝑦 = Correlation(x,y) 𝑟𝑥𝑦 = z-score: 𝑧= 𝑥 1 −𝑥 𝑠 𝑐𝑜𝑣 (𝑥,𝑦) 𝑠𝑥 𝑠𝑦 = =VAR(AA:BB) 𝑛 −1 𝑣𝑎𝑟(𝑥) 𝑠𝑥𝑦 𝑠𝑥 𝑠𝑦 (𝑥 𝑖 −𝑥 )(𝑦 𝑖 −𝑦 ) 𝑛 −1 =STDEV(AA:BB) =COVARIANCE.S(AA:BB,CC:DD) =CORREL(AA:BB,CC:DD) unction GE(AA:BB) A:BB) (AA:BB) RIANCE.S(AA:BB,CC:DD) A:BB) (AA:BB) RIANCE.S(AA:BB,CC:DD) EL(AA:BB,CC:DD) Predicted Actual Point Bowl Game Score Point Margin Margin Outback Auburn 38 Northwestern 35 5 3 Gator Florida State 33 West Virginia 21 1 12 Capital One Penn State 19 LSU 17 3 2 Rose Ohio State 26 Oregon 17 -2 9 Sugar Florida 51 Cincinnati 24 14 27 Cotton Mississippi State 21 Oklahoma State 7 3 14 Alamo Texas Tech 41 Michigan State 31 9 10 Fiesta Boise State 17 TCU 10 -4 7 Orange Iowa 24 Georgia Tech 14 -3 10 Championship Alabama 37 Texas 21 4 16 Score Auburn 38 Northwestern 35 Florida State 33 West Virginia 21 Penn State 19 LSU 17 Ohio State 26 Oregon 17 Florida 51 Cincinnati 24 Mississippi State 21 Oklahoma State 7 Texas Tech 41 Michigan State 31 Boise State 17 TCU 10 Iowa 24 Georgia Tech 14 Alabama 37 Texas 21 Actual Point Margin 3 12 2 9 27 14 10 7 10 16 Frequency Frequency Bowl Game Outback Gator Capital One Rose Sugar Cotton Alamo Fiesta Orange Championship Predicted Point Margin 5 1 3 -2 14 3 9 -4 -3 4 Predicted Point Margin Point Margin Frequency -5--1 3 0-4 4 5-9 2 10-15 1 Grand Total 10 Frequency Predicted Point Margin Frequency Frequency 5 4 3 2 1 0 -5--1 0-4 5-9 Predicted Margins Predicted Point Margin 10-15 Bowl Game Outback Gator Capital One Rose Sugar Cotton Alamo Fiesta Orange Championship Score Auburn 38 Northwestern 35 Florida State 33 West Virginia 21 Penn State 19 LSU 17 Ohio State 26 Oregon 17 Florida 51 Cincinnati 24 Mississippi State 21 Oklahoma State 7 Texas Tech 41 Michigan State 31 Boise State 17 TCU 10 Iowa 24 Georgia Tech 14 Alabama 37 Texas 21 Predicted Point Margin 5 1 3 -2 14 3 9 -4 -3 4 Actual Point Margin 3 12 2 9 27 14 10 7 10 16 Actual Point Margin Predicted Margin 0-4 5-9 10-14 15-19 25-30 Grand Total -5--1 2 1 3 0-4 1 2 1 4 5-9 1 1 2 10-15 1 1 Grand Total 2 2 4 1 1 10 Bowl Game Outback Gator Capital One Rose Sugar Cotton Alamo Fiesta Orange Championship Score Auburn 38 Northwestern 35 Florida State 33 West Virginia 21 Penn State 19 LSU 17 Ohio State 26 Oregon 17 Florida 51 Cincinnati 24 Mississippi State 21 Oklahoma State 7 Texas Tech 41 Michigan State 31 Boise State 17 TCU 10 Iowa 24 Georgia Tech 14 Alabama 37 Texas 21 Predicted Point Margin 5 1 3 -2 14 3 9 -4 -3 4 Actual Point Margin 3 12 2 9 27 14 10 7 10 16 Predicted Mean Standard Deviation Actual 3 11 5.53774924 7.1336449 Predicted z-score 0.3611576 -0.3611576 0 -0.9028939 1.9863666 0 1.0834727 -1.2640515 -1.0834727 0.1805788 Actual z-score -1.1214464 0.1401808 -1.2616271 -0.2803616 2.2428927 0.4205424 -0.1401808 -0.5607232 -0.1401808 0.700904 No outliers Bowl Game Outback Gator Capital One Rose Sugar Cotton Alamo Fiesta Orange Championship Predicted Point Score Margin (x) Auburn 38 Northwestern 35 5 Florida State 33 West Virginia 21 1 Penn State 19 LSU 17 3 Ohio State 26 Oregon 17 -2 Florida 51 Cincinnati 24 14 Mississippi State 21 Oklahoma State 7 3 Texas Tech 41 Michigan State 31 9 Boise State 17 TCU 10 -4 Iowa 24 Georgia Tech 14 -3 Alabama 37 Texas 21 4 Actual Point Margin (y) 𝒙𝒊 − ഥ 𝒙 2 -2 0 -5 11 0 6 -7 -6 1 3 12 2 9 27 14 10 7 10 16 Mean 3 11 Standard deviation 5.537749242 7.133644853 Correlation coefficient [A Correlation coefficient [B using Excel function CORREL( : ) ഥ) 𝒚𝒊 − 𝒚 -8 1 -9 -2 16 3 -1 -4 -1 5 𝒙𝒊 − ഥ 𝒙 ഥ 𝒚𝒊 − 𝒚 -16 -2 0 10 176 0 -6 28 6 5 Summation 201 Covariance 22.33333333 Correlation coefficient [A] 0.56533878 xcel function CORREL( : )] 0.56533878 ഥ 𝒙𝒊 − ഥ 𝒙 𝒚𝒊 − 𝒚 𝒏−𝟏 𝒄𝒐𝒗𝒙𝒚 𝒄𝒐𝒗𝒙𝒚 𝒄𝒐𝒓𝒓𝒆𝒍𝒂𝒕𝒊𝒐𝒏 = 𝝆 = 𝒐𝒓 𝒓 = 𝝈𝒙 𝝈𝒚 𝒔𝒙 𝒔𝒚 𝒄𝒐𝒗𝒂𝒓𝒊𝒂𝒏𝒄𝒆 =
Purchase answer to see full attachment