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