POWER BI QUESTIONS (TAXATION) Question 1 Northern Medical Supplies Inc

POWER BI QUESTIONS (TAXATION)

Question 1

Northern Medical Supplies Inc. (Northern) is a small, family-run business in northern Ontario that has been in business for almost 20 years. Northern sells medical supplies to small and medium-sized medical clinics across the country. It also has a small retail storefront for walk-in customers who require the medical products at home.

Three years ago, Northern started expanding its product offering by carrying some specialized medical equipment, which it sells to small hospitals in Canada. Sales tax has always been an issue for Northern, because it carries so many zero-rated products; the company is now even more concerned because of the increase in specialized equipment.

Because Northern 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, Northern 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, Northern 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.

Northern’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 (the sales system can also produce summaries by provincial rate, but that is outside the scope of this project)

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:

1. Create a table visualization for sales by year.
2. Create a column chart visualization for sales by year.
3. Create a table visualization for sales by year with a column for GST as a percentage of sales.
4. Create a card visualization with the count of products.

Follow the steps outlined below.

Hints:

• Your visualization has to be selected before you can work on it. To select your visualization, simply click on it in Power BI.
• Using Microsoft’s Snipping Tool, you can easily capture an image of the specific visualization requested.

Question 2

Further to Question 1, management now wants you to use Power BI to further analyze the product and sales information you have imported to identify the GST errors in Northern’s sales system.

Required:

1. Create a table visualization of all products and their applicable tax rate.
2. Create a table visualization that shows all products with a tax rate greater than 5%.
3. Create a table visualization that shows the total sales and total GST collected on products using incorrect tax rates.
4. Create a table visualization that shows the amount that should have been collected using the correct 5% rate.
5. Create a table visualization that shows the calculation of the known GST error.

Use the same Power BI file from Question 1.