I am a newbie and only working with Excel for 3 months. This is my first dashboard and although intimidated by this challenge and all the talent, I wanted to do what I could with what I currently know. I enjoyed this challenge. In addition to conducting the analysis, I also played the stakeholder role. This allowed me to do a lot of self-examination about the reasons for my choices. What are the benefits for me and the trick-or-treaters? My goal is to make sure all trick-or-treaters leave with a treat they love while keeping costs low.

Goal:

The primary goal of this project was to identify the top three candies to hand out on Halloween that would maximize trick-or-treater satisfaction. By doing so, I aimed to make my house the most popular stop for candy on the block, ensuring a memorable experience for trick-or-treaters of all ages.

Business Needs:

The business need here was to find a balanced selection of candy that meets the following criteria:

• Popularity: Ensure the candies are well-liked by the majority of trick-or-treaters, as measured by win percentage in online votes.
• Variety: Include a range of candy types (chocolate, fruity, and crunchy) to appeal to different tastes and preferences.
• Cost-effectiveness: Maintain a budget-friendly selection without sacrificing quality or appeal, by taking price percentage into account.
• Moderate Sugar Content: Factor in sugar content as a secondary consideration, while still prioritizing taste and enjoyment for the trick-or-treaters.

How I Discover and Present Insights:

To discover the top candy choices, I:

1. Analyzed the dataset containing 85 candies with attributes like win percentage, price percentage, and sugar percentage.
2. Filtered the candies to identify the best-performing ones in terms of popularity, price, and variety.
3. Ranked the top candies based on how well they performed across these factors, while also ensuring a balance between different candy types (chocolate, fruity, crunchy).
4. Visualized the results using charts and tables in an interactive Excel dashboard, allowing for easy comparison and analysis of the final choices.
5. The final selection was based on insights that showed the highest win percentages, a variety of textures and flavors, and a reasonable price point to ensure both popularity and cost control.

By combining data-driven decision-making with popular candy types, I was able to select three candies that will appeal to a broad audience and ensure the house remains a Halloween favorite for trick-or-treaters.

Analytical report: The selection of Reese's Miniatures, Sour Patch Kids, and Nerds

It is important to balance several factors when choosing Halloween candy, including popularity, budget, and variety. As my primary candy choice this Halloween, I will be handing out Reese's Miniatures, Sour Patch Kids, and Nerds. Even though sugar content is a growing concern, especially among parents monitoring their children's intake, it was not the primary factor in my decision. To ensure that all trick-or-treaters would be satisfied, I prioritized win percentage (popularity), cost-effectiveness, and providing a variety of flavors and textures.

1. Reese's Miniatures:

· Win Percentage: 81.87% (Highly Popular)

· Price Percentage: 51.1% (Moderate)

· Sugar Content: 3.3 grams per miniature

· Characteristics: Chocolate, peanut/almondy

It was decided to select Reese's Miniatures since they offer the widely loved combination of chocolate and peanut butter at a more affordable price and size than the full-size Reese's Peanut Butter Cups. Even though Reese's Peanut Butter Cups are often considered the top candy choice, miniatures allow for more candies to be distributed without exceeding budgetary restrictions.

Consideration of sugar content:

In addition to sugar content, we chose Reese's Miniatures because they allow for smaller portions. Reese's Cups contain 8 grams of sugar, while Miniatures contain 3.3 grams. It is true that if a child consumes 3 miniatures, they exceed the sugar content of one full-sized cup (9.9g vs. 8g). However, in the context of Halloween—where children collect many different candies—parents are more likely to monitor overall consumption rather than control individual pieces of candy. Since miniatures are individually wrapped, they allow for better portion control over time.

2. Sour Patch Kids:

• Win Percentage: 59.86% (Popular)

• Price Percentage: 11.6% (Moderate)

• Sugar Content: ~5 grams per serving

• Characteristics: Fruity, Sour, Chewy

In contrast to the rich, chocolatey Reese's Miniatures, Sour Patch Kids provide a fruity and sour experience. Its sweet and sour flavors appeal to children and add variety to the candy selection, so there is something for everyone.

3. Nerds:

• Win Percentage: 55.35% (Above Median)

• Price Percentage: 43.3% (Budget-Friendly)

• Sugar Content: ~12 grams per small package

• Characteristics: Fruity, Crunchy, Hard Candy

Nerds were chosen to complete the variety of candy offerings with a crunchy, hard candy option. While Nerds rank lower in popularity than Reese’s and Sour Patch Kids, they are still well-liked, falling above the median win percentage in the dataset. Moreover, Nerds are incredibly budget-friendly, making them a great option when handing out a larger quantity of candy.

Why sugar content was not the primary factor:

Though sugar content is important, especially for parents monitoring their children's intake, it was not the primary consideration for choosing these three candies. Here’s why:

1. Halloween is a Special Occasion:

Halloween is a time when candy consumption naturally increases, and parents typically monitor the overall intake after the candy is collected. Since kids gather a variety of treats, the focus is on providing a fun experience rather than limiting sugar intake at the moment of collection. Parents can regulate candy consumption after the event.

2. Portion control:

Reese's Miniatures allow for smaller portions, making it easier for parents to manage candy intake. Similarly, Nerds' small package sizes and Sour Patch Kids' sour intensity mean that children may consume them more slowly. This gives parents much more control over how much candy is consumed.

3. Prioritizing Variety and Popularity:

