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)