Transforming raw sales data into actionable business insights using Power Query, Power Pivot, DAX, and Interactive Dashboards.
The FNP Sales Analysis Dashboard is an end-to-end Business Intelligence project developed in Microsoft Excel to analyze sales performance and customer purchasing behavior for Ferns N Petals (FNP).
The dashboard converts raw transactional data into meaningful business insights through interactive visualizations, enabling stakeholders to monitor KPIs, evaluate sales trends, analyze customer behavior, and make data-driven business decisions.
The project analyzes 1,000 customer orders across multiple gifting occasions, product categories, cities, and time periods using advanced Excel features including:
- ✅ Power Query Editor
- ✅ Power Pivot
- ✅ Data Modeling
- ✅ DAX Measures
- ✅ Pivot Tables
- ✅ Pivot Charts
- ✅ KPI Cards
- ✅ Interactive Slicers
- ✅ Timeline Filters
A short demonstration showing the dashboard's interactive filters and visualizations.
Click below to watch the demo
▶ [Dashboard Demo](Assets/Dashboard demo.mp4)
Ferns N Petals receives thousands of customer orders across different occasions, products, and cities. Analyzing this large volume of transactional data manually makes it difficult to identify sales trends, customer behavior, seasonal demand, and product performance.
Without a centralized reporting solution, business stakeholders face challenges in:
- Tracking overall business performance
- Identifying high-revenue occasions
- Monitoring monthly sales trends
- Evaluating customer spending behavior
- Measuring product category performance
- Identifying top-selling products
- Understanding regional demand
- Making quick, data-driven decisions
To address these challenges, an interactive Excel dashboard was developed that consolidates raw sales data into an intuitive reporting solution.
The primary objectives of this project were to:
- Monitor overall business performance using KPI Cards.
- Analyze revenue generated across different gifting occasions.
- Identify high-performing product categories.
- Understand customer purchasing behavior based on order time.
- Analyze monthly revenue trends.
- Identify top-performing products.
- Evaluate city-wise order distribution.
- Calculate average customer spending.
- Monitor average order delivery time.
- Build an interactive management dashboard using Microsoft Excel.
| Category | Tools Used |
|---|---|
| Spreadsheet Software | Microsoft Excel |
| Data Cleaning | Power Query Editor |
| Data Modeling | Power Pivot |
| Calculations | DAX (Data Analysis Expressions) |
| Reporting | Pivot Tables |
| Visualization | Pivot Charts |
| Dashboard Components | KPI Cards |
| Interactivity | Slicers & Timeline Filters |
| Analytics | Business Intelligence |
| Documentation | GitHub Markdown |
FNP-Sales-Analysis-Dashboard
│
├── Assets
│ └── Dashboard Demo.mp4
│
├── Dashboard
│ └── FNP Sales Analysis Dashboard.xlsx
│
├── Dataset
│ ├── customers.csv
│ ├── orders.csv
│ └── products.csv
│
├── Documentation
│ └── Executive Summary.pdf
│
├── Images
│ ├── Dashboard.png
│ ├── Revenue by Occasion.png
│ ├── Revenue by Category.png
│ ├── Revenue by Months.png
│ ├── Revenue by Order Hours.png
│ ├── Top 5 Products by Revenue.png
│ └── Top 10 Cities by Orders.png
│
└── README.md
- Interactive Excel Dashboard
- Automated KPI Reporting
- Power Query Data Transformation
- Power Pivot Data Modeling
- DAX Measures
- Interactive Filters
- Dynamic Charts
- Business Insights
- Sales Performance Analysis
- Customer Behavior Analysis
- Executive Summary Documentation
- Professional GitHub Project Structure
💡 This project demonstrates practical skills in Excel-based Business Intelligence, Data Cleaning, Data Modeling, DAX, Dashboard Design, and Business Analytics by transforming raw sales data into actionable business insights.
The FNP Sales Analysis Dashboard has been designed to provide an interactive and user-friendly experience for business users. It enables stakeholders to explore sales performance dynamically through filters and visualizations without modifying the underlying dataset.
- 📌 Dynamic KPI Cards
- 📈 Revenue Analysis by Occasion
- 🛍 Revenue Analysis by Product Category
- ⏰ Revenue Analysis by Order Hour
- 📅 Monthly Revenue Trend
- 🏆 Top 5 Products by Revenue
- 🌍 Top 10 Cities by Orders
- 🎛 Interactive Slicers
- 📆 Timeline Filters
- ⚡ Automated KPI Calculations
- 📊 Dynamic Pivot Charts
- 📑 Business-Friendly Dashboard Layout
The dashboard tracks important business metrics to provide a quick overview of overall sales performance.
| KPI | Description |
|---|---|
| 💰 Total Revenue | Total revenue generated from all customer orders |
| 📦 Total Orders | Total number of orders placed |
| 💵 Average Customer Spend | Average amount spent per customer |
| 🚚 Average Delivery Time | Average number of days required for delivery |
These KPIs automatically update based on the selected filters and slicers.
The project follows a structured Business Intelligence workflow.
Raw Dataset
│
▼
Power Query
(Data Cleaning & Transformation)
│
▼
Power Pivot
(Data Modeling)
│
▼
DAX Measures
(KPI Calculations)
│
▼
Pivot Tables
│
▼
Pivot Charts
│
▼
Interactive Dashboard
│
▼
Business Insights
Before analysis, the raw sales dataset was cleaned and transformed using Power Query Editor.
- Imported raw Excel data
- Removed duplicate records
- Checked missing values
- Standardized text formatting
- Corrected date formats
- Renamed columns
- Converted data types
- Prepared analysis-ready tables
Using Power Query reduced manual effort while making the dashboard refreshable whenever new data is added.
To improve scalability and performance, Power Pivot was used to create a relational data model.
The model establishes relationships between multiple tables, allowing efficient aggregation and filtering across the dashboard.
- Better dashboard performance
- Centralized calculations
- Reduced duplicate formulas
- Easier maintenance
- Dynamic reporting
Dynamic KPIs were created using Data Analysis Expressions (DAX).
Some of the measures include:
- Total Revenue
- Total Orders
- Average Customer Spend
- Average Order Delivery Time
- Revenue by Occasion
- Revenue by Category
- Monthly Revenue
- Top Product Revenue
These measures automatically recalculate whenever users interact with slicers or timeline filters.
The dashboard consists of multiple interactive visualizations designed to answer key business questions.
Compares revenue generated during different gifting occasions.
Shows the contribution of each product category to total revenue.
Analyzes customer purchasing behavior throughout the day.
Displays seasonal sales performance across months.
Highlights the five highest revenue-generating products.
Displays the cities generating the highest number of orders.
Each visualization updates dynamically based on user selections.
To improve usability, multiple slicers and timeline filters have been incorporated.
- Occasion
- Order Date
- Delivery Date
- Month
These filters enable users to drill down into specific business scenarios and perform customized analysis instantly.
This project demonstrates practical knowledge of advanced Microsoft Excel capabilities.
- Power Query
- Power Pivot
- Data Modeling
- DAX Measures
- Pivot Tables
- Pivot Charts
- KPI Card Design
- Dashboard Development
- Interactive Reporting
- Data Cleaning
- Data Transformation
- Conditional Formatting
- Business Intelligence
- Data Visualization
This dashboard helps business stakeholders:
- Monitor business performance
- Identify high-performing occasions
- Analyze customer spending behavior
- Improve inventory planning
- Optimize marketing campaigns
- Track regional sales performance
- Make data-driven business decisions
---# 📊 Business Insights
The dashboard uncovers valuable business insights by analyzing sales performance across occasions, product categories, customer behavior, and regional demand.
The analysis compares revenue generated across different gifting occasions.
- 🥇 Anniversary generated the highest revenue.
- 🎁 Raksha Bandhan was one of the strongest performing occasions.
- 🌈 Holi contributed significantly to overall sales.
- 🎂 Birthday sales remained stable throughout the year.
- ❤️ Valentine's Day generated comparatively lower revenue.
- 🪔 Diwali recorded the lowest revenue among the major occasions.
High-performing occasions should receive increased marketing budgets, promotional campaigns, and inventory allocation to maximize seasonal revenue.
The category analysis identifies which product types contribute the most towards total revenue.
- One product category contributes nearly 30% of the total revenue.
- Multiple categories consistently perform well throughout the year.
- Some categories have lower contribution and present opportunities for business growth.
Lower-performing categories can be improved by introducing:
- Combo Offers
- Festival Bundles
- Personalized Recommendations
- Cross-selling Strategies
- Limited-Time Discounts
The hourly sales trend provides insights into customer purchasing behavior.
- Customer orders are distributed throughout the day.
- Multiple revenue peaks indicate active purchasing during different time slots.
- Certain hours consistently outperform others.
Schedule:
- Flash Sales
- Email Campaigns
- Push Notifications
- Paid Advertisements
- Social Media Promotions
during peak ordering hours to maximize customer engagement and increase conversion rates.
The monthly trend highlights seasonal demand and revenue fluctuations.
- February recorded the highest revenue.
- Revenue declined after the seasonal peak.
- Sales remained relatively stable during later months.
Businesses should prepare before high-demand months by increasing:
- Inventory Levels
- Delivery Workforce
- Marketing Budget
- Customer Support Capacity
to avoid operational bottlenecks and maximize revenue opportunities.
The dashboard identifies the highest revenue-generating products.
- Magnum Set
- Premium Gift Packs
- Hamper Collections
- Decorative Gift Boxes
- Gift Bundles
These products should receive:
- Priority Inventory Allocation
- Homepage Placement
- Premium Advertising
- Bundle Recommendations
- Promotional Campaigns
Regional analysis highlights the cities generating the highest number of customer orders.
- Dhanbad
- Imphal
- Kavali
These cities represent strong customer markets and should be targeted through:
- Local Marketing Campaigns
- Faster Delivery Services
- Regional Promotions
- Customer Loyalty Programs
Based on the dashboard analysis, the following recommendations are proposed:
Focus promotional spending on:
- Anniversary
- Raksha Bandhan
- Holi
to maximize seasonal revenue.
Increase sales through:
- Combo Deals
- Cross-selling
- Festival Bundles
- Personalized Recommendations
- Promotional Discounts
Although the average delivery time is approximately 5.7 days, reducing it below 4 days can improve customer satisfaction and encourage repeat purchases.
Launch promotional campaigns during the busiest ordering hours identified by the dashboard.
Expand marketing efforts in high-performing cities while analyzing low-performing regions to identify future growth opportunities.
Maintain higher inventory levels for top-selling products before major gifting occasions to prevent stock shortages and maximize revenue.
A detailed project report explaining the complete business problem, objectives, dashboard development process, insights, and recommendations is available in the documentation.
📄 Executive Summary
Documentation/
└── Executive Summary.pdf
You can also access it directly from the repository.
- Data Cleaning
- Data Transformation
- Business Intelligence
- Dashboard Development
- Power Query
- Power Pivot
- Data Modeling
- DAX Measures
- KPI Design
- Interactive Reporting
- Sales Analytics
- Data Visualization
- Decision Support
- Microsoft Excel
Future enhancements that can be implemented include:
- SQL Database Integration
- Power BI Dashboard Version
- Sales Forecasting
- Customer Segmentation
- Automated Dashboard Refresh
- Predictive Analytics
- Customer Lifetime Value Analysis
- Profitability Analysis
- Inventory Forecasting
- Machine Learning-Based Sales Prediction
---# 📂 Repository Structure
FNP-Sales-Analysis-Dashboard
│
├── Assets
│ └── Dashboard Demo.mp4
│
├── Dashboard
│ └── FNP Sales Analysis Dashboard.xlsx
│
├── Dataset
│ ├── customers.csv
│ ├── orders.csv
│ └── products.csv
│
├── Documentation
│ └── Executive Summary.pdf
│
├── Images
│ ├── Dashboard.png
│ ├── Revenue by Occasion.png
│ ├── Revenue by Category.png
│ ├── Revenue by Month.png
│ ├── Revenue by Order Hour.png
│ ├── Top Products.png
│ └── Top Cities.png
│
├── README.md
└── LICENSE
This project successfully demonstrates how Microsoft Excel can be transformed into a powerful Business Intelligence solution capable of analyzing large volumes of sales data.
The dashboard provides meaningful insights that support:
- 📊 Data-Driven Decision Making
- 📈 Sales Performance Analysis
- 🎯 Marketing Strategy Optimization
- 📦 Inventory Planning
- 🚚 Delivery Performance Monitoring
- 👥 Customer Behavior Analysis
- 🌍 Regional Sales Analysis
During this project, I strengthened my practical understanding of:
- Advanced Microsoft Excel
- Power Query Editor
- Power Pivot
- Data Modeling
- DAX (Data Analysis Expressions)
- Pivot Tables
- Pivot Charts
- Interactive Dashboard Design
- KPI Development
- Business Intelligence Reporting
- Data Cleaning & Transformation
- Business Analytics
The dashboard enables business users to:
✅ Monitor sales performance in real time
✅ Analyze customer purchasing behavior
✅ Identify seasonal demand
✅ Track product performance
✅ Improve marketing strategies
✅ Optimize inventory management
✅ Support data-driven decision-making
This project is licensed under the MIT License.
You are free to use, modify, and distribute this project for educational purposes.
Special thanks to:
- Microsoft Excel
- Power Query
- Power Pivot
- The open-source data analytics community
- Ferns N Petals dataset used for learning purposes
Hi, I'm Abdullah 👋
🎓 BCA Student
📊 Aspiring Data Analyst
📈 Future Data Scientist
I enjoy solving business problems through data analysis and building interactive dashboards that transform raw data into meaningful insights.
Currently learning:
- Excel
- SQL
- Python
- Power BI
- Statistics
- Machine Learning
https://github.com/AbdullahCodes01
https://www.linkedin.com/in/abdullah-764333380/
If you found this project helpful or interesting:
⭐ Star this repository
🍴 Fork it
💬 Share your feedback
📢 Connect with me on LinkedIn
Your support motivates me to build more data analytics projects.