This project focuses on Store Sales based on a dataset with 240,001 rows, which was cleaned and transformed using Power Query, reducing it to 20,001 rows by following a star schema model in Excel. The data has been analyzed through Pivot Tables, DAX, and Macros to extract actionable insights. This dashboard helps understand customer behavior, sales performance, profitability trends, and store comparisons.
- A Store dataset typically includes information about the products, customers, and sales associated with store.
- The dataset contained 240,001 rows .
-
Visualization With Microsoft Excel :-
-
Power Query : Used for data cleaning and transformation.
-
Pivot Tables : Employed to summarize and analyze the structured data.
-
DAX (Data Analysis Expressions) : Leveraged for complex calculations and to create calculated fields in the dashboard.
-
Macros : Used to automate data filtering and enhance dashboard interactivity.
-
NOTE :-
- Used Star Schema Design for data modeling :
- A star schema was implemented to separate fact and dimension tables, making it easier to perform aggregations and ensuring faster querying. This schema enhances the efficiency of the analysis by simplifying data relationships between fact and dimension tables, improving dashboard performance and clarity.
- By Following Star Schema , the dataset size was reduced from 240,001 rows to 20,001 rows with the help of Power Query, making it more manageable for analysis.
- Customer Analysis : provide insights into the profit generated from male and female customers, and breaks down the average spending by customer age groups, showing which age groups are most profitable.
- Profitability over Time : This includes a profit trend and month-over-month growth rate, which could help in identifying seasonal patterns or trends in sales effectiveness.
- Profitability by Weekday : Analyzes which days of the week generate the most profit, useful for staffing and marketing efforts.
- Product Analysis : Details the top-selling and most profitable products, as well as product return and refund rates. This is critical for inventory management and identifying which products are most valuable to the business.
- Store Budget vs Revenue : Presents a comparison of revenue vs target for each store, which helps in understanding which stores are under or over-performing against their targets.
- Month-by-Month Analysis : Offers a detailed month-by-month breakdown of total revenue and targets, highlighting variances. This can be important for financial planning and performance management.
- Quarterly Revenue Analysis : Shows revenue generated per quarter against the average, providing a quick view of performance relative to the norm. .
- Weekday/Weekend Revenue Analysis : Gives a comparison of revenue generated on weekdays versus weekends, which can help in making staffing and operational decisions. .
- Monthly Revenue vs Target : Tracks monthly performance against goals, which is fundamental for short-term financial planning and adjustments.
