ACCT 240 GST Big Data Project Information Fall 2021 Tanakas Medical Supplies It is September 2021 and you have just started your new position with Tanakas Medical Supplies (TMS) as the assistant financial manager. TMS is located in Victoria, BC sells medical supplies within British Columbia. On your third day on the job, Tina Tanaka, the owner, notifies you that she just received a letter from Canada Revenue Agency. They informed her that they will be undertaking a GST audit on her books next month. The audit will consist of the latest three years of GST returns. TMS carries a large number of zero-rated products, but also has products that have either only GST applied, or GST and PST applied. Their sales system is integrated with its accounting software, and the sales, inventory, cost of goods sold, and sales tax accounts are updated nightly. When preparing its GST return, TMS uses the balances in the general ledger but does not cross reference with individual sales invoices in the sales system. Tina asks you to review the data and inform her if you suspect there are any problems. If possible, if you find an error, you should pull the related invoice so that she can review it to determine what happened. Tina provides you with a data file containing the following: ProductNumber: a unique identifier assigned to each new product; the first six digits represent the year and month the product was added, and the last two digits represent the order in which the product was added ProductDescription: a description of the product Price: the price charged by Northern (cost + 100% markup) TaxRate1: applicable tax rate 2016-2020: total sales by product for the year indicated 2016-(F)Tax to2020-(F)Tax: tax charged on that product for the year) Tina has asked that you send her an email after your initial review so that she has an overview of the scope of any problems.

Required: Part 1: Create a pivot table in excel to analyse the data to determine which product sales invoices to pull from the files to review. Part 2: Write an email to Tina to explain your findings. Required Submission: 1. Create a word document and save it as LASTNAME_GST2021F 2. Label your word document with your name and student number on the first two lines. 3. Take screen shots of the following steps as you complete your analysis** a. A table showing all products and their applicable tax rates b. Additional columns showing the total sales and the total tax collected on products c. Additional column showing the tax amount that should have been collected d. A table showing where the errors have occurred e. A breakdown by year where the errors have occurred 4. Write an email to Tina Tanaka to update her on your progress and include it on a separate page of your word document 5. Submit your word document to the assignments folder.