Question

I need someone to do this for me please.

PC/SPREADSHEET PROBLEM #1 --AGING OF RECEIVABLES - 25 POINTS DUE-Monday Tuesday, NOVEMBER 4/5, 2019 American Box Company is negotiating a working capital line of credit with its finance company/bank. The finance company/bank told American Box's CFO (Chief Financial Ollicer) that the line of credit request would require an aging of receivables schedule. Because you will soon become a freshly-minted Accounting Principles I graduate, the CFO has delegated the assignment to you. (And because you secretly desire the CFO's job and perks, you eagerly accept.) The AR data is on the next page. 3 REQUIREMENTS: Prepare an aging of receivables schedule (similar to, but NOT EXACT, the example on page 357. And compute American Box's net realizable value (gross receivables less allowance for doubtful accounts). And determine the maximum amount the finance company/bank will lend to American Box on a revolving line of credit (secured by Accounts Receivable), based on current AR 1.5% American Box estimates bad debt expenses as follows: 1-30 days 31-60 days 3.0% 61-90 days 6.0% 91 days + The finance company/bank will lend American Box the following %'s on its TOTAL 15% AR: 1-30 days 31-60 days 61-90 days 91 days + 90% 70% 25% nothing You will turn in two pages: the spreadsheet with the answer and then with the formulas. No more than two pages and no less than two pages. To print to one page, go to file, print, settings (at the bottom find no scaling, using the drop down menu, choose fit sheet to one page. To show formulas in your cells instead of S and W, go to file, options, advanced, display options for this worksheet (click on the 2 box)THERE ARE AT LEAST 6 THINGS THAT IF YOU DO OR FAIL TO DO WILL RESULT IN LOSING 5 POINTS FOR THIS ASSIGNMENT - SO FOLLOW DIRECTIONS. 1. You MUST list each individual invoice number and dollar amount. 2. You MUST use formulas. 3. You MUST print formulas. 4. You MUST calculate financing level correctly. 5. You MUST turn in EXACTLY 2 pages, no more and no less. 6. DO NOT list the actual number of days outstanding for any invoice.

Answer 1

Please give positive ratings so I can keep answering. Thanks!

American box
Calculation of allowance for uncollectible balance
			A		B	C=A*B	D=A-C
Days Past due	Customer	Invoice No.	Amount $	Exact days due	% uncollectible	Allowance $	Net Receivable
1-30 days
	Airflow Inc.	6300	2,700.00	22.00	1.50%	40.50	2,659.50
	Airflow Inc.	6301	18,000.00	17.00	1.50%	270.00	17,730.00
	Airflow Inc.	6302	9,100.00	16.00	1.50%	136.50	8,963.50
	Airflow Inc.	6303	2,600.00	8.00	1.50%	39.00	2,561.00
	Baxter & Sons	40162	22,000.00	21.00	1.50%	330.00	21,670.00
	Baxter & Sons	40163	24,200.00	15.00	1.50%	363.00	23,837.00
	Baxter & Sons	40164	21,000.00	13.00	1.50%	315.00	20,685.00
	Findley and Associates	56029	33,000.00	26.00	1.50%	495.00	32,505.00
	Findley and Associates	56030	94,000.00	12.00	1.50%	1,410.00	92,590.00
	Irish Potato Company	81010	55,000.00	23.00	1.50%	825.00	54,175.00
	Irish Potato Company	81075	64,000.00	10.00	1.50%	960.00	63,040.00
	Lumber Depot	11514	13,465.00	23.00	1.50%	201.98	13,263.03
Total 1-30 days			359,065.00			5,385.98	353,679.03
31-60 days
	Baxter & Sons	40160	14,000.00	35.00	3.00%	420.00	13,580.00
	Findley and Associates	56027	11,000.00	55.00	3.00%	330.00	10,670.00
	Findley and Associates	56028	31,000.00	41.00	3.00%	930.00	30,070.00
	Lumber Depot	11512	29,000.00	34.00	3.00%	870.00	28,130.00
Total 31-60 days			85,000.00			2,550.00	82,450.00
61-90 days
	Airflow Inc.	6299	4,125.00	86.00	6.00%	247.50	3,877.50
	Kentucky Staples Company	10915	4,400.00	70.00	6.00%	264.00	4,136.00
	Lumber Depot	11509	8,500.00	88.00	6.00%	510.00	7,990.00
Total 61-90 days			17,025.00			1,021.50	16,003.50
91 days+
	Baxter & Sons	40082	1,600.00	139.00	15.00%	240.00	1,360.00
	Kentucky Staples Company	10912	21,000.00	110.00	15.00%	3,150.00	17,850.00
	Kentucky Staples Company	10914	8,000.00	98.00	15.00%	1,200.00	6,800.00
Total 91 days+			30,600.00			4,590.00	26,010.00
Total			491,690.00			13,547.48	478,142.53
So total allowance for doubtful debt is $ 13,547.48
And total net accounts receivable is $ 478,142.53
Calculation of maximum amount of finance:
	E	F	G=E*F
Days Past due	Amount $	% given	Maximum amount $
1-30 days	359,065.00	90%	323,158.50
31-60 days	85,000.00	70%	59,500.00
61-90 days	17,025.00	25%	4,256.25
91 days+	30,600.00	0%	-
Total	491,690.00		386,914.75
So maximum amount of finance/ loan from bank is $ 386,914.85.