Exploratory Analysis of US Fortune Companies 2023/24
240032297
https://github.com/mt5763standrews/individual-assignment-2-latb1
1 Introduction
In this report we perform an exploratory analysis of data gathered by Fortune Magazine on key company data from the years 2023 and 2024. We are focusing on key variables including Market Cap, Economic Sector, Profits, Revenue, Assets, Employees and the proportion of female CEOs.
Our goal is to uncover relationships between these variables, to understand how these differ between the two years and to fit a linear model for each year that takes into account Revenue, Economic Sector, Profits and Assets to predict company Market Cap.
Finally, we attempt to explain these data and explore how these findings could be used to inform investment decisions in the future.
2 Methods
The data used in this analysis was collected by Fortune Magazine in 2023 and 2024, detailing key values of company performance, as well as general information. The selection criterium for inclusing in their list was company revenue. The data for 2023 contains 1000 values with 31 variables, whilst the data for 2024 contains 500 values with 16 variables.
We decided to compare only the top 500 companies from each year by revenue. This is because the top 500 companies differ significantly in measures of size and other variables to the next biggest 500, meaning that the inclusion of the latter will skew the data significantly and lead us to false conclusions about the top performers in the US economy. Moreover, we don’t want the data from the year 2023 to dominate our analysis and would rather have a more balanced representation of data as this increases our chance of finding meaningful relationships that hold up in a larger time window.
Our linear models consider the following variables: Economic Sector - Role of the company in terms of output, ranked from primary to quaternary Revenue - Total amount of income of a company from sales and services provided Profits - Net income of a company Assets - Total value of company holdings
We used these variables to predict Market Cap. We fitted separate linear models for each year using the log of Market Cap as the response variable. Our predictors were Economic Sector, Profits, natural log of Assets and natural log of Revenue.
We used R-Studio version 2024.04.2 (R version 4.4.1) to perform our analysis.
3 Results
Data Visualization
Distribution of company revenues between 2023-24
[Fig 2.1] Density plot for the difference in revenue distribution between 2023 and 2024
[Fig 2.2] Boxplots for the difference in revenue distribution between 2023 and 2024 (left) Boxplot containing all data, (right) boxplot showing Revenue < 40000.
The revenue distribution density plot [Fig. 2.1] shows a large spike around the lower end of the distribution and a long tail to the right for both years, which tells us that most companies had comparatively lower revenues whilst a few companies had outstanding revenues. There is also a lower peak for 2024, meaning that that company revenue is spread out slightly more evenly.
Additionally, the boxplots [Fig. 2.2] show us that the median revenue in 2024 is slightly higher than in 2023, whilst the variability is skews more upwards in 2024. They also show us that there are quite a lot of outliers who performed exceptionally well in both years, whilst the median revenue is comparatively low.
Proportion of Female CEOs
[Fig 3.1] Proportion on female CEOs compared between 2023 and 2024. Note: figure shows values between 0% and 25% for better visibility of the difference between the values
The bar plot [Fig 3.1] shows us that the proportion of female CEOs in the top 500 companies decreased from around 10.4% to 10.2%.
Profit vs Market Cap
[Fig 4.1] Relationship between profit and market value
The scatterplot [Fig. 4.1] expresses the relationship between profit and market value. We can see that there seems to be a positive and mostly linear relationship between the two variables in both years, although the graph for 2024 suggests a decrease in the power of the relationship as market cap increases in relation to 2023.
That said, we must be careful not to ignore outliers, especially in the more scarcely populated area to the right-hand side of the graph. Especially in the year 2024 there seems to be a lot of variability between values as market cap increases. This fact, in combination with the scarcity of data about these extremes, makes it hard to draw meaningful conclusions about the relationship between profits and market cap for companies that vastly differ from the norm.
Employees across Economic Sectors
[Fig 5.1] Number of employees across different economic sectors
From the bar plot [Fig. 5.1] we can surmise that the tertiary sector (service industry) employs by far the most people. Meanwhile, the secondary and quaternary sectors both employ a similar amount of people but only a fraction of the tertiary sector, whilst the primary sector employs only very few people. It is also notable that the absolute change in the number of employees was quite small in the primary and secondary sectors, whilst there are notable increases in employee numbers in bioth the tertiary and quaternary sectors.
Relationship between Market Cap, Assets and Revenue
[Fig 6.1] (left) marketcap vs assets, (middle) marketcap vs revenue, (right) revenue vs assets between 2023 and 2024
[Fig 6.2] Version of Fig. 6.1. showing Revenue < 200,000 and Assets < 400,000. (left) marketcap vs assets, (middle) marketcap vs revenue, (right) revenue vs assets between 2023 and 2024
[Fig 6.3] Bubble chart of market cap, assets and revenue between 2023 and 2024
[Fig 6.4] Bubble chart of market cap, assets and revenue between 2023 and 2024 (limits Assets < 500,000 and Revenue < 500,000)
[Fig 6.5] Bubble chart of market cap, assets and revenue between 2023 and 2024 (limits Assets < 200,000 and Revenue < 200,000)
Judging from the scatterplots showing the associations between the three variables [Fig. 6.1] & [Fig. 6.2], there seems to be an upward trend in the graphs, pointing to a positive relationship between all three variables. However, these relationships become unstable with a very large variance once we reach a certain threshold, with an overall downward trend. This can partially be attributed to the lack of data around these extremes, but it is likely that te relationship does become much weaker once we reach larger values.
The relationship between revenue and market cap seems to be the weakest overall as values are more uniformly distributed, whilst that between assets and market cap and assets and revenue look stronger. We can also see this relationship in the bubble chart [Fig. 6.3], which shows that revenue can be very large independent of market cap, whilst there is a stronger relationship between revenue and assets. Additionally, the graph showing the majority of companies towards the lower end of values [Fig. 6.5] tells us that at the lower end there is a relationship between assets and market cap.
Between the two years, the relationships between the variables stay positive and similar. There is a large difference in the plots showing assets and revenue and a smaller difference in the other plots, though these can likely be explained by the variability in the data.
Statistical Modelling
We performed statistical modelling between the market value and several predictors: economic sector, profits, assets and revenue.
Fitting Linear Models for Data from 2023-24
##
## Call:
## lm(formula = log(MarketCap) ~ EconomicSector + Profits + log(Assets) +
## log(Revenue), data = fortune23)
##
## Residuals:
## Min 1Q Median 3Q Max
## -7.1885 -0.5784 0.0635 0.6963 2.8186
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 0.91649381 0.35896065 2.553 0.0108 *
## EconomicSector2 0.38131208 0.11649564 3.273 0.0011 **
## EconomicSector3 -0.06896194 0.10326486 -0.668 0.5044
## EconomicSector4 0.73694952 0.12207682 6.037 0.0000000022506461 ***
## Profits 0.00003467 0.00000686 5.054 0.0000005175659313 ***
## log(Assets) 0.48260863 0.03697390 13.053 < 0.0000000000000002 ***
## log(Revenue) 0.38442308 0.05045518 7.619 0.0000000000000616 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 1.102 on 952 degrees of freedom
## (41 Beobachtungen als fehlend gelöscht)
## Multiple R-squared: 0.5158, Adjusted R-squared: 0.5128
## F-statistic: 169.1 on 6 and 952 DF, p-value: < 0.00000000000000022[Fig 7.1] Fitted linear model for data from 2023
##
## Call:
## lm(formula = log(MarketCap) ~ EconomicSector + Profits + log(Assets) +
## log(Revenue), data = fortune24)
##
## Residuals:
## Min 1Q Median 3Q Max
## -7.0128 -0.5190 0.0854 0.6726 3.0902
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 2.833275881 0.661233153 4.285 0.00002221967683627 ***
## EconomicSector2 0.369411867 0.163600741 2.258 0.024406 *
## EconomicSector3 -0.161962633 0.140823449 -1.150 0.250687
## EconomicSector4 0.946141022 0.179755935 5.263 0.00000021594186004 ***
## Profits 0.000037980 0.000006616 5.741 0.00000001696985728 ***
## log(Assets) 0.414557302 0.050688742 8.178 0.00000000000000274 ***
## log(Revenue) 0.284126021 0.076017504 3.738 0.000209 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 1.081 on 467 degrees of freedom
## (26 Beobachtungen als fehlend gelöscht)
## Multiple R-squared: 0.4794, Adjusted R-squared: 0.4728
## F-statistic: 71.69 on 6 and 467 DF, p-value: < 0.00000000000000022[Fig 7.2] Fitted linear model for data from 2024
In both models [Fig. 7.1] & [Fig. 7.2], the R-squared value is around 50% meaning that our fitted models explain about 50% of the variance in the market cap. Our p-values are extremely small, making our models statistically significant. The predictors are all significant in both years, expect for the tertiary sector. Moreover, the statistical significance of most predictors has decreased from 2023, making market cap less predictable from these variables in 2024. This is also supported by our slightly smaller R-squared values for the 2024 model.
More model discussion is found in Appendix A.
4 Discussion
Based on our findings, there are several key relationships between the variables in the company data we analysed, especially when comparing the data between 2023 and 2024.
The revenue distribution flattened somewhat between 2023 and 2024 and the median revenue decreased slightly, which means that aggregate company revenue is spread out more evenly. Assuming that these results are significant and not just random variation, we could surmise that if this trend continues, it may be recommendable to diversify our portfolio.
Moreover, we found that profits are positively correlated with market cap, which likely means that investors look to company profits as a key indicator of whether to invest. The proportion of female CEOs fell from 10.4% to 10.2% between the two years. This may be part of a wider trend, but it could also be caused by random fluctuation. An analysis of data from previous years is needed to determine which is the case.
By far the biggest proportion of employees in the top US companies is employed in the tertiary sector, which is not surprising given that the US is a post-industrial economy, and higher-order industries tend to be overrepresented in the list of top companies due to their relatively large profit margins. Furthermore, we found that the number of employees in the tertiary and quaternary sector increased significantly whilst the other sectors employ roughly the same amount of people that they did before, which means that there seems to be a shift towards higher-order sectors. This could be due to increased funding, better career opportunities or simply the fact that these sectors are growing to fill a need in the market.
From out statistical analysis of the relationship between market cap and economic sector, revenue, profits and assets, we can surmise that there is a generally positive relationship between these variables. This finding is reasonable considering that economic indicators tend to be closely linked on average, because the markets take into account these indicators when making their investment decisions. That said, there are of course outliers that over- or underperform and it is important to keep this in mind.
5 Conclusions
In summary, based on the relationship within the data and the predictive model of the market cap, there are several components one should look out for when deciding which companies to invest in in the future. Several key variables including profits, assets and revenue are important, investors seem to especially look out for profits. Year-on-year revenue distributions widened, signalling that it may be wise to diversify our portfolio. Additionally, the trend of employees seeking employment in tertiary and quaternary industries may warrant more investment in these sectors.
Due to the complicated nature of the data presented, we recommend that further research be conducted before making investment decisions. Whilst doing so, it may be recommendable to cluster the data or otherwise deal with the preponderance of overperformers which skew the data and are more variable than the vast majority of companies. Lastly, it is important to keep in mind that these companies are only the top 500 companies in the US and are thus not representative of the wider economy, so any conclusions drawn here only hold weight in relationship to the top 500 US companies by revenue as compiled by Fortune magazine, rather than the wider economy.
Appendix A
Cleaning Data
First we needed to unify both tables for the data from 2023 and 2024 so we could compare them.
To do this, we got rid of unwanted columns that are not useful in for our analysis, renamed relevant columns to sensible names and converted our data to sensible types which are useful for comparison.
We then stratified the company sectors into easily distinguishable categories from primary to quaternary producers. We also added columns containing the date of the data collection, the employee revenue and each company’s profit margin that year.
Finally, we combined the datasets. The dataset from 2023 had 1000 values, so we only included the top 500 companies from this dataset for comparison with the dataset of 500 values from 2024.
## Company State MarketCap Revenue Profits Assets Employees FounderCEO FemaleCEO
## 1 Walmart AR 484852.8 648125 15511 252399 2100000 FALSE FALSE
## 2 Amazon WA 1873675.8 574785 30425 527854 1525000 FALSE FALSE
## 3 Apple CA 2647973.8 383285 96995 352583 161000 FALSE FALSE
## Date EconomicSector RevenueEmployee ProfitMargin
## 1 2024-03-31 3 0.3086310 0.02393211
## 2 2024-03-31 3 0.3769082 0.05293284
## 3 2024-03-31 4 2.3806522 0.25306234[Fig 1.1] Table of cleaned data for 2024
## Company State MarketCap Revenue Profits Assets Employees FounderCEO
## 1 Walmart AR 397475 611289 11680 243197 2100000 FALSE
## 2 Amazon WA 1058440 513983 -2722 462675 1541000 FALSE
## 3 Exxon Mobil TX 446424 413680 55740 369067 62000 FALSE
## FemaleCEO Date EconomicSector RevenueEmployee ProfitMargin
## 1 FALSE 2023-03-31 3 0.2910900 0.019107165
## 2 FALSE 2023-03-31 3 0.3335386 -0.005295895
## 3 FALSE 2023-03-31 1 6.6722581 0.134741829[Fig 1.1] Table of cleaned data for 2023
Confidence Intervals for Regression Coefficients
## 1.5 % 98.5 %
## (Intercept) 0.13634715629 1.69664047214
## EconomicSector2 0.12812643504 0.63449773265
## EconomicSector3 -0.29339248090 0.15546860530
## EconomicSector4 0.47163401653 1.00226503244
## Profits 0.00001976303 0.00004957964
## log(Assets) 0.40225144495 0.56296581607
## log(Revenue) 0.27476638512 0.49407977963[Fig 7.3] 97% Confidence intervals for the estimated regression coefficients (2023)
## 1.5 % 98.5 %
## (Intercept) 1.39394148971 4.27261027202
## EconomicSector2 0.01329515999 0.72552857454
## EconomicSector3 -0.46849903860 0.14457377188
## EconomicSector4 0.55485861626 1.33742342685
## Profits 0.00002357933 0.00005238017
## log(Assets) 0.30422095581 0.52489364888
## log(Revenue) 0.11865547876 0.44959656289[Fig 7.4] 97% Confidence intervals for the estimated regression coefficients (2024)
Reliability of confidence intervals
Using a linear model comes with several key assumptions, including a true linear relationship between predictors and outcomes and constant variance in their relationships, as well as normality of errors and independence of residuals.
We can conduct formal statistical tests to test our assumptions.
Testing for normality of errors using Wilk-Shapiro test
##
## Shapiro-Wilk normality test
##
## data: residuals(lm23)
## W = 0.93361, p-value < 0.00000000000000022##
## Shapiro-Wilk normality test
##
## data: residuals(lm24)
## W = 0.93069, p-value = 0.00000000000005176This test revealed that errors are not normally distributed for our models.
Testing for constant variance using Breusch-Pagan test
## Non-constant Variance Score Test
## Variance formula: ~ fitted.values
## Chisquare = 30.63659, Df = 1, p = 0.000000031117## Non-constant Variance Score Test
## Variance formula: ~ fitted.values
## Chisquare = 1.614685, Df = 1, p = 0.20383The variance for the model of 2024 data has constant variance, whilst the model for 2023 data does not.
Testing for independence of errors using Durbin-Watson test
## lag Autocorrelation D-W Statistic p-value
## 1 -0.002080264 2.003903 0.972
## Alternative hypothesis: rho != 0## lag Autocorrelation D-W Statistic p-value
## 1 0.04000898 1.915441 0.316
## Alternative hypothesis: rho != 0Both models have independent errors.
Testing for linearity of errors using component/residual plots
These plots suggest that in both models, the predictors Assets and Revenue have a fairly linear relationship, whilst profits jump very high around the lower end of values and then become fairly stable. This means that linearity of errors assumption has been violated for Profits.
Summing up, our model assumptions of error independence and linearity hold up fairly well, whilst our assumption of normality of errors is severely violated and our assumption of constant variance only holds up for one of the models. This means that a linear model may not be the ideal candidate for this type of data and that our confidence intervals do not describe the data very accurately. Given that several of the relationships seem to be more parabolic or at least curved, we should consider fitting other types of models. We could also consider a non-parametric approach.
## fit lwr upr
## 1 11.12764 10.78478 11.4705[Fig 7.5] Test of the model’s predictive capabilities with the following parameters: quaternary sector, 100,000 employees, revenue of $100,000MM, loss of $5,000MM and assets of $50,000MM (2023)
## fit lwr upr
## 1 11.34606 10.89126 11.80085[Fig 7.6] Test of the model’s predictive capabilities with the following parameters: quaternary sector, 100,000 employees, revenue of $100,000MM, loss of $5,000MM and assets of $50,000MM (2024)
Reliability of model
## Employees Revenue Profits Assets Market Cap
## 5% 29605.19 17119.51 1322.200 42982.55 29964.17
## 97% 43323.04 23428.93 2128.896 79286.94 49380.18
## Inputs 100000.00 100000.00 -5000.000 50000.00 84120.03[Fig 7.7] Confidence intervals and given input values of predictors for data from 2023
At first glance, the two results of ~11 billion seems like a fair estimate knowing the data, but there are some caveats. Firstly, it should be noted that our model does not consider employees as a predictor and so including it in our prediction input may give misleading results, if it has statistical significance.
Secondly, the table in [Fig. 7.7] shows us that two of the given input values for our prediction fall well outside the confidence intervals of the individual predictors independently from our model, though Profits and Assets fall within or relatively close to our confidence intervals. This could suggest that the input values are extremes, i.e. outliers, within the data. Therefore, our model may not have sufficient training data to take these values into account, leading to inaccurate extrapolation. Lastly, whilst the predicted value for the market cap also does not fall within the 97% confidence interval of our data, its range is quite large and therefore it is unclear whether the result is reasonable.
In summary, our model likely has some predictive capabilities, but it struggles to give accurate results for data outside of a certain range. This is likely because of powerful outliers in the data, which call into question the validity of a linear model in this circumstance.