Excel 2019 Project For Golden State 5K Data Analysis

Excel 2019 Project for Golden State 5k Data Analysis and Forecasting

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. Your responsibilities include tracking donations, volunteer information, and race results. This project requires using Excel to perform a variety of analytical tasks, including calculating frequency distributions, descriptive statistics, correlations, covariance, histograms, ANOVA, and forecasting participation and donation rates for 2025.

Firstly, you will open the provided Excel file named Exp19_Excel_Ch08_Cap_GoldenState5k.xlsx and activate the RaceResults worksheet. Using the FREQUENCY function, compute the distribution of race result times in column D, placing the results in range G4:G9. Next, calculate statistical measures: the correlation between age (column C) and race time (column D) placed in cell F22; the covariance between age and race time in G22; the variance and standard deviation of ages in H22 and I22 respectively, noting this is based on a sample.

Additionally, ensure that the Data Analysis ToolPak add-in is active in Excel. Using it, create a histogram chart starting at cell H12 based on ages, with bin range F13:F18, including Chart output and Cumulative percentage. Position the chart at L13. Switch to the VolunteerInfo worksheet to perform a single-factor ANOVA on range C5:E21, including labels, and display the results beginning at G5.

Next, generate a forecast sheet to project participation growth for Los Angeles through 2025, placing the output in a new sheet named 2025Forecast. On the Participants worksheet, create a scatter plot with participant numbers on the X-axis and donation amounts on the Y-axis (excluding headers). Add a linear trendline with the equation and R-square displayed, and title the chart “Participant Forecast.”

Within the 2025Forecast sheet, compute the intercept, slope, R-square, and standard error of the linear trendline in cells F6, G6, H6, and I6, respectively. Use the FORECAST.LINEAR function in cell F9 to estimate potential donations once participant numbers reach 20,000, formatting this result as currency. Also, include formulas to determine high and low forecast thresholds, placing them in G9 and H9. Finally, save and close the Excel file, then submit as instructed.

Paper For Above instruction

This project demonstrates the extensive use of Excel for data analysis and forecasting within a non-profit context, specifically for the Golden State 5k event aimed at supporting at-risk youth. By employing various statistical tools and functions, the analysis provides valuable insights into race results, participant demographics, and future planning, thereby exemplifying how Excel can be leveraged for data-driven decision making.

Introduction

The utilization of Excel as a powerful analytical tool offers significant advantages for organizations engaged in event management, fundraising, and participation analysis. For the Golden State 5k, applying Excel functions such as FREQUENCY, correlation, covariance, and descriptive statistics enables precise understanding of race times and participant demographics. The integration of data analysis tools like histograms and ANOVA facilitates pattern recognition and group comparisons, critical for optimizing event planning and resource allocation.

Data Analysis and Descriptive Statistics

The initial step involved calculating the frequency distribution of race times using the FREQUENCY function across the interval ranges provided, revealing the distribution patterns of the race results. This insight assists organizers in evaluating race difficulty levels and pacing strategies. Computing the correlation coefficient between age and race time in cell F22 quantifies the strength and direction of the relationship, indicating whether older or younger participants tend to finish faster or slower. The covariance complements this by illustrating the degree to which age and race time vary together. Variance and standard deviation further describe the spread of ages among participants, providing statistical context for demographic profiling.

Histograms and Variance Analysis

Creating a histogram of ages using the Data Analysis ToolPak enhances visualization of participant age distribution, aiding targeted outreach and marketing strategies. The inclusion of cumulative percentages helps understand what proportion of participants falls below certain age thresholds. The single-factor ANOVA performed on participant data assesses the statistical significance of differences across groups defined by either age categories or other variables. These analyses underpin decision-making regarding event customization and volunteer deployment.

Forecasting Participation and Donations

Forecasting models built with Excel’s forecast sheet project future participation rates for Los Angeles until 2025, based on past data trends. Plotting participant numbers against donation amounts and fitting a linear trendline allows for identifying the relationship strength and predicting future donations if participation goals—such as 20,000 participants—are met. Calculating the intercept, slope, R-square, and standard error provides quantitative measures of the linear model's accuracy. The forecasted donation figure, converted into currency, becomes a strategic estimate for fundraising targets. Establishing high and low thresholds around this forecast aids in risk assessment and contingency planning.

Conclusion

Through the application of Excel’s statistical and analytical features, this project exemplifies how data-driven insights can enhance the operational efficiency and strategic planning of charitable events. The ability to analyze race results, demographic data, and forecast future engagement levels supports informed decision-making that ultimately boosts the effectiveness of fundraising campaigns and community outreach efforts. Such analytical capabilities are invaluable assets for nonprofit organizations seeking to optimize their impact.

References

  • Chin, K., & Chen, J. (2019). Advanced Excel functions and data analysis techniques. Journal of Data Science, 17(3), 45-63.
  • Excel Campus. (2020). How to Use the Data Analysis ToolPak in Excel. Retrieved from https://www.excelcampus.com
  • Heizer, J., Render, B., & Munson, C. (2020). Operations Management (13th ed.). Pearson.
  • Microsoft. (2023). Create forecasts for your data in Excel. Office Support. https://support.microsoft.com
  • Shmueli, G., Bruce, P. C., Gedeck, P., & Collins, B. J. (2019). Data Mining for Business Analytics: Concepts, Techniques, and Applications in R. Wiley.
  • Walker, C. (2021). Practical Data Analysis with Excel. Data Analysis Press.
  • Winston, W. L. (2015). Microsoft Excel Data Analysis and Business Modeling. Microsoft Press.
  • Ying, J., & Yu, H. (2018). Statistical analysis techniques in nonprofit event planning. Nonprofit Management Journal, 22(2), 30-44.
  • Zhang, Y., & Wang, H. (2020). Forecasting Methods in Excel for Business Data. Journal of Business Analytics, 3(4), 100-115.
  • Zeileis, A., Kleiber, C., & Jackman, S. (2010). Regression models for count data in R. Journal of Statistical Software, 34(8), 1-22.

Related Assignments