International Group Taxes
Simplified Tax Computation
Overview
Companies headquartered in the UK need to report on their earnings from anywhere in the world, as well as the tax they paid on those earnings in their respective countries.
When preparing the tax return, companies need to use the Profit before Tax amount that they’ve already calculated for their accounts and make some adjustments – what are known as add-backs and deductions. These adjustments will result in the taxable income amount on which a tax rate is applied.
Because companies may pay tax on income from overseas, this adds another level of trickiness. They may earn money and pay taxes in the local currency, but it still needs to be reported in GBP (their required reporting currency). Any tax paid overseas may be eligible for a deduction in the UK. For accountants performing this exercise, they want to be able to do this efficiently across potentially many entities.
Whilst the UK corporate tax rules are complex, for the purposes of this exercise we’re going to create a workflow that represents a simplified tax calculation.
Objective
Create a workflow that will consolidate tax information and apply appropriate FX rates to convert the post-tax income (which is the net of pre-tax income minus the taxes paid locally) from local currencies to a reporting currency.
Produce an Excel file with a company-by-company list of pre-tax income, add-backs, taxable income and tax due.
Optionally as an extension, you can also create a formatted PDF for the consolidated pre-tax income, add-backs, taxable income, tax, and effective tax rate for the group.
Processes
1. Bring in the quarterly E&P data, clean it, and aggregate it into yearly totals for each company.
2. Bring in the other datasets and blend them into one stream so that each company code has the associated filing entity, tax values, and FX rates.
3. Combine the two streams and apply the FX rate to income, tax and add-backs. Calculate the taxable income and taxes required given a 19% tax rate.
4. Prepare a spreadsheet with company totals.
Optional: Calculate the effective tax rate and prepare a formatted tax return PDF for the entire organization showing pre-tax income, add backs, taxable income, tax, and effective tax rate.
Data Dictionary
Fx Rates 18.csv
The exchange rates for the tax return, translating between the local and reporting currency (GBP).
Column Name
Data Type
Description
Country
String
Name of the country
Currency
String
Name of the currency
currency Code
String
ISO 4217 currency codes
Currency units per £1
Numeric (decimal)
Exchange rate to GBP
Master Company Data.xlsx
Column Name
Data Type
Description
Company
String
Comprising the company code and company name (aka entity)
Company Code
String
Three-character code uniquely identifying each of the companies in the group for internal reporting purposes
Country
String
Name of the country where company operates
Currency Code
String
ISO 4217 currency codes
Filing Entity
String
Three-character code uniquely identifying each of the companies in the group for external reporting purposes
FY18 – E&P Source Data.xlsx
Note the sheet names; each refers to one of the four quarterly periods that make up FY18
Column Name | Data Type | Description |
Company | String | Comprising the company code and company name (aka entity) |
Accrued Bonus | Numeric (decimal) | All of these are add-backs; expenses in the accounts which need to be added to the pre-tax income for tax purposes. They increase the amount on which the tax needs to be calculated |
Accrued Pension | ||
Accrued Severance | ||
Accrued Vacation | ||
Allowance for Bad Debts | ||
Deferred Income | ||
Employee Stock Options | ||
Inventory Reserve | ||
Nondeductible Expense | ||
Penalties and Fines | ||
Depreciation | ||
Amortization | ||
Legal Reserve | ||
Rent Smoothing |
F18 - CY Taxes.xlsx
Column Name
Data Type
Description
Company Code
String
Three-character code uniquely identifying each of the companies in the group for internal reporting purposes
Pretax Income
Numeric (decimal)
The pre-tax income as per the accounts (in local currency)
CY Taxes Local Currency
Numeric (decimal)
Taxes paid locally (in local currency)