For this assignment, you will analyze the data from the TC Ice Cream data set and identify trends and patterns. You will submit an analysis report in the form of a PowerPoint presentation. You will also create multiple charts to present the descriptive analysis you performed.
Within the TC Ice Cream Excel Workbook, you will use the sheets “Flavor One Analysis” and “Flavor Two Analysis” to create your visualizations. Select two flavors within the division you selected in Module Two.
Complete the following visualizations for each flavor. The visualizations for Flavor One should be completed in the “Flavor One Analysis” sheet and the visualizations for Flavor Two should be completed in the “Flavor Two Analysis” sheet. It is recommended that you use pivot table filtering to filter the data to the specific division and flavor you have selected.
Specifically, you must address the following rubric criteria:
- Excel Workbook
- Present the key descriptive statistics for the five quantitative variables in a table (quantity sold, advertising expenses, flavor rating, social media posts, and event promotions). The key descriptive statistics include the mean, median, standard deviation, and range.
- Analyze the TC Ice Cream data to identify trends and patterns for the two flavors you have selected. For each flavor, create the following visualizations.
- Create a line chart (also known as a trend chart) using the qualitative variable Date and the quantitative variable Quantity Sold to provide a visualization displaying quantity sold by month.
- Create a line chart (also known as a trend chart) using the qualitative variable Date and the quantitative variable Flavor Rating (average) to provide a visualization displaying flavor rating (average) by year/quarter/month.
- Create a combo chart using the qualitative variable Date (x-axis) and the quantitative variables: Quantity Sold (clustered column) and Advertising expenditures (line – secondary axis).
- Create a combo chart using the qualitative variable Date (x-axis) and the quantitative variables: Quantity Sold (clustered column) and total – Social Medial Posts and Event Promotions (line – secondary axis). Hint: create a pivot table variable to sum the social media posts and event promotions.
- Create a box and whiskers chart using the qualitative variable Quantity Sold. This will help identify any outliers in the quantity sold data.
- Create a histogram for the quantitative variable Flavor Rating. It is recommended to use the data analysis “add-in” and the analysis tool “Histogram” to create the data for the visualization.
- PowerPoint Presentation Using the Milestone One PowerPoint Template, complete the following:
- Outliers: Identify any outliers that you see and explain how they have an impact on the overall trends. Outliers are the data points that can have an impact on your averages and basic descriptive analysis.
- Analysis: Form and explain your initial hypothesis based on your analysis of the charts and trends in Excel.
- Which option (Option A or Option B) would you choose at this point in your analysis, given the monthly expense range of $500–$2,750?
- Why did you choose your selected attributes?
- Describe any relationships or trends you observed while conducting your analysis.
- Your presentation should include key visualizations and analysis to support your report.
