Case Analysis

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.

Les désignations « Comptables professionnels agréés du Canada », « CPA Canada » et « CPA » sont des marques de commerce ou de certification de Comptables professionnels agréés du Canada.

© 2019 Comptables professionnels agréés du Canada. Tous droits réservés. 24/04/18

Assurance — Power BI Problem

Scenario (90 minutes)

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.

Assurance — Power BI Problem Problem

2 / 19

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.

Assurance — Power BI Problem Problem

3 / 19

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.

Assurance — Power BI Problem Problem

4 / 19

Appendix I

Step 1:

Open Power BI and import the file “Assurance_PowerBI_Data” file using the “Get Data”

function. Use the screenshots below to guide you through the steps.

1. Select “Get Data”.

2. Select “Excel” and then find your

“Assurance_PowerBI_Data” file.

Assurance — Power BI Problem Problem

5 / 19

3. Select the “Accounts

Receivable Log” workbook.

You should see a preview of the

data that looks like this.

4. Click “Load”.

Assurance — Power BI Problem Problem

6 / 19

Step 2:

Once you have successfully imported the data into Power BI, you can now work with the

data. Start by creating a visualization using the “Card” function and the “InvoiceAmount”

field to determine the total amount of sales made on credit.

Now customize the appearance of your visualization. You can add titles and

background colours and remove data labels. At a minimum you want to add a title.

Submission: Take a screenshot of your card, with the title, and include it as your response to task 1.i.

1. Select “Card” under

visualisations.

2. Select “InvoiceAmount”

1. Select the paint roller

(i.e. formatting menu).

2. Toggle the Title to On, and type

“Total Sales” into the “Title Text” to

add a title to the card.

Set the font size to 18 and choose

the centre alignment.

Assurance — Power BI Problem Problem

7 / 19

Step 3:

Hint: If you right-click on the Visualization tab (“page 1”), you can rename the tab to reflect your work. You can also “duplicate” the page so that your initial analytical work is transferred to a new page, where further analysis can be performed if required.

Hint: Ensure that your dates imported correctly. Sometimes, they default to a number format. If this happens select “edit queries”, right click on the field you want to update, for each date column select “change type” and change to “date” and select “replace current”. Once updated, apply changes.

1. In a new tab, select the

“Stacked column chart”

2. Select “InvoiceDate” from the

dataset and drop it into the “Axis”

field.

The “InvoiceDate” field, provides

the fiscal quarter, month, and day,

in addition to the year. You only

need the information by year.

Eliminate the unwanted

information by simply clicking the

X next to the data you want to

exclude. Exclude the quarter,

month, and day.

3. Select “InvoiceAmount”

from the dataset and drop it

into the “Value” field.

Assurance — Power BI Problem Problem

8 / 19

Now add a title.

Submission: Include a screenshot of the stacked column chart you created displaying the total amount of sales by year as your response to task 1.ii.

Step 4:

Equally important as knowing the amount of sales on credit is knowing the number of customers that HGL has. This could impact HGL’s exposure to credit risk, and factor into your assessment of the allowance for doubtful accounts.

1. In a new tab, create a

card visualization. 2. Select “customerID”,

3. Adjust your fied to

“Count (Distinct)”.

You must select the

distinct option to

make sure that you

are only counting

distinct IDs.

1. Select the paint roller.

2. Toggle the Title to On, and type

“Total Sales by Year” into the “Title

Text” to add a title to the card.

Set the font size to 18 and choose

the centre alignment.

Assurance — Power BI Problem Problem

9 / 19

Submission: Include a screenshot of the card you created displaying the count for number of customers as your response to task 1.iii. Be sure to format your visualization appropriately.

Step 5:

It is now possible to consider how much of HGL’s reported credit sales are attributable to each customer.

1. In a new tab, create a

table visualization.

2. Select the fields “customerID” and “InvoiceAmount” to display the customers and their credit sale amounts.

Assurance — Power BI Problem Problem

10 / 19

To quickly present HGL’s credit exposure by customer, create a clustered bar chart.

2. Your table will likely not display all of the data initially. Expand the size of the table to show all data by clicking on and dragging the edge.

1. In a new tab, select the

“Clustered bar chart”

2. Select “CustomerID” from

the dataset and drop it into

the “Axis” field, and “Invoice

Amout” into the Value field.

Assurance — Power BI Problem Problem

11 / 19

To add some visual “punch” to your chart, on the formatting menu, add a title, background colour, and turn Data Labels to “on.”

Submission: Include a screenshot of the table and the clustered bar chart you created displaying the total sales by customer as your response to task 1.iv.

Assurance — Power BI Problem Problem

12 / 19

Step 6:

Up to this point, you have been examining total credit sales for the past two years. Now it is time to consider if sales on credit have grown year over year. A quick way to do this is to duplicate the table from the previous step.

Remove (delete) the clustered bar chart from the new page, to give yourself some additional working room. Activate the table (by clicking on it).

1. Duplicate the tab from Step 5 above

by right clicking on the tab and

selecting Duplicate Page. In the new

