Need help with Excel Tax Assignment Part C - Federal Tax Payable

Assignment 2 Tax Return to be Completed in Excel Jim Holden is 39 years old and married to Naomi Holden. They have been married for 17 years. They have a 13-year-old son Amos, and an 8-year-old daughter Alex. Alex has a severe form of cerebral palsy which required her to start using a wheelchair in 2017. Amos has income from part timejobs of $1,400, while Alex has no income of her own. Other family information for the 2017 taxation year is as follows: 0 Jim worked 212 hours as a search and rescue volunteer. He received no compensation for this work. . The family's medical expenses, all of which were paid for by Jim, were as follows: Hair Replacement Fees For Jim $4,300 Prescription Contact Lenses For Naomi 850 Teeth Whitening Fees For Naomi 950 Prescription Glasses For Amos 350 Psychologist Consulting Fees For Amos 1,500 Physiotherapy Fees For Alex 725 Total $8,675 Jim is a self-employed military consultant, mainly for the Canadian armed forces. When he began consulting in 2012, he purchased an office building for $750,000, of which $180,000 reflected the estimated fair market value of the land. It was a new building and it has been allocated to a separate Class 1. Jim uses all of the building for his practice. On January 1, 2017, the building had a UCC of $431,676. In an attempt to attract foreign clientele, Jim upgrades his office during March, 2017. He spends $204,000 on new furniture and fixtures. The old furniture and fixtures were sold for $31,000. These assets had a capital cost of $93,000 and a UCC of $34,284. Other asset acquisitions during 2017 are as follows: New Computer $ 1,200 Applications Software 2,044 Client List From Retiring Military Consultant 41,000 Jim uses his personal car to drive to many different bases for his consulting. On January 1, 2017 he acquired a new vehicle at a cost of $42,000. During the year, it was driven a total of 32,000 kilometers, of which 29,000 related to these visits. Operating costs for the year totaled $4,800. Other 2017 costs of operating his business, determined on an accrual basis, are as follows: Page 1 of 4 Building Operating Costs $24,300 Payments To Staff 42,000 Miscellaneous Office Costs 17,500 Meals With Clients 15,300 On January 1, 2017, Jim had accounts receivables of $92,000. During 2017, his collections total $321,000. On December 31, 2017, the accounts receivable has increased to $105,000 and he also has $6,400 of unearned revenue. Naomi works as a senior engineer for a large Canadian public company. Her 2017 salary is $62,000 from which her employer withholds the following amounts during the year: Registered Pension Plan Contributions* $2,500 EI Premiums 836 CPP Contributions 2,564 *Naomi's employer makes a matching contribution of $2,500. In order to deal with her employer's out-of-town customers, Naomi is required to do some amount of travel. She uses her own vehicle for this travel, currently an automobile that she purchased on January 1, 2017 at a cost of $38,000. During 2017, she drove the vehicle 36,000 kilometers, of which 31,500 were employment related. Her operating costs for the year were $3,465. In addition to automobile costs, Naomi has other travel costs as follows: Hotels $3,200 Food On Out Of Town Trips 1,300 Naomi's employer provides her with the following allowances for her travel, in addition to her salary: Hotels And Food $4,400 Use Of Personal Automobile ($100 Per Week) 5,200 Because of their work, Jim and Naomi required a part time caregiver to look after the children after school. They paid the caregiver $13,900 for 2017. Through careful spending for himself and his family, Jim has been able to accumulate an investment portfolio of over $500,000. As a result, he has the following items earned in 2017: Capital Gains $22,960 Eligible Dividends 8,500 Interest Income 5,500 Total $36,960 Naomi purchased 5% 3 year bonds with a face value of $4,000 for $4,110 on April 1, 2014 with interest paid at maturity. Naomi received the maturity value of $4,000 plus interest on April 1, 2017. Both Jim and Naomi have invested regularly in RRSPs. Information for these plans is as follows: Page 2 of 4 Jim's Plan At the beginning of 2017, there was $11,000 in unused deduction room in Jim's RRSP. There were also $13,000 in undeducted contributions in the plan. During 2017, he contributes $22,000 to the plan. He plans to take the maximum deduction available on the basis of this information. In January, 2015, Jim withdrew $25,000 from his RRSP under the Home Buyers' Plan to purchase a new home which was easier for Alex to navigate in her wheelchair. Because he acquired the home for a lower price than anticipated, he repaid $10,000 of the Home Buyers' Plan balance in that year. He did not make any repayment during either 2016 or 2017. The old family home was in Jim's name only. It was purchased in 1994 for $236,000 and sold for a price of $510,000. Real estate fees were $12,500. The family does not own any other real estate properties. However, in 2009, Naomi claimed the principal residence exemption to eliminate a $179,000 capital gain on a cottage she sold. She designated the cottage as a principal residence for the years 1999 to 2008. The family paid a moving company $1,650 to transfer their possessions between the two Calgary houses. Jim's Earned Income for 2016 was $123,000. He did not have a pension adjustment. Naomi's Plan At the beginning of 2017, there was unused deduction room in Naomi's plan of $7,000. She had no undeducted contributions. In December, 2017, Naomi withdraws $7,000 from her RRSP to loan to her brother. In February, 2018, her brother, after a successful trip to Las Vegas, pays her back the $7,000, along with a gift of $5,000 for being such a great sister. Naomi immediately contributes the $7,000 to her RRSP. She plans to take the maximum deduction available for 2017 based on this information. Naomi's Earned Income for 2016 was $51,000. Her employer reported a pension adjustment for that year of $4,800. Jim and Naomi will allocate tax credits between them to minimize the family's tax liability. Where either spouse can claim the credit and it makes no difference in the combined tax payable, Jim will claim the credit. Page 3 of 4