Final Project – Project 2 on Market Prices

———

For my final project, I will use a dataset that contains information on 2000 companies, published by Forbes. I’m interested in the profits of companies and how they relate to company categories and geographies. By observing patterns and building a multiple linear regression model, I will better understand the relationship between profits and other characteristics of companies.

Step one: exploring the relationship between profits and other variables in the dataset.

Read in the data and remove and rows that have an NA

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(ggplot2)

forbes <- read.csv("Forbes2000.csv")
forbes <- na.omit(forbes)

str(forbes)
## 'data.frame':    1995 obs. of  9 variables:
##  $ X          : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ rank       : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ name       : chr  "Citigroup" "General Electric" "American Intl Group" "ExxonMobil" ...
##  $ country    : chr  "United States" "United States" "United States" "United States" ...
##  $ category   : chr  "Banking" "Conglomerates" "Insurance" "Oil & gas operations" ...
##  $ sales      : num  94.7 134.2 76.7 222.9 232.6 ...
##  $ profits    : num  17.85 15.59 6.46 20.96 10.27 ...
##  $ assets     : num  1264 627 648 167 178 ...
##  $ marketvalue: num  255 329 195 277 174 ...
##  - attr(*, "na.action")= 'omit' Named int [1:5] 772 1085 1091 1425 1909
##   ..- attr(*, "names")= chr [1:5] "772" "1085" "1091" "1425" ...
head(forbes)
##   X rank                name        country             category  sales profits
## 1 1    1           Citigroup  United States              Banking  94.71   17.85
## 2 2    2    General Electric  United States        Conglomerates 134.19   15.59
## 3 3    3 American Intl Group  United States            Insurance  76.66    6.46
## 4 4    4          ExxonMobil  United States Oil & gas operations 222.88   20.96
## 5 5    5                  BP United Kingdom Oil & gas operations 232.57   10.27
## 6 6    6     Bank of America  United States              Banking  49.01   10.81
##    assets marketvalue
## 1 1264.03      255.30
## 2  626.93      328.54
## 3  647.66      194.87
## 4  166.99      277.02
## 5  177.57      173.54
## 6  736.45      117.55
summary(forbes)
##        X               rank            name             country         
##  Min.   :   1.0   Min.   :   1.0   Length:1995        Length:1995       
##  1st Qu.: 499.5   1st Qu.: 499.5   Class :character   Class :character  
##  Median : 999.0   Median : 999.0   Mode  :character   Mode  :character  
##  Mean   : 999.9   Mean   : 999.9                                        
##  3rd Qu.:1500.5   3rd Qu.:1500.5                                        
##  Max.   :2000.0   Max.   :2000.0                                        
##    category             sales            profits             assets       
##  Length:1995        Min.   :  0.010   Min.   :-25.8300   Min.   :   0.27  
##  Class :character   1st Qu.:  2.010   1st Qu.:  0.0800   1st Qu.:   4.02  
##  Mode  :character   Median :  4.360   Median :  0.2000   Median :   9.33  
##                     Mean   :  9.709   Mean   :  0.3811   Mean   :  34.07  
##                     3rd Qu.:  9.575   3rd Qu.:  0.4400   3rd Qu.:  22.75  
##                     Max.   :256.330   Max.   : 20.9600   Max.   :1264.03  
##   marketvalue    
##  Min.   :  0.02  
##  1st Qu.:  2.72  
##  Median :  5.15  
##  Mean   : 11.90  
##  3rd Qu.: 10.62  
##  Max.   :328.54

1. Which company types generate the highest and lowest profits?

Calculate total profits by category

profits_category <- forbes %>%
  group_by(category) %>%
  summarise(total_profits = sum(profits))

Plot profits by category

