100% satisfaction guarantee Immediately available after payment Both online and in PDF No strings attached
Previously searched by you
Solutions and Test Bank for Introduction to Federal Income Taxation in Canada, 45th Edition by Nathalie Johnstone (All Chapters included)$89.49
Add to cart
Solutions and Test Bank for Introduction to Federal Income Taxation in Canada, 45th Edition by Nathalie Johnstone (All Chapters included)
47 views 1 purchase
Course
FT-45
Institution
University Of Montreal
Complete Solutions Manual and Short Questions Answers Test Bank for Introduction to Federal Income Taxation in Canada, 45th Edition by Nathalie Johnstone, ISBN13: 9781773792484.... There is no Test Bank for Chap 1 (Full Chapters are included in reverse order from Chapter 22 to 1)...Chapter 01: Intr...
Chapter 19: Partnerships, Trusts, and Death of a Taxpayer
Chapter 20: International Taxation in Canada
Chapter 21: Goods and Services Tax (GST)/Harmonized Sales Tax (HST) Chapter 22: Data Analytics
and Information Systems in Tax
,The Solutions Manual and Short Question Answers Test bank is organized in
reverse order, with the last chapter displayed first, to ensure that all chapters
are included in this document. (Complete Chapters included Ch22-1)
CHAPTER 22
Data Analytics and Information Systems in Tax
Solution 1
Tax Issue Identified:
We are to prepare the T2 Schedule 5 Tax Calculation Supplementary—Corporations for the company for 2024.
Context:
This schedule is the corporate tax return schedule which is used to allocate federal taxable income to provinces
and other jurisdictions in which the company has a permanent establishment (“PE”) as defined in the Income Tax
Regulations (“ITRs”).
To prepare this schedule, we need to determine in which jurisdictions the company had PEs during the year.
Then, we will need to analyze the company’s data to allocate its gross revenues and salaries and wages to those
jurisdictions consistent with the ITRs.
Plan for Analysis:
1)Identify ITRs relevant for determining the PE jurisdictions.
2)Determine the PE jurisdictions.
3)Determine the gross revenues allocable to the PE jurisdictions.
4)Determine the salaries and wages allocable to the PE jurisdictions.
5)Allocate the federal taxable income to those PE jurisdictions based on the formula in the ITRs.
Stage 2: Data Understanding
Data Identification
To determine where PEs are located, we will need to review where the company has fixed places of business. In
addition, we will need to review the activities of its employees and independent contractors to determine their
functions and activities.
We will also need to gather data files on the company’s gross revenues and salaries and wages paid for the
allocation process.
Data Collection
We gathered information on fixed places of business and the activities of its employees and independent
contractors to determine their functions and activities.
We were provided with the following data files to do our analysis:
o The database schema of the company’s files needed for this problem with “FK”
denoting “foreign key”. A foreign key acts as a cross-reference between tables that
links to the primary key of another table.
o tSales file (this file shows the company’s 2024 sales transactions)
o tPayroll file (this file shows the salary and benefits of the employees in the year)
o tCustomer file (this shows customer billing and shipping addresses)
o tEmployees file (this file shows extracts from the employee master file)
o tProducts file (this file shows the company’s products sold in the year and related
prices)
o tLocations file (this file shows the company’s production and head office locations)
Data Explored
655
,656 Introduction to Federal Income Taxation in Canada
All of the data files were reviewed for errors by visually scanning the data files. In addition, the files were
converted to tables in Excel to filter the data fields which facilitated the review of high observations, low
observations, missing data and duplicates for potential errors/omissions.
Stage 3 Data Preparation
Data Cleaning
As per the instructions provided, no actual follow up was done at this stage and so no data was “cleaned” or
adjusted except the function and province of the tsalary file employee noted below. All other data was retained as
provided.
The following items were identified for follow up in the files:
B)The following represent items on which we should follow up to determine if there are errors in the data
before we finalize the results shown above for use in the preparation of the tax return for the year:
(all the data files were reviewed for errors by visually scanning the data files and doing summary statistics such as
high obvservations, low observations, missing data and duplicates.
tsales file
i) There were missing invoices from 3101 to 3201 -this issue was located by determing that the
difference between the invoice numbers at beginning of the year and the end of the year was
higher than the number of invoices in the year.
ii) There is a duplicate invoice #3100 which was located using the conditional formatting
function in excel.
iii) There are a few invoice numbers with no data. An example is the first one shown below:
Inv # Inv Date
2239 2022-01-04
iv) The following invoices have negative sales quantities:
salesID customerID salesDate productID locationID Quantity WholesalePriceemployeeID
3317 81 2022-08-23 4 3 -1 1225 21
3332 77 2022-08-27 3 4 -2 1000 24
3345 58 2022-08-30 4 4 -3 1225 21
v) The following invoices have significantly higher sales quantities and sales amounts compared to the rest of the sales in the year:
salesID customerID salesDate productID locationID Quantity WholesalePriceemployeeID
2801 10 2022-04-30 6 3 88 1800 27
2807 45 2022-05-01 6 4 61 1800 32
vi) The following invoices do not have salespeople assigned to them:
salesID customerID salesDate productID locationID Quantity WholesalePriceemployeeID
2382 15 2022-02-10 8 4 9 2400 NA
2433 39 2022-02-25 7 4 7 2000 NA
2479 15 2022-03-07 10 4 8 2500 NA
2580 15 2022-04-01 7 3 10 2000 NA
2833 38 2022-05-09 6 4 3 1800 NA
2879 38 2022-05-22 4 3 19 1225 NA
3087 15 2022-07-21 3 4 17 1000 NA
3204 39 2022-07-25 2 4 6 780 NA
3240 38 2022-08-04 9 4 15 2500 NA
3313 45 2022-08-21 6 4 11 1800 NA
3409 38 2022-09-15 2 3 17 780 NA
3420 15 2022-09-19 7 4 11 2000 NA
3461 39 2022-09-30 1 4 5 600 NA
3606 38 2022-11-08 6 3 3 1800 NA
3675 39 2022-11-30 8 4 19 2400 NA
There appears to be an inconsistency between the temployee file and the tsalary file for the following employee(s):
temployee file
15 Chris Smit RnD R&D 8/27/2021 blank for t23 Galaxy Ottawa ON
tsalary file
314 Alex Ovekc1/1/2022 12/31/2022 35979 7195.8 1798.95 RnD
Note that the temployee file information was adjusted to show Alex Ovekchin as being based in Ontario since his
function in the payroll file is shown as RnD which is based on Ontario.
Constructing and Integrating Data
a)To proceed with the salaries and wages per PE, the following steps were necessary in Excel:
,Solutions to Chapter 22 Assignment Problems 657
1.create a copy of the tPayroll file for analysis so the original data is not affected by the analysis to be done
2.calculate gross salary and wages by employee (this includes salary, bonus and taxable benefits as
provided)
3.use ““vlookup”” function from the tLocation file to allocate employees to provinces
The following shows the first 2 lines of this new table:
employee periodStarperiodEnd salaryWagbonus taxableBenjobDescrip gross salary and work locatlocation province
Arliene Mu1/1/2024 12/31/2024 79549 17049.8 4429.95 salesPerso $ 101,028.75 St. StephenNB
Lek Bathow1/1/2024 12/31/2024 91449 17429.8 4524.95 salesPerso $ 113,403.75 St. StephenNB
b)To proceed with the gross revenues per PE, the following steps were necessary in Excel:
1.tSales file was copied into a new Excel file for analysis so the original data is not affected by the analysis
activities
2.gross revenue was calculated for each invoice
3.tCustomer file was copied into this Excel file in a new tab, and “vlookup” function used to determine “ship
to addresses” for each invoice required for allocation process
4.tEmployees file was copied into this Excel file in a new tab and “vlookup” function used to determine their
reporting location for customer invoice allocation by reporting location of the appropriate salesperson, as
necessary
5.tLocation file was copied into this Excel file in a new tab, and “vlookup” function used to determine where
each invoice’s products were produced, as necessary
The following shows the first 2 lines of this updated sales analysis file:
salesID customerI salesDate productID locationID Quantity WholesaleemployeeI gross revenues ship country ship prov report to where produced
2226 3 2024-01-01 4 4 10 1225 21 $ 12,250 Canada NB NB BC
2227 2 2024-01-01 7 4 9 2000 33 $ 18,000 Canada NB ON BC
Stage 4 – Modeling
Firstly, we need to determine the jurisdictions where the company had PE’s.
It appears that the company had PE’s in BC, NB and BC where it had fixed places of business.
The company’s employees located in other jurisdictions and/or performing functions in other jurisdictions did
not appear to have contracting authority or to have inventory from which they fulfilled customer orders, so
there do not appear to have been any other PE’s than in those three provinces.
Given these three PE jurisdictions, we use Excel’s pivot table function to determine the salaries and wages and
gross revenues attributable to each PE jurisdiction.
A)For salaries and wages, the analysis shows the following results:
Row Labels Sum of gross salary and benefits
BC 1426502.1
NB 1871610.05
ON 2383042.12
Grand Total 5681154.27
B)For gross revenues, the analysis provides the following results depending on where the products were shipped:
, 658 Introduction to Federal Income Taxation in Canada
Shipments for Canadian Provinces
Row Labels Sum of gross revenues
0 10962120
AB 1381250
BC 3315230
NB 2033085
NL 387005
NS 657885
ON 4301485
QC 2041390
SK 909735
Grand Total 25989185
Note that row “0” represents non-Canadian province shipments (i.e., exports).
Below, we use a pivot table to show the sales in non-PE provinces attributable to salespeople reporting to
locations in PE provinces. Again, foreign sales are shown as 0 below.
Below, we see sales to US customers and the PE province in which those sales were produced since the
company does not have a PE in the US. The company does not sell to any other countries.
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller mizhouubcca. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $89.49. You're not tied to anything after your purchase.
