90.2K

Verified Solution

Link Copied!

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$/$HST charged on sales has

historically been in the range of $3\%$ to $4\%$ of total sales. However, over the past five

years, GST$/$HST remitted seems to be increasing $$ yet there has been no change in

the federal rates. Management does not understand why it is having to remit so much

more GST$/$HST 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$/$HST 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$/$HST 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$/$HST 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$)$

$$ TaxRate$1$: applicable GST rate

$2018-2022$: total sales by product for the year indicated

$2018-($F$)$Tax to$2022-($F$)$Tax: GST$/$HST charged on that product for the year

Power BI Questions $($Taxation$)$

$3/2$

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

$$ TaxRate$1$: dollar amount of GST charged on invoice

$$ InvoiceTotal: total dollar amount of sales price $+$ applicable tax rate$($s$)$

Require Create a table visualization that shows the total sales and total GST$/$HST

collected on products using incorrect tax rates using poer bi

Answer & Explanation Solved by verified expert

See Answer