J. HTAX230-1-Jan-Jun2020-SA2-BL-10112019 (1)

School: Northern Virginia Community College - Course: CON 090 - Subject: Accounting

QUESTION 1(35 marks) Template Open the Excel template for this assessment and navigate to the 'Inputs' and 'Calculations'sheets. Scenario You are a consultant for a tax advisory firm. The tax advisors would like a spreadsheet that can assist them to calculate the tax liability in the 2020 year of assessment for individuals with medical aid and medical expenses. The spreadsheet should therefore be able to calculate their schedule tax, section 6A medical aid tax credit and section 6B medical aid tax credit. The spreadsheet should be as simple and easy as possible for them to use, and therefore you want to use formulas to automate the process as much as possible.Note:the tax advisors only assist people 65 years or older and families with disabilities. Therefore, the section 6B medical aid tax credits should be calculated using the formula applicable to this group. Course work To be able to complete this assessment, you will need to refer to the content from this module on medical aid tax credits. This assessment is based on example 7.8 in your textbook, but is not limited to this example. Research and Excel skills required To be able to complete this assessment, you will need to have knowledge on the following Excel functions. You will need to research these using the Excel help function or the internet if you are not familiar with Excel: V-lookups 'If' functions Basic equations: add, subtract, multiply, divide, percentages Instructions / Tasks-Inputs Sheet 1. Refer to the'Inputs'sheet. 2. This sheet is what the tax advisors will use. They will enter: The person's annual salary in cell B2

92HTAX230-1-Jan-Jun2020-SA2-BL-V2-10112019Theannual contributions that the person's employer makes to their medical aid in cell B3 The annual contributions that the person makes to their medical aid in cell B4 The total annual qualifying medical expenses in cell B5 The number of people on the medical aid in cell B6 The age of the person whose tax liability is being calculated in cell B7 3.As the 'Inputs' sheet will be used by advisors, you need not change anything here. However, you should use it to test if your 'Calculations' spreadsheet works. Instructions / Tasks-Calculations Sheet 1.Refer to the 'Calculations' sheet. This is the sheet that will calculate the tax liability for the individual. The tax advisors should not have to enter anything on this sheet. Therefore, it should be fully automated, based on the formulas that you use. 2. Refer to the following section of the sheet: This is the tax table for 2020. You need not change anything here. However, you will need to refer to this table to calculate the schedule tax for the individual. 3. Refer to the following section of the sheet:

93HTAX230-1-Jan-Jun2020-SA2-BL-V2-10112019This part of the spreadsheet has been completed for you. The total monthly section 6A medical aid tax credits will automatically be calculated based on the number of people on the medical aid (entered into the 'Input' sheet by the tax advisor). 4. Refer to the following section of the sheet and complete it using the guidelines given: In cell C3, enter a formula to calculate the total taxable income of the individual, based on the 'Inputs' sheet. In cellC5, use the Vlookup function to lookup the individual's taxable income on the table and return the base tax (column G) associated with that taxable income. In other words, you are returning the highlighted part of the below SARS tax table taken from the SARS website: Taxable income (R)Rates of tax (R) 1-195 85018% of taxable income 195 851-305 85035 253 + 26% of taxable income above 195 850 305 851-423 30063 853 + 31% of taxable income above 305 850 423 301-555 600100 263 + 36% of taxable income above 423 300 555 601-708 310147 891 + 39% of taxable income above 555 600 708 311-1 500 000207 448 + 41% of taxable income above 708 310 1 500 001 and above532 041 + 45% of taxable income above 1 500 000 In cell C6, use the Vlookup function to lookup the individual's taxable income on the table and return the marginal tax rate (column H) associated with that taxable income. In cell C7, use the Vlookupfunction to lookup the individual's taxable income and return the value in column J associated with that taxable income.

94HTAX230-1-Jan-Jun2020-SA2-BL-V2-10112019In cell C8, calculate the additional tax (i.e. not the base tax) using the person's taxable income and the amounts in cells C7 and C8. In other words, you are calculating the highlighted part of the below SARS tax table taken from the SARS website: Taxable income (R)Rates of tax (R) 1-195 85018% of taxable income 195 851-305 85035 253 + 26% of taxable income above 195 850 305 851-423 30063 853 + 31% of taxable income above 305 850 423 301-555 600100 263 + 36% of taxable income above 423 300 555 601-708 310147 891 + 39% of taxable income above 555 600 708 311-1 500 000207 448 + 41% of taxable income above 708 310 1 500 001 and above532 041 + 45% of taxable income above 1 500 000 In cell C9, calculate the total schedule tax, which is the total of the base tax and additional tax. 5. Refer to the following section of the sheet: CALCULATION OF s6B MEDICAL AID TAX CREDIT Section 6B medical tax credit In cells A11:C19, you will set up the calculation for the section 6B medical aid tax credit. Use your own descriptions, layout and formulas here. Ensure that everything flows logically and is easy to understand. Use column B for descriptions and column C for formulas.