Pareto Analysis of Coffee Blend Transactions Using Excel | Realcode4you

Task 1 - Pareto Analysis

The Pareto analysis of coffee blend transactions shows that a small number of coffee blends contribute to the majority of total sales volume. The most frequently sold coffee blends are Americano with Milk (809 transactions) and Latte (757 transactions), followed by Americano (564 transactions) and Cappuccino (486 transactions).

From the cumulative percentage line, it can be observed that the top four coffee blends (Americano with Milk, Latte, Americano and Cappuccino) together contribute to approximately 75-76% of total transactions. When Cortado is included, the cumulative percentage exceeds the 80% threshold, reaching approximately 84-85%.

This indicates that around 5 out of 8 coffee blends account for over 80% of total sales, which supports the Pareto principle (80/20 rule). The remaining blends-Hot Chocolate, Cocoa and Espresso-contribute relatively less to overall transaction volume.

Business Insight:

• The business should focus on maintaining quality and availability of the top-performing blends (especially Americano with Milk and Latte).

• Promotions, pricing strategies and inventory planning should prioritize these high-demand items.

Lower-performing products may require marketing efforts or reconsideration depending on profitability.

Task 2(a): Coffee Blend Revenue Analysis

We performed a PivotTable analysis to evaluate the total sales revenue generated by each coffee blend. Using the dataset, we aggregated the sum of revenue (money) for each coffee type and plotted the results using a column chart.

From the analysis, we observed that Latte generated the highest total revenue (26,875.30), followed closely by Americano with Milk (24,751.12). Other strong contributors include Cappuccino (17,439.14) and Americano (14,650.26). On the lower end, Espresso generated the least revenue (2,690.28) among all coffee blends.

The visualization clearly shows that a few coffee blends contribute significantly more revenue compared to others. This suggests that customer preference is heavily skewed toward popular items such as Latte and Americano-based drinks.

Insight: We can conclude that focusing on high-revenue products such as Latte and Americano with Milk can maximize profitability. At the same time, low-performing products like Espresso may require promotional strategies or pricing adjustments to improve their contribution to overall revenue.

Task 2(b): Sales Volume by Day of Week

We conducted a PivotTable analysis to determine the distribution of coffee transactions across different days of the week. We calculated the count of transactions (coffee_name) and plotted the results using a column chart.

From the chart, we observed that Tuesday recorded the highest number of transactions (572), followed by Monday (544) and Friday (532). On the other hand, Sunday had the lowest sales volume (419), followed by Saturday (470).

The results indicate that weekdays generally experience higher sales compared to weekends. This suggests that customers are more likely to purchase coffee during working days, possibly due to routine consumption habits such as morning or work-related coffee breaks.

Insight: The business can optimize staffing and inventory during peak days such as Tuesday and Monday. Additionally, marketing strategies or special promotions could be introduced during weekends to increase customer traffic and boost sales.

Task 2(c): Monthly Sales Volume Analysis

We performed a PivotTable analysis to understand how coffee sales are distributed across different months. The total number of transactions was calculated for each month and visualized using a line chart to highlight trends over time.

From the analysis, we found that March recorded the highest number of transactions (494), followed by October (426) and February (423). In contrast, April had the lowest sales volume (168), followed by January (201) and June (223).

The line chart indicates noticeable fluctuations in sales across different months, suggesting possible seasonal trends or variations in customer behavior. Some months show strong performance, while others experience a decline in transaction volume.

Insight: The business can investigate the reasons behind high-performing months such as March and October to replicate similar strategies in lower-performing months. Seasonal promotions, discounts, or targeted campaigns may help improve sales during weaker months like April.

Hire our excel experts to get help in your business analytics projects using excel. Here you will get high quality work with proper explaination without any Plagiarism issues.

You will also get help using Tableau, Power Bi, Rapid Minner etc. business analytics tools.

For more details contact us:

realcode4you@gmail.com