so my question is basically how would you approach analysis B? Please help me to explain how do you find the percent written off in each outstanding receivable age? Thanks
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. 1 Analysis: a. C. 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). 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? 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. e. 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 Percent of sales Percent of outstanding receivable written off by outstanding on account Receivable age group receivables written off 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) A B E 2014-12-31 Date: customer 1 NDO Soooo Vou AWN. 10 11 12 13 total sales to customer 174,744 171,555 102,033 96,049 137,383 197,473 194,912 87,486 119,849 136,266 50,098 162,382 159,723 102,965 139,777 145,390 226,903 164,633 183,829 221,423 amount 104,314 132,526 19,616 74,675 48,405 99,481 111,788 63,506 47,354 53,988 9,993 78,637 85,991 89,909 64,701 82,823 136,797 67,456 145,469 137,412 date purchased date paid 2014-10-21 2015-01-13 2014-12-18 2015-01-05 2014-09-25 2015-01-27 2014-10-03 2015-01-26 2014-11-27 2015-01-27 2014-11-12 2015-01-05 2014-11-24 2015-01-14 2014-12-23 2015-02-06 2014-12-05 2015-01-26 2014-10-09 2015-01-15 2014-09-27 2015-02-23 2014-10-14 2015-01-20 2014-10-12 2015-01-05 2014-12-01 2015-01-14 2014-09-12 2015-02-15 2014-09-16 2015-01-19 2014-11-07 2015-01-01 2014-11-13 2015-01-20 2014-11-12 2015-01-09 2014-11-19 2015-01-02 19 20 21 183 470 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. 1 Analysis: a. C. 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). 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? 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. e. 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 Percent of sales Percent of outstanding receivable written off by outstanding on account Receivable age group receivables written off 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) A B E 2014-12-31 Date: customer 1 NDO Soooo Vou AWN. 10 11 12 13 total sales to customer 174,744 171,555 102,033 96,049 137,383 197,473 194,912 87,486 119,849 136,266 50,098 162,382 159,723 102,965 139,777 145,390 226,903 164,633 183,829 221,423 amount 104,314 132,526 19,616 74,675 48,405 99,481 111,788 63,506 47,354 53,988 9,993 78,637 85,991 89,909 64,701 82,823 136,797 67,456 145,469 137,412 date purchased date paid 2014-10-21 2015-01-13 2014-12-18 2015-01-05 2014-09-25 2015-01-27 2014-10-03 2015-01-26 2014-11-27 2015-01-27 2014-11-12 2015-01-05 2014-11-24 2015-01-14 2014-12-23 2015-02-06 2014-12-05 2015-01-26 2014-10-09 2015-01-15 2014-09-27 2015-02-23 2014-10-14 2015-01-20 2014-10-12 2015-01-05 2014-12-01 2015-01-14 2014-09-12 2015-02-15 2014-09-16 2015-01-19 2014-11-07 2015-01-01 2014-11-13 2015-01-20 2014-11-12 2015-01-09 2014-11-19 2015-01-02 19 20 21 183 470
