Projects in Microsoft Excel
Tech Sales
Problem
The company faced a critical lack of visibility into commercial performance and product profitability. Management was based on raw transactional data and static views, which prevented real-time monitoring of the gap between targets and revenues, and made it difficult to identify volatility in sales by consultant and category.
Solution
Development of a dynamic Business Intelligence tool that transformed raw data into strategic insights. The solution included:
-
Target vs. Actual Monitoring: Monthly comparison to highlight performance fluctuations;
-
Salesperson vs. Product Matrix: Data cross-referencing to identify specialists and guide training;
-
Intuitive Interface: Dark Mode design with navigation via segmentation menus (interactive filters), allowing quick transitions between temporal views and categories;
-
Seasonal Analysis: Dynamic filters for the years 2019 to 2021 to understand historical sales behavior.
Result
The analysis brought immediate clarity to decision-making, revealing that:
-
Identification of Gaps: Detection of negative seasonality in the months of February and April;
-
Revenue Pillars: It was found that VBA (R$ 136,800.00) and Python (R$ 115,650.00) support revenue;
-
Individual Performance: Identification of consultant Gaya as Top Performer (R$ 112,200.00) and detection of consultants with greater deviation from targets (such as Marcello);
-
Action Plan: Generation of recommendations for seasonality adjustments in Q1 and optimization of the product mix (combined sale of Excel/Power BI with VBA/Python niches).
Tool used
-
Microsoft Excel: Main project environment;
-
Power Query: Used for the entire ETL process (Data Extraction, Transformation, and Loading);
-
Pivot Tables: Applied for data modeling;
-
Native Visualization: Integrated charts and cards for the final dashboard.
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 collection
Problem
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
Development of a Business Intelligence solution applied to personal library management. The project focused on:
-
Asset Monitoring: Real-time visualization of total invested capital and market value of books;
-
Demographic Analysis: Automatic distribution by genre (predominance of Romance and Spiritualism) and linguistic diversity (Portuguese/English);
-
Reading Performance Indicators: Donut charts to monitor progress (read vs. unread) and identify the "Shelf Elite" (best-rated books);
-
UI/UX Design: Dark Mode interface with optimized visual hierarchy, using object grouping techniques and grid alignment for intuitive navigation.
Result
The tool transformed the collection into a strategic asset with actionable data:
-
Financial Visibility: Identification of a literary portfolio exceeding R$ 11.000.00, highlighting rare works (e.g., Eustáquio);
-
Reading Efficiency: It was found that 48% of the collection has already been completed, allowing prioritization of the remaining 52% through the "Best Rated" section;
-
Acquisition Insights: Discovery that the "Novel" genre represents 40% of the works, generating a recommendation for diversification into less represented categories, such as Short Stories and Children's Literature;
-
Historical Mapping: Identification of acquisition peaks between 2010 and 2014.
Tool used
-
Microsoft Excel: Development platform;
-
Power Query: Used for ETL (cleaning and structuring raw data);
-
Pivot Tables: For modeling metrics and calculating average investment;
-
Visualization Elements: Use of shapes, cards, and native charts for a modern and professional interface.
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).