Analysis of Carls Cookies

A. Analysis of Carls Cookies

Step 1: Read the Scenario

SCENARIO: Carls Cookies, established in 2019, is a boutique bakery. Carls Cookies has built a loyal customer base due to its commitment to quality and unique product offerings. Carl, the owner, has a strong desire to optimize production and marketing strategies to maximize revenue and maintain high customer satisfaction. Carl has collected sample data over the past 85 weeks and wants you to analyze this data to uncover insights that can help address his questions.

Step 2: Look Over the Data

Use Carls Cookies Excel workbook to help you with this case, which is located here:

A breakdown of each column is as follows:

WeekThe chronological week in which data was collected.YearThe year in which data was collected.QuarterThe financial quarter in which the data was collected.Gross SalesThe dollar amount of gross sales for the week.Cookies SoldThe number of cookies sold for the week.Selling Price / CookieThe amount charged per cookie.Snickerdoodles SoldHow many snickerdoodle cookies were sold that week.Peanut Butter SoldHow many peanut butter cookies were sold that week.Pumpkin SoldHow many pumpkin cookies were sold that week.Sugar SoldHow many sugar cookies were sold that week.Oatmeal Raisin SoldHow many oatmeal raisin cookies were sold that week.White Choc. Mac. SoldHow many white chocolate macadamia cookies were sold that week.Gingerbread SoldHow many gingerbread cookies were sold that week.Black and White SoldHow many black and white cookies were sold that week.Macaron SoldHow many macarons were sold that week.Cost to Make CookiesThe cost of ingredients used to make all cookies in a particular week.Labor CostThe labor cost of Carls Cookies for the week.Rent CostThe rent cost for Carls Cookies for the week.Utilities CostThe cost of all utilities for the week.Marketing CostThe amount Carls Cookies spent on marketing that week.Type of MarketingWhat kind of marketing Carls Cookies did for the week.Discounts GivenThe number of discounts Carls Cookies gave to customers that week.Customer Sat. RatingThe average customer satisfaction rating that week.Average Customer SpendThe amount the average customer spent that week.

ASSIGNMENT: For this touchstone, you are going to analyze the sample data using the analytical techniques you have learned in Challenges 1.2, 2.1, and 2.2 and present your findings as a PowerPoint presentation. Your audience is Carl, the owner of Carls Cookies. You should answer all of Carls questions in your presentation. Your analysis should be presented in a PowerPoint presentation that includes speaker notes, which explain your points. Use visualizations to summarize your findings.Step 3: Read Carl’s Questions

Question 1: Carl is offering several cookie varieties. He wants to optimize his operations by identifying underperforming cookies. Carl has provided you with sales data, including weekly cookie sales and selling prices, as well as associated costs. After analyzing customer feedback, Carl found trends in customer satisfaction:

• Peanut Butter Cookies: Consistently receive the lowest satisfaction ratings, with complaints about dryness and lack of flavor.
• Pumpkin Cookies: Average satisfaction ratings, with some customers mentioning they are too seasonal for year-round appeal.
• Macaron Cookies: Not always everyones favorite but have good customer satisfaction ratings overall.

