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 uncollectible accounts on the balance sheet. 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. You requested that the firm's IT personnel provide you with historic information dating back five years (2013-2017). You are going to use that information to provide an informed data-based report backing your conclusions. Instructions to download the file: 1. Download the file "ReceivablesDataSimulation.xlsm" from Blackboard. 2. Open the file using Excel. You might need to "enable macros" on your program for the code to work. To generate the data, click on the "Generate Data" button. The file produces a simulated database (different data for each student). 3. Save the file under the name (any of these four Excel file extensions) "AR_LastName_FirstName_StudentID.xlsx/xlsm/xltx/xltm" - you will submit this file on Blackboard under this name, with all your data work, along with a short report. Explanations and Assumptions: Once the data are generated you will see six worksheets, each pertaining to accounts receivables outstanding at each year-end. The worksheets "Dec 31 2013", "Dec 31 2014", "Dec 31 2015", "Dec 31 2016", and "Dec 31 2017" contain the historical data, and the worksheet "Dec 31 2018" contains the outstanding receivables for which you need to estimate the allowance. The firm had a different number of customers each year as well as total credit sales. There are five columns in each worksheet: - Column A: the customer ID - Column B: the total sale on account and invoiced during the year - Column C: the account receivable balance outstanding at year-end - Column D: the date when the invoice was issued - Column E: the date when the invoice balance was paid off in the following year (this column is not in the "Dec 312018 " worksheet). c. 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?) d. 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). Assume that if the receivable was not paid over the course of the following year, the entire amount would be written off. In that case, the corresponding cell in column E remains blank. Assume four categories of aging receivables: (1) 0-30 days outstanding, (2) 31-60 days outstanding, (3) 61-90 days outstanding, and (4) above 90 days outstanding. The worksheet "Solution" contains a table which needs to be filled with the answers. The "Solution" sheet is the only sheet that will be graded - therefore, all answers should be placed on this sheet. Do not put anything on other worksheets. Excel files will be auto graded. For the system to work, you cannot change the names of the data sheets or any of the data columns (a-e in the files) - that includes adding sums, averages or anything else to these columns. You can add as many work sheets as you like to the file but do not change / add any data to columns A-E in the existing data sheets. Requirements: 1. Submit Excel file "AR_LastName_FirstName_StudentID" to Blackboard - the "Solution" worksheet must have: a. For each year, calculate the percentage of account receivables written off out of total credit sales. b. For each year, calculate the percentage of account receivables written off in each category of outstanding receivable age (think about which dates are relevant when assigning the categories of aging receivables). c. Collect the data for years 2013-2017 (total outstanding receivables, percentage of receivables written off relative to credit sales, and percentage of receivables written off in each aging category of outstanding receivables), and fill in the "Solution" worksheet. 2. You are required to prepare and submit a short report of your findings on Blackboard. The report should not be more than one page long and should be submitted under the name "AR_LastName_FirstName_StudentID.pdf'. The report should provide the following: a. A brief description of the task you were given, the data used and your method of analysis. b. A table presenting the analysis results in the following format*: * Please be sure to include a heading and explanation of the table you present. 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 uncollectible accounts on the balance sheet. 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. You requested that the firm's IT personnel provide you with historic information dating back five years (2013-2017). You are going to use that information to provide an informed data-based report backing your conclusions. Instructions to download the file: 1. Download the file "ReceivablesDataSimulation.xlsm" from Blackboard. 2. Open the file using Excel. You might need to "enable macros" on your program for the code to work. To generate the data, click on the "Generate Data" button. The file produces a simulated database (different data for each student). 3. Save the file under the name (any of these four Excel file extensions) "AR_LastName_FirstName_StudentID.xlsx/xlsm/xltx/xltm" - you will submit this file on Blackboard under this name, with all your data work, along with a short report. Explanations and Assumptions: Once the data are generated you will see six worksheets, each pertaining to accounts receivables outstanding at each year-end. The worksheets "Dec 31 2013", "Dec 31 2014", "Dec 31 2015", "Dec 31 2016", and "Dec 31 2017" contain the historical data, and the worksheet "Dec 31 2018" contains the outstanding receivables for which you need to estimate the allowance. The firm had a different number of customers each year as well as total credit sales. There are five columns in each worksheet: - Column A: the customer ID - Column B: the total sale on account and invoiced during the year - Column C: the account receivable balance outstanding at year-end - Column D: the date when the invoice was issued - Column E: the date when the invoice balance was paid off in the following year (this column is not in the "Dec 312018 " worksheet). c. 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?) d. 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). Assume that if the receivable was not paid over the course of the following year, the entire amount would be written off. In that case, the corresponding cell in column E remains blank. Assume four categories of aging receivables: (1) 0-30 days outstanding, (2) 31-60 days outstanding, (3) 61-90 days outstanding, and (4) above 90 days outstanding. The worksheet "Solution" contains a table which needs to be filled with the answers. The "Solution" sheet is the only sheet that will be graded - therefore, all answers should be placed on this sheet. Do not put anything on other worksheets. Excel files will be auto graded. For the system to work, you cannot change the names of the data sheets or any of the data columns (a-e in the files) - that includes adding sums, averages or anything else to these columns. You can add as many work sheets as you like to the file but do not change / add any data to columns A-E in the existing data sheets. Requirements: 1. Submit Excel file "AR_LastName_FirstName_StudentID" to Blackboard - the "Solution" worksheet must have: a. For each year, calculate the percentage of account receivables written off out of total credit sales. b. For each year, calculate the percentage of account receivables written off in each category of outstanding receivable age (think about which dates are relevant when assigning the categories of aging receivables). c. Collect the data for years 2013-2017 (total outstanding receivables, percentage of receivables written off relative to credit sales, and percentage of receivables written off in each aging category of outstanding receivables), and fill in the "Solution" worksheet. 2. You are required to prepare and submit a short report of your findings on Blackboard. The report should not be more than one page long and should be submitted under the name "AR_LastName_FirstName_StudentID.pdf'. The report should provide the following: a. A brief description of the task you were given, the data used and your method of analysis. b. A table presenting the analysis results in the following format*: * Please be sure to include a heading and explanation of the table you present