accounting project analysis and 1 page report.

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 ..... 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 ... 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. 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 D remains blank. Assume 4 categories of aging receivables: (1) 0-30 days outstanding, (2) 30-60 days outstanding, (3) 6090 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)