tab click on the table to select it.

2. Add the field “InvoiceDate”. You should now have the “customerID”, “InvoiceAmount”, and the subfields for “InvoiceDate” (Year, Quarter, Month and Day) in your table.

Assurance — Power BI Problem Problem

13 / 19

Note that the sum of the “InvoiceAmount” for each table for each year should equal the total sales amount. The same rule applies to the number of customers.

Submission: Include a screenshot of the tables you created displaying the total sales by customer by year as your response to task 1.v.

3. Remove the quarter, month, and day information (refer to instructions in Step 3 if required)

and leave the year. Then, copy and paste the table (click on the table, and then use the copy

and paste options on the Home ribbon) on the same tab, so you have two tables with the same

information (you can move the tables by dragging and dropping).

4. Now, use the “Filters” feature of Power BI to make one table showing data for 2017 and another table showing data for 2018.

To do this:

 Use the drop-down arrow by the “InvoiceDate - Year” field under Filters to expand your filter options and in the drop- down menu under “Filter Type” choose “Basic filtering”.

 You will see a list of all years included in the table and you will have the option to select or de-select each year.

o For the first table, select “2017”.

o For the second table, select “2018”.

Assurance — Power BI Problem Problem

14 / 19

Step 7:

Once you have a good understanding of sales on credit, it is time to examine the accounts receivable balance at year end. You should consider the number of outsanding invoices and balance of the accounts receivable, because this may have an impact on other tests of details performed later in the audit.

To determine the number of invoices outstanding at year end, a card is again the most appropriate tool. Create a card on a new tab:

A simple count will give you all invoices for which an outstanding balance was calculated.

Submission: Include a screenshot of the card you created displaying the total number of invoices outstanding as your response to task 1.vi.

1. In a new tab, create a

card visualization.

2. Select ‘Outstanding.

3. Be sure to use the

drop-down menu on the

count of the oustanding

under the Field heading

and select the “Count

(Distinct)” feature for this

visualization.

Assurance — Power BI Problem Problem

15 / 19

Step 8:

Just like when you calculated total sales (Task #1, Step 2), the total balance ($) outstanding at December 31, 2018, can also be easily displayed using a card.

Submission: Include a screenshot of the cards you created displaying the total dollar value of invoices outstanding as your response to task 1.vii.

1. In a new tab, create a

card visualization.

2. Select ‘Outstanding.

Assurance — Power BI Problem Problem

16 / 19

Appendix II

Customer analysis

Step 1:

You can also view a detailed listing of the invoices outstanding at year end by using a table. This table will be the starting point for future analysis of the likelihood of collection of the receivable and the valuation of the allowance for doubtful accounts.

1. In a new tab, create a

table visualization.

2. Select the “customerID”, “InvoiceNumber”, and “Outstanding” fields.

Assurance — Power BI Problem Problem

17 / 19

Submission: Include a screenshot of the table displaying all of the outstanding invoices at year end as your response to task 2.i.

3.Sort using the outstanding column from highest to lowest (click on column heading until it sorts in the order you need).

You may also need to resize your table.

Assurance — Power BI Problem Problem

18 / 19

Step 2:

Submission: Include a screenshot of the table displaying the filtered outstanding invoices , dates, and days late at year end as your response to task 2.ii. (Okay if table does not show all rows).

1. Create a duplicate of the table you created in Step 1, so

that you now have two identical tables. Select the second

table. 2. Building on the detailed outstanding list, select the “InvoiceDate” and “DaysLate” fields to add them to the table.

3. As the fiscal quarter is not required for this analysis, you can remove that information by clicking on the X for the “Quarter” field under the “InvoiceDate” Values.

4. You will need to add some additional filtering to the table to eliminate the “current” invoices.

 Use the “Filters” and select the drop- down arrow for the “DaysLate” field,

o Select “is greater than” from the drop-down options

o Type in “0” o Click “Apply filter”.

This means that only invoices that are at least one day past the due date are included in your data subset.

5. We are also only interested in outstanding balances greater than 0, so using the same process select the “Outstanding” field and select the “is greater than” option from the drop- down menu, type in “0”, and click “Apply filter”.

Assurance — Power BI Problem Problem

19 / 19

Step 3:

Finally, consider the oldest outstanding invoices in the accounts receivable log. Using a table, identify customers who have invoices more than 180 days late.

Submission: Include a screenshot of the table that displays customers who regularly pay more than 180 days late as your response to task 2.iii.

1. In a new tab, copy and paste your table from task 2.ii, which displays the the number of days late for each invoice. Using the drop down menu next to “DaysLate” select “Average.” This changes the focus of the information from individual invoices to customer payment history.

3. Add a filter for the average of “DaysLate” by selecting the drop-down menu, choosing “is greater than”, typing 180, and clicking “Apply filter”.

The DaysLate filter can be removed by clicking the “x” in the right-side of the filter.

2. Now remove “InvoiceDate” and “InvoiceNumber” from Values and add “InvoiceAmount”. HINT: You can always rearrange the order of the fields by dragging and dropping them into your desired position.