Forecasting Beverage Sales in Kota Kinabalu During the Chinese New Year Season

Executive Summary

This case study analyzes the beverage sales performance across different store locations during key events, such as Chinese New Year, using both descriptive and predictive analytics. The analysis revealed that certain beverages, particularly Spritzer 1.5L, consistently perform well during high-demand periods. By employing a linear regression model, we can forecast future sales and adjust inventory and promotional strategies to meet anticipated demand spikes. The predictive model was evaluated, resulting in an average prediction error (RMSE) of 276.51, demonstrating reasonable accuracy for forecasting sales.

Problem Statement

Retail businesses often face challenges in predicting sales performance during seasonal events, leading to missed opportunities in inventory optimization and promotional strategies. This study aims to predict future beverage sales during significant events, such as Chinese New Year, to help retailers make informed decisions on stocking and marketing.

Approach

Dataset Overview: The dataset includes detailed transaction-level data covering sales during promotional periods, major festivals, and across multiple store locations.

Study Framework: Using SQL for business intelligence, key metrics such as total sales, top-performing products, and promotional impacts were identified. A linear regression model was built to predict future sales based on factors like promotions, day of the week, and seasonal events.

Descriptive and Predictive Analytics: Historical data was used to build the linear regression model, which was evaluated using RMSE to ensure its effectiveness in forecasting future sales.

Results

Top-Performing Products and Store Locations

Spritzer 1.5L: Highest total sales in Kota Kinabalu during Chinese New Year, with 358 units sold, generating $2,737.32.

Pepsi 330ml: Second-highest in sales, generating $1,307.75 from 330 units sold.

Key Store Locations: Kota Kinabalu, George Town, and Johor Bahru were the top-performing locations, with total sales of $52,178.38, $55,781.62, and $53,551.06, respectively.

Predictive Model Performance

RMSE (Root Mean Squared Error): The model achieved an RMSE of 276.51, indicating that the average prediction error for sales is approximately $276.51.

Key Predictors: The model identified promotions, major festivals, and specific store locations as significant factors impacting sales performance.

Insights on Sales Trends

Sales during major festivals such as Chinese New Year saw significant spikes, particularly for beverages like Spritzer and Milo.

Coca-Cola 330ml: Consistently underperformed across all store locations, suggesting a need for a revised marketing strategy.

Visualization

Explore the complete interactive visualization here:

Key Insights

• Sales Trends During Festive Periods: Spritzer 1.5L and Milo 1L consistently perform well during high-demand periods like Chinese New Year. Retailers should focus their inventory and promotions on these products.
• Predictive Model Accuracy: The model’s RMSE of 276.51 suggests it can reasonably forecast future sales, allowing businesses to anticipate demand and adjust accordingly.
• Importance of Promotions: Promotional periods significantly influence sales, making them a crucial factor in planning marketing strategies.

Recommendations

• Focus on High-Performing Products: Increase stock and marketing efforts for top-selling beverages like Spritzer 1.5L and Milo 1L during Chinese New Year and other key events.
• Optimize Promotions Based on Predictions: Use the predictive model to identify which products are likely to experience sales spikes and tailor promotions accordingly to maximize revenue.
• Revise Strategy for Low-Performing Products: Consider revising marketing strategies for low-performing products like Coca-Cola 330ml to increase visibility and sales during peak periods.

Conclusion

By leveraging descriptive and predictive analytics, businesses can make data-driven decisions to optimize inventory, promotions, and staffing during high-demand periods. The predictive model’s accuracy allows retailers to confidently plan for future sales, ensuring they have the right products in stock and maximizing revenue potential during events like Chinese New Year.