You have been hired as a consultant by TC Ice Cream. As part of its expansion plan, TC Ice Cream wants to limit the number of flavors that they sell and only focus on those that are the most profitable. You will do an analysis of two flavors for a specific division and determine which flavor should be kept and which should be removed.
- Option A: Keep Flavor One
- Option B: Keep Flavor Two
The TC Ice Cream management team has tasked you with doing an in-depth analysis to determine which of the two options will be the most profitable, given the monthly advertising expense of $5,000. Use the division that you selected in Module Two and the two flavors that you selected for Milestone One. Consider the TC Ice Cream data set that contains data about quantity sold, advertising expenses, flavor rating, social media posts, and event promotions.
Prompt
Part 1: Data Analysis Workbook
Analyze the data from the provided TC Ice Cream data set and identify current and future trends and patterns in operations.
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 of the . 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:
- 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 qualitative 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.
- Outliers: Identify any outliers that you see and explain how they have an impact on the overall admission and expense trends. Outliers are the data points that can have an impact on your descriptive analysis.
- Perform two bivariate regressions to provide recommendations.
- For the first bivariate regression for Flavor One, the dependent variable should be Quantity Sold. Choose an independent variable from one of the remaining attributes (Advertising Expenses, Flavor Rating, Social Media Posts, Event Promotions).
- Use the Flavor One Bivariate Analysis sheet.
- For the second bivariate regression for Flavor Two, the variable should be Quantity Sold. Choose an independent variable from one of the remaining variables (Advertising Expenses, Flavor Rating, Social Media Posts, Event Promotions).
- Which independent variable seems most appropriate to lead you to a monthly quantity sold of 3,00012,000 gallons?
- Perform two multivariate regressions to provide recommendations.
- For the first multivariate regression, the dependent variable should be Quantity Sold (the column in the data set). Choose two independent variables from one of the remaining attributes (Advertising Expenses, Flavor Rating, Social Media Posts, Event Promotions). Use the Flavor One Multi. Analysis sheet.
- Which combination of the attributes seems most appropriate to lead you to the expense of $5,000?
- For the second multivariate regression, the dependent variable should be Quantity Sold (the column name in the data set). You should choose two independent variables from one of the remaining attributes (Advertising Expenses, Flavor Rating, Social Media Posts, Event Promotions). Use the Flavor Two Multi. Analysis sheet.
- Which combination of the attributes would seem appropriate to lead you to a monthly quantity sold of 3,00012,000 gallons?
Part 2: Final Recommendation Analysis PowerPoint Presentation
Create a PowerPoint presentation designed for the TC Ice Cream management team to share the results of your analysis and provide your final recommendation.
- Current State Analysis (slides 14): State the option (A or B) that you chose and three reasons for your choice.
- Analyze the histogram of the flavor ratings for each flavor you selected.
- Which option (Option A or Option B) would you choose based on your analysis, given the monthly advertising expenses of $5,000?
- Why did you choose your selected attributes?
- Describe any relationships or trends you observed while conducting your analysis.
- Predictions and Trends Analysis (slides 57): Present the predictions based on the regressions that you have analyzed.
- Briefly describe the key elements for each regression presented in Excel. Cite the regression coefficient, regression line equation, p value, and significance F in your description.
- Which of the independent variables are impactful, and why?
- Executive Audience Summary (slide 8): Summarize your findings for an executive audience.
What to Submit
To complete this project, you must submit the following:
- TC Ice Cream Excel Workbook:
Submit your completed as an Excel file. - Final Recommendations Analysis Presentation:
Submit a PowerPoint presentation with 8 to 10 slides. There is no template for this assignment, so you may use an earlier presentation template or create your own design. Any sources should be cited according to APA style. Consult the for more information on citations.
Project Rubric
| Criteria | Exceeds Expectations (100%) | Meets Expectations (90%) | Partially Meets Expectations (70%) | Does Not Meet Expectations (0%) | Value |
|---|---|---|---|---|---|
| Descriptive Statistics | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Presents descriptive statistics for the five quantitative variables (mean, median, standard deviation, and range) with no errors or omissions for the two flavors selected | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include presenting error-free statistics or including critical data | Does not attempt criterion | 10 |
| Flavor One Trends and Charts | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Creates six visualizations for flavor one to identify trends | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include creating more charts or trends, and using data variables with minimal errors or omissions | Does not attempt criterion | 10 |
| Flavor Two Trends and Charts | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Creates six visualizations for flavor two to identify trends | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include creating more charts or trends, and using data variables with minimal errors or omissions | Does not attempt criterion | 10 |
| Outliers | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Identifies specific outliers in the data and describes how specific outliers impact the descriptive analysis | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include highlighting specific outliers and their impact on charts or trends | Does not attempt criterion | 10 |
| Bivariate Regressions | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Performs two linear bivariate regressions to provide recommendations | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include clearly citing each of the outputs from regressions used to weigh the models usability, citing the regression best fit line model, and analyzing the scatterplot, best fit line, and residual plots | Does not attempt criterion | 10 |
| Multivariate Regressions | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Performs two linear multivariate regressions to provide recommendations | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include clearly citing each of the outputs from regressions used to weigh the models usability, citing the regression best fit line model, and analyzing the scatterplot, best fit line, and residual plots | Does not attempt criterion | 10 |
| Current State Analysis | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Explains the analysis in a clear and detailed manner | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include ensuring the analysis includes specific mention of data points, outliers, and trends that connect strongly with Excel analysis | Does not attempt criterion | 10 |
| Predictions and Trends Analysis: Bivariate Regression | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Describes the key elements for each bivariate regression analysis in a clear and detailed manner | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include ensuring that the report clearly mentions the attributes used for bivariate regression, connects with the given scenario, or cites the usability of the bivariate regression model to predict outcomes; connects the analysis strongly with Excel analysis | Does not attempt criterion | 10 |
| Predictions and Trends Analysis: Multivariate Regression | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Describes the key elements for each multivariate regression analysis in a clear and detailed manner | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include ensuring that the report clearly mentions the attributes used for multivariate regression, connecting with the given scenario, and citing the usability of the multivariate regression model to predict outcomes; connects the analysis strongly with Excel analysis | Does not attempt criterion | 10 |
| Executive Audience Summary | Exceeds expectations in an exceptionally clear, insightful, sophisticated, or creative manner | Creates a summary of findings that is appropriately tailored for an executive audience | Shows progress toward meeting expectations, but with errors or omissions; areas for improvement may include relying too heavily on visual or written analysis | Does not attempt criterion | 5 |
| Clear Communication | Exceeds expectations with an intentional use of language that promotes a thorough understanding | Consistently and effectively communicates in an organized way to a specific audience | Shows progress toward meeting expectations, but communication is inconsistent or ineffective in a way that negatively impacts understanding | Shows no evidence of consistent, effective, or organized communication | 5 |
| Total: | 100% | ||||
Get this or a similar paper done in as fast as 4 hours, 24/7.
NB: We do not sell prewritten papers. All essays are written from scratch according to specific needs and instructions
✔Secure Service ✔ Plagiarism Free ✔On-time Delivery
Leave a Reply
You must be logged in to post a comment.