+1(978)310-4246 credencewriters@gmail.com
  

ACC 304
Accounts Receivable Data Analytics Assignment
School of Accountancy and MIS
DePaul University
Summer 2022
The purpose of this assignment is to introduce accounting students to the tools available in
Microsoft Excel to perform data analytics in accounting. There are two parts to the assignment.
In Part I you will be using Excel to work with data. In Part II you will use the data from the
Excel worksheet to answer questions. This assignment must be submitted in the Submissions
folder in D2L by 9:00 a.m., Wednesday, August 10, 2022. This assignment is worth 10% of
your total grade for Acc. 304.
You must submit in two separate files for this project:
•
•
Part I: an Excel file with worksheets 1-5
Part II: a Word document with your answers to questions 1-11.
No late submissions will be accepted. All submissions must include the Excel file and the
Word document.
Introduction:
•
•
•
Data analytics can broadly be defined as the process of examining sets of data with the
goal of discovering useful information from patterns found in the data. This discovered
useful information can be used to make better decisions.
Being well trained in data analytics is important for future accountants.
In Acc. 304 we will examine a spreadsheet containing outstanding A/R transactions. We
will use some features in Excel to allow an understanding of the data. This will allow the
company to make better decisions.
1
Part 1:
In this assignment, you will use the Excel files called “Acc 304 2022-A/R” to evaluate a
company’s Accounts Receivables and the related Allowance for Uncollectible Accounts. The
Excel file consists of Cambridge Corporation’s sales and accounts receivable data for 2021.
Cambridge sells restaurant equipment and operates in 10 different locations (Location 1, 2…..10)
in the United States. Each location tracks its own sales and accounts receivables.
The spreadsheet has 8 different columns:
COLUMN
TRANSACTION NO
LOCATION ID#
CUSTOMER ID#
SALES TRANSACTION
AMOUNT
SALES DATE
1/1/21 A/R BALANCE
12/31/21 A/R BALANCE
CURRENT YEAR END
DESCRIPTION
Sale transaction
Branch location
Specific customer
Sale made in 2021
Date of sale
Accounts receivable 1/1/2021 balance
Accounts receivable 12/31/2021 balance
Fiscal year ending date
Cambridge sets up an aging schedule with five different time categories 0-30 days, 31-60 days,
61-90 days, 91 to 120 days, and over 120 days. Based on past experience, Cambridge applies
percentages below to different time categories to estimate the required balance in the Allowance
for Doubtful Accounts.
Time
Category
1
2
3
4
5
Time
Category
under 30 days
31-60 days
61–90 days
91–120 days
over 120 days
Estimated % of A/R as
Uncollectible
2%
5%
10%
20%
30%
How to Use Excel: Linkedin learning has some short videos about how to use Excel. Use the
following link and you can login with your campus connect information.
The following link has videos about basic Excel and pivot tables which are quite helpful:
https://www.linkedin.com/learning/excel-essential-training-office-365
2
Part I: Using Excel : Use “Acc 304 2022-AR”
1) Open the Excel file and note the worksheet titled “Data”. All of your analysis will be on
additional worksheets you will create in this Excel file.
2) Create “Sheet 1” in the Excel file and create Pivot Tables to determine the following:
a. Totals for the following columns by location: (i.e. you will have a total for each
location of their Current ales, AR Prior Balance and AR Current Balance):
• Current Sales
• A/R Prior Balance
• A/R Current Balance
b. In Sheet 1, use the 3 columns to the right your results from (a) and calculate the
following ratios for each location:
i. A/R as a percentage of sales (A/R /Sales),
ii. A/R Turnover = Net Sales/Average Accounts Receivable
iii. A/R Days Uncollected = (365/A/R Turnover)
3) Create “Sheet 2” in the Excel file to do the following: You will use the IF and
VLOOKUP/XLOOKUP functions in Sheet 2. (you must use these functions)
a. Copy the data from the Data worksheet into Sheet 2
b. Calculate the number of days between the sale date and the fiscal year end date
for each transaction to determine the aging of the A/R. (Hint: you need to
use/create a column to calculate the age of A/R, which is the time between the
sales date and the year end 12/31/2021… the easiest way is to subtract the sale
date from 12/31/21. This will tell you the number of days since the sale occurred
(i.e. the aging for each sale.)
c. Label the columns to the right … one for each aging category and then a total.
d. Using the IF function to assign the value (1-5 from the table above) that
corresponds with each aging category in each column (i.e., 0-30 will be “1”, 3160 will be “2”, 61-90 will be “3”, etc.). For example, if the number of days since
the sale occurred (how long the A/R has been outstanding) is less than 31 assign
the value “1”, etc.
Your column headings should be as follows: Category 1, Category 2, Category 3,
Category 4, Category 5 and Total Categories
e. Add the Category columns horizontally (crossfoot) to compute “Total Categories”
for each line. The subtotal for each line should be the numbers 1, 2, 3, 4, 5.
f. Copy the A/R aging table from above into Sheet 2
g. Using the A/R aging table use the VLOOKUP/XLOOKUP function to assign an
uncollectible percentage to each sale based upon the aging category. For
example, using the VLOOKUP/XLOOKUP function if the total aging category is
a 2 then assign the percentage that corresponds to Category 2.
h. Calculate the required allowance for each line item multiplying the appropriate
aging percentage by the A/R balance for each line item.
i. Calculate the total required allowance for uncollectible accounts.
3
4) Create “Sheet 3” to determine the ending A/R balance and the Allowance for
Uncollectible Accounts for each time category. Create a Pivot Table to determine these
totals.
5) Create “Sheet 4” to determine the ending A/R balance and the Allowance for
Uncollectible Accounts by each location for each time category. Create a Pivot Table to
determine these totals
6) Create “Sheet 5” to determine the A/R balance and the balance of Allowance for
Uncollectible Accounts for each customer. Create a Pivot Table to determine these totals.
Part II: Use the data from the Excel file worksheets to answer the following questions
1. How many locations did Cambridge Corporation have in 2021?
2. How many total customers did Cambridge Corporation have in 2021?
3. Based upon the ratios you calculated identify which location has the highest A/R as a
percentage of sales?
4. Based upon the ratios you calculated in “Sheet 1” identify which location has the lowest
A/R turnover?
5. Based upon the ratios you calculated in “Sheet 1” identify which location has the longest
A/R collection period?
6. Complete the table below: What is the dollar balance of A/R and estimated amount of for
each aging category as of December 31, 2021?
Time
Category
1
2
3
4
5
Time
Category
under 30 days
31-60 days
61–90 days
91–120 days
over 120 days
Estimated % Dollar Amount of
of A/R as
A/R
Uncollectible
2%
5%
10%
20%
30%
4
Estimated Amount
Uncollectible
7. Complete the table below to analyze the customers. Using Sheet 5 identify which
customer has the highest dollar amount of A/R in each aging category.
Time
Category
1
2
3
4
5
Time
Category
under 30 days
31-60 days
61–90 days
91–120 days
over 120 days
Customer
Number
Highest Dollar
Amount of A/R for
this Customer
8. Which customer has the highest total amount of uncollectible accounts? What is this
amount of uncollectible accounts?
9. What is the required ending balance in the Allowance for Uncollectible Accounts for
Cambridge at the end of 2021? (this is based upon your analysis in the excel worksheets)
10. The balance of Cambridge’s Allowance for Uncollectible Accounts was $550,000 as of
January 1, 2021. Based upon your analysis in the excel worksheet what is the amount of
Bad Debt Expense that needs to be recorded as of December 31, 2021?
11. Based upon the data you have analyzed what recommendations would you have for
management about any specific locations or customers regarding the credit
policies/adherence to credit policies?
5
6

Purchase answer to see full
attachment

  
error: Content is protected !!