BUS 3343 AMS Clement Hardware Inc Data Analytics Customer Dataset Program

  

To answer the audit questions, you need to download the software to complete. The software operation process is already in the following documents. You need to answer the questions as required and use the software as required

All questions involving personal information, please ask me
Power BI Instructions – for Download
Microsoft offers a free desktop version of Power BI that you can download:
https://powerbi.microsoft.com/en-us/desktop/
I would recommend you try the download at least a couple of days in advance of your assignment to
avoid issues. The majority of students in this class will have already used this during this semester for
other accounting courses.
Once installed, it may prompt you to login/sign in, however this is optional, you do not need an account
to complete this assignment. To bypass this, simply use the X in the top right hand corner and proceed
with the activity.
Step by step instructions are included in the assignment document along with screenshots of what you
need to do.
There is a separate document containing data, this will open in the Power BI, that is the only way you
are able to use it.
If you are having struggles, there are tutorials available on U-Tube that will help – click the HELP button
in Power BI to take you there.
If you are a MAC User, let me know.
A couple of points to consider:
1. Report View – this is where you will produce your visualizations.
2. Visualizations – This is the wide array of visualizations available to present and interpret data.
3. Fields – Once your data has been linked to your Power BI file, you will find a listing of available
data there.
4. Help – this is where you will find your built in resources, including training videos, the blog, and
community chats – you can use these to troubleshoot as you work through it.
How your submission should look:
You will submit a series of screenshots of your visualizations, you will be prompted with the activity each
time you need to capture a screenshot for submission – bring these screenshots into Word and submit
the word document when complete. There are only 3 visualizations for this assignment to get you
familiar with the software.
Thanks, and welcome to Data Analytics, which will be much used as you move forward in your careers in
accounting.
Business 3343 – Power BI Assignment
Clement Hardware Inc. (CHI) is a small, family-owned hardware store in Montréal,
Québec. It has a small customer base that tends to place regular orders. You, CPA,
have just been assigned as the audit senior on the CHI audit for the period ended
December 31, 2018.
As senior, the first account you are to analyze is the allowance for doubtful accounts.
CHI has provided you with an electronic copy of its accounts receivable log for the past
two years to help you determine whether the allowance for doubtful accounts is
reasonable.
The dataset provided contained the following fields:
CustomerID
InvoiceNumber
InvoiceDate
DueDate
YearEndDate
InvoiceAmount
PaymentDate
PaymentAmount
Outstanding
DaysToSettle
DaysLate
Unique identifier for each customer
Invoice number for sales made on credit
Date of credit sale
30 days after the invoice date
December 31, 2018
Invoice amount (in Canadian dollars)
Date invoice was paid
Amount paid
InvoiceAmount less PaymentAmount
Days elapsed between InvoiceDate and PaymentDate
Days elapsed between DueDate and PaymentDate (or
YearEndDate if payment not received)
Required:
One of the key concerns when evaluating the allowance for doubtful accounts is a
customer’s payment history. CHI’s accounting system tracked both the number of days
taken to settle an invoice (DaysToSettle) and the number of days 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. Using the
data file “AUA-QUESTION2”:
i.
ii.
iii.
Create a table displaying all of the outstanding invoices at year end.
Create a table displaying all “late” outstanding invoices at year end.
Identify the customer that has the oldest outstanding invoices at year end.
Hint: Your visualization has to be selected before you can work on it. To select your
visualization, simply click on it in Power BI.
Instructions:
i.
You can create a detailed listing of the invoices outstanding at year end by using a
table. This table can 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.
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.
ii.
1. Create a duplicate of the table you created in (i), 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 and 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 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”.
iii. Use the table from (ii) and sort from highest to lowest based on “DaysLate” and
identify the customer with the highest number of days late.

Purchase answer to see full
attachment