Refocus Special Project: Food Story Dashboard with Microsoft Power BI
View interactive dashboard: https://www.novypro.com/project/food-story-dashboard-with-microsoft-power-bi-power-bi
The Food Story Dashboard project aimed to address the challenges faced by the Food Story management team regarding the limited understanding of the company’s performance and the lack of interactive data analysis tools. The project involved creating an interactive performance analysis dashboard using Microsoft Power BI. The dashboard allowed for comprehensive data collection from various relevant sources, data cleaning and preparation, exploration and visualization, and deriving valuable insights and recommendations. Through this project, the management team gained access to a visually appealing and user-friendly dashboard that provided relevant information on sales, orders, expenses, and other factors influencing the success of Food Story. The project’s objectives were to empower the management team with effective decision-making tools based on factual and available data. The dashboard enabled smarter decision-making, improved operational efficiency, and the formulation of appropriate strategies for the future growth and success of Food Story.
Introduction
The challenge faced was the limited understanding of Food Story’s performance by the management team. Without deep insights into the company’s performance, the management team struggled to make informed decisions for the future of Food Story. Additionally, the lack of visualization and interactive dashboards for data analysis made the monitoring and evaluation process inefficient. Without proper tools, the management team found it difficult to visually and comprehensively analyze sales, orders, expenses, and other factors affecting the success of Food Story.
Objective
The objective of this project is to address the aforementioned challenges by creating an interactive performance analysis dashboard using Power BI. Through the creation of this dashboard, data will be collected from various relevant sources and analyzed comprehensively. With clear and easily understandable visualizations, this dashboard will provide relevant information to the management team, enabling them to better visualize and understand Food Story’s performance. The ultimate goal is to provide the management team with an effective decision-making tool based on available facts and data. The dashboard aims to help the management team make smarter decisions, improve operational efficiency, and formulate appropriate strategies for the future growth and success of Food Story.
Data Sources
The data for this project is sourced from Food Story Management.
Data Cleaning and Preparation
- Duplicates were removed, blank rows were eliminated, and outliers were identified using the Z-score method.
- A relationship model was created to establish data connections.
- A new column was added to extract the hour from the sales timestamp.
- Discount categories were organized and cleaned.
- Sales day was extracted and classified into weekday or weekend.
Data Exploration and Visualization
- Total bill and grand total were calculated by month and year and visualized using line and stacked column charts.
- Total bill by discount and no discount was classified and visualized using a donut chart.
- The discount category with the highest grand total was determined using a clustered bar chart.
- The busiest order times were identified by extracting the sales hour and visualized with a clustered column chart.
- The grand total for weekdays and weekends was compared using a donut chart.
- The branch contributing the most to the grand total was visualized using a clustered column chart.
Insights
- Based on the data, the total sales order at the customer level is 100,935 with a grand total of IDR 9,306,309,649.
- In terms of monthly performance, the highest revenue was achieved in March 2022, amounting to IDR 1,077,792,631, with a total of 5,940 bills. The highest number of bills occurred in August 2022, reaching 7,978 bills, with a revenue of IDR 532,131,466. Conversely, the lowest total bill and grand total were recorded in January 2021, with 945 bills and IDR 43,404,680, respectively, likely due to the business’s initial opening phase.
- From the comparison between orders with and without promotions, it was found that 97.9% of orders were made without promotions, accounting for 74,153 orders, while the remaining 2.1% (1,593 orders) involved promotions.
- The discount category that contributed the most was the 20% discount category, generating a grand total of IDR 53,792,680.
- The busiest order time was around 12 noon, with a total of 223 orders, followed by 11 am and 1 pm, which are closely related to lunchtime. The number of orders also increased during the evening hours of 5 pm to 7 pm, which corresponds to dinner time.
- Based on the data obtained, 54.3% of the grand total was generated on weekdays, amounting to IDR 56,896,016, while the remaining 45.7% (IDR 47,881,167) was generated on weekends.
- The branch that contributed the highest to the grand total was FoodStory Sawah Besar, with a total revenue of IDR 50,615,163.
Recommendations
- Promotional Strategies: Since a majority of the total orders (97.9%) were made without promotions, there is an opportunity to increase revenue by implementing targeted promotional strategies. Consider offering attractive discounts or special offers to incentivize customers to make more purchases. Analyze the successful promotion categories and replicate their effectiveness in driving sales.
- Capitalize on Peak Months: March 2022 was the month with the highest income, while August 2022 had the highest total bills. Identify the factors that contributed to the success in these months and replicate them in other periods. Create targeted marketing campaigns during peak months to attract more customers and increase revenue.
- Enhance Weekday Revenue: Although the weekends accounted for 45.7% of the total revenue, focus on maximizing revenue on weekdays. Implement weekday-specific promotions or loyalty programs to encourage customers to choose your business over competitors during weekdays. Consider partnering with local businesses or offices to offer exclusive deals to their employees during lunch breaks or after-work hours.
- Optimize Busy Hours: The busiest time for orders was around lunchtime and dinner time. Ensure that you have adequate staffing and resources during these peak hours to provide efficient service and prevent delays. Implement strategies such as pre-ordering, online reservations, or special offers for customers who order during non-peak hours to distribute the demand evenly throughout the day.
- Branch Expansion and Replication: FoodStory Sawah Besar branch contributed significantly to the overall revenue. Consider replicating the successful strategies implemented at this branch in other locations. Conduct market research to identify potential areas for branch expansion and evaluate the feasibility of opening new branches in strategic locations to reach a wider customer base.
Visit my portfolio: https://dataexplorewithyani.my.canva.site/
BI portfolio: https://www.novypro.com/profile_projects/trihandayani
LinkedIn profile: http://www.linkedin.com/in/tri-handayani007