Question
ABC Company Purveyors of Consumer Stuff is analyzing its credit sales and accounts receivable. The posted Excel file (excel file information below) contains sales data
ABC Company Purveyors of Consumer Stuff is analyzing its credit sales and accounts receivable. The posted Excel file (excel file information below) contains sales data for the companys credit customers as of the report date specified on the schedule (the database). A review of the companys credit sales policy specifies the following possible categories for its outstanding receivables: If A/R outstanding 30 days or less it should be classified as Current If A/R outstanding at least 31 days but less than or less than 61 days it should be classified as Watch List If A/R outstanding at least 61 days but less than or less than 91 days it should be classified as classified as Credit Manager Alerted If A/R outstanding at least 91 days but less than or less than 121 days it should be classified as Credit Dept. Supervisor Alerted If A/R outstanding at least 121 days but less than or less than 181 days it classified as Collection Agency Deferral If A/R outstanding at least 181 days or more, it should be classified as Write Off
PART 1:
Using the posted Excel file, complete the following analysis of accounts receivable by adding and using a worksheet named Acct Receiv Analysis (hint copy the original database to this sheet). Required: 1. Format the schedule to look professional (e.g., use of bolding, shading, text alignments, etc. where appropriate). 2. Format the Transaction Date field (i.e., column in the database) to have dates presented in the XX/XX/XX format (i.e., the date January 1, 2020 would be 01/01/20). 3. Assume the date of the report is August 20, 2020. 4. Add an appropriately labeled column that calculates the days each account has been outstanding (based on the report date). 5. Add a data table (off to side on right side of spreadsheet) that incorporates the account classifications identified above. 6. Add, and appropriately label, a second column in the spreadsheet schedule that applies the calculated days outstanding against the data table created above and returns the appropriate account classification as of the date of the report.
PART 2
Create a NEW worksheet in same Excel file named Acct Receiv Analysis II and complete the following analysis (hint copy the original database to this sheet). Required: 1. Copy over your analysis from the prior worksheet. 2. Add 15 days to the date of the report (note this essentially provides a snapshot of what the report would look like if no activity occurred over following 15 days). 3. Assuming no new sales are made and no collections on accounts have been received, how many accounts would still be considered Current as at this new report date?
Posted Excel File Information:
ABC Company - Purveyors of Consumer Stuff | ||||
Master Credit Summary Report | ||||
Report Date: | ||||
Customer Name | Transaction | |||
First | Last | Customer # | Date | Amount |
Alice | Jones | A1200 | Wednesday, July 15, 2020 | 2,400.00 |
Frank | Footer | Z1425 | Wednesday, July 1, 2020 | 1,650.00 |
Larry | Fine | X1105 | Thursday, February 6, 2020 | 980.00 |
Moe | Howard | A1450 | Saturday, April 25, 2020 | 3,200.00 |
Peggy | Bundy | X1850 | Tuesday, July 21, 2020 | 1,550.00 |
Robert | Smith | Z2285 | Saturday, February 22, 2020 | 1,125.00 |
Marsha | Brady | A5400 | Sunday, August 2, 2020 | 2,240.00 |
Arnold | Ziffel | C1000 | Thursday, March 5, 2020 | 1,140.00 |
Thurston | Howell III | X2400 | Sunday, June 21, 2020 | 1,400.00 |
Theodore | Cleaver | A3300 | Monday, April 20, 2020 | 2,800.00 |
Wilma | Flintstone | D5600 | Thursday, April 30, 2020 | 1,375.00 |
Doby | Gillis | A2600 | Tuesday, August 4, 2020 | 2,050.00 |
Joe | Friday | C1475 | Sunday, May 31, 2020 | 1,175.00 |
Gomer | Pyle | X1200 | Friday, February 21, 2020 | 750.00 |
Barney | Miller | Z1000 | Wednesday, August 19, 2020 | 2,600.00 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started