Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit



MGAB01 FINANCIAL ACCOUNTING I  2021 Fall

 

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.

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 might 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 Quercus under this name, with all your data work, along with a short report in pdf format (see below).

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. 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 and 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 E 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, find the percentage of accounts written off out of total sales on account.

b.    For each year, find 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 amount of receivables, the percentage of receivables written off out of receivable sales, and the percentage per aging category of outstanding            receivables).

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 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_SectionXX.pdf”. 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:

 

 

Year

Total

outstanding receivables

Percent of sales on account     written off

Percent of outstanding receivable written off by Receivable age group

 

 

 

0-30

30-60

60-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 aging of receivables or percent of sales? What should be the percentages? Why?

4.    What should be the bad debt expense recognized in 2018? (provide the analysis of the                outstanding receivables / credit sales 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 sot use to better get a sense of the write-offs)