Question
Personal Income Tax Calculator (Data File needed for this exercise: None) Every year, Durham Financial Services provides free income tax related services for individuals who
Personal Income Tax Calculator (Data File needed for this exercise: None) Every year, Durham Financial Services provides free income tax related services for individuals who live in Oshawa. Currently Durham Financials is considering creating personal income tax application capable of calculating net income after tax based on the Canadian Federal tax rates, Ontario Provincial tax rates, Canada Pension Plan (CPP), and Employment Insurance (EI) for the year 2021. Because of your past Excel and VBA coding experience, you are hired to create the personal income tax application capable of computing income after tax and generate report. The net income after tax and other values are calculated as follows: 1) CPP and EI If the annual income is greater than $56,300, then EI = $889.54, Otherwise EI = Annual Income * 1.58% If the annual income is greater than $61,600, then CPP = $3,166.45, Otherwise CPP - (Annual Income - 3500) * 5.45% Where the $3500 amount is the basic exemption amount for 2021. 2) Federal Tax on taxable income Taxable income annual income - CPP - EI 15% on the first $49,020 of taxable income, plus 20.5% on the next $49,020 of taxable income (on the portion of taxable income over $49,020 up to $98,040), plus 26% on the next $53,939 of taxable income (on the portion of taxable income over $98,040 up to $151,978), plus 29% on the next $64,533 of taxable income (on the portion of taxable income over $151,978 up to $216,511), plus 33% of taxable income over $216,511 3) Calculate Provincial Tax on taxable income 5.05% on the first $45,142 of taxable income, plus 9.15% on the next $45,145 of taxable income (on the portion of taxable income over 45,142 up to $90,287), plus 11.16% on the next $59,713 of taxable income (on the portion of taxable income over $90,287 up to $150,000), plus 12.16% on the next $70,000 of taxable income (on the portion of taxable income over $150,000 up to $220,000), plus 13.16% of taxable income over $220,000 4) Net Income after tax and other calculations Total Tax = Federal Tax + Provincial Tax Annual Net Income = Annual Income - CPP - EI - Total Tax Monthly Net Income = Net Take-home / 12 Biweekly Net Income = Net Take-home / 26 Weekly Net Income = Net Take-home / 52 For more info on federal and provincial tax rates please visit: https://www.canada.ca/en/revenue- agency/services/tax/individuals/frequently-asked questions-individuals/canadian-income-tax- rates-individuals-current-previous-years.html For your reference you can use the Income Tax.xlsm file attached as you complete this assignment. Your task is to create an application similar to this file. Note that the code in this file is password-protected. Task 1: Prepare Excel Macro-Enabled file Refer to Fig. I as you complete Task 1. (a) Create a Macro-Enabled workbook named Durham TaxServices.xlsm. Insert a Documentation worksheet and add appropriate title for the workbook in cell Al. In cell B3 enter your name. In cell B4 enter your student number. In cell B5 enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook. In cell A100, enter your ontariotehu.net email address. (b) Add a new worksheet called Personal Income Tax Calculator. (c) Add a button (from ActiveX Control group). Change the caption to display Run Tax Calculator (d) Add a button (from ActiveX Control group). Change the caption to display Reset Worksheet (e) Create a reporting area similar to Fig. 1. Save your file. Task 2: Create Income Tax Calculator User Form Refer to Fig. 2 as you complete Task 2. (a) Create a user form similar to Fig. 2. Be sure to change the user form caption to Personal Income Tax Calculator (b) Add any income tax related image (you can use Google image search on income tax). Save your file. Durham Financial Services 1 Run Tax Calculator Clear Worksheet 10 11 12 13 14 15 10 First Name Last Name SIN Annual income CPP 1 Provincial Tax Federal Tax Total Tax Net Take-home Monthly Bi-weekly Weekly Fig. 1: Initial Setup Personal Income Tax Calculator X Durham Financial Services Run Tax Calculator Clear Worksheet First Name Last Name SIN Annual income CP Provincial Tau Federal Tax Total Tax Net Take-home Monthly Bi-weekly Weekly Fig. 1: Initial Setup Pesonel Income Tax Calculator Durham Financial Services First Name: Last Name: Ametlicom Generate Region Reset Form Annual income: Close Fig. 2: Personal Income Tax Calculator User Form Task 3: Write codes for each button Refer to Fig. 3 as you complete Task 3. (a) The Annual Net Income button should compute and display the annual net income amount for the user (don't forget to format this value). (b) The Reset button should clear the user form (four textboxes and one label). (C) The Generate Report button compute should display the required information onto the Personal Income Tax Calculator worksheet as shown in Fig. 3. (d) The Close button should close the user form. Task 4: ActiveX Buttons and Error Handling (a) Link the Income Tax Calculator user form to your Run Tax Calculator ActiveX control button (b) Program the Reset Worksheet ActiveX control button to clear the contents of your worksheet (C4:C16) (C) The Personal Income Tax Calculator user form should have an error handling mechanism to avoid application malfunction due to empty textboxes. For example, if one of the textboxes is empty, then s the program should display an error message similar to Fig. 4. (d) Test your application Save and close your workbook. NOTE: ALL CALCULATION SHOULD BE DONE THROUGH VBA. Personal income to Calculator Durham Financial Services Run Tax Calculator Clear Worksheet Durham Financial Services $72.929.63 First Name: Lastme: Annual Net Income 10 First Name Last Name SIN Annual Income CPP EL Provincial Tax Federal Tax Total Tax Net Take-home Monthly Bi-weekly Weekly Amin Ibrahim 123456789 $100,000.00 $3,100.45 $889.54 $7,041.76 $16.972.42 $24.014.18 $71.925.83 $5.994.15 $2.766.53 $1.383.27 SNE Generate Report Annual Income: 300000 12 13 14 Reset Form 15 Case Personal Income Tax Calculator Microsoft Excel Durham Financial Services one or more text boxes are emptyi! OK First Name: Amin Last Name: Ibrahim Annual Net Income SIN: 123456789 Generate Report Annual Income: Reset Form Close Fig. 4: Personal Income Tax Calculator Error Message
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started