Step 1: Data Preparation

financial_data <- read.csv("financial_data.csv")
sales_data <- read.csv("sales_data.csv")
product_info <- read.csv("product_info.csv")

# Clean up and format
financial_data$Date <- as.Date(financial_data$Date)
sales_data$Date <- as.Date(sales_data$Date)

product_info <- product_info %>% select(-X)
sales_data <- sales_data %>% select(-X)

combined_data <- financial_data %>%
  left_join(product_info, by = c("ProductName" = "Product")) %>%
  left_join(sales_data, by = c("ProductName" = "Product", "Date" = "Date"))
## Warning in left_join(., sales_data, by = c(ProductName = "Product", Date = "Date")): Detected an unexpected many-to-many relationship between `x` and `y`.
## ℹ Row 8 of `x` matches multiple rows in `y`.
## ℹ Row 62 of `y` matches multiple rows in `x`.
## ℹ If a many-to-many relationship is expected, set `relationship =
##   "many-to-many"` to silence this warning.
combined_data <- combined_data %>%
  rename(Category_financial = Category.x, Category_info = Category.y)

Step 2: Financial Data Analysis

2.1 Sales Performance Over Time

combined_data %>%
  group_by(Date) %>%
  summarize(DailySales = sum(TotalSales, na.rm = TRUE)) %>%
  ggplot(aes(x = Date, y = DailySales)) +
  geom_line(color = "steelblue") +
  labs(title = "Sales Performance Over Time",
       x = "Date", y = "Total Sales")

2.2 Top & Bottom Performing Products

product_summary <- combined_data %>%
  group_by(ProductName) %>%
  summarize(
    TotalSales = sum(TotalSales, na.rm = TRUE),
    AvgUnitPrice = mean(UnitPrice, na.rm = TRUE),
    UnitsSold = sum(UnitsSold, na.rm = TRUE)
  ) %>%
  arrange(desc(TotalSales))

head(product_summary, 10)
## # A tibble: 10 × 4
##    ProductName TotalSales AvgUnitPrice UnitsSold
##    <chr>            <dbl>        <dbl>     <int>
##  1 Product_10      53029.         59.2       993
##  2 Product_3       43446.         61.5       687
##  3 Product_7       33623.         49.2       669
##  4 Product_6       29263.         61.1       481
##  5 Product_5       26191.         59.9       474
##  6 Product_2       25256.         52.4       491
##  7 Product_4       24099.         52.8       452
##  8 Product_9       23329.         55.4       393
##  9 Product_8       20120.         60.6       315
## 10 Product_1       17888.         47.7       362
tail(product_summary, 10)
## # A tibble: 10 × 4
##    ProductName TotalSales AvgUnitPrice UnitsSold
##    <chr>            <dbl>        <dbl>     <int>
##  1 Product_10      53029.         59.2       993
##  2 Product_3       43446.         61.5       687
##  3 Product_7       33623.         49.2       669
##  4 Product_6       29263.         61.1       481
##  5 Product_5       26191.         59.9       474
##  6 Product_2       25256.         52.4       491
##  7 Product_4       24099.         52.8       452
##  8 Product_9       23329.         55.4       393
##  9 Product_8       20120.         60.6       315
## 10 Product_1       17888.         47.7       362

2.3 Sales by Category

combined_data %>%
  group_by(Category_financial) %>%
  summarize(CategorySales = sum(TotalSales, na.rm = TRUE)) %>%
  ggplot(aes(x = reorder(Category_financial, -CategorySales), y = CategorySales)) +
  geom_col(fill = "darkgreen") +
  labs(title = "Total Sales by Category",
       x = "Category", y = "Total Sales") +
  coord_flip()

2.4 Profitability Analysis

combined_data <- combined_data %>%
  mutate(
    EstimatedCost = UnitPrice * 0.6,
    TotalCost = EstimatedCost * UnitsSold,
    Profit = TotalSales - TotalCost
  )

combined_data %>%
  group_by(ProductName) %>%
  summarize(TotalProfit = sum(Profit, na.rm = TRUE)) %>%
  arrange(desc(TotalProfit)) %>%
  head(10)
## # A tibble: 10 × 2
##    ProductName TotalProfit
##    <chr>             <dbl>
##  1 Product_10       21212.
##  2 Product_3        17379.
##  3 Product_7        13449.
##  4 Product_6        11705.
##  5 Product_5        10477.
##  6 Product_2        10103.
##  7 Product_4         9639.
##  8 Product_9         9331.
##  9 Product_8         8048.
## 10 Product_1         7155.

Step 3: Insights and Recommendations

Insights

  • Sales Performance: Sales peaked significantly during specific periods, suggesting strong seasonality that the company can plan inventory and marketing around.
  • Top Products:
    • Product_10 led all products with a total of $53,028.86 in sales.
    • Product_3 and Product_7 followed closely, contributing significantly to overall revenue.
  • Bottom Products:
    • Product_1 and Product_8 had the lowest total sales, at $14,209.48 and $19,846.03 respectively.
  • Category Trends:
    • Furniture was the top-performing category with $69,732.36 in total sales.
    • Electronics closely followed with $69,275.78.
    • Toys was the lowest-performing category at $33,384.91, signaling potential issues with demand or product mix.
  • Profitability (estimated): High-selling products were generally profitable, but some items with lower sales may still offer solid profit margins, suggesting opportunities in underleveraged products.

Recommendations

  • Stocking Strategy:
    • Prioritize inventory for Product_10, Product_3, and Product_7 — especially before peak seasons.
    • Review stocking of Product_1 and Product_8; consider discontinuing or bundling with better sellers to clear inventory.
  • Category Focus:
    • Continue investing in Furniture and Electronics, which are core revenue drivers.
    • Reevaluate the Toys category — explore if sales can be improved through promotions, rebranding, or adjusting the product lineup.
  • Profit Optimization:
    • Review cost structures for high-sales, low-profit items.
    • Increase visibility of medium-selling, high-margin products during off-peak seasons.
  • Promotions & Marketing:
    • Launch targeted promotions around peak months to maximize revenue.
    • Consider marketing bundles that include top sellers and underperformers to stimulate interest across the catalog.