Exp19_Excel_Ch08_Cap_Golden_State_5K Project Description: You are a volunteer for the Golden State 5k, an annual 5k held across several cities in California to raise money for at risk youth. As part of your duties, you track donations, volunteer information, and race results. This year you have decided to use Excel to calculate frequency distribution by age and time, calculate various descriptive statistics, and forecast participation rate as well as donation rate for 2025 Steps to Perform: Step Instructions Points Possible 1 0 Start Excel Download and open the file named Exp19_Excel_Cho_Cap Golden State5k xdsx. Grader has automatically added your last name to the beginning of the filename Ensure the RaceResults worksheet is active, then use the FREQUENCY function to calculate the frequency distribution of the race results in column D. Place your results in the range G4 G9 2 5 3 5 5 4 4 5 5 5 6 6 7 7 8 Enter a function in cell F22 to calculate the correlation between age (Columns C) and race time (Column D) Enter a function in cell G22 to calculate the covariance between age and race time. Enter a function in cell H22 to calculate the variance of the ages in the data set. Note this is a sample of data not a population Enter a function in cell 122 to calculate the standard deviation of the ages in the data set. Ensure the Data Analysis ToolPak add-in is active. Use the Data Analysis ToolPak to create a histogram with chart output starting in cell H12 based on the ages of the runners surveyed Use the range F13.F18 as the Bin Range Ensure that Chart output and Cumulative percentage is included in the results. Place the upper left hand comer of the chart in cell L13. Ensure the Volunteerinfo worksheet is active. Use the Data Analysis ToolPak to perform a single factor ANOVA on the range C5-E21 (Including column lables). Place the results starting in cell G5 Create a Forecast Sheet that depicts year over year growth in participation for the city of Los Angeles. Set the Forecast end year as 2025 and place the results on a new worksheet named 2025Forecast 8 9 5 9 8 10 Ensure the Participants worksheet is active then create a scatter plot chart that places the Participant observations on the X axis and the Donation dollars on the Yaxis (do not include column headings). Add the chart title Participant Forecast and a linear trendline to the chart that also shows the Equation and the R-square Enter a function in cell F6 to calculate the intercept of the linear trendline created in the prior step 7 11 7 12 Enter a function in cell G6 to calculate the slope of the linear trendline. 7 13 Enter a function in cell H6 to calculate the R-square of the linear trendine 14 Enter a function in cell 16 to calculate the Standard Error 7 Created On: 11/20/2020 Exp19. Excel_Chos_Cap - Golden State 5K 1.1 Grader - Instructions Excel 2019 Project Step Points Possible 15 7 Instructions Use the FORECAST LINEAR function in cell F9 to forecast potential donations once the goal of 20,000 participants is reached. Format the results as Currency Complete your analysis by adding formulas in the range G9 H9 to calculate the high and low thresholds of the forecast Save and close EXP19_Excel_Cho8_CAP_Golden State5k. Exit Excel Submit the file as directed 16 8 17 0 Total Points 100 A B C D E F G H 1 2 Nm 4 5 6 7 8 9 10 11 NNNNN 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Golden State 5k Runner ARC Time Duration Frequency 8806 20 0:18:43 0:14:24 3092 20 0:16:34 0:28:48 6756 21 0:28:48 0:43:12 3768 22 0:14:24 0:57:36 6062 22 0:57:36 1:12:00 4746 22 0:27:22 1:26:24 3503 23 0:22:19 8113 23 0:28:48 2913 24 0:17:17 ge 8526 25 0:22:19 18 9205 27 0:27:22 21 1897 27 0:50:52 30 3569 28 0:25:38 40 7294 28 0:17:43 50 3428 29 0:16:34 60 3299 36 0:43:12 >60 4022 43 0:57:36 2987 44 1:01:12 Correlation Coaciance Variance Standard Deviatio 9309 44 0:19:03 5215 46 1:01:12 8683 47 1:04:48 4305 47 0:35:17 5796 47 1:03:22 6689 48 0:19:03 4673 49 1:04:48 8613 49 1:04:48 9469 51 6:33:24 1936 51 1:04:48 5436 55 1:37:12 4752 55 1:04:48 2398 55 1:04:48 6065 56 0:48:58 4957 57 1:04:48 6971 57 1:37:12 5885 1:26:24 2612 58 0:35:17 2617 59 0:48:58 Race Results Volunteerinfo Participants 36 37 38 58 39 40 AI B E F G H , 1 55 | 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 SO 51 S2 53 54 55 56 57 58 59 60 61 9309 5215 8683 4305 5796 6689 4673 8613 9469 1936 5436 4752 2398 6065 4957 6971 5885 2612 2617 7566 6377 1569 5568 2488 8206 5389 6214 1895 5038 5296 3463 7803 C D 44 0:19:03 46 1:01:12 47 1:04:48 47 0:35:17 47 1:03:22 48 0:19:03 49 1:04:48 49 1:04:48 51 0:33:24 51 1:04:48 55 1:37:12 1:04:48 SS 1:04:48 56 0:48:58 57 1:04:48 57 1:37:12 58 1:26:24 58 0:35:17 59 0:48-58 60 1:26:24 61 0:43:12 62 1:04:48 62 1:37:12 63 1:04:48 63 0:14:24 63 0:25:38 66 0:17:17 67 1:37:12 67 1:33:36 67 0:33:24 68 1:04:48 0:18:43 %mm 88w8%w 88%8%wmu am B BB RaceResults Volunteerino Participants A D E 1 F G H 2 Golden State 5k Location Combined 3 4 5 6 7 8 9 10 11 12 Year Los Angeles San Diego San Jose 2005 3,000 2,250 2,000 2006 3,750 2,375 2,200 2007 4,000 2,400 2.500 2008 4,500 3,200 3,000 2009 5,000 3,175 3,025 2010 7,500 4,000 4,500 2011 8,750 4,500 5,000 2012 9,000 5,000 5,480 2013 9,000 5,210 6,000 2014 9,200 5,500 6,200 2015 10,000 6,000 6,500 2016 10,250 6,025 7,000 2017 11,750 6,250 7,100 2018 12,250 6,800 7,500 2019 12,500 7,000 7,800 2020 15,000 7,800 8,000 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 89 40 RaceResults Volunteerinfo Participants Ready A1 B C D E F G H Golden State Sk Location Los Angeles 20000 2 3 4 5 6 Intercept Slope RS Standard Etro Forecast High Low 7 . 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Year Participants Donations 2005 3000 $10,000.00 2006 3750 $14,500.00 2007 4000 $17,000.00 2008 4500 $20,000.00 2009 5000 $22,500.00 2010 7500 $30,000.00 2011 8750 $45,000.00 2012 9000 $50,100.00 2013 9000 $56,000.00 2014 9200 $58,250.00 2015 10000 $60,125.00 2016 10250 $62,500.00 2017 11750 $64,000.00 2018 12250 $70,000.00 2019 12500 $75,000.00 2020 15000 $85,500.00 32 33 34 35 36 37 38 39 40 RaceResults Volunteerinfo Part Nipants Exp19_Excel_Ch08_Cap_Golden_State_5K Project Description: You are a volunteer for the Golden State 5k, an annual 5k held across several cities in California to raise money for at risk youth. As part of your duties, you track donations, volunteer information, and race results. This year you have decided to use Excel to calculate frequency distribution by age and time, calculate various descriptive statistics, and forecast participation rate as well as donation rate for 2025 Steps to Perform: Step Instructions Points Possible 1 0 Start Excel Download and open the file named Exp19_Excel_Cho_Cap Golden State5k xdsx. Grader has automatically added your last name to the beginning of the filename Ensure the RaceResults worksheet is active, then use the FREQUENCY function to calculate the frequency distribution of the race results in column D. Place your results in the range G4 G9 2 5 3 5 5 4 4 5 5 5 6 6 7 7 8 Enter a function in cell F22 to calculate the correlation between age (Columns C) and race time (Column D) Enter a function in cell G22 to calculate the covariance between age and race time. Enter a function in cell H22 to calculate the variance of the ages in the data set. Note this is a sample of data not a population Enter a function in cell 122 to calculate the standard deviation of the ages in the data set. Ensure the Data Analysis ToolPak add-in is active. Use the Data Analysis ToolPak to create a histogram with chart output starting in cell H12 based on the ages of the runners surveyed Use the range F13.F18 as the Bin Range Ensure that Chart output and Cumulative percentage is included in the results. Place the upper left hand comer of the chart in cell L13. Ensure the Volunteerinfo worksheet is active. Use the Data Analysis ToolPak to perform a single factor ANOVA on the range C5-E21 (Including column lables). Place the results starting in cell G5 Create a Forecast Sheet that depicts year over year growth in participation for the city of Los Angeles. Set the Forecast end year as 2025 and place the results on a new worksheet named 2025Forecast 8 9 5 9 8 10 Ensure the Participants worksheet is active then create a scatter plot chart that places the Participant observations on the X axis and the Donation dollars on the Yaxis (do not include column headings). Add the chart title Participant Forecast and a linear trendline to the chart that also shows the Equation and the R-square Enter a function in cell F6 to calculate the intercept of the linear trendline created in the prior step 7 11 7 12 Enter a function in cell G6 to calculate the slope of the linear trendline. 7 13 Enter a function in cell H6 to calculate the R-square of the linear trendine 14 Enter a function in cell 16 to calculate the Standard Error 7 Created On: 11/20/2020 Exp19. Excel_Chos_Cap - Golden State 5K 1.1 Grader - Instructions Excel 2019 Project Step Points Possible 15 7 Instructions Use the FORECAST LINEAR function in cell F9 to forecast potential donations once the goal of 20,000 participants is reached. Format the results as Currency Complete your analysis by adding formulas in the range G9 H9 to calculate the high and low thresholds of the forecast Save and close EXP19_Excel_Cho8_CAP_Golden State5k. Exit Excel Submit the file as directed 16 8 17 0 Total Points 100 A B C D E F G H 1 2 Nm 4 5 6 7 8 9 10 11 NNNNN 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Golden State 5k Runner ARC Time Duration Frequency 8806 20 0:18:43 0:14:24 3092 20 0:16:34 0:28:48 6756 21 0:28:48 0:43:12 3768 22 0:14:24 0:57:36 6062 22 0:57:36 1:12:00 4746 22 0:27:22 1:26:24 3503 23 0:22:19 8113 23 0:28:48 2913 24 0:17:17 ge 8526 25 0:22:19 18 9205 27 0:27:22 21 1897 27 0:50:52 30 3569 28 0:25:38 40 7294 28 0:17:43 50 3428 29 0:16:34 60 3299 36 0:43:12 >60 4022 43 0:57:36 2987 44 1:01:12 Correlation Coaciance Variance Standard Deviatio 9309 44 0:19:03 5215 46 1:01:12 8683 47 1:04:48 4305 47 0:35:17 5796 47 1:03:22 6689 48 0:19:03 4673 49 1:04:48 8613 49 1:04:48 9469 51 6:33:24 1936 51 1:04:48 5436 55 1:37:12 4752 55 1:04:48 2398 55 1:04:48 6065 56 0:48:58 4957 57 1:04:48 6971 57 1:37:12 5885 1:26:24 2612 58 0:35:17 2617 59 0:48:58 Race Results Volunteerinfo Participants 36 37 38 58 39 40 AI B E F G H , 1 55 | 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 SO 51 S2 53 54 55 56 57 58 59 60 61 9309 5215 8683 4305 5796 6689 4673 8613 9469 1936 5436 4752 2398 6065 4957 6971 5885 2612 2617 7566 6377 1569 5568 2488 8206 5389 6214 1895 5038 5296 3463 7803 C D 44 0:19:03 46 1:01:12 47 1:04:48 47 0:35:17 47 1:03:22 48 0:19:03 49 1:04:48 49 1:04:48 51 0:33:24 51 1:04:48 55 1:37:12 1:04:48 SS 1:04:48 56 0:48:58 57 1:04:48 57 1:37:12 58 1:26:24 58 0:35:17 59 0:48-58 60 1:26:24 61 0:43:12 62 1:04:48 62 1:37:12 63 1:04:48 63 0:14:24 63 0:25:38 66 0:17:17 67 1:37:12 67 1:33:36 67 0:33:24 68 1:04:48 0:18:43 %mm 88w8%w 88%8%wmu am B BB RaceResults Volunteerino Participants A D E 1 F G H 2 Golden State 5k Location Combined 3 4 5 6 7 8 9 10 11 12 Year Los Angeles San Diego San Jose 2005 3,000 2,250 2,000 2006 3,750 2,375 2,200 2007 4,000 2,400 2.500 2008 4,500 3,200 3,000 2009 5,000 3,175 3,025 2010 7,500 4,000 4,500 2011 8,750 4,500 5,000 2012 9,000 5,000 5,480 2013 9,000 5,210 6,000 2014 9,200 5,500 6,200 2015 10,000 6,000 6,500 2016 10,250 6,025 7,000 2017 11,750 6,250 7,100 2018 12,250 6,800 7,500 2019 12,500 7,000 7,800 2020 15,000 7,800 8,000 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 89 40 RaceResults Volunteerinfo Participants Ready A1 B C D E F G H Golden State Sk Location Los Angeles 20000 2 3 4 5 6 Intercept Slope RS Standard Etro Forecast High Low 7 . 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Year Participants Donations 2005 3000 $10,000.00 2006 3750 $14,500.00 2007 4000 $17,000.00 2008 4500 $20,000.00 2009 5000 $22,500.00 2010 7500 $30,000.00 2011 8750 $45,000.00 2012 9000 $50,100.00 2013 9000 $56,000.00 2014 9200 $58,250.00 2015 10000 $60,125.00 2016 10250 $62,500.00 2017 11750 $64,000.00 2018 12250 $70,000.00 2019 12500 $75,000.00 2020 15000 $85,500.00 32 33 34 35 36 37 38 39 40 RaceResults Volunteerinfo Part Nipants