Hannaford’s General Store Ltd. (HGL) is a small, family-owned hardwarde store in Winnipeg, Manitoba. It has a small customer base that tends to place regular orders. You, CPA, have just been assigned as the audit senior on the inaugural HGL audit for the period ended December 31, 2018. Previously, HGL had reviews completed by your firm. As senior, you will be responsible for dealing with accounts where professional judgment is required, including accounts that require management estimation. The first account you are to analyze is the allowance for doubtful accounts. HGL has provided you with an electronic copy of its account receivable log for the past two years to help you determine if the allowance for doubtful accounts is appropriate. HGL’s policy is to record an allowance equal to 3% of outstanding receivable balances at year end. At December 31, 2018, HGL has recorded an allowance of $1,040.

The dataset provided contained the following fields:

CustomerID Unique identifier for each customer

InvoiceNumber Invoice number for sales made on credit

InvoiceDate Date of credit sale

DueDate 30 days after the invoice date

YearEndDate December 31, 2018

InvoiceAmount Invoice amount (in Canadian dollars)

PaymentDate Date invoice was paid

PaymentAmount Amount paid

Outstanding InvoiceAmount less PaymentAmount

DaysToSettle Days elapsed between InvoiceDate and PaymentDate

DaysLate Days elapsed between DueDate and PaymentDate (or YearEndDate if payment not received)

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.

Task #1

Sales analysis

To begin your analysis of the allowance for doubtful accounts, it’s important to get a good understanding of HGL’s operations, including the magnitude of sales made on credit over the past two years and whether sales on credit are increasing or decreasing.

i. Create a card visualization for total sales.

ii. Create a stacked column visualization for total sales by year.

iii. Create a card visualization for number of customers.

iv. Create a table displaying total sales by customer, followed by a cluster bar chart displaying each customer’s sales as a percentage of the total.

v. Create a table displaying total sales by customer for 2017 and 2018.

vi. Create a card visualization for the number of invoices outstanding at year end.

vii. Create a card visualization for the dollar value of the invoices outstanding at year end.

Submit your response to each using screenshots from Power BI and by following the steps outlined in Appendix I.

Task #2

Customer analysis

One of the key concerns when evaluating the allowance for doubtful accounts is a customer’s payment history. HGL’s accounting system tracked both the number of days taken to settle an invoice (DaysToSettle) and the number of days that the payment was late (DaysLate).

As items sold in the last 30 days of the fiscal year are not “due” until the new year, it is reasonable to exclude these current items from our analysis of old invoices.

i. Create a table displaying all of the outstanding invoices at year end.

ii. Create a table displaying all “late” outstanding invoices at year end.

iii. Create a table that displays customers who regularly pay more than 180 days late.

Submit your response to each using screenshots from Power BI and by following the steps outlined in Appendix II.

Task #3

Prepare a memo to the audit partner on the appropriateness of the allowance for doubtful accounts using the information you have gathered above. The partner may wish to discuss the allowance with HGL’s management, so be as specific as possible with your examples and state any assumptions you have made in your analysis.

Tunde and Christine are a married couple and partners in a business that sells books and stationery. The business proves to be extremely successful, and they open up a number of branches. In order to limit their liability, they instruct their solicitor to incorporate the business, calling the new company Tunchris Plc. Around the same time, EPP is about to release a new type of stationery. Tunde and Christine are keen to acquire as many of these stationery as possible. Tunde hears of a potential source (Readwide) and is offered fifty boxes of stationery. Eager to purchase the stationery, Tunde does not wait until the company is incorporated and enters into a contract with Readwide ‘for and on behalf of Tunchris Plc. Christine is also offered a number of stationery and, prior to the company being incorporated, she enters into a contract with Plabo Ltd for forty boxes of stationery. She signs the contract ‘Tunchris pp. Christine (a director).’ The certificate of incorporation is issued and, at the first board meeting of Tunchris Plc, Tunde and Christine ratify both contracts. Tunde and Christine both have large stationery stock. Tunde sells to Tunchris Plc a number of stationery that he acquired prior to engaging in the company’s formation. Christine sells to the company a number of stationery that she acquired whilst the company was being formed. Shortly thereafter, Readwide refuse to sell Tunde the fifty boxes promised, as it believes that it can sell all the stationery to the public for a higher price. Christine is concerned that Plabo Ltd will also refuse to sell the forty boxes promised. A shareholder of Tunchris Plc, Wisdom, discovers the above and seeks your advice regarding whether or not any breaches of the law have occurred. Would your answer differ if Tunchris Plc Ltd had been an off the shelf company purchased by Tunde and Christine?