FOOD AND BEVERAGE SALES ANALYSIS USING POWER BI.
Hey Datafam,
I am thrilled to share my latest data analysis project with you. ENJOY!!
This project is focused on analyzing sales data and providing insights for a fictitious e-commerce (Food and Beverage)Company using the Power Bi tool to clean, transform and visualize its dataset.
Case study: Lilo Foods and Beverages provides its customers with a range of services, including sales of food products and drinks. As part of its commitment to improving customer experience and optimizing its operations. Lilo Foods and Beverages have engaged me as an external consultant to conduct a data analytics project to analyze its sales data and provide a report/dashboard that provides insights into the business questions.
Business Questions: Lilo Foods and Beverages wants to answer several business questions which include:
- What is the total revenue generated?
- What is the total number of orders placed?
- What is the Average ticket price?
- What are the Top 10 product groups that generated the most revenue?
- Top 3 salespersons by orders and by revenue?
- Total orders by product category?
- Monthly distribution of revenue and orders?
- Distribution of revenue by channel?
- Total revenue by product category?
- The quarterly trend of revenue and orders?
You can download the practice dataset Here
The dataset provided has one fact table(sales data)which contains all the quantitative information and also has 260,097 rows and 11 columns and a dimension table (product data) which stores attributes that describe the objects in a fact table and has 798 rows and 4 columns. Now let’s bring in our data. Let’s start with ‘Sales-data’.
Click on “Get data” to import the Excel file.
Next, click on “Sheet 1” to select the table to import. To get this data into power bi we can either choose “Load” or “Transform Data”. When you load data you can use the data directly as you import on power bi desktop. If you transform you can modify the data. So because I want to modify my data I will click on “Transform Data”.
Now that we have the data in the power query editor, I renamed “Sheet1” as “ Sales”. Looking at the dataset it also looks okay but to be sure, I checked the column quality to confirm if there are any empty cells or errors and I also checked the data type of each column and everything seemed fine. It’s time to close and apply my data to my power bi desktop for further analysis.
The same process of importing and transforming data was done for the “Product data”.
Next, I created a new table called “DAX” for my new measures.
Going through the business questions again I realized I will need to calculate the Total Revenue which is the total amount of income the company brings in from selling its products, ATP (Average ticket price) which is the metric that provides details on the average amount spent per customer per visit, Total orders which is the summation of all completed individual purchase transactions.
Now to calculate total revenue which is the “sum of sales amount”, I will be adding a new column that will contain the sales amount which is “quantity * unit-price” into the sales data.
Back to my report view, I will start calculating my measures and adding them to the DAX table I created. First on the list is TOTAL REVENUE, The Dax measure I used is :
Next is TOTAL ORDERS:
AVERAGE TICKET PRICE (ATP):
Lilo Food and Beverage company wants to know the total revenue generated, the total number of orders placed, and the average ticket price.
Insights : From the card chart above we can see that a total of $18m was made in revenue from a total of 52.6 thousand orders and the average amount a customer was willing to spend on a product was $340.7.
Lilo Food and Beverages wants to also know the Top 10 product groups that generated the most revenue.
Insights : The Bar chart above shows wheat flour to be the highest revenue-generating product group that generated a revenue of $4,473,241 alongside other product groups like oil, yeasts, flour, liquor, candy, manioc flour, spices, sugar, and popcorn.
Recommendation : According to the analysis above I will recommend the company allocate additional resources and attention to these 10 product groups since they are the highest revenue-generating product groups.It could be by exploring new market segments, expanding distribution channels to maximize its potential, and engaging with customers through surveys or social media platforms to gather insights that can guide product development, marketing strategies, and customer retention efforts. Building strong customer relationships can lead to repeat business and positive word-of-mouth, driving revenue growth.
After seeing the top 10 revenue-generating product groups I decided to show the bottom 10 product groups.
The bar chart showed that tomato sauce didn’t generate any revenue at all and it also shows other product groups with low revenues. To avoid just scrapping these product groups it is important to determine what is working for the top-performing product group. From the previous analysis I think the quality of these products should be reviewed and also it is important for LILO to do a customer survey to determine exactly what the customers prefer or maybe investigate trends in the market it is also possible these products are outdated.
Also, as a data analyst I had more questions like; Are the revenue generating products actually bringing in enough profit into the company? and because I didn’t have enough data like the cost price of the products to conduct a deeper analysis I was unable to get an answer to this question. Moving forward I will reach out to the management team for more data to answer this questions but for now I will focus on providing answers to the management business questions.
Next, Is a chart showing the Top 3 salespersons by orders and by revenue.
Insights : From the Barcharts above it shows that Julio Lima, Carla Ferreira, and Gustavo Gomes were the 3 top salespersons by Orders and Revenue.
Recommendation : With this insight, the Lilo management team can recognize and reward the top 3 salespersons for their exceptional performance. This can motivate them to continue their high level of sales and inspire other sales team members. Implementing an incentive program, such as bonuses, commissions, or special rewards, can further encourage healthy competition and drive sales growth.
Also, the team can Organize knowledge-sharing sessions or mentorship programs where the top 3 salespersons can share their strategies, techniques, and best practices with the rest of the sales team. This helps disseminate successful approaches and uplifts the overall sales capabilities of the team.
Total orders by product category?
Insight : The Donut chart shows that the Food category(47,414 orders,90.21%)had more orders than the Drink category(5,146 orders,9.79%).
Total revenue by product category?
Insights : The Donut chart above shows that the Food category generated a total revenue of $16,366,812 (91.39%) while the Drink category generated a total revenue of $1,542,421(8.61%)
Distribution of revenue by channel?
Insight: The Donut chart above shows that the retail channel is generating the highest revenue at 48.56% , the distributor channel at 34.05%, and the online channel at 17.39%.
Recommendation : Since the retail channel is generating the highest revenue at 48.5%, it is important for Lilo Food and Beverages to continue strengthening its presence and performance in this channel. This can involve improving relationships with retail partners, optimizing product placement and visibility on store shelves, and conducting promotional activities to drive sales.
While the distributor channel accounts for 34.05% of the revenue, there is still room for growth. The data suggest that increasing the number of distributors or expanding into new regions can help capture additional market share and boost revenue. Conduct market research to identify potential distributor partnerships and develop strategies to enhance distribution efficiency.
Although the online channel represents 17.39% of the revenue, it is a growing segment in the food and beverages industry. It is crucial for Lilo Food and Beverages to invest in its online presence, optimize its e-commerce platforms, and provide a seamless customer experience. This can include improving website design, implementing effective digital marketing campaigns, and offering convenient online purchasing options.
Monthly distribution of revenue and orders?
Insight : The chart above shows that October was the month with the highest revenue and highest orders.
Recommendation : With this information, Lilo Food and Beverage can identify the factors that contributed to the high revenue and orders in October. Analyze marketing campaigns, promotions, seasonal trends, or any other factors that may have driven customer engagement during that month. Use this information to replicate successful strategies in other months or replicate successful campaigns during similar periods to boost revenue and orders.
Also, they can analyze customer behavior during October to understand their preferences and motivations. Identify what products or promotions attracted customers during that time and adapt marketing strategies to cater to those preferences in other months. This can include offering seasonal discounts, limited-time promotions, or exclusive products to encourage higher customer engagement and boost revenue and orders.
The quarterly trend of revenue and orders?
Insight: The Barchart shows the 4th quartile generated the highest revenue.
Assessing revenue by quarter allows the company to evaluate its financial performance over specific time periods. It helps identify trends, patterns, and fluctuations in revenue generation. By comparing revenue across different quarters, the company can determine if its sales are consistently growing, stabilizing, or facing challenges.
Conclusion
- According to the analysis above it shows that lilo food and beverages sales are consistently growing by each quartile.
- The Food category had the highest orders placed and also generated the highest revenue in the product category.
After answering all the business questions I created a clear and interactive dashboard where the Lilo Food and Beverages management team can interact with the report.
All the data can be visualized at a single glance on this dashboard.
Thank you so much for reading through I would really appreciate if you would Drop your Comments, Suggestions and Recommendations, always looking to Improve, Learn and Grow.
Until Next Time,
BYE.
