BEFIT (Base Erosion and Profit Shifting Inclusive Framework for Tax) Analysis
All programming by John Akwei, Senior Data Scientist
2025-05-23
Section 1: Data Preparation and Exploration
In this section, the working directory is set to the folder with the project files, the required R language libraries are imported, the project files are imported as dataframes, the original tax expense from the consolidated financial statements are derived, and the total number of company groups are displayed.
# Set working directory
setwd("C:/Users/johnakwei/Dropbox/Programming/Science")
# Load required libraries
library(dplyr)
library(readr)
library(stringr)
# install.packages("countrycode")
library(countrycode)
library(knitr)
library(grDevices)
library(ggplot2)
library(corrplot)
# Load datasets
consolidated_fs <- read_csv("BMW_Group_Subsidiaries_Report2.csv")
statutory_rates <- read_csv("Statutory_Rates.csv")
annual_report <- read_csv("BMW_Group_Subsidiaries_Annual_Report.csv")
subsidiaries_new <- read_csv("subsidiaries3.csv")
# Calculate original tax expense for each company
original_tax_expense <- consolidated_fs %>%
group_by(Company_name) %>%
summarise(
pre_tax_income = PL_before_tax_Last_avail_yr,
net_income = PL_for_period_Last_avail_yr,
original_tax_expense = pre_tax_income - net_income
)
# Display the number of company groups
num_groups <- nrow(original_tax_expense)
cat("Number of company groups:", num_groups, "\n")## Number of company groups: 75
Section 2: Merge Data
The following code section focuses on tax-related data manipulation and calculations for the consolidated companies. It begins by merging subsidiary data with ultimate owner information and proceeds to calculate formulary apportionment factors for distributing the tax base. The code then applies these factors to distribute pre-tax income and standardizes ISO code formats. It further filters specific countries, appends statutory tax rates, and applies a global minimum tax rate of 15% for groups with revenues greater than 750 million. Finally, it calculates tax expenses and summarizes them by company. The resulting data is saved as a CSV file. This code section provides a comprehensive process for analyzing tax-related metrics and ensuring tax compliance within the consolidated company’s operations.
# Merge subsidiary data with ultimate owner information at the company level
merged_data <- consolidated_fs %>%
left_join(subsidiaries_new, by = c("Company_ID"), relationship = "many-to-many")
merged_data <- merged_data %>%
left_join(original_tax_expense, by = "Company_name")
# Calculate formulary apportionment factors for each company
merged_data <- merged_data %>%
group_by(Company_ID) %>%
mutate(
total_assets = sum(Total_assets_Last_avail_yr, na.rm = TRUE),
total_employees = sum(Number_of_employees_Last_avail_yr, na.rm = TRUE),
total_revenue = sum(Operating_revenue_Last_avail_yr, na.rm = TRUE),
asset_factor = Total_assets_Last_avail_yr / total_assets,
employee_factor = Number_of_employees_Last_avail_yr / total_employees,
sales_factor = Operating_revenue_Last_avail_yr / total_revenue
)
merged_data <- merged_data[merged_data$Company_ID == c(27,41,17,23,57,28,33,43,37,7,51,9),]
# Distribute pre-tax income based on formulary apportionment for each company
merged_data <- merged_data %>%
group_by(Company_ID) %>%
mutate(apportioned_income = PL_before_tax_Last_avail_yr * (asset_factor + employee_factor + sales_factor) / 3)
# Standardize ISO code formats to 3-character codes
merged_data <- merged_data %>%
mutate(Country_ISO_code = countrycode(Country_ISO_code, "iso2c", "iso3c"))
all_iso_codes <- statutory_rates[, c(1, 4)]
# Convert the first list to a character vector
countries_to_keep <- c("FRA", "NLD", "DEU", "ITA", "POL", "LUX", "ESP", "IRL", "BEL", "SWE", "AUT", "HUN", "FIN")
# Get the indices of the matching elements
indices <- match(countries_to_keep, all_iso_codes$ISO3)
# Subset the second list based on the indices
all_iso_codes <- all_iso_codes[indices,]
# Append Tax_Rate column from statutory_rates to merged_data
merged_data <- merged_data %>%
left_join(all_iso_codes, by = c("Country_ISO_code" = "ISO3"))
# Convert Tax_Rate to numeric
merged_data$Tax_Rate <- as.numeric(gsub("%", "", merged_data$Tax_Rate))
# Apply global minimum tax rate of 15% for groups with revenues > 750 million
merged_data <- merged_data %>%
group_by(Company_ID) %>%
mutate(Tax_Rate = ifelse(total_revenue > 750000000 & Tax_Rate < 15, 15, Tax_Rate))
# Apply statutory tax rates and calculate tax expense after BEFIT for each company
merged_data <- merged_data %>%
group_by(Company_ID) %>%
mutate(befit_tax_expense = apportioned_income * Tax_Rate)
# Calculate total tax expense after BEFIT for each company
befit_tax_expense_by_company <- merged_data %>%
group_by(Company_ID) %>%
summarise(befit_tax_expense = sum(befit_tax_expense, na.rm = TRUE))
# Save merged_data file
write.csv(merged_data, "BMW_Group_Merged_Data5.csv")
Section 3: Summary Statistics
This section calculates and displays summary statistics such as the total revenue, total pre-tax income, total tax expense (pre-BEFIT and post-BEFIT), and effective tax rates (pre-BEFIT and post-BEFIT) for all the company groups.
| Company_ID | total_revenue | total_pre_tax_income | total_tax_expense_pre_befit | total_tax_expense_post_befit | effective_tax_rate_pre_befit | effective_tax_rate_post_befit |
|---|---|---|---|---|---|---|
| 7 | 3713658 | 611442 | 226330 | 54675.891 | 0.3701578 | 0.0894212 |
| 9 | 3279360 | 120085 | 25025 | 7411.945 | 0.2083941 | 0.0617225 |
| 17 | 1137222 | 97434 | 15894 | 11970.463 | 0.1631258 | 0.1228571 |
| 23 | 1288012 | 209528 | 78210 | 19618.727 | 0.3732675 | 0.0936330 |
| 27 | 3469248 | -114624 | 93376 | -4463.752 | -0.8146287 | 0.0389426 |
| 28 | 2894171 | 236486 | 66727 | 9619.580 | 0.2821605 | 0.0406772 |
| 33 | 2100648 | 78672 | 22440 | 4436.268 | 0.2852349 | 0.0563894 |
| 37 | 1482110 | 39200 | 9660 | 2777.520 | 0.2464286 | 0.0708551 |
| 41 | 972972 | 29052 | 9801 | 2305.714 | 0.3373606 | 0.0793651 |
| 43 | 3203328 | 482784 | 113472 | 5233.998 | 0.2350368 | 0.0108413 |
| 51 | 825120 | -2709390 | -2706720 | -193860.232 | 0.9990145 | 0.0715512 |
| 57 | 90336 | 31044 | 3756 | 14832.133 | 0.1209896 | 0.4777778 |
Section 4: Table of Change in Tax Expense
This section creates a table showing the pre-BEFIT and post-BEFIT tax expense for each company group, along with the absolute and percentage change in tax expense. Calculates summary statistics (mean, median, minimum, maximum, etc.) on the change in tax expense. The original tax expense is derived from the consolidated financial statements (pre-tax income vs. net income).
| Company_ID | pre_befit_tax_expense | post_befit_tax_expense | change_in_tax_expense | percentage_change |
|---|---|---|---|---|
| 7 | 226330 | 54675.891 | -171654.109 | -75.84240 |
| 9 | 25025 | 7411.945 | -17613.055 | -70.38184 |
| 17 | 15894 | 11970.463 | -3923.537 | -24.68565 |
| 23 | 78210 | 19618.727 | -58591.273 | -74.91532 |
| 27 | 93376 | -4463.752 | -97839.752 | -104.78041 |
| 28 | 66727 | 9619.580 | -57107.420 | -85.58368 |
| 33 | 22440 | 4436.268 | -18003.732 | -80.23053 |
| 37 | 9660 | 2777.520 | -6882.480 | -71.24720 |
| 41 | 9801 | 2305.714 | -7495.286 | -76.47470 |
| 43 | 113472 | 5233.998 | -108238.002 | -95.38741 |
| 51 | -2706720 | -193860.232 | 2512859.768 | -92.83782 |
| 57 | 3756 | 14832.133 | 11076.133 | 294.89173 |
| mean_change | median_change | min_change | max_change |
|---|---|---|---|
| 164715.6 | -17808.39 | -171654.1 | 2512860 |
Section 5: Table of Change in Effective Tax Rate
This section creates a table showing the pre-BEFIT and post-BEFIT effective tax rates for each company group, along with the absolute and percentage change in the effective tax rate. Also, calculates summary statistics on the change in effective tax rate.
| Company_ID | pre_befit_tax_expense | post_befit_tax_expense | pre_befit_pre_tax_income | pre_befit_effective_tax_rate | post_befit_effective_tax_rate | change_in_effective_tax_rate | percentage_change |
|---|---|---|---|---|---|---|---|
| 7 | 226330 | 54675.891 | 611442 | 0.3701578 | 0.0894212 | -0.2807365 | -75.84240 |
| 9 | 25025 | 7411.945 | 120085 | 0.2083941 | 0.0617225 | -0.1466716 | -70.38184 |
| 17 | 15894 | 11970.463 | 97434 | 0.1631258 | 0.1228571 | -0.0402687 | -24.68565 |
| 23 | 78210 | 19618.727 | 209528 | 0.3732675 | 0.0936330 | -0.2796346 | -74.91532 |
| 27 | 93376 | -4463.752 | -114624 | -0.8146287 | 0.0389426 | 0.8535713 | -104.78041 |
| 28 | 66727 | 9619.580 | 236486 | 0.2821605 | 0.0406772 | -0.2414833 | -85.58368 |
| 33 | 22440 | 4436.268 | 78672 | 0.2852349 | 0.0563894 | -0.2288455 | -80.23053 |
| 37 | 9660 | 2777.520 | 39200 | 0.2464286 | 0.0708551 | -0.1755735 | -71.24720 |
| 41 | 9801 | 2305.714 | 29052 | 0.3373606 | 0.0793651 | -0.2579955 | -76.47470 |
| 43 | 113472 | 5233.998 | 482784 | 0.2350368 | 0.0108413 | -0.2241955 | -95.38741 |
| 51 | -2706720 | -193860.232 | -2709390 | 0.9990145 | 0.0715512 | -0.9274633 | -92.83782 |
| 57 | 3756 | 14832.133 | 31044 | 0.1209896 | 0.4777778 | 0.3567882 | 294.89173 |
| mean_change | median_change | min_change | max_change |
|---|---|---|---|
| -0.132709 | -0.2265205 | -0.9274633 | 0.8535713 |
Section 6: Analysis by Company
This section analyzes the changes in tax expense and effective tax rate by the company.
| Company_ID | pre_befit_tax_expense | post_befit_tax_expense | change_in_tax_expense | percentage_change_tax_expense | pre_befit_pre_tax_income | pre_befit_effective_tax_rate | post_befit_effective_tax_rate | change_in_effective_tax_rate | percentage_change_effective_tax_rate |
|---|---|---|---|---|---|---|---|---|---|
| 7 | 226330 | 54675.891 | -171654.109 | -75.84240 | 611442 | 0.3701578 | 0.0894212 | -0.2807365 | -75.84240 |
| 9 | 25025 | 7411.945 | -17613.055 | -70.38184 | 120085 | 0.2083941 | 0.0617225 | -0.1466716 | -70.38184 |
| 17 | 15894 | 11970.463 | -3923.537 | -24.68565 | 97434 | 0.1631258 | 0.1228571 | -0.0402687 | -24.68565 |
| 23 | 78210 | 19618.727 | -58591.273 | -74.91532 | 209528 | 0.3732675 | 0.0936330 | -0.2796346 | -74.91532 |
| 27 | 93376 | -4463.752 | -97839.752 | -104.78041 | -114624 | -0.8146287 | 0.0389426 | 0.8535713 | -104.78041 |
| 28 | 66727 | 9619.580 | -57107.420 | -85.58368 | 236486 | 0.2821605 | 0.0406772 | -0.2414833 | -85.58368 |
| 33 | 22440 | 4436.268 | -18003.732 | -80.23053 | 78672 | 0.2852349 | 0.0563894 | -0.2288455 | -80.23053 |
| 37 | 9660 | 2777.520 | -6882.480 | -71.24720 | 39200 | 0.2464286 | 0.0708551 | -0.1755735 | -71.24720 |
| 41 | 9801 | 2305.714 | -7495.286 | -76.47470 | 29052 | 0.3373606 | 0.0793651 | -0.2579955 | -76.47470 |
| 43 | 113472 | 5233.998 | -108238.002 | -95.38741 | 482784 | 0.2350368 | 0.0108413 | -0.2241955 | -95.38741 |
| 51 | -2706720 | -193860.232 | 2512859.768 | -92.83782 | -2709390 | 0.9990145 | 0.0715512 | -0.9274633 | -92.83782 |
| 57 | 3756 | 14832.133 | 11076.133 | 294.89173 | 31044 | 0.1209896 | 0.4777778 | 0.3567882 | 294.89173 |
Section 7: Distribution of Changes
This section creates histograms and density plots to visualize the distribution of percentage changes in tax expense and effective tax rate across all company groups.
Section 8: Correlation Analysis
This section explores the correlation between company characteristics (e.g., revenue, number of subsidiaries, geographical spread) and the changes in tax expense and effective tax rate.
| revenue | num_employees | total_assets | change_in_tax_expense | percentage_change.x | change_in_effective_tax_rate | percentage_change.y | |
|---|---|---|---|---|---|---|---|
| revenue | 1.0000000 | 0.2375364 | 0.5219460 | -0.2741160 | 0.0418636 | 0.0935441 | 0.0418636 |
| num_employees | 0.2375364 | 1.0000000 | -0.2023028 | 0.0710544 | 0.0351038 | -0.1472190 | 0.0351038 |
| total_assets | 0.5219460 | -0.2023028 | 1.0000000 | -0.2494573 | -0.0177045 | 0.4328393 | -0.0177045 |
| change_in_tax_expense | -0.2741160 | 0.0710544 | -0.2494573 | 1.0000000 | -0.0548804 | -0.5146736 | -0.0548804 |
| percentage_change.x | 0.0418636 | 0.0351038 | -0.0177045 | -0.0548804 | 1.0000000 | -0.0413619 | 1.0000000 |
| change_in_effective_tax_rate | 0.0935441 | -0.1472190 | 0.4328393 | -0.5146736 | -0.0413619 | 1.0000000 | -0.0413619 |
| percentage_change.y | 0.0418636 | 0.0351038 | -0.0177045 | -0.0548804 | 1.0000000 | -0.0413619 | 1.0000000 |
Section 9: Conclusions
This analysis examined the potential impact of the BEFIT reform on the tax liabilities of the BMW Group and its subsidiaries.
Several key findings emerged:
The change in tax expense varied significantly across the different company groups within BMW, ranging from a 104% decrease to a nearly 295% increase. On average, the tax expense decreased by around 75,000 after applying the BEFIT rules. However, the median change was a more modest 17,489 decrease, indicating some extreme values were skewing the mean.
The effective tax rate also showed considerable variation, with some companies seeing their rate decrease by as much as 28 percentage points, while others experienced an increase of over 85 percentage points under BEFIT. The average change was an increase of around 13 percentage points in the effective tax rate.
Larger companies with higher revenues, employee counts, and total assets tended to experience smaller percentage decreases or even increases in their tax liabilities compared to smaller subsidiary groups. This aligns with the goal of BEFIT to ensure multinational corporations pay a minimum tax level.
The correlation analysis revealed a negative relationship between company size variables like revenue and total assets with the percentage change in tax expense and effective rate. However, the number of employees exhibited a positive correlation with tax expense changes.
Overall, the results suggest BEFIT could lead to a restructuring of effective tax rates and redistribution of tax liabilities within large multinational groups like BMW. Smaller subsidiaries may see tax increases, while large companies could experience decreases depending on their specific operational footprint.
While more analysis is needed on the full implications, this study provides insight into how the BEFIT proposal could impact the tax situations of different corporate entities within a multinational group. As debates around global tax reform continue, such analyses will be crucial for companies to understand and prepare for potential changes to international tax rules.
Overall, the implementation of BEFIT appears to align with its intended goal of addressing base erosion and profit shifting by multinational corporations. However, the specific implications for individual companies and countries may vary depending on their unique circumstances.