ggplot(profits_category, aes(x = reorder(category, total_profits), y = total_profits)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Total Profits by Company Category", x = "Category", y = "Annual Profits ($M)")

The top three categories of companies for generating annual profit are banking, oil and gas, and diversified financials. The lowest three in generating profits are capital goods, trading companies, and telecommunication services (which is the only category that is losing money).

2. Which country generates the most profits?

Calculate total profits by country

profits_country <- forbes %>%
  group_by(country) %>%
  summarise(total_profits = sum(profits))

Plot profits by country

ggplot(profits_country, aes(x = reorder(country, total_profits), y = total_profits)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Total Profits by Country", x = "Country", y = "Annual Profits ($M)")

Companies in the U.S. are generating over ten times as much as any other country, therefore, I will log-transform total profits, to produce a more helpful visualization.

Plot log-transformed profits by country

ggplot(profits_country, aes(x = reorder(country, total_profits), y = log(total_profits))) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Log-Transformed Annual Profits by Country", x = "Country", y = "Log(Annual Profits)")
## Warning in log(total_profits): NaNs produced
## Warning in log(total_profits): NaNs produced
## Warning: Removed 6 rows containing missing values (`position_stack()`).

The top three profit-generating countries are the U.S., Canada, and the U.K.

3. Which company and country generate the most sales?

Calculate total sales by category and country

sales_category <- forbes %>% 
  group_by(category) %>% 
  summarise(total_sales = sum(sales))

sales_country <- forbes %>%
  group_by(country) %>%
  summarise(total_sales = sum(sales))

Plot sales by category and country

ggplot(sales_category, aes(x = reorder(category, total_sales), y = total_sales)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Annual Sales by Company Category", x = "Category", y = "Annual Sales ($M)")

ggplot(sales_country, aes(x = reorder(country, total_sales), y = total_sales)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Annual Sales by Country", x = "Country", y = "Annual Sales ($M)")

As for sales, the company categories that have the highest sales are consumer durables, oil and gas, and banking. The lowest are hotels, restaurants and leisure, software and services, and semiconductors.

The countries with the highest sales are the U.S., Japan, and the U.K.

———

Step two: Compare the U.S. and Japan.

1. Which country has the highest rank using Forbes ranking?

Filter data for the U.S. and Japan

us_japan <- forbes %>%
  filter(country %in% c("United States", "Japan"))

Find the highest rank for the U.S. and Japan

highest_ranks<- us_japan %>%
  group_by(country) %>%
  summarise(highest_rank = min(rank))
print(highest_ranks)
## # A tibble: 2 × 2
##   country       highest_rank
##   <chr>                <int>
## 1 Japan                    8
## 2 United States            1

OR calculate the mean rank for each country

mean_ranks <- us_japan %>%
  group_by(country) %>%
  summarise(mean_rank = mean(rank))
print(mean_ranks)
## # A tibble: 2 × 2
##   country       mean_rank
##   <chr>             <dbl>
## 1 Japan             1144.
## 2 United States      945.

The U.S. (1) has the higher Forbes ranking for an individual company compared to Japan (8). American companies have a higher average ranking (945) compared to Japanese companies (1144).

2. Which company types are more common in the USA? In Japan?

Count the number of companies by category for each country

category_counts <- us_japan %>%
  group_by(country, category) %>%
  summarise(count = n()) %>%
  arrange(country, desc(count))
## `summarise()` has grouped output by 'country'. You can override using the
## `.groups` argument.
print(category_counts)
## # A tibble: 50 × 3
## # Groups:   country [2]
##    country category                     count
##    <chr>   <chr>                        <int>
##  1 Japan   Banking                         69
##  2 Japan   Diversified financials          24
##  3 Japan   Consumer durables               22
##  4 Japan   Transportation                  20
##  5 Japan   Capital goods                   19
##  6 Japan   Construction                    18
##  7 Japan   Business services & supplies    17
##  8 Japan   Chemicals                       14
##  9 Japan   Trading companies               13
## 10 Japan   Materials                       12
## # ℹ 40 more rows

Plot the results

ggplot(category_counts, aes(x = reorder(category, count), y = count, fill = country)) +
  geom_bar(stat = "identity", position = "dodge") +
  coord_flip() +
  labs(title = "Company Company in the U.S. and Japan",
       x = "Company Category",
       y = "Count",
       fill = "Country") +
  scale_fill_manual(values = c("darkblue", "darkorange")) +
  theme(plot.title = element_text(hjust = 0.5),
        legend.position = "top")

For the U.S., the most common company types are banking, diversified financials, and utilities. For Japan, the most common banking, diversified financials, and consumer durables.

———

Step three: Build a multiple linear regression model to estimate profits using assets, market value and sales. Use the summary() function to find the coefficients and goodness-of-fit of the model. Use the anova() function to identify which variable appears to have the greatest effect on profits. Look at the distribution of residuals.

1. Build the multiple linear regression model

fit1 <- lm(profits ~ assets + marketvalue + sales, data = forbes)
summary(fit1)
## 
## Call:
## lm(formula = profits ~ assets + marketvalue + sales, data = forbes)
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -29.2169  -0.0189   0.1160   0.2107   8.9495 
## 
## Coefficients:
##               Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -0.1186259  0.0380039  -3.121  0.00183 ** 
## assets      -0.0008395  0.0003781  -2.220  0.02651 *  
## marketvalue  0.0363340  0.0018183  19.982  < 2e-16 ***
## sales        0.0098892  0.0024331   4.064    5e-05 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 1.472 on 1991 degrees of freedom
## Multiple R-squared:  0.3059, Adjusted R-squared:  0.3049 
## F-statistic: 292.5 on 3 and 1991 DF,  p-value: < 2.2e-16

The high t-value and low p-value for Market Value represents statistical significance.

As for the overall model, the large f-value and small p-value suggest the model may be useful. However, the low r-squared indicates the model may not fit the input data well. However, given the large data set, r-squared may not be a useful indicator for judging the fit of the model.

2. Use ANOVA to identify which variable has the greatest effect on profits

anova(fit1)
## Analysis of Variance Table
## 
## Response: profits
##               Df Sum Sq Mean Sq F value    Pr(>F)    
## assets         1  312.8  312.84  144.40 < 2.2e-16 ***
## marketvalue    1 1552.8 1552.77  716.71 < 2.2e-16 ***
## sales          1   35.8   35.79   16.52 5.001e-05 ***
## Residuals   1991 4313.6    2.17                      
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

The large f-value and small p-value for Market Value variable suggests that it might be the best explanatory variable.

3. Check the distribution of residuals

residuals <- residuals(fit1)
hist(residuals, main = "Distribution of Residuals")

plot(fit1, which = 1)

plot(fit1, which = 4)

The histogram of the residuals is not well-distributed, and the diagnostic plots indicate that there may be some bias in the model.

———

Step four: Build two models using the same variables but for Japanese and American companies. Use the anova() function to look at which variables are the most important for each region? What differences do you observe?

Filter data for the U.S. and Japan

us <- forbes %>% filter(country == "United States")
japan <- forbes %>% filter(country == "Japan")

1. Build the multiple linear regression models for each country

us_fit1 <- lm(profits ~ assets + marketvalue + sales, data = us)
japan_fit1 <- lm(profits ~ assets + marketvalue + sales, data = japan)

summary(us_fit1)
## 
## Call:
## lm(formula = profits ~ assets + marketvalue + sales, data = us)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -4.4954 -0.0620  0.1217  0.1953  7.8811 
## 
## Coefficients:
##               Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -0.1465337  0.0336610  -4.353 1.53e-05 ***
## assets       0.0043831  0.0003655  11.993  < 2e-16 ***
## marketvalue  0.0339639  0.0012747  26.645  < 2e-16 ***
## sales        0.0138408  0.0020376   6.793 2.25e-11 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.8118 on 744 degrees of freedom
## Multiple R-squared:  0.795,  Adjusted R-squared:  0.7942 
## F-statistic: 961.8 on 3 and 744 DF,  p-value: < 2.2e-16
summary(japan_fit1)
## 
## Call:
## lm(formula = profits ~ assets + marketvalue + sales, data = japan)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -8.2316 -0.1651  0.0184  0.2160  5.3909 
## 
## Coefficients:
##               Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -0.0731901  0.0551181  -1.328    0.185    
## assets      -0.0126813  0.0004999 -25.368   <2e-16 ***
## marketvalue  0.0765111  0.0062517  12.239   <2e-16 ***
## sales       -0.0006585  0.0034725  -0.190    0.850    
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.8108 on 312 degrees of freedom
## Multiple R-squared:  0.6857, Adjusted R-squared:  0.6827 
## F-statistic: 226.9 on 3 and 312 DF,  p-value: < 2.2e-16

Looking at the r-squared, f-value, and p-value for both models, it looks like the model is a better fit for the individual countries (U.S. and Japan) compared to the model for all countries.

2. Perform ANOVA to identify the most important variables for each country

anova(us_fit1)
## Analysis of Variance Table
## 
## Response: profits
##              Df Sum Sq Mean Sq  F value    Pr(>F)    
## assets        1 975.23  975.23 1479.783 < 2.2e-16 ***
## marketvalue   1 895.96  895.96 1359.498 < 2.2e-16 ***
## sales         1  30.41   30.41   46.142 2.253e-11 ***
## Residuals   744 490.32    0.66                       
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
anova(japan_fit1)
## Analysis of Variance Table
## 
## Response: profits
##              Df  Sum Sq Mean Sq F value Pr(>F)    
## assets        1 284.846 284.846 433.311 <2e-16 ***
## marketvalue   1 162.574 162.574 247.310 <2e-16 ***
## sales         1   0.024   0.024   0.036 0.8497    
## Residuals   312 205.100   0.657                   
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Both models for the U.S. and Japan have high f-values and low p-values for both Assets and Market Value, indicating that these variables are better explanatory variables (explain variance) of profit, compared to the Sales variable.

residuals <- residuals(us_fit1)
hist(residuals, main = "Distribution of Residuals for the U.S.")

plot(us_fit1, which = 1)

plot(us_fit1, which = 4)

residuals <- residuals(japan_fit1)
hist(residuals, main = "Distribution of Residuals for Japan")

plot(japan_fit1, which = 1)

plot(japan_fit1, which = 4)

Both models’ residuals are normally distributed, suggesting that the models for individual countries do a good job of capturing the relationship between the tested variables and profit. The models could be improved by removing the sales variable.

———

Conclusion

Looking at the statistical analysis using linear regression models, market value and assets are statistically significant indicators of a company’s total annual profits. Interestingly, sales are not significant indicator variables. This may be the case for several reasons. First, market value represents the overall value of a company which considers it’s place within the stock market, based on aspects like growth, competition, and industry trends. Assets represent the total capital resources owned by a company. Sales, on the other hand, are not necessarily correlative to a company’s profits, given the volatility of sales and markets year to year. This relationship may be due to the fact that these are all publicly-traded companies and rely on many metrics beyond sales to explain the annual profit of a company.

This was a useful exercise in combining the analytic and interpretive skills gained from this Intro to R course. It required selecting subsets of a dataset, visualizing data using ggplot2, performing basic statistical analysis, and interpreting the results for variance and explanatory characteristics. I came away with a better grasp of performing basic R functions on a quantitative dataset. Thanks Simon for a good class!

———