Capsim Draft

Code

# raw_sheets <- import_list("../Other/capsim/raw.xlsx", setclass = "tibble")
raw_sheets <- import_list("raw.xlsx", setclass = "tibble")

# clean_sheets <- lapply(raw_sheets, function(x) {
#   x %>%
#     t() %>%
#     as.data.frame() %>%
#     row_to_names(row_number = 1)
# }) 


master <- bind_rows(raw_sheets, .id = "Round")

Code

master_clean <- master |>
  mutate(
    date = make_date(as.integer(str_sub(Round, start = -1)) + 2024),
    sales = Sales...8,
    ebit = EBIT...9,
    industry = ifelse(company %in% c("Andrews", "Baldwin", "Erie", "Digby"), "Low", "High")
  ) |>
  select(
    -c(
      "Sales...59",
      "EBIT...65",
      "EBIT...64",
      "...39",
      "...40",
      "...51",
      "...52",
      "...62",
      "...63",
      "Sales...64",
      "EBIT...70",
      "EBIT...9",
      "Sales...8",
      # "Income Statement Survey",
      "Cash flows from financing activities"
    )
  )

Code

baldwin <- master_clean %>%
  filter(company == "Baldwin")

Code

ggplot(master_clean, aes(x=date, y=sales, colour = company, group = company)) +
  geom_line(linewidth = 1.4) +
  theme_bw() +
  labs(title = "Sales per Year")

Code

ggplot(master_clean, aes(x=date, y=Close, colour = company, group = company)) +
  geom_line(linewidth = 1.4) +
  theme_bw() +
  labs(title = "Stock Price per Year")

Code

ggplot(master_clean, aes(fill = company, y=`market share`, x=date)) +
  geom_col(position = "fill") +
  theme_bw() +
  labs(title = "Market Share per Year")

Code

ggplot(master_clean, aes(x=date, y=-1*(`Plant improvements (net)`), colour = company, group = company)) +
  geom_line(linewidth = 1.4) +
  theme_bw() +
  geom_dl(aes(label = company), method = "last.polygons") +
  # gghighlight() +
  # facet_wrap(~industry) +
  labs(title = "Cash Spent on Plant Improvements per Year") +
  theme(legend.position = "none")

Code

ggplot(master_clean, aes(x=date, y=`Contribution Margin`, colour = company, group = company)) +
  geom_line(linewidth = 1.4) +
  # geom_line(data = baldwin |> select(-industry), color = "black", linewidth = 1.4) +
  theme_bw() +
  # geom_label_repel(aes(label = company, group = company)) +
  # geom_label_repel(data = baldwin |> select(-industry), color = "black", aes(label = company, group = company)) +
  # geom_dl(aes(label = company), method = "last.polygons") +
  gghighlight() +
  facet_wrap(~industry) +
  labs(title = "Contribution Margin per Year") +
  theme(legend.position = "none")

Compare to real Companies

Code

stocks <- tq_get(
  c(
    "CMCSA",
    "SOFI"
  ),
  get = "stock.prices",
  from = today() - years(7),
  to = today()
  ) %>% 
  mutate(
    date = date + years(6)
  )

Code

ggplot(master_clean, aes(x=date, y=Close, colour = company, group = company)) +
  geom_line(linewidth = 1.4) +
  geom_line(
    data = stocks,
    aes(
      x = date,
      y = adjusted,
      colour = symbol,
      group = symbol
    ),
    linewidth = 1.4
    ) +
  theme_bw() +
  labs(title = "Stock Price per Year")