Data Analytical Project
(Adapted from CPA Power BI)Scenario
Hannaford’s General Store Ltd. (HGL) is a small, family-owned hardware 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:
- Customer ID
- Unique identifier for each customer
- Invoice Number Invoice number for sales made on credit
- Invoice Date Date of credit sale
- Due Date 30 days after the invoice date
- Year End Date December 31, 2018
- Invoice amount (in Canadian dollars)
- Payment Date
- Date invoice was paid
- Payment Amount
- Amount paid
- Outstanding Invoice Amount less Payment Amount
- Days To Settle
- Days elapsed between Invoice Date and Payment Date Days
- Late 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.
Chartered Professional Accountants of Canada, CPA Canada, CPA are trademarks and/or certification marks of the Chartered Professional Accountants of Canada. 2019, Chartered Professional Accountants of Canada. All Rights Reserved.
Instructions
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.
- Create a card visualization for total sales.
- Create a stacked column visualization for total sales by year.
- Create a card visualization for number of customers.
- 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.
- Create a table displaying total sales by customer for 2017 and 2018.
- Create a card visualization for the number of invoices outstanding at year end.
- 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.
