Individual Data Analytics Project Estimating Allowance for Uncollectible 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 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 x1s " from Blackboard. 2. Open the file using Excel. The file is structured to produce a simulated database. To generate the data 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_StudentiD. xlsx/xlsm/xltx/xltm ". 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 are generated you will see six worksheets, each pertaining to accounts receivables outstanding at each year-end. The worksheets "Dec 312013 ", "Dec 312014 ", "Dec 312015 ", "Dec 312016, and "Dec 312017 " contain the historical data, and the worksheet "Dec 312018 " 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 N/A in the "Dec 312018 " worksheet). 1 Individual Data Analytics Project Estimating Allowance for Uncollectible 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 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 x1s " from Blackboard. 2. Open the file using Excel. The file is structured to produce a simulated database. To generate the data 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_StudentiD. xlsx/xlsm/xltx/xltm ". 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 are generated you will see six worksheets, each pertaining to accounts receivables outstanding at each year-end. The worksheets "Dec 312013 ", "Dec 312014 ", "Dec 312015 ", "Dec 312016, and "Dec 312017 " contain the historical data, and the worksheet "Dec 312018 " 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 N/A in the "Dec 312018 " worksheet). 1
