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.
Project Objective
The central objective was to develop a sales monitoring solution that would allow the transition from a static view to results-oriented management. The main focus was to create an environment where leadership could identify, in real time, which consultants are meeting their targets, the profitability per product line (such as VBA and Python), and where the seasonal bottlenecks affecting the company's annual revenue are located.
Key Analytics
Performance Target vs. Actual: Monthly comparative monitoring between established targets and generated revenue, highlighting performance fluctuations throughout the year.
Product Category Analysis: Detailed revenue identification by software/language, allowing visualization of the sales force of products such as Excel, Power BI, Python, and VBA.
Consultant Ranking (Top Performers): Consolidated visualization of revenue per salesperson, accompanied by pie charts showing the individual contribution of each to the overall result.
Salesperson vs. Product Matrix: Data cross-referencing to identify specialists in each tool, assisting in the strategic allocation of sales and training efforts.
Historical and Seasonal Analysis: Dynamic filters by year (2019, 2020, 2021) to understand sales behavior and revenue evolution in the long term.
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.
Project highlights
Modern and Clean Visuals: The project uses a dark theme (Dark Mode) with high contrast, which facilitates quick reading of metrics and gives the report a much more professional and organized look.
Intuitive Navigation: Implementation of data segmentation menus that function as interactive filters, allowing for quick transitions between different time views and categories.
Integrated Layout: The use of grouped and aligned elements ensures that charts and cards function as a single piece, avoiding the visual clutter typical of common spreadsheets.
Conclusion
Analysis of Target vs. Revenue: Identification of negative seasonality in the months of February and April, where revenue fell below the established target.
Product Performance: 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 annual targets.
Recommended actions
Seasonality Adjustment: Implement incentive and cross-selling campaigns during low-demand months (Q1) to balance the annual revenue curve.
Targeted Training: Empower underperforming salespeople in high-margin products (such as Power BI), using the techniques of sales leaders as a reference.
Focus on Volatile Products: Create retention strategies for products with the greatest sales fluctuations, aiming to stabilize recurring revenue.
Indicator Management: Use the dashboard for individual coaching meetings, focusing on the recovery of salespeople with the largest target gaps.
Product Mix Optimization: Encourage the combined sale of core products (Excel/Power BI) with specific niches (VBA/Python) to increase the average ticket size.
Data source
A fictitious (simulated) transactional database, developed to represent a real-world scenario of commercial operations. The dataset comprises sales records from 2019 to 2021, containing detailed information on consultants, product categories (Excel, Power BI, Python, VBA), clients, regions, and unit value metrics versus established targets.
Book Inventory
Context
This project arose from the need to organize and catalog a personal collection of over 300 literary works. The lack of a centralized record made it difficult to control investments, identify gaps by literary genre, and track reading status. The goal was to transform a static list of books into a dynamic and visual collection management tool.
Project Objective
The central objective was to develop a Business Intelligence solution applied to the management of personal libraries. The main focus was to create an environment where the user could monitor, in real time, the total value invested in the collection, the average quality of the works (through evaluations), the historical growth of the collection, and the demographic distribution of the books (languages and categories).
Key Analytics
Inventory and Investment Management: Monitoring of the total volume of books and the financial capital allocated to the collection, including average investment metrics per work.
Distribution by Genre and Language: Identification of the predominance of genres (Romance and Spiritualism) and the linguistic diversity of the collection, highlighting works in Portuguese and English.
Quality Ranking (Best Rated): Consolidated visualization of the works with the highest assigned ratings, allowing for quick identification of the "Shelf Elite".
Historical and Seasonal Analysis: Line graph demonstrating the evolution of acquisitions over the decades, allowing for understanding of the peaks in collection growth (especially between 2010 and 2014).
Reading Status: Donut chart to track reading progress, differentiating between books read, in progress, and unread.
Tool used
Technology: Developed entirely in Microsoft Excel, using Power Query for data cleaning and structuring (ETL), PivotTables for modeling metrics, and native visualization elements (Shapes, Cards, and Charts) for the final interface.
Project highlights
The project utilizes a UI/UX design focused on "Dark Mode" dashboards, optimizing readability and visual hierarchy. Furthermore, it implements object grouping and grid alignment techniques to ensure layout integrity and ease of navigation.
Conclusion
Collection Profile: It was found that the "Novel" genre is the main pillar of the collection, representing almost 40% of the works.
Investment: The collection has a market value exceeding R$ 11,000.00, with rare works (such as Eustáquio) reaching significant individual values.
Reading Progress: The dashboard indicates that approximately 48% of the collection has already been completed, signaling an opportunity to focus on works with "Unread" status.
Longevity: The presence of classic works (such as The Divine Comedy) demonstrates the historical depth of the collection.
Recommended actions
Prioritization of Reading: Focus on works in the "Best Rated" section that are still listed as "Unread" or "Reading".
Genre Balance: Consider diversification into less represented categories (such as Short Stories and Children's Books) to balance the library's mix.
Acquisition Control: Use the "Average Investment" metric to evaluate future purchases and maintain the growth of the collection within a sustainable range.
Preservation of Rare Books: Give special attention to the conservation of the "Top 5 most expensive books" identified in the dashboard.
Data source
Customized database containing detailed records of 302 works, including columns for Author, Genre, Language, Year of Publication, Purchase Price, Reading Status, and Personal Rating (Score).