Question
Sandy medical supplies Inc. (Sandy) is a small, family-run business in China that has been in business for almost 50 years. Sandy sells medical supplies
Sandy medical supplies Inc. (Sandy) is a small, family-run business in China that has been in business for almost 50 years. Sandy sells medical supplies
to small and medium-sized medical clinics across the country.
Five years ago, Sandy started expanding its product offering by carrying some
specialized medical equipment, which it sells to small hospitals in USA. Sales tax
has always been an issue for Sandy, because it carries so many zero-rated products;
the company is now even more concerned because of the increase in specialized
equipment.
Because Sandy carries a large number of zero-rated products (that is, products to
which GST/HST applies at the rate of 0%), total GST charged on sales has historically
been in the range of 3% to 4% of total sales. However, over the past five years, GST
remitted seems to be increasing — yet there has been no change in the federal rate.
Management does not understand why it is having to remit so much more GST and has
asked for your advice.
Five years ago, Sandy invested in a new sales system that 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, Sandy uses the
balances in the general ledger but does not cross reference with individual sales
invoices in the sales system.
In addition, because of the special rules around medical devices, the various taxes (if
applicable) are assigned at the product level rather than at the sales level. This ensures
that zero-rated products are not taxed in error.
Sandy’s management has asked you to review its GST activity over the past five
years to help it understand what has been happening. As a first step, management has
asked you to compile some sales and GST information.
You have been provided with a data file (TAX_Power_BI_data) containing the following:
First tab: “Product List”
• 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 GST rate
• 2014-2018: total sales by product for the year indicated
• 2014-(F)Tax to2018-(F)Tax: GST charged on that product for the year
Second tab: “Sales”
• customerID: a unique identifier assigned to each customer
• Country: country of customer
• InvoiceNumber: number assigned to each invoice, unique to each sale
• InvoiceDate: date of the sale
• Product: dollar amount of product sales from the invoice
• TaxRate1: dollar amount of GST charged on invoice
• InvoiceTotal: total dollar amount of sales price + applicable tax rate(s)
Required:
a) Create a table visualization for sales by year.
b) Create a column chart (graph) visualization for sales by year.
c) Create a table visualization for sales by year with a column for GST as a percentage
of sales. (Hint: you need to add a new column for this step)
d) Create a table visualization of all products and their applicable tax rate
e) Create a table visualization that shows all products with a tax rate greater than 5%.
f) Create a table visualization that shows the total sales and total GST collected on
products using incorrect tax rates
g) Create a table visualization that shows the amount that should have been collected
using the correct 5% rate.
h) Create a table visualization that shows the calculation of the known GST error.
(Hint: you need to add a new column for this step)
Step by Step Solution
3.42 Rating (155 Votes )
There are 3 Steps involved in it
Step: 1
a Create a table visualization for sales ...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