Hired as a consultant by TC Ice Cream. As part of itsexpansion plan

at
Categories
Tags

 

 

 

You have been hired as a consultant by TC Ice Cream. As part of itsexpansion plan, TC Ice Cream wants to limit the number of flavors thatthey sell and only focus on those that are the most profitable. You willdo an analysis of two flavors for a specific division and determinewhich 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 anin-depth analysis to determine which of the two options will be the mostprofitable, given the monthly advertising expense range of $500$2,750.Use the division that you selected in Module Two and the two flavorsthat you selected for Milestone One. Consider the TC Ice Cream data setthat contains data about quantity sold, advertising expenses, flavorrating, 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. Thevisualizations for Flavor One should be completed in the Flavor OneAnalysis sheet and the visualizations for Flavor Two should becompleted 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 fivequantitative variables in a table (quantity sold, advertising expenses,flavor rating, social media posts, and event promotions). The keydescriptive statistics include the mean, median, standard deviation, andrange.
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 thequalitative variable Date and the quantitative variable Quantity Sold toprovide a visualization displaying quantity sold by month.
Create a line chart (also known as a trend chart) using thequalitative 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) andAdvertising Expenditures (line – secondary axis).
Create a combo chart using the qualitative variable Date (x-axis)and the quantitative variables Quantity Sold (clustered column) andtotal Social Medial Posts and Event Promotions (line – secondaryaxis). Hint: create a pivot table variable to sum the social media postsand event promotions.
Create a box and whiskers chart using the qualitative variableQuantity Sold. This will help identify any outliers in the Quantity Solddata.
Create a histogram for the qualitative variable Flavor Rating. It isrecommended to use the data analysis add-in and the analysis toolHistogram to create the data for the visualization.

 

Sample Solution

Part 1: Data Analysis Workbook

1. Key Descriptive Statistics:

Create a table for each flavor (Option A and B) summarizing the following:

  • Quantity Sold: Mean, Median, Standard Deviation, Range
  • Advertising Expenses: Mean, Median, Standard Deviation, Range
  • Flavor Rating: Mean, Median, Standard Deviation, Range
  • Social Media Posts: Mean, Median, Standard Deviation, Range
  • Event Promotions: Mean, Median, Standard Deviation, Range

2. Trends and Patterns:

For each flavor, create the following visualizations:

  • Line Chart: Quantity Sold vs. Date (monthly)
  • Line Chart: Flavor Rating (average) vs. Date (yearly/quarterly/monthly)
  • Combo Chart: Quantity Sold vs. Date (clustered column) with Advertising Expenses (line, secondary axis)
  • Combo Chart: Quantity Sold vs. Date (clustered column) with Social Media Posts & Event Promotions (combined line, secondary axis)
  • Box and Whisker Chart: Quantity Sold
  • Histogram: Flavor Rating

3. Analysis & Interpretation:

  • Analyze each visualization for both flavors, looking for trends, patterns, and outliers.
  • Identify strong points and weaknesses for each flavor based on sales, ratings, advertising costs, social media engagement, and event promotion effectiveness.
  • Consider the monthly advertising expense range ($500-$2,750) and how each flavor aligns with this budget in terms of ROI and future profit potential.

4. Conclusion & Recommendation:

  • Based on your analysis, recommend which flavor (Option A or B) should be kept to maximize profitability for the chosen division, considering all relevant factors.
  • Support your recommendation with data-driven insights from the visualizations and analysis.

Additional Tips:

  • Use pivot tables and filters to efficiently segment data for the specific division and flavors.
  • Utilize appropriate chart types to effectively visualize trends and relationships.
  • Consider seasonality and external factors that might impact sales and trends.
  • Present your findings clearly and concisely, highlighting key insights and supporting your recommendation.

By following these steps and thoroughly analyzing the data, you can provide a well-informed and data-driven recommendation to TC Ice Cream on which flavor to keep for optimal profitability. Remember, this is a hypothetical exercise, and you should not treat it as a substitute for professional advice.

This question has been answered.

Get Answer
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, Welcome to Compliant Papers.
Hi, how can I help?