Variety was crucial in candy selection. Offering a mix of chocolate, fruity, sour, and crunchy options ensures that all trick-or-treaters find something they enjoy. Additionally, the win percentage (popularity) was heavily weighted in the decision, as the goal is to make the house a favorite stop for trick-or-treaters.

4. Budget-Friendly Options:

With the inclusion of Nerds, I ensured that the selection remained budget-friendly, allowing more candy to be distributed while maintaining quality.

Conclusion:

While sugar content was considered, the main factors driving the selection of Reese’s Miniatures, Sour Patch Kids, and Nerds were popularity, variety, and budget. These three candies offer a well-rounded selection of flavors and textures, appealing to a broad range of trick-or-treaters. Reese’s Miniatures caters to chocolate lovers, Sour Patch Kids provides a fruity and sour option, and Nerds add a fun, crunchy element to the mix. Together, these three candies ensure that trick-or-treaters of all tastes will find something they love, making my house a top stop on Halloween night.

I was learning as I was cleaning, processing, analyzing, and visualizing.

Data Analysis Process:

1. Data Cleaning and Preparation:

The first step in my analysis involved organizing and cleaning the dataset. I ensured that all relevant data—such as candy names, win percentages, price percentages, and sugar percentages—were properly formatted. No major data cleaning was required as the dataset was clean and ready to use.

I added helper columns to categorize the candies based on various factors. For example, I created categories for budget-friendly, moderately priced, and high-priced candies based on price percentiles. Similarly, I used sorting and filtering to examine candy popularity.

2. Formulas Used in the Analysis:

A. Win Percentage Ranking:

To rank candies based on their win percentage (popularity):

=TEXTJOIN(" ", TRUE,

IF([@chocolate]=1, "· Chocolate", ""),

IF([@fruity]=1, "· Fruity", ""),

IF([@caramel]=1, "· Caramel", ""),

IF([@peanutyalmondy]=1, "· Peanut/Almondy", ""),

IF([@nougat]=1, "· Nougat", ""),

IF([@crispedricewafer]=1, "· Crisped rice wafer", ""),

IF([@hard]=1, "· Hard Candy", ""),

IF([@bar]=1, "· Candy Bar", ""),

IF([@pluribus]=1, "· Multiple in pack", ""))

This allowed me to have control over the text appearing on different places in my dashboard. (I know there is probably an easier way, but for now this is what I know.

B. Price Categorization (Budget-Friendly, Moderate, High):

To categorize candies based on price, I used the QUARTILE function to divide the data into budget-friendly, low-priced, moderately-priced, and high-priced categories:

=IF(L3<=QUARTILE.EXC($L$2:$L$86, 1), "Lower Price", IF(L3>QUARTILE.EXC($L$2:$L$86, 3), "Higher Price", "Moderate Price"))

I repeated this for the sugar and win columns as well.

This allowed me to divide the candies into three groups:

• Budget-Friendly: Below the 25th percentile of pricepercent.

• Moderately Priced: Between the 25th and 75th percentiles.

• High-Priced: Above the 75th percentile.

C. Combination of Metrics (Win + Price + Sugar):

To evaluate candies based on a combination of win percentage, price percentage, and sugar content, I calculated a weighted score. This formula combines win percentage and price percentage, while considering sugar percentage:

=([@winpercent]/[@pricepercent]) + (1/[@sugarpercent])

In this formula:

• The first part ([@winpercent]/[@pricepercent]) prioritizes candies that have high popularity and low prices.

• The second part (1/[@sugarpercent]) adds a small penalty for higher sugar content, ensuring that candies with lower sugar percentages are slightly favored.

This calculation was not quite what I was looking to accomplish, so I chucked it.

D. A lot of filtering and sorting. To extract the candies based on overall performance, I used Excel’s filtering capabilities:

1. I sorted the calculated scores from highest to lowest.

2. I applied a filter to only display the candies without chocolate, peanuty/almondy, nougat, etc. Looked for other varieties that were in the higher win percentage, but also needed to balance the budget.

3. Visualizing the Results:

-PivotTables:

To further analyze and present the data, I used PivotTables to compare different attributes like win percentage, price percentage, and sugar percentage across all candies. I was able to easily switch between different metrics and quickly identify trends using the PivotTable.

For example, the PivotTable displayed the average win percentage, price percentage, and sugar percentage for the top-performing candies, allowing me to make data-driven decisions.

-Interactive Dashboard:

To make the analysis more user-friendly and interactive, I created an Excel dashboard with:

• Slicers: To filter candies by characteristics like chocolate, fruity, or hard candy.

• PivotCharts: To connect to the slicer to show each candy and the rank in each category, additional information about the candy shows as well.

• Donut chart: to display what coatings, fillings, etc. are in the candies selected on the slicer.

• My top 3 picks

• The top 10 picks by popularity

This dashboard made it easier to explore the dataset and instantly see the top choices based on various criteria.

Conclusion:

Using Excel, I was able to efficiently analyze the candy dataset to select the best three candies for Halloween: Reese’s Miniatures, Sour Patch Kids, and Nerds. The analysis prioritized popularity, variety, and affordability, while also taking into account the different textures and flavors preferred by trick-or-treaters. By combining Excel’s formulas, PivotTables, and visualization tools, I was able to make informed decisions and present the data in a clear, interactive format.

This data-driven approach ensures that my candy selection will be a hit with trick-or-treaters, helping me become the most popular house on the block this Halloween.