d1 <- haven::read_dta("tech_co_cstat.dta") %>% 
  filter(sale > 0) %>% 
  mutate(conm = stringr::str_to_title(conm)) # Converts the string to title case

Q1

Create a bar graph of the average annual profits of each company using the variable oibdp such that the bars are arranged in descending order.

d1_1 <- d1 %>%
  group_by(conm) %>% 
  summarize(avg_profit = mean(oibdp), .groups = "drop")
d1_1 %>%
  ggplot(aes(x=avg_profit, y= reorder(conm, avg_profit))) +
  geom_bar(stat = "identity") +
  labs(x="Average Profit in $millions", y="Company") +
  scale_x_continuous(labels = scales::dollar)

Q2

Modify the plot in Q1 to add text labels to the bars. Use hjust = -0.2 and size = 3 for the text labels.

d1_1 %>%
  ggplot(aes(x=avg_profit, y= reorder(conm, avg_profit))) +
  geom_bar(stat = "identity") +
  labs(x="Average Profit in $millions", y="Company") +
  scale_x_continuous(labels = scales::dollar) +
  geom_text(aes(label= round(avg_profit/1000, 0)), 
            hjust= -0.2, size= 3)

Q3

In finance, it is widely believed that companies with more R&D prefer lower debt. Let’s explore whether we can observe this relationship in our data. Create a scatterplot with debt_ratio on the X axis and rnd_int on the Y axis.

d1_2 = d1 %>%
  mutate(debt_ratio = (dlc + replace_na(dltt, 0)) / at,
         rnd_int = xrd / sale)
ggplot(d1_2, aes(x=debt_ratio, y=rnd_int)) +
  geom_point() +
  geom_smooth(method="lm") +
  labs(x="Debt Ratio", y="R&D to Sales Ratio") +
  scale_x_continuous(breaks = c(0.0, 0.1, 0.2, 0.3, 0.4, 0.5))

Q4

Profit margin is simply profits divided by sales. Compare profit margins of the following six companies - Apple, IBM, Facebook, Paypal, Amazon, and Qualcomm - over the full sample period. Use fyear on the X axis. fyear is the fiscal year.

d1_4 <- d1 %>% 
  filter(tic %in% c(
    "AAPL", "FB", "IBM", "PYPL", "AMZN", "QCOM")) %>% 
  mutate(pr_margin = oibdp / sale,
         fyear = as.Date(paste(fyear, "12", "31", sep = "-")))
ggplot(d1_4, aes(x=fyear, y=pr_margin)) +
  geom_line() +
  facet_wrap(conm ~.) +
  labs(x="Fiscal Year", y="Profit Margin") +
  scale_y_continuous(labels = scales::percent)

Q5

Tesla is the largest car manufacturer in the world by market value. But what about sales? Let’s compare sales and market value over the 10/11 years period in our sample.

First create a data frame that you can use to create a plot where you can compare sales and market value in the same plot. This requires rearranging the data into “long” form, where we will stack Tesla’s sales and market value on top of each other.

d1_5 <- d1 %>% 
  filter(conm == "Tesla Inc") %>% 
  mutate(mkt_val = prcc_f * cshpri) %>% # Create market value
  select(conm, datadate, mkt_val, sale) %>% 
  pivot_longer(cols = c(mkt_val, sale), 
               names_to = "fin_var", 
               values_to = "fin_value")

Print first few rows of d1_5 to understand what this data set is.Now using d1_5, create the following plot using datadate on the X axis:

d1_5 %>%
  head(5)
## # A tibble: 5 x 4
##   conm      datadate   fin_var fin_value
##   <chr>     <date>     <chr>       <dbl>
## 1 Tesla Inc 2010-12-31 mkt_val     2481.
## 2 Tesla Inc 2010-12-31 sale         117.
## 3 Tesla Inc 2011-12-31 mkt_val     2867.
## 4 Tesla Inc 2011-12-31 sale         204.
## 5 Tesla Inc 2012-12-31 mkt_val     3636.
ggplot(d1_5, aes(x=datadate, y=fin_value)) +
  geom_line(aes(color = fin_var)) +
  labs(x= "Date", y= element_blank(), 
       color = "Financial Variable") +
  scale_y_continuous(labels = scales::dollar_format(
    prefix = "$")) +
  theme(legend.position = "top")

Q6

When the time variable is discrete, we can also show a time trend using a bar plot. This is quite common in practice. fyear is an integer so we can use it as a discrete variable and create a bar plot of profits for Facebook and IBM as shown below. Manually change the fill of bars using the following colors: c("#5cc9f5", "#b131a2")

d1_6 = d1 %>%
  filter(tic == "FB" | tic == "IBM")
ggplot(d1_6, aes(x= fyear, y= oibdp, fill = conm)) +
  geom_bar(stat="identity") +
  facet_wrap(.~ conm, nrow=2) +
  scale_fill_manual(values = c("#5cc9f5", "#b131a2")) +
  theme(legend.title = element_blank(), 
        legend.position = "top",
        panel.grid.minor.x = element_blank()) +
  labs(x = "Fiscal Year", y = "Profits in $ million") +
  scale_x_continuous(breaks = seq(2010, 2020, 1))

Q7

Use Mark Zuckerberg’s cutout to create the following visualization. You are free to position the picture anywhere and in size you want. Just don’t cover the bars.

d1_7 = d1 %>%
  filter(tic == "FB")
library(png)
ggplot(d1_7, aes(x= fyear, y= oibdp, fill = conm))+
  geom_bar(stat="identity") +
  scale_fill_manual(values = "#5cc9f5") +
  theme(legend.position = "none",
        panel.grid.minor.x = element_blank()) +
  annotation_raster(readPNG(
    "mark-zuckerberg-celebrity-mask.png"), 
    xmin = 2013, xmax= 2015, ymin= 25000, ymax= 35000,
    interpolate = T) +
  labs(x = "Fiscal Year", y = "Profits in $ million") +
  scale_x_continuous(breaks = seq(2010, 2020, 1))

Bonus Graph

d1_8 = d1 %>%
  filter(tic %in% c("FB", "TSLA", "AAPL"))
d1_8$datadate = as.Date(d1_8$datadate)
ggplot(d1_8, aes(x=datadate, y= mkvalt)) +
  geom_line(aes(color = conm)) +
    scale_y_continuous("Market Value in $ million", 
        labels = scales::dollar_format(prefix = "$")) +
  scale_x_date(element_blank(),
        limits = as.Date(c('2011-01-01','2023-01-01')),
        date_breaks = "2 years",
        labels = lubridate::year) +
  theme(axis.title.y = element_text(hjust = 1, size = 9))
## Warning: Removed 4 row(s) containing missing values (geom_path).