WOULD NEED TO WORK ON BLACKBOARD ACCOUNT. Individual Data Analytics Project: Estimating Allowance for Doubtful Accounts
General:
You have been tasked with providing the CFO of your firm an analysis of the receivables outstanding at the end of 2018 and advising her as to what should be the ending balance of the allowance for doubtful accounts on the financial reports. After learning in accounting about the different methods of estimating bad debt expense you decided to analyze the aging and percentage of sales methods using historical data of the firm and use that information to provide an informed data-based report backing your conclusions.
You requested that the firm’s IT personnel provide you with historic information dating back 5 years. The information they supplied you contains the total sales on account for the year and the receivables outstanding at year-end of 2013-2017 for each customer as well as the dates they were paid off the following year. Any outstanding receivables which were not paid over the course of the year following the invoice were written off and sold for cents on the dollar to a debt collector (disregard the amount received for the sale of these receivables in your calculations).
Instructions to download the file:
1. Download the file “ReceivablesDataSimulation.xls” from Blackboard (Individual Data Analytics Project folder).
2. Open the file using Excel. The file is structured to produce a simulated database. To generate the database, click on the “Generate Data” button. You will need to “enable macros” on your program in order for the code to work.
3. Save the file under the name “AR_LastName_FirstName_SectionXXX.xls” you will need to submit this file on Blackboard under this name, with all your data work, along with a short report.
Explanations and assumptions:
Once the data is generated you will see 6 worksheets, each pertaining to accounts receivable outstanding at each year-end. Worksheets “Dec 31 2013”, “Dec 31 2014”, “Dec 31 2015”, “Dec 31 2016”, and “Dec 31 2017” contain the historical data, and worksheet “Dec 31 2018” contains the outstanding receivables for which you need to estimate the allowance (6 identical data sheets are created for grading purposes – see explanation below). There are five columns of data in each worksheet:
· Column A presents customer ID
· Column B presents the total amount invoiced during the year
· Column C presents the amounts outstanding at year end
· Column D presents the date the outstanding invoice was issued
· Column E presents the date the invoice was paid the following year (this column is N/A in the “Dec 31 2018”worksheet.)
· The firm had a different number of customers each year as well as total receivable sales.
Assume that if the receivable was not paid over the course of the following year, the entire amount is written off. In that case, the corresponding cell in column D remains blank.
Assume 4 categories of aging receivables: (1) 0-30 days outstanding, (2) 30-60 days outstanding, (3) 60-90 days outstanding, and (4) over 90 days outstanding.
Analysis:
a. For each year, calculate the percentage of accounts written off out of total sales on account.
b. For each year, calculate the percent written off in each outstanding receivable age (think about which dates are relevant when aging receivables).
c. Collect the data for years 2013-2017 (the dollar amount of receivables, the percentage of receivables written off out of receivable sales, and the percentage per aging category of outstanding receivables).
Worksheet “Solution” contains a table which HAS to be filled with the answers.
PLEASE NOTE THAT THIS IS THE ONLY SHEET THAT WILL BE GRADED – THEREFORE ALL ANSWERS SHOULD BE ENTERED ON THIS SHEET. DO NOT CHANGE LOCATION OF ANY OF THE CELLS IN THIS SHEET. ONLY THE HIGHLIGHTED CELLS WILL BE GRADED AND USED!!
Excel files will be auto graded. To be able to grade the assignment the file contains 6 additional worksheets (one for each data sheet created) that are locked and cannot be changed. The names of these worksheets are “Dec 31 2013 Copy”, “Dec 31 2014 Copy”, “Dec 31 2015 Copy”, “Dec 31 2016 Copy”, “Dec 31 2017 Copy”, and “Dec 31 2018 Copy”. Please do not try to alter these sheets.
d. Analyze your findings given the question you were asked (which method should be used): some questions you may want to answer: is receivable aging the right choice for the company given the historical data? Should all the years be included in the analysis? Should you use mean / median to calculate the percentages? Are there any extreme observations that should not be included in the analysis? Do the number of customers matter in a given year? Does the dollar amount of receivables vary significantly and what should you conclude given these differences?
e. Based on your recommendation, calculate the amount of bad debt expense you should report for 2018. Assume beginning balance of Allowance for Doubtful Accounts is zero in 2018.
Report:
You are required to prepare and submit a short report of your findings. The report should not be more than one page long and should be saved under the name “AR_LastName_FirstName_SectionXXX.pdf” (note: “PDF” format). The report should provide the following:
1. A brief description of the task you were given, the data used and your method of analysis.
2. A table presenting the results of the percentages of write-offs by “percent of sales” and by “aging group” for each year in the following format (same as “solution” worksheet):
Year
Total outstanding receivables
Percent of sales on accounts written off
Percent of outstanding receivable written off by Receivable age group
0-30
31-60
61-90
Over 90
2013
$
2014
$
2015
$
2016
$
2017
$
2018 (predicted)
$
Please be sure to include a heading and explanation of the table you present.
3. Your recommendations and the reasoning behind them – should you use percent of credit sales or aging of receivables? (Some questions you may want to consider are: is the aging method the right choice for the company given the historical data? Should all the years be included in the analysis? Should you use mean or median to calculate the percentages? Are there any extreme observations that should not be included in the analysis? Do the number of customers matter in a given year? Does the balance of total receivables vary significantly and what you conclude given these differences?)
4. Based on your recommendation, calculate the amount of bad debt expense you should report for 2018. Assume beginning balance of Allowance for Uncollectible Accounts is zero in 2018 (Provide the analysis of the credit sales or outstanding receivables in each aging category for 2018 that led you to this number in the table above).
5. Anything else you would like to share with your CFO (can you think of different data you might be able to use to get a better sense of the write-offs)?
Submitting your report:
1. The pdf report you saved under the name “AR_LastName_FirstName_SectionXXX.pdf” will need to be submitted on Blackboard along with all your data work (the excel file “AR_LastName_FirstName_SectionXXX.xls”).
2. Go to the “Individual Data Analytics Project” section of Blackboard and click on the “Accounts Receivable Analysis” assignment. Please follow directions to submit your final Project Paper (pdf) and the analysis (excel file).
3. Both of these files need to be submitted in order for you to be graded on your project. If either file is missing, you will get a zero on the project.
All submissions are due by April 21st at 11:59 PM.
