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 blank in the “Dec 31 2018” worksheet).
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. PLEASE NOTE THAT THIS IS THE ONLY SHEET THAT WILL BE GRADED – THEREFORE ALL ANSWERS SHOULD BE PLACED ON THIS SHEET!!
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.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
