Portfolio
Objective
The primary objective of this analysis was to assess the overall performance of the superstore and address the following key questions:
- How is the store performing concerning its revenue and profit? Are there any discernible seasonal trends? What is the performance of different customer segments, and which segment is performing the best?
- How are the employees contributing to the store’s performance? Which employees merit promotion, and who requires further attention?
Data preparation
Deleted columns:
– Postal code
– Country (since all data pertains to the USA)
– Row ID
– Customer name (customer ID was retained as a unique identifier)
Manipulations:
– Modified the format of sales, discounts, and profit.
– Transformed the date format from mm-dd-yy to dd-m-yy.
– Consolidated data from the “people” and “return” sheets.
– Converted data from a range to a table format, facilitating future data manipulation, and assigned appropriate names to the tables.
Analysis
For the purpose of conducting a descriptive analysis, specific columns, namely sales, quantity, discount, and profit, were selected to perform descriptive statistics. This analysis was carried out by navigating to “Data” -> “Data Analysis” -> “Descriptive Statistics” in the tool.
The results revealed a right-skewed distribution, implying that the mean, mode, and median may not precisely represent the central tendency of the data. To address this issue rigorously, histograms were generated for these columns, and further calculations were made using interquartile ranges (IQR).
Here are the key findings
Sales: The most common sales amount is USD 12.96, with 50% of sales falling within the range of USD 17.27 to USD 209.94.
Quantity: It was observed that products were ordered in quantities up to 4 units, with an average of 3.79 units sold per transaction. The most common quantity ordered is 3 units, and 50% of the quantity ordered falls within the range of 2 to 5 units.
Discount: Products were sold with discounts up to 10%. The average discount rate is 16%, but the most frequent case is no discount (mode=0). 50% of discount percentages lie within the range of 0% to 20%.
Total Sales and Profit: Over a four-year period, the total sales amounted to $2,297,200.86, with a total profit of $286,397.02.
To assess the store’s performance, the gross profit margin was calculated using the profitability index formula (total profit divided by total sales), resulting in a 12% margin.
However, when compared to industry benchmarks, specifically the Margins by Sector (US) in October 2023, it was noted that the retail segment typically achieves a gross profit margin of approximately 24%. This indicates that the Superstore is underperforming in terms of profitability.
To investigate this issue further, a profitability framework was implemented, and the initial hypothesis suggested that the lower profitability ratio may be due to higher operating costs. Further analysis and strategic adjustments may be required to improve the store’s performance and align it with industry standards.
In order to gain a more comprehensive understanding of the revenue streams, the analysis was extended to consider three customer segments and three product categories. While the dataset did not directly provide total costs, the available information on discounts allowed for the calculation of initial unit prices, which, in turn, facilitated the estimation of total costs.
Here are the key insights drawn from the initial review of total revenue:
Seasonality: A distinct seasonality pattern was observed, with revenue consistently increasing every fourth quarter of each year. This pattern starts in the first quarter of each year and gradually builds over time, demonstrating a positive and consistent growth trend in the revenue stream over the four-year period.
Revenue and Profit Comparison: Despite the overall growth in total sales performance, with steady increases in both total revenue and total profit each year, there was a noteworthy anomaly in the fourth quarter of 2017. During this period, a significant gap was observed between revenue and profit, indicating a potential issue that warrants further investigation.
This discrepancy between revenue and profit in the fourth quarter of 2017 should be thoroughly examined. It may indicate challenges in cost management, pricing strategies, or other factors affecting the profitability of the Superstore during that specific period. Further analysis is required to identify the root causes of this gap and develop strategies to address and prevent such occurrences in the future.
The review of total costs provided valuable insights into the Superstore’s cost dynamics. Given that a detailed cost breakdown was not available in the dataset, a general assumption was made to assess the overall cost trends. Here are the key findings:
Cost Trends Over the Years: An overall trend of increasing costs over the years was observed. This aligns with the seasonality pattern, as costs tend to be lower in the first quarter of each year and reach their peak in the fourth quarter. However, a notable exception was identified in the fourth quarter of 2017, where costs experienced significant year-over-year growth. This increase in costs in 2017 Qtr4 contributed to the gap between revenue and profit during that period.
Factors Influencing Cost Increase: While the dataset did not provide a detailed breakdown of costs, it is common in practice for cost increases to be influenced by various factors, including economic conditions (such as inflation and taxation), political situations (including changes in legislation), supply and demand dynamics, and other market-specific variables.
Analysis of Price and Quantity Changes: To gain further insights into the increased costs in 2016 and 2017, a more detailed examination of price and quantity changes was conducted by product category. This period was selected for closer scrutiny due to the notable rise in costs. Analyzing how price and quantity changes vary by category can help uncover the underlying reasons for the cost increase during that period.
The detailed analysis of price and quantity changes by product category and sub-category reveals several important insights:
– Office Supplies: Within the Office Supplies category, certain sub-categories, namely envelopes, fasteners, and supplies, have experienced significant changes. Notably, the price increase didn’t fully compensate for the decrease in quantity, and vice versa. This indicates that cost dynamics within these sub-categories are critical and may require closer attention.
– Despite the perception that Office Supplies might be the main driver of costs, a more comprehensive evaluation of year-over-year changes in gross margin reveals that the Furniture category actually has the lowest profitability ratio. This is likely due to the higher costs associated with certain items like bookcases and tables.
– When focusing on the Office Supplies sub-category, it’s evident that, apart from supplies, which exhibited a negative profit margin over the last three years, the gross margin for this category has been generally favorable. This suggests that the Office category is performing well, except for supplies, which may require further analysis and adjustments to improve profitability.
– Technology Category: The Technology category stands out for its overall positive gross margin performance. However, in 2017, the machine sub-category has garnered attention due to its negative profit ratio. This sub-category’s performance should be examined more closely to identify and address the underlying factors impacting profitability.
Indeed, despite the Furniture category generating a higher total revenue compared to Office Supplies, it’s evident that the total cost associated with Furniture is higher than that of Office Supplies and Technology. This observation highlights a key point: total revenue alone doesn’t necessarily determine profitability.
The cost structure within each category can significantly impact on the overall profitability of that category. In the case of Furniture, it appears that higher costs, possibly due to specific items within the category such as bookcases and tables, are affecting its profitability.
Segment analysis
Consumer Segment:
The Consumer segment is the top performer in terms of both revenue and profit. This segment generates the highest total revenue and profit among the three.
Despite its strong revenue and profit performance, the Consumer segment has the lowest profit margin, indicating that its profitability is comparatively lower when compared to its revenue.
Corporate Segment:
The Corporate segment’s performance in terms of revenue and profit falls in between the Consumer and Home Office segments.
The Corporate segment’s profit margin is likely moderate, as it balances revenue and profit effectively.
Home Office Segment:
The Home Office segment, while having the lowest revenue and profit among the three segments, exhibits the highest profit margin. This indicates that the Home Office segment is highly profitable relative to its revenue.
Discounts can have a substantial impact on profit margins, and it appears that the Consumer segment is benefiting more from these discounts compared to the Corporate and Home Office segments.
Using a combination of Pivot Tables and formulas (including INDEX, MODE, MATCH, MIN, COUNTIF, and AVERAGE), I identified the best and worst selling SKU and described them by color. The best-selling SKU was 799 in Dark Blue, while the worst-selling SKU was 439 in Rust. The analysis also revealed an average order value of $278.02.
I also analyzed the sales data by determining which weekday had the highest sales from June to September. To do this, I used a combination of the SUMPRODUCT and WEEKDAY formulas, and then applied conditional formatting. I created a column (column A) that extracted the weekday from the main Date_time column to help with the analysis.
As a result, Tuesday was found to be the day with the highest sales, with a total of $24,112.00, while Wednesday had the lowest sales, with a total of $15,891.00.
To identify trends in the data, I created a Pivot Table with the rows representing the months and the values being the sum of total sales. The data was then visualized using a line chart and trend line.
As it is clearly seen on the graph, the e-commerce store had a linear trend, with a small increase in August which can be explained by the back-to-school and work situation.
In order to make predictions, I used the TREND formula and calculated the accuracy of the prediction using the ABS formula. To double-check my prediction, I decided to start it from September 30th so that I could observe any deviation.
Afterwards, I visualized the actual results with the predicted ones to see if the trend had changed. As it is clearly seen on the graph, the e-commerce store still had a linear trend with a minor spike in December, which can be explained by the upcoming holidays.
Recommendations
- Stock with best-selling SKU and consider expanding the portfolio:
- Identify the complementary products that can be offered along with the best-selling SKU, based on customer preferences and buying patterns.
- Conduct market research to assess the demand for complementary products and their potential profitability.
- Determine the optimal pricing strategy for the complementary products based on market research and competitive analysis.
- Test the new products in the market to gauge customer response before investing heavily in inventory.
- Observe how the quantity of sold SKUs varies from month to month to identify the pattern and set the correct MOQ:
- Analyze the historical sales data to identify the patterns in the quantity of SKUs sold in different months.
- Set the minimum order quantity (MOQ) for each SKU based on the historical sales data and projected demand for each month.
- Re-evaluate the MOQ on a regular basis to ensure that it is aligned with the changing customer demand and market conditions.
- Use inventory management software to track the sales and inventory levels to ensure that the MOQ is being met and inventory is being replenished on time.