MOS 3362b - Introduction to Taxation in Canada
Fall 2024 - Tax Assignment
PURPOSE: The purpose of this assignment is to provide practice organizing and analyzing data to
determine the federal income tax payable for a taxpayer. This assignment also provides experience using professional communication and using Excel functions, both of which are beneficial in tax and accounting roles.
DUE DATE: Sunday, December 1, at 11:55 pm. Will be accepted until Thursday, December 5 at 11:55 pm without penalty. No assignments will be accepted after December 5 for any reason. Completing this assignment is a requirement for passing this course.
INSTRUCTIONS: Complete this assignment using this PDF document and the Excel file that is provided on OWL. When you have completed the assignment, submit two separate documents on the OWL Assignment page: (1) your completed Excel document and (2) your letter to Zendor along with a self-reflection. DO NOT SUBMIT AS PDFs. Any document submitted as a pdf will receive a grade of zero. For your letter, use 200 to 300 words, 12-point font and single spacing.
Ensure that you use a professional format and tone when communicating with Zendor. You want him to be impressed so that he will ask you to help him again in the future. Remember - you are writing TO Zendor, not just about him.
For yourself-reflection, briefly describe how long it took to complete this assignment and any challenges you encountered with completing it. Also include ways you felt the assignment was beneficial. Your reflection should be 100 - 200 words.
MEETING NOTES
As a senior accountant with the firm Better and Best, you have been asked to prepare the tax return for Zendor McConnell, an electrician operating as a sole proprietor. This is your second year preparing Zendor’s income tax return.
Zendor has given you an Excel file with a list of all his transactions for 2024. He has said that he is not certain whether everything relates to his business, and that it may include some items from other years. You made a note to watch for items from years other than 2024 and to carefully consider whether transactions are applicable for income tax purposes.
Additional Information
1. Zendor is 52 years old and lives in London, Ontario. His revenue comes from
providing electrical services for businesses in London and in Kitchener. He tracks
the revenue from these two locations separately in order to best understand his business profitability.
2. Zendor is married to Barbara, age 48, who is disabled. She receives a disability pension of $1,000 per month and has a T-2201 that has been accepted by CRA.
The couple has one son, Adam, who is 20 years old, in good health, and attends university full time. Adam’stuition for 2024 was $16,000. He earned $20,000 working during the summer. He would like to transfer as much of his tuition tax credit to his father as possible.
Barbara’smother, Janice, is 73 years old and lives with the family. She is in good health and has pension income of $36,000 per year. She helps take care of the home and does things that Barbara is unable to do. She does not receive any compensation for this.
3. Home office expenses: One room in Zendor’s home is dedicated solely as his business office. It is
the only place that he uses as an office. The office occupies 12% of the floor space of the home.
Zendor does all his administrative work in this office, including purchasing supplies, scheduling jobs, and billing customers. The following home office expenses were incurred in 2024:
• Utilities – heat and water
• Internet (used 60% for personal purposes)
• Municipal property tax – City of London
• Office supplies – pens, paper, toner for printer, etc.
• Office furniture - new desk and chair
4. Van expenses: On January 1, 2024, Zendor sold a customized van for $15,000 that he had purchased for $80,000 and had a UCC of $5,650. This was the only item in its UCC class. On the same day he purchased a new customized van. He paid for the new van with the $15,000 from the sale of the old vanand wrote a cheque for the additional remaining cost. Both vans could hold two passengers (the driver and one other) and were used exclusively to bring workers and supplies to work sites. The old van was not a zero-emissions vehicle; however, the new van was a zero-emissions vehicle. Operating expenses included gas, auto insurance, vehicle license & registration. Zendor does not use the van for any personal purposes. He has a separate personal automobile that is not used for the business. None of the personal automobile’s costs are on the Excel file you were given.
5. Travel expenses (other than van): Because of the distance, Zendor stays overnight at a hotel in Kitchener when he does work there that will take more than one day. He eats his meals at local restaurants.
In August 2024, Zendor flew to Vancouver to attended a three-day Electrician’s Convention at the Vancouver Heritage Hotel in Vancouver, BC. He attends this convention every year, as it is a good opportunity to catchup on the latest electrical devices and techniques. This year, Adam went with Zendor so that he could spend sometime exploring Vancouver. Zendor extended the stay at the hotel by two extra days to allow extra time to spend with his son. No meals were provided by the conference. Zendor spent $200 on meals each day in Vancouver. This was equally split between meals for himself and meals for Adam.
6. General Expenses: In 2024, Zendor paid for a membership in Electrical Contractor Association of Ontario. He also has a magazine subscription to Electrical Contractor Magazine (ECM).
He made charitable donations to his church and to the Canadian Red Cross Society, both of which are a registered Canadian charities. He also made periodic donations to the New Liberal Conservative Party of Canada,a registered Canadian political party.
He has a gym membership at an Athletic Club that requires monthly payments. He made occasional contributions to his RRSP.
He remits quarterly Income tax instalments to CRA based on his Notice of Assessment. Instalments include the CPP for self-employed individuals. He chose not to participate in EI
7. Other: The following additional information relates to Zendor for 2024.
He received non-eligible dividends from Pristine Products, Inc. and interest income on a savings account with Fortune National Bank.
He received foreign dividends on shares he owns in Frontline Corporation, an American company. 22% foreign income tax was withheld on this dividend, and Zendor received the net amount. He recorded the Canadian dollar amount on his Excel file.
He sold to his sister, Nancy, shares in Allister, Ltd. that he had purchased for $50,000. At the time of the sale, the shares had a FMV of $75,000.
DETAILED INSTRUCTIONS
1. Ignore GST/HST/PST and Provincial Income Tax for this assignment.
2. Open the Excel file provided for this assignment. Goto Cell 1A of the Bank Transaction tab and add
your name and student number. Now select the row that says Date, Account, and Amount. Under the Data menu at the top of your worksheet, select “Filter” to create filters at the top of each column.
The data has some errors and needs to be cleaned. Click the filter arrow for the “Account” column
and uncheck the “Select All” button. Next check the two “ Revenue …” accounts to filter the data to
only show revenue accounts. All revenue amounts should be positive. Find three numbers that were entered as negative numbers. Make those numbers positive. Now remove the checks beside the
Revenue accounts and select only Internet. There should have been 12 equal monthly payments, but an error was made one month. Find and fix the error.
3. Go back again to the filter arrow for the “Account” column and check the “Select All” button to make all the data visible again. Click OK.
Now create a PivotTable. Click anywhere in the data. From the “Insert” menu, select “PivotTable”
and ‘From Table/Range’ . In the PivotTable pop-up window, accept “New Worksheet” for the location for your PivotTable. Click OK. Change the tab name for your new worksheet to ‘2024’ and add your initials. For example, ‘2024-RAS’ . Add your name and student ID to Cell 1A.
In the “PivotTable Fields” menu on the right-hand side of the page, put a check mark beside
“Account” and “Amount” . [Note – you have to click on the PivotTable box in columns A – B to make the PivotTable Fields show up.]
A PivotTable should have appeared that summarizes the total dollar values for each revenue and
each expense account. Change the column heading from “Row Labels” to “Account” and leave the column heading that says, “Sum of Amount” . Format numbers so that they have a dollar sign, a
comma, and no decimal places. Negative numbers should be shown with brackets, if you have that option on your computer.
4. Click on the PivotTable. From the PivotTable Analyze tab on the menu bar, select “Insert Timeline” . Put a checkmark in the box that says “Date” . Click OK. You will see a timeline that includes all the months for 2023, 2024, and 2025. Make sure that Months is chosen in the upper right corner of the timeline box.
Expand the timeline box from the left and from the right so that the months of January through
December 2024 can be seen. Select only these months by dragging the highlighted bar toward these months from both sides. This ensures that only current year transactions are included in your
PivotTable. When only the months of 2024 have been selected and are visible in your timeline, take a screenshot of your timeline and paste it into Column B in the Analysis tab under Exhibit 1 –
Timeline. Resize to fit the space provided. [Note: do not use copy and paste for the timeline – it has to be a screenshot or a picture.]
5. Go back to the 2024 worksheet. You should still seeyour Timeline box there. You can minimize it
and click on it to drag it out of your way, but do not remove it. Filter the PivotTable using the arrow next to “Account” and uncheck the “Select All” box. Then check only the boxes next to the two
“ Revenue …” accounts. You now have a PivotTable that shows the total for the revenue accounts for
2024. Copy and paste your PivotTable into Columns B and C of your Analysis worksheet under
Exhibit 2 – Revenue for 2024. Do not use a screenshot for this part. Adjust columnwidth if needed.
6. Go back to the 2024 worksheet. Click on the PivotTable. Under the “PivotTable Analyze” option on
the menu bar, click on PivotChart. Select pie chart. Click on your chart. Use the ‘+ sign’ by the upper right corner of the chart and pick format options you think are helpful. Add a descriptive title, and put your last name at the end of the Title to show that it was made by you. Use the paintbrush by the
upper right corner to pick a colour that you like. Click OK. Take a screenshot of your pie chart and paste it into Column B of your Analysis Worksheet under Exhibit 3 – Pie Chart. Change the size as needed to fit comfortably. [Do not use copy and paste for this part, as that would make your charts change when you do the nextsteps.]
(For Mac Users, under the “PivotTable Analyze” option on the menu bar, click on PivotChart. A bar graph will appear. To change Bar Graph into pie chart, select “Design” on the menu bar, select
“Change Chart Type” and scroll down to “Pie” and select first option under 2D Pie Section.)
7. Go back to the 2024 worksheet. Move your chart out of your way, but do not remove it. Filter the
PivotTable by clicking on the arrow next to “Account” at the top of the PivotTable and checking only the boxes next to home office expenses in accordance with information provided in the Meeting
Notes. You will need to read carefully through the list of accounts. You should now have a
PivotTable that shows all home office expenses for 2024. Copy and paste this PivotTable into
Columns B and C in the Analysis worksheet under the heading Exhibit 4 – Home Office Expenses.
Expand Column B if needed. The new office desk and chair replaced an old one with an original cost of $3,000, a UCC of $250, and afair market value of $0.00. The old desk and chair were donated to a local charity. Notice what has happened to your pie chart.
8. Go back to the 2024 worksheet. Filter the PivotTable by checking only the boxes next to van
expenses in accordance with information provided in the Meeting Notes. Include the purchase of the van. You should now have a PivotTable that shows all van expenses for 2024. Copy and paste this PivotTable into Columns B and C in your Analysis worksheet under the heading Exhibit 5 – Van Expenses.
9. Go back to the 2024 worksheet. Filter the PivotTable by checking only the boxes next to travel
expenses in accordance with the information provided in the Meeting Notes. You should now have a PivotTable that shows all travel expenses for 2024. At this point, some personal travel will be
included. You will sort that out later. Copy and paste this PivotTable into Columns B and C in your Analysis worksheet under the heading Exhibit 6 – Travel Expenses.
10. Go back to the 2024 worksheet. Filter the PivotTable by checking on all the boxes next to general
expenses in accordance with the information provided in the Meeting Notes. You should now have a PivotTable that shows all general expenses for 2024. Copy and paste this PivotTable into Columns B and C in your Analysis worksheet under the heading Exhibit 7 – General Expenses.
11. In Column D, beside Exhibit 4 through Exhibit 7, enter the amount that would be tax deductible for each line. In Column E show any calculations used to determine the deductible amount, or provide an explanation of your amount in Column D. For anything that requires CCA, add 0.00, and a note that CCA applies.
12. Under Exhibit 8 – Net Business Income, use the relevant subtotals from column D to calculate Zendor’s net business income. Complete any CCA calculations and add to your calculation.
13. Go back to the 2024 worksheet. Filter the PivotTable by checking on any accounts you have not
included in the previous exhibits. Copy and paste this PivotTable into Columns B and C in your Analysis worksheet under the heading Exhibit 9 – Other Items. You will need to decide whether any of them
should be used in your income tax calculations.
14. Now goto the last worksheet – Income Tax Payable. Use the net business income you have
determined on the Analysis worksheet and any other information from the Meeting Notes and/or the Bank Transactions file to determine Zendor’s net income tax payable.
Letter to Zendor:
At the end of your meeting with Zendor, he asked you some important questions that he needs your help with. Please write a 200-to-300-word letter that answers his questions. Use 12-point font and single spacing. Use a professional format and tone. Remember that you are writing TO Zendor, not just about him. Direct your comments to him.
1) He would like to know
• His income tax payable (or refund) for the year. If he owes any additional amount, when it needs to be paid. When his income tax return is due.
• When Barbara’s income tax return is due.
• When Janice’s income tax return is due.
• Whether Adam needs to file a tax return, and if so, when it is due. He has heard that
individuals with income below $15,000 do not need to file a tax return and would like you to clarify this.
2) As his business has grown, he is finding himself too busy and has been turning away profitable
customers due to a lack of time. He is considering adding an assistant but is unsure how to structure his relationship with the assistant. He would like you to explain how to distinguish between an employee and a self-employed contractor. Please share three specific points with him.
3) Zendor has a friend, Lawrence, who has asked him to get some advice from you. Lawrence owns a
cottage near Huntsville, Ontario that he rents out during summer months. He is planning to surprise his wife, Cheryl, by gifting her the cottage as a December holiday gift. He wants your advice on the most tax efficient manner to transfer the property. He also wants to know the tax consequences for any future rental income and for the eventual sale of the cottage for himself and for Cheryl. Is it
possible for Lawrence to avoid all future tax implications related to the cottage? If so, please explain how this can be achieved. Lawrence has told you that no CCA has ever been claimed on the cottage.