Road to Data Analyst #3: Building Dashboard for Superstore (in Excel)
Now that I’ve learned the basics of Excel, it’s time for me to create my own analysis dashboard. Today, I’ll be analyzing data from a superstore that I found on Kaggle.
A quick view of my output:
Prior to commencing my task, I took the initiative to review analysis notebooks crafted by fellow analysts. This endeavor provided me with valuable insights and compelled me to conduct further research, thereby enhancing my understanding of effective techniques for analyzing stored data.
To kick off my analysis, I conducted research on the crucial metrics used to gauge business performance. Among the numerous significant key metrics, I focused on a few key ones which I learned about:
- Sales/Revenues
- Profit (Net Profit/Gross Profit)
- Cost of Goods Sold (COGS)
Kindly find my notes below to understand more:
These key metrics will serve as essential indicators in evaluating the performance and financial health of the business. Above are just a few examples of the key financial metrics used to measure the performance of a business. The selection of metrics may vary depending on the industry, company size, and specific objectives.
About Dataset
With growing demands and cut-throat competition in the market, a Superstore is seeking your knowledge in understanding what works best for them. They would like to understand which products, regions, categories, and customer segments they should target or avoid.
Dataset: https://www.kaggle.com/datasets/vivek468/superstore-dataset-final
Asking Questions:
Before we start to create visualization or analysis, it’s important we understood the objectives and ask the questions.
I have categorized the questions into three sections: sales analysis, product analysis, and customer & order analysis. This division aims to assist the business in comprehending its performance from 2014 to 2017 and identifying areas of focus and improvement.
Sales Analysis:
- How have sales and profit trends evolved over time (monthly, yearly)?
- Which periods experienced high or low sales and profit?
- Is there an overall growth or decline in sales and profit?
- What is the geographical distribution of sales and profit?
- What are the profit margin and loss (%) by the state?
- Which region generates the most sales?
Product Analysis:
- What is the distribution of sales by product category and sub-category?
- Which category of products generates the highest revenue and profit?
- What are the top-selling or most profitable products in the superstore?
Customer & Order Analysis:
- Which customer segment generates the highest sales and profit?
- Which customers have made the most purchases in terms of the number of orders and total sales?
- What are the patterns, frequency, and volume of orders?
- What is the average order value?
- What is the average shipping time for each ship mode?
- What are the discount rates by category and sub-category?
- What is the impact of discounts on sales?
- How are discounts distributed (by percentage or range)?
Data Understanding
There are a total of 9994 records and 21 attributes in this dataset. By understanding the metadata, we know that some of the attributes can be removed since there are not useful for analysis. Besides, it’s also important for us to have a view of the full datasets to check if there’re many cells that have null values and etc.
Data Cleaning
Before we start cleaning the data, it’s a good practice that we make a copy of the raw data and do the cleaning on the copy instead of the raw data directly. This is because if we make some mistakes in the data cleaning, we can always refer to or recover back the raw data.
Remove attributes
🖊 Remove Row ID which is redundant for analysis.
Note: In this case, we will retain the customer names for the customer analysis in order to determine which customers have made the most significant contributions to sales.
Check null values
Select all data > Ctrl + G > ‘Special’ > ‘Blanks’.
(To remove rows if null values exist) Ctrl + Minus > Entire row
🖊 There are no null values in this dataset.
Remove duplicates
Select all data > click the ‘Data’ tab > select ‘Removes duplicates’ icon under ‘Data Tools’
🖊 There is 1 duplicate record that has been removed.
After data cleaning, there are a total of 9993 unique records and 20 attributes in this dataset.
Add distinct counts for order and customer
🖊 Add columns to make a distinct count on the orders and customers.
=IF(COUNTIF($A$A:A2,A2)>1,0,1)
Calculate delivery days
🖊 Add columns for delivery days by calculating the differences in ship date and order date.
=E2-C2
When both C & E columns are in DATE format
Add a column to differentiate if the item has discount
🖊 We can add an additional column to indicate whether the discount is 0% or not easily identify if a particular transaction had no discount applied or if there was a discount, indicating whether it’s “No Discount” or “After Discount” respectively. This additional information can provide valuable insights during the analysis process.
=IF(W2=0,"NO DISCOUNT","AFTER DISCOUNT")
With the data and the additional columns I have prepared, I now have a solid foundation for creating pivot tables and charts to analyze the sales, product, and customer data. Pivot tables and charts are powerful tools that can help you summarize and visualize information in a meaningful way.
Create Pivot Tables
A pivot table is a data summarization tool that allows us to extract & analyze large amounts of data in a more organized and meaningful way. Pivot tables enable us to group, sort, filter, and calculate data based on different criteria, providing a flexible and interactive way to explore and summarize data.
Select all data > click the ‘Insert’ tab and click on the ‘PivotTable’ > Choose a ‘Location’ > click ‘OK’
Below are some of the pivot tables created for different analyses:
Create extra calculated fields for profit margin & average order value
A calculated field in a pivot table allows us to perform calculations using existing fields within the pivot table. This can be helpful when I need to derive additional insights or perform custom calculations on the summarized data.
Select any cell within the pivot table > click the ‘PivotTable Analyze’ tab and click on the ‘Fields, Items, & Sets’ group and select the ‘Calculated Field’ > Enter ‘Name’ and ‘Formula’ which describes the calculation you want to perform > click ‘OK’ to create the calculated field
Data Visualization
Excel is a powerful tool that allows us to create visually appealing charts and leverage analysis tools. We can make use of the data obtained from the pivot tables we created earlier to build a variety of charts, enabling us to present the information in a more engaging and insightful manner.
I have created three dashboards for sales performance, product analysis, and customer & order analysis as below:
Link to access the Excel file for the dashboard: Excel — Superstore Analysis
From the dashboard above, we can gain a clear understanding of several key insights:
- Total sales, total profit, and total profit margin in each year: This information allows us to track the financial performance of the business over time and identify any significant trends or patterns.
- Breakdown of total sales, total profit, and total profit margin (%) for each month based on the selected year: This breakdown enables us to analyze the variations in performance across different months within the selected year, helping identify peak sales periods and understand profitability trends.
- Total sales by region: The dashboard provides insights into sales figures categorized by region which allows us to assess the sales performance of different regions and identify areas of potential growth or focus.
- Top 10 sales and profit by state: By analyzing the data, we can determine the states that have generated the highest sales and profit. This information helps us identify the top-performing states and potentially replicate successful strategies in other regions.
- Top 5 profit margins by state: The dashboard enables us to identify the states with the highest profit margins. By focusing on these states, we can understand the factors contributing to their profitability and potentially apply similar strategies in other regions.
- Top 5 loss by state: The dashboard also highlights the states that have experienced the highest losses. This information helps us identify areas of concern and focus our attention on improving performance and reducing losses in those states.
From the above dashboard, we can derive important insights regarding the product analysis:
- Total sales and profit by product categories: This information helps us identify the most successful and lucrative product categories within the business.
- Total profit margin by product sub-categories (%): This information provides insights into the profitability of specific product sub-categories, helping us understand which sub-categories yield higher profit margins.
- Top 10 selling products (by total sales amount): The dashboard highlights the top 10 products based on their total sales amount which helps us identify the best-selling products, allowing us to focus on their success factors and potentially replicate their performance in other products.
- Top 10 selling products (by total sales volume): This provides insights into the products that have the highest sales quantities, allowing us to prioritize and allocate resources accordingly.
- Top 10 profitable products (by profit): The dashboard showcases the top 10 products with the highest profit. This knowledge helps us identify the most profitable products, enabling us to concentrate on strategies that contribute to their success and profitability.
- Total customers by customer segments: To understand the customer composition and prioritize segments for targeted marketing efforts or customer relationship management.
- Total sales by customer segments: To identify the segments that contribute the most to overall sales and focus on strategies to enhance sales within specific segments.
- Top 3 customers who made the most purchases: To recognize the most valuable customers and develop strategies to retain their loyalty and encourage repeat purchases.
- Total orders in each month: The dashboard provides a breakdown of total orders in each month. This insight helps us identify any seasonal patterns or fluctuations in order volume, allowing us to adjust inventory, resources, and marketing strategies accordingly.
- Total orders by days of the week: This information provides insights into customer behavior and allows us to optimize operations and resources accordingly.
- Average order value: The dashboard showcases the average value of each order. This insight helps us assess the typical spending pattern of customers and develop strategies to increase the average order value, such as upselling or cross-selling techniques.
- Ship mode and average shipping time: The dashboard provides information on different shipping modes and their corresponding average shipping times. This insight helps us optimize logistics and customer satisfaction by selecting the most efficient shipping methods.
- Average discount rates across all subcategories: To assess the effectiveness of discount strategies and make informed decisions regarding pricing and promotions.
- Sales difference before and after discount: To understand the impact of discounts on overall sales and assess the effectiveness of discounting strategies.
Conclusion
The analysis provided in the dashboard offers valuable insights into the performance of the superstore from 2014 to 2017 across sales, product analysis, and customer & order aspects. By examining the results displayed in the dashboard, we can easily determine essential information such as monthly sales, profitability analysis, top customers and etc.
By leveraging these insights, businesses can gain a deeper understanding of their performance, identify areas of improvement, and formulate effective strategies to enhance overall business operations. The information provided in the dashboard serves as a foundation for data-driven decision-making, helping the superstore make informed choices to optimize sales, improve profitability, and enhance the overall customer experience.
Link to access the Excel file for the dashboard: Excel — Superstore Analysis
Follow me / Let’s Connect!
My Medium Account: https://medium.com/@itsfangying
My LinkedIn Account: https://www.linkedin.com/in/itsfangying/