I published dashboards on Power BI services so anyone can open and interact with F&B Sales & Operations Performance Management
Alternatively, you can interact with the dashboard directly below.
Project Title: F&B Sales & Operations Performance Management
My Role: Data Analyst / Operation Analyst
Project Goal:
The primary goal of this project is to create a dynamic, truth analytics dashboard for the Supply Chain and Operations departments. Aims to transform raw daily sales data into actionable insights, enabling key stakeholders to:
Improve Forecast Accuracy: Identify historical sales trends, seasonality, and product performance (Revenue vs. Volume) to enhance future demand predictions.
Optimize Operational Efficiency: Provide visibility into total units sold, order count, and sales channel contribution to better plan inventory, production, and logistics capacity.
Drive Commercial Strategy: Evaluate the performance of individual salespersons, managers, and product groups to optimize resource allocation and commercial effort.
The company was facing a significant challenge rooted in inconsistent data visibility across its Commercial, Supply Chain, and Operations teams. This absence of a unified data source led directly to two major, interconnected issues.
Firstly, relying on disparate daily sales reports and manual spreadsheets hindered the ability to accurately capture historical seasonality and market trends. This deficiency directly resulted in inaccurate demand forecasting, causing both frequent stockouts of essential, high-revenue items and excess inventory for lower-priority products.
Secondly, management lacked a cohesive, real-time view to effectively align sales performance with operational capacity. Specifically, it was difficult to quickly connect a salesperson's performance metrics (e.g., low volume) to specific product mix issues or to determine which sales channels required immediate resource and logistical adjustment.
The core objective of this project was to establish a single, trusted source of truth that would transition the organization from reactive reporting to proactive decision-making. To achieve this, the dashboard needed to answer several critical business questions:
For Supply Chain: How does the current year's sales trend compare to the previous years' seasonality, and what is the relationship between sales value (Revenue) and sales volume (Quantity)?
For Operations: What are the primary sources of demand, specifically which sales channels are driving the highest order count, to ensure adequate capacity and optimized logistics?
For Commercial Management: Who are the top performers in terms of both total revenue generation and maintaining a high average unit price, and how is the revenue split across different product groups and managers?
For Inventory Strategy: Which products should be prioritized in the forecast to minimize inventory holding costs while maximizing commercial return?
Source Data: The raw transactional data was sourced primarily from flat CSV files (containing sales, quantity, and product details)
Modeling Strategy: I organized the raw data by linking different tables together to ensure accuracy across all filters. This involved establishing clear one-to-many relationships between our transactional data and the tables that provide context, effectively managing the flow of information
Interactive Dashboard: Interactive Power BI Dashboard acts as a unified analytical tool for Sales Management, Supply Chain Management, and Operations, allowing users to instantly filter and assess the impact of an individual salesperson on the company's overall commercial performance.
Visual Strategy: The dashboard follows a clear, top-down structure:
KPIs (Right): The top right section is dedicated to high-level KPI Cards such as Total Revenue ($17.91M), Total Units Sold ($6M), and Average Order Value ($68.86), providing immediate context of the selected period.
Filters (Left & Top): The large Salesperson selector and the date/channel/supervisor filters enable granular analysis of any team member or time frame.
Trend Focus (Center): The core of the report is the Year-over-Year (YoY) Revenue Comparison chart, which is crucial for identifying seasonal patterns and growth rates.
The comprehensive analysis enabled by this dashboard immediately highlighted several key strategic insights for the business:
Dual Performance Gap: We identified a clear distinction in performance using the Product Performance Matrix (Revenue vs. Volume). A small group of products drives high revenue but low volume (high-value items), while the majority drives low revenue but moderate volume. This confirms the need for two distinct forecasting strategies: one focusing on value accuracy and the other on volume stability.
Supervisor Contribution: The Revenue by Supervisor chart revealed a significant concentration of performance, with Diego A. and Diogo C. accounting for the overwhelming majority of revenue contribution. This strongly suggests that training and resource allocation should be prioritized for their teams, but also highlights a single point of failure risk if their performance declines.
Channel Strategy Misalignment: While the Retail channel generates the largest absolute revenue share, the Distributor and Online channels show significant growth potential. The detailed Revenue by Channel & Product Category chart showed that certain categories (like 'Food' and 'Drink') are disproportionately dependent on the Retail channel, necessitating a focused strategy to diversify the product mix across the Online channel to ensure future growth.
Salesperson Revenue Focus: By viewing the metrics for individual sellers (e.g., Carla Ferreira), we could confirm her status as the Top Salesperson, but also identify her primary revenue drivers (Product Group and Product Category), allowing management to set more targeted goals for her to push specific strategic products.
The implementation of the F&B Sales & Operations Performance Management Dashboard delivered immediate and measurable value directly related to strategic planning:
Operational Planning Improvement: Real-time visibility into the Monthly Sales & Volume Performance trend allows the Operations team to now proactively anticipate volume peaks (seasonality). This capability directly supports efforts to reduce unplanned overtime and minimize costly external warehousing needs during peak seasons.
Sales Strategy Focus: The ability to instantly assess Supervisor Revenue Contribution and the Product Performance Matrix provides management with the necessary data to allocate resources effectively. This targeted data clarity enables a strategy to shift focus to high-value product groups, thereby improving overall sales margin potential.
1.Integrate Demand Forecast Data for Accuracy:
Action: Integrate official budget or projected demand figures to calculate the essential Forecast Accuracy (MAPE).
Target: The immediate goal is to establish a baseline MAPE of 20% or lower, as this is a common, acceptable benchmark for basic forecasting environments. Future efforts will aim to push this below 15%.
Rationale: Without measuring forecast vs. actuals, Demand Planning lacks the core metric to drive improvement and minimize the costs associated with forecast errors.
2.SKU Portfolio Management & Complexity Control:
Action: Implement a Product Churn Analysis feature to track the stability and complexity of the number of active products (SKUs) over time.
Threshold: The system will flag an alert if the count of active SKUs increases by more than 5% Month-over-Month (MoM).
Rationale: A rapid surge in SKU count (over 5%) directly signals increasing complexity for the Operations team, leading to higher inventory holding costs, increased warehouse management effort, and potential inventory errors.
3.Sales Team Capacity & Load Balancing:
Action: Implement a feature to track the average Order Count per Salesperson and the Average Unit Price (AUP) dispersion within each Supervisor's team.
Threshold: The system will flag a warning if the team's average Order Count increases by more than 10% MoM without a corresponding increase in the number of active salespeople, or if the AUP dispersion (standard deviation) exceeds 15%.
Rationale: A sudden 10% increase in order count MoM signals potential sales fatigue and burnout risk, while high AUP dispersion (>15%) within a team suggests inconsistent pricing or discounting behavior, requiring immediate managerial coaching to ensure uniform margin protection across all sellers.
4.Logistical Capacity Modeling:
Action: Utilize the Total Units Sold and Total Order Count data to model logistical capacity requirements.
Target: The model will aim to predict resource needs with a 95% confidence level based on forecasted volume.
Rationale: Linking the sales forecast directly to resource needs helps the Operations team optimize staffing and minimize transportation costs by avoiding last-minute, high-cost capacity sourcing.