Projects in Microsoft Excel
Sales Dashboard
Context
This project aims to solve the lack of visibility regarding commercial performance and product profitability. The objective was to transform raw transactional data into a dynamic Business Intelligence (BI) tool, allowing for monitoring the gap between targets and revenues, as well as identifying sales volatility by consultant and category.
Data source
Fictitious (simulated) transactional database containing sales records from 2019 to 2021. The dataset includes information on salespeople, customers, regions, products, and unit values.
Data preparation
Data Cleaning: Performed using Power Query and advanced formulas to correct inconsistent dates, handle null values, and standardize text formats. Calculated "Month" and "Year" columns were created to enable time series analysis.
Tool used
Technology: Developed entirely in Microsoft Excel, using Power Query for ETL, Pivot Tables for modeling, and native visualization elements for the final dashboard.
Conclusion
Analysis of Target vs. Revenue: Identification of negative seasonality in the months of February and April, where revenue fell below the established target.
Performance by Product: It was found that VBA (R$ 136,800.00) and Python (R$ 115,650.00) are the main pillars of revenue for the period.
Salesperson Ranking: Gaya stands out as the Top Performer, reaching a total volume of R$ 112,200.00.
Target Gap: Identification of specific salespeople, such as Marcello, who show the greatest percentage deviation from the annual targets.
Recommended actions
1. Seasonality Adjustment: Implement incentive and cross-selling campaigns during low-demand months (Q1) to balance the annual revenue curve.
2. Targeted Training: Empower underperforming salespeople in high-margin products (such as Power BI), using the techniques of sales leaders as a reference.
3. Focus on Volatile Products: Create retention strategies for products with the greatest sales fluctuations, aiming to stabilize recurring revenue.
4. Management by Indicators: Use the dashboard for individual coaching meetings, focusing on the recovery of salespeople with the largest target gaps.
5. Product Mix Optimization: Encourage the combined sale of core products (Excel/Power BI) with specific niches (VBA/Python) to increase the average ticket size.
Offshore Safety Management
Context
This project aims to analyze workplace accidents in an offshore production company, using simulated data with realistic characteristics, in order to identify patterns, risks, and opportunities for prevention.
Data source
Fictitious (simulated) database, built for study and portfolio purposes, based on real patterns of workplace accidents in the offshore sector.
Data preparation
Correcting inconsistencies in records to ensure the integrity of the analysis.
Tool used
Developed in Microsoft Excel.
Conclusion
-
Critical Vulnerability: Identification of a high incidence of accidents due to inadequate use of PPE.
-
Temporal Analysis: Discovery of seasonal variations that impact preventive planning.
-
Risk Sectors: Mapping of specific areas with a higher frequency of serious incidents.
-
Operational Impact: Productivity Metrics.
Recommended actions
1. Focus on the most critical sectors: Implement training and safety reinforcement in the sectors that recorded the highest number of accidents, with constant monitoring of the results.
2. Awareness campaigns on the use of PPE: Intensify communication and monitoring of the correct use of PPE, aiming to reduce serious accidents associated with its non-use.
3. Continuous analysis of seasonality: Establish monthly monitoring of accidents to anticipate periods of higher risk and plan specific preventive actions.
4. Improvement of work processes: Review and adapt operational procedures in activities associated with the most frequent and serious types of accidents, to reduce risks.
5. Investment in occupational health: Develop programs to monitor employees on leave, aiming for a safe return and reduction of time off.
These actions, based on data analysis, can contribute to the reduction of accidents, improvement of safety in the offshore environment, and optimization of company resources.
Project phases
A. Operational Database: Initial dataset containing offshore incident records. The processing phase focused on correcting record numbering and standardizing risk categories, eliminating noise that could compromise the accuracy of safety indicators.
B. Clean Data Processing: In this step, I applied data processing via Power Query to ensure the accuracy of the risk analysis. The process included removing duplicates, standardizing accident categories, and rigorously formatting date and severity fields, structuring a solid foundation for generating safety KPIs.
C. Analysis (Pivot Tables): I used Pivot Tables to model the safety data and extract critical operational metrics. In this step, the data was aggregated to calculate the accident rate by sector, the severity of occurrences (Days Off Time), and the correlation between the use of PPE and the incident rate. This modeling was essential to structure the indicators that feed the final dashboard.
D. Dashboard (Final Visualization): The dashboard was developed as a decision support tool, prioritizing visual clarity regarding critical risk indicators. I used bar charts for accident frequency, severity indicators, and segmenters by unit/sector. This interface allows management to instantly identify hazard areas and direct training and resources to prevent new occurrences.
This dashboard provides a 360º view of operational safety, transforming raw data into strategic decisions for reducing offshore accidents.