-
Sales Performance Monitoring: Track and analyze sales data in real-time, including overall sales, individual product performance, and sales trends over time.
-
Customer Insights: Gain valuable insights into customer behavior, preferences, and purchase patterns to target marketing efforts, optimize promotions, and enhance customer satisfaction.
-
Store Performance Analysis: Analyze sales data across different store locations, compare performance metrics, and identify top-performing stores as well as areas requiring improvement.
-
Revenue and Profit Analysis: Track revenue and profitability metrics, identify areas of high or low profitability, and make data-driven decisions to maximize profits.
-
Product Performance Analysis: Analyzing the sales data to identify top-selling products, best-performing categories, and trends to optimize inventory management and sales strategies.
-
Data Visualization and Reporting: Create visually appealing and easy-to-understand dashboards, charts, and reports to effectively communicate sales performance and insights to stakeholders.
- We have 2 sheets to view, Set the Data in both Table with Table Name: MASTER DATA and INPUT DATA under TABLE DESIGN TAB .
-
For data cleaning check for any duplicates, null value. Since data we have is sorted.
-
Apply VLOOKUP at input data to extract data from master data. In reference to product ID.
- we have created few additional column:
TOTAL SELLING PRICE, 
TOTAL COST PRICE, 
DAY , 
MONTH , 
YEAR ! image
As we are done with preparing database > we need to create Pivot tables.
1.Go to ANALYSIS New Sheet > Insert Pivot Table > Name each Pivot Table Accordingly.
2.Create Pivot Table As Follow:
- DAILY : Days with respect to Total selling price.
- TOTAL PROFIT : To calculate total profit and profit percentage from given price > LINKING VALUE TO PIVOT TABLE WILL MAKE IT DYNAMIC .
- MONTHLY EXPENSES
- PRODUCT ANALYSIS : Product is shown with respect to theie unit of measurement, selling price and quantity > keep pivot table in TABULAR FORM under Report Layout.
- YEARLY ANALYSIS
- CATEGORY ANALYSIS
- PAYMENT MODE AND SALES TYPE
*PREPARE VISUALS
-
Add SLICERS IN DASHBOARD > LINK ALL SLICERS TO EVERY PIVOT TABLE
-
Go To Slicer Tab> Report Connections> Select All Pivot Table We Want To Link.
- ADD AREA CHART FOR DAY WISE SALES :
- CALCULATE TOTAL SALES, TOTAL PROFIT, PROFIT PERCENTAGE AND FORMAT CELL ON MONTHLY BASIS BY CONNECTING IT TO MONTHLY PIVOT TABLE :
-To add bar chart with total sales, total profit and profit percentage we need to connect all values for dynamic use.
-From INSERT(DEVELOPER TAB) > Add 3 Checkboxes, so that we can show or hide sales, profit or profit%. We need to add if function into our cell to link true or false condition with the table value so that our column chart look presentable.
- NOW INSERT THE CHART> FORMATTING>CONNECT WITH TABLE > FORMAT CHART
- FROM PRODUCT ANALYSIS: WE USE RANK FUNCTION TO GET TOP PRODUCT
*usage of offset function is highly recommended to connect everything with pivot table and make it dynamic.
*rank in reference to sales, don't forget to lock the range by using F4 KEY .
EACH PRODUCT ON SALES BASIS :
- FROM CATEGORY ANALYSIS: INSERT HIERARCHY CHART , As Hierarchy chart doesn't work with pivot table we again need to use offset function and make seperate chart to link it with dynamic hierarchy chart > find Rank with respect to sales.
- ADDING YEARLY PRESENTER: In form of pie chart for year 2021 and 2022.
- ADDING SALES TYPE :
- ADDING PAYMENT MODE :
Make a layout in powerpoint presentation.
Now copy paste the Layout on excel in another sheet and name that sheet as dashboard. Copy each presentable chart from Analysis sheet and Paste each table in new sheet named as Dashboard. Adjust the table in desired form.
Do Check your each table is dynamic and clear .
DATA ANALYSIS - ADDING CHARTS, SLICERS ETC. AND LINKING EACH CHART TO EACH OTHER FOR DYNAMIC VIEW.