Chart 3: Profit margins

library(tidyverse)
library(readxl)
library(scales)
library(knitr)

load("C:/Users/riaku/Downloads/webshop.RData") 

library(readxl)
income_statement <- read_excel("C:/Users/riaku/Downloads/Income_statement.xlsx")

library(readxl)
promotions <- read_excel("C:/Users/riaku/Downloads/promotions.xlsx")

library(readxl)
prices <- read_excel("C:/Users/riaku/Downloads/Prices.xlsx")


##price lookup table;

base_prices <- tibble(
  merchandise = c("Tshirt", "Mug", "Hoodie", "Poster"),
  unit_price = c(30, 15, 50, 25),  
  cost = c(12, 6, 20, 10))

##reshaping shopping basket from wide to long;


basket_long <- shopping_basket %>%
  pivot_longer(cols = c(Tshirt, Mug, Hoodie, Poster),
               names_to = "merchandise",
               values_to = "quantity") %>%  
  filter(quantity > 0)


##joining with purchese to get payment and discount information;

merch_details <- basket_long %>%
  left_join(purchases, by = "transactionID") %>%
  left_join(base_prices, by = "merchandise")


## calculating revenues and profit 

profit_by_transaction <- merch_details %>% group_by(transactionID) %>%
  mutate(
    basket_value_before = sum(quantity * unit_price),
    item_proportion = (quantity * unit_price) / basket_value_before,
    revenue_after_discount = Payment[1] * item_proportion,
    revenue_before_discount = quantity * unit_price,
    discount_amount = revenue_before_discount - revenue_after_discount,
    total_cost = quantity * cost,
    profit_after_discount = revenue_after_discount - total_cost,
    profit_before_discount = revenue_before_discount - total_cost) %>% 
  ungroup()



## join by merchendise type

profit_summary <- profit_by_transaction %>% group_by(merchandise) %>%
  summarise(
    units_sold = sum(quantity),
    revenue_before_discount = sum(revenue_before_discount),
    revenue_after_discount = sum(revenue_after_discount),
    total_discount_given = sum(discount_amount),
    discount_rate = (total_discount_given / revenue_before_discount) * 100,
    total_cost = sum(total_cost),
    profit_before_discount = sum(profit_before_discount),
    profit_after_discount = sum(profit_after_discount),
    profit_margin_before = (profit_before_discount / revenue_before_discount) * 100,
    profit_margin_after = (profit_after_discount / revenue_after_discount) * 100,
    transactions = n_distinct(transactionID),
    avg_unit_price = mean(unit_price),
    avg_unit_cost = mean(cost)) %>%
  arrange(desc(profit_after_discount))


##finally putting everything toghether;

total_summary <- profit_summary %>%
  summarise(
    total_units = sum(units_sold),
    total_revenue_before = sum(revenue_before_discount),
    total_revenue_after = sum(revenue_after_discount),
    total_discounts = sum(total_discount_given),
    overall_discount_rate = (total_discounts / total_revenue_before) * 100,
    total_costs = sum(total_cost),
    total_profit_before = sum(profit_before_discount),
    total_profit_after = sum(profit_after_discount),
    profit_margin_before = (total_profit_before / total_revenue_before) * 100,
    profit_margin_after = (total_profit_after / total_revenue_after) * 100,
    profit_lost_to_discounts = total_profit_before - total_profit_after)

total_summary

## # A tibble: 1 × 11
##   total_units total_revenue_before total_revenue_after total_discounts
##         <int>                <dbl>               <dbl>           <dbl>
## 1       83513              2718145             2589945          128200
## # ℹ 7 more variables: overall_discount_rate <dbl>, total_costs <dbl>,
## #   total_profit_before <dbl>, total_profit_after <dbl>,
## #   profit_margin_before <dbl>, profit_margin_after <dbl>,
## #   profit_lost_to_discounts <dbl>

### PROFIT MARGINS

p3 <- profit_summary %>%
  ggplot(aes(x = reorder(merchandise, profit_margin_after))) +
  geom_col(aes(y = profit_margin_before, fill = "Before Discount"), 
           alpha = 0.6, width = 0.7) +
  geom_col(aes(y = profit_margin_after, fill = "After Discount"), 
           width = 0.7) +
  geom_text(aes(y = profit_margin_after, 
                label = sprintf("%.1f%%", profit_margin_after)), 
            hjust = -0.2, size = 3.5) +
  coord_flip() +
  scale_y_continuous(labels = function(x) paste0(x, "%"),
                     expand = expansion(mult = c(0, 0.15))) +
  scale_fill_manual(values = c("Before Discount" = "lightpink", 
                               "After Discount" = "darkred")) +
  labs(title = "Profit Margins by Merchandise Type",
       subtitle = "How discounts impact profitability",
       x = NULL,
       y = "Profit Margin (%)",
       fill = NULL) +
  theme_minimal(base_size = 12) +
  theme(legend.position = "top",
        panel.grid.major.y = element_blank())

p3