Using the provided sales data, create a PivotTable (format as a heatmap) and a PivotChart of units of cookies sold by cookie type. Based on your findings and the customer feedback Carl supplied, recommend whether Carl should continue (or discontinue), improve, or expand production of specific cookie varieties. Specifically make sure to identify underperforming cookies using sales data, recommend whether to continue, improve, or discontinue specific varieties. (Slide #2)

Question 2: Carl wants to understand how his marketing strategies (flyer, coupon, tv, radio, and shopper) boost his store’s sales. He has gathered data on the different marketing campaigns, including total gross sales, number of cookies sold, and marketing costs for each campaign. He gives you the following information about marketing to consider:

• The competition spends 10% of their gross sales on marketing.
• Carl spends about 7% of his gross sales on marketing.
• Studies show if Carl moves to social media marketing, he can cut his marketing costs to $300 per week, but he will also incur a 5% drop in gross sales.

Carl is seeking your assistance to analyze the effectiveness of these marketing strategies based on total gross sales, average gross sales per marketing media, and gross sales generated per marketing dollar spent. Visualize which type of marketing is the most effective overall and which one performs best on average. Based on the data and Carls insights, should he adjust his marketing strategy? (Slide #3)

Question 3: Carl is looking to understand the financial trends of his business and how he can navigate them while boosting weekly net income. He has provided data on his gross profit and all associated costs over several months. After reviewing costs for his business, Carl provides you with the following information:

• Selling Price per Cookie: Has remained unchanged at $3.50 for the past two years, but he is thinking about raising the price to $4.00 to keep up with rising costs.
• Utilities Cost: Generally stable, but there are rumors of rate hikes of 10%.
• Cost to Make Cookies: Variable, often influenced by inflation and commodity pricing. It is expected that these costs will increase by 6% in the next 12 months.

Carl is asking for your help to analyze his income trends, specifically focusing on net income (Net Income = Gross Profit All Costs). Specifically, he wants to identify trends considering the following scenarios:

• Current weekly net income
• New weekly net income if he raised his prices to $4 per cookie and there are no cost increases
• The increase in weekly net income if prices increase to $4 per cookie but costs do not increase
• New net income per week if both costs and price of cookies increase

Based on this data, how could changing price and cost factors impact his overall profitability? Using the data and the information Carl provided, what are your recommendations to boost weekly net income? (Slide #4 and calculations on Slide #5)

Question 4: Carl is interested in understanding customer spending habits at his store. He has collected data on the average amount each customer spends per week. Carl notices the following trends:

• High-Spending Customers: A small percentage of customers make large purchases regularly (between $34 and $35).
• Moderate-Spending Customers: The majority of customers spend a consistent, moderate amount each week (between $28-$33). These customers can usually be upsold by $2-$3 per customer if deals are offered.
• Low-Spending Customers: Customers who make infrequent purchases ($27 or less) are often more demanding and want to sample cookies, increasing costs.

Carl is seeking your assistance to analyze the distribution of the average amount a customer spends per week. He wants to learn what insights can be drawn from this data to better understand customer behavior and improve his profit margin. What recommendations can you give, based on the data? (Slide #6)

Question 5: Carl wants to gain insight into the performance of his business throughout the year and has provided data on his gross sales in the spreadsheet. Carls business goals are:

• To attain quarter-over-quarter growth in gross sales of 5%
• To mediate any quarterly declines in sales
• To have his geometric mean per year average 5%

Based on the trend and geometric mean data you calculated and Carls goals, should Carl raise his prices, reduce the costs of the ingredients he uses in his cookies, or both? (Slide #7)

Question 6: In 2023, Carl began a discount incentive to help increase customer satisfaction. The incentive is that for any order over $30, the customer would receive 10% off their entire order. Carl has collected data on the number and cost of discounts given each week and the corresponding average customer satisfaction ratings. Carl wonders:

• Should I continue with the discounts?
• Are the costs of the discounts worth any potential increases in customer satisfaction?
• What if I discontinue the discounts?

Carl is seeking your assistance in determining the strength of any relationship between the number of discounts he gives each week and their corresponding customer satisfaction scores. Using this data, what advice would you give him that would answer his questions about the discounts? (Slide #8 and show calculations on Slide #9)

Question 7: Every quarter, Carl often ends up ordering too many ingredients because he does not have a valid model to reference for purchases. In addition, some ingredients for his cookies need a longer lead time to order. Because of these issues, Carl needs to understand the relationship between the business quarter and the type of cookies sold in that quarter. He has gathered data on the number of each type of cookie sold across the four business quarters for 2023. Carl has the following questions:

• Is there an effective model I can use to help me order ingredients?
• Are type of cookie and quarter sold dependent variables?
• Should I use the data I observed as a guide to help me order ingredients in the future?

Carl is seeking your assistance to analyze whether there is a significant association between the business quarter and the type of cookie sold. Based on the data, does Carl have a strong model he can use as a guide to order ingredients for upcoming quarters? (Slide #10)

Step 4: Analyze the Provided Excel Dataset to Derive Insights

You should answer all of Carls questions in your presentation. Here is a basic template with slide headers that you can use, but it is not required.

Make sure to use visuals (graphs, tables, heatmaps) to enhance your findings. At minimum you should have 11 slides.

• Slide 1 Title Slide
• Slide 2 Question 1 on Product Performance Analysis
• Which cookies are underperforming, and what should Carl do?
• Analyze weekly sales and customer satisfaction ratings.
• Recommend whether Carl should discontinue, improve, or expand certain varieties.
• Slide 3 Question 2 on Marketing Strategy Evaluation
• Slide 4 Question 3 on Financial Trends Analysis
• Slide 5 Excel Calculations for Financial Trends for Question #3
• Slide 6 Question 4 on Customer Spending Behavior
• Slide 7 Question 5 on Quarterly Sales Performance
• Slide 8 Question 6 on Discount Incentive Effectiveness
• Slide 9 Excel Calculations for Discounts for Question 6
• Slide 10 Question 7 on Ingredient Ordering Model
• Slide 11 Summary and Recommendations for Carl Slide

Step 5: Add Speaker Notes Explaining Your Methodology, Analysis, and Recommendations

Your analysis should be presented in a PowerPoint presentation that includes speaker notes, which should clearly explain your analysis and recommendations.

Speaker notes for each question should address the following:

1. The data you used (and why you used that data) to answer the question.
2. A justification of why you chose the data visualization you used.
3. A detailed explanation of how the data supports your answer.
4. The rationale of any calculations you made, if any.

Step 6: Review the Grading Rubric to Ensure All Criteria are Met

Review the rubric to ensure that you understand how you will be evaluated. Also review the requirements to ensure that your Touchstone is complete.

Step 7: Submit Your Touchstone

Submit your completed Touchstone (as a .pptx file) using the blue button at the top of this page.

B. Rubric

Advanced (100%)Proficient (85%)Acceptable (75%)Needs Improvement (50%)Non-Performance (0%)Completeness of Responses to Carls Questions

All seven of Carls questions have been answered fully and accurately. Each question is weighted equally within this category. (25%)

All 7 questions are thoroughly answered with detailed analysis and appropriate conclusions for each. There is clear evidence of deep understanding and insight.5-6 questions are fully answered, with minor gaps or incomplete interpretations in some areas. Overall analysis and conclusions are solid, though some areas could be improved.3-4 questions are answered, but significant gaps in the analysis or reasoning exist. The work demonstrates basic understanding but lacks depth in several areas.1-2 questions are answered, or most answers show a lack of thoroughness, accuracy, or completeness. The analysis is superficial or incorrect in several areas.None of the questions are adequately answered, or all answers are entirely missing depth, accuracy, and completeness.Data Accuracy, Analytical Techniques, and Use of Excel

Data analysis is accurate, appropriate analytical techniques were used (as learned in Challenges 1.2 through 2.2), and Excel was used effectively to perform the analysis. (30%)

All data calculations and analyses are accurate, and appropriate analytical techniques (e.g., statistical methods, trend analysis) are used for each of Carls 7 questions. Excel is used effectively and efficiently throughout the analysis with no errors.Most analyses are correct, with minor errors or incomplete application of analytical techniques in some cases. Excel is used effectively, though some areas could be optimized for better efficiency.Some analyses contain errors or misinterpretations, and the appropriate analytical techniques are not consistently applied. Excel is used, but with noticeable inefficiencies or mistakes.Several analyses are inaccurate or misinterpreted, and the appropriate analytical techniques are frequently misapplied. Excel usage is flawed, and its application to the analysis is limited or incorrect.Most analyses are inaccurate or missing. Analytical techniques are either not applied or applied incorrectly, and Excel is used minimally or not at all.Presentation Design & Use of Appropriate Visualizations

The design of the presentation is effective, with a specific focus on whether the most appropriate visualizations (charts, graphs, etc.) are chosen to best represent the analysis for each of Carls 7 questions. (20%)

Each of Carls 7 questions is accompanied by the most appropriate and effective visual (e.g., bar chart, line graph, pie chart, scatter plot, etc.), clearly and accurately communicating the analysis. The choice of visual is based on best practices for data representation, enhancing the clarity and impact of the presentation.Most questions are accompanied by appropriate visuals that support the analysis, though some may not fully align with the optimal choice for data representation. The overall presentation remains clear and effective.Visuals are included for most questions, but several do not represent the data in the best possible way, leading to reduced clarity and effectiveness of the analysis. Some improvement in visual selection is needed.Visuals are present for only a few questions, and many of them are not the best choice for effectively communicating the data. The analysis lacks clarity due to inappropriate or poorly designed visuals.Visuals are missing for most or all questions, or the chosen visuals are ineffective and fail to support the analysis. The presentation does not clearly communicate the data.Clarity and Insightfulness of Speaker Notes

Speaker notes clearly explain the analysis for each of Carl’s 7 questions, offering insights, recommendations, and detailed explanations. (15%)

Speaker notes are thorough, insightful, and clearly explain the analysis for each of Carls 7 questions. They provide detailed explanations, relevant insights, and actionable recommendations, demonstrating a deep understanding of the data and findings.Speaker notes are clear and adequately explain the analysis for most of Carls questions. Some minor gaps in detail or insights may exist, but the recommendations are generally relevant and supported by the data.Speaker notes provide explanations for some of Carls questions but may lack depth or clarity. Several explanations may be incomplete, and insights or recommendations are either minimal or not strongly linked to the data.Speaker notes are incomplete, unclear, or lacking depth for most of Carls questions. The analysis is not well-explained, and the insights or recommendations are shallow or only marginally related to the data.Speaker notes are largely absent or fail to explain the analysis effectively for the majority of Carls questions. Recommendations, if provided, are irrelevant or not data driven.Technical Execution & Use of Excel

Technical aspects of the project are accurate and complete, including correct calculations, adherence to the PowerPoint structure, and the effective use of Excel for analysis. (10%)

All calculations are correct, and Excel is used efficiently and effectively for data analysis, including appropriate use of formulas, charts, and other tools. The presentation fully adheres to all assignment requirements, including slide structure and file format.Most calculations are correct, with only minor errors. Excel is generally used effectively, though some inefficiencies or minor mistakes may be present. The presentation follows most assignment requirements, with slight deviations in slide structure or format.Some calculations are incorrect, and Excel is not fully utilized effectively in parts of the analysis. Several assignment requirements are missing or incomplete, such as the correct number of slides or proper file format.Many calculations are incorrect, and Excel is used poorly or only partially. Numerous assignment requirements are not met, and the presentation does not align with key structural or formatting expectations.Most or all calculations are incorrect, and Excel is either not used or used ineffectively. The majority of assignment requirements, such as slide structure and file format, are not fulfilled.

C. Requirements

The following requirements must be met for your submission:

• Hand in a .pptx file with slides listed above.
• Use a readable 11- or 12-point font.
• All writing must be appropriate for an academic context. Follow academic writing conventions (correct grammar, spelling, punctuation, and formatting).
• Plagiarism of any kind is strictly prohibited.
• Submission must include your name and the date (included in the template).

This assignment provides a practical experience in business analytics, honing skills essential for data-driven decision-making in business environments. Your analysis and recommendations will help Carl optimize his operations and improve profitability.

Good luck, and enjoy uncovering insights for Carl’s Cookies!