Question

1. Open a new Excel Spreadsheet. 2. Create a bold-faced heading for your spreadsheet that contains the following: a. Excel Exercise 2 b. Qantas Accounts receivable c. Today's date Hint: Todays date function in Excel is =TODAY() 3. Two rows down from your heading, create a row containing the following column headings: a. Customer ID b. Customer name c. Beginning balance d. Sales e. Collections f. Ending balance 4. Enter the data from the scenario into the spreadsheet. At the end of the entries, create a row for "Totals" and calculate totals for all financial columns. 5. Two rows beneath the scenario data, calculate the average collection period. 6. Include a row for net sales underneath this calculation.

Suggest form

Answer 1

Q1)  

Qantas Accounts receivable
06/01/2020
Customer ID	Customer Name	Beginning balance	Sales	Collection	Ending Balance
F01-235-00	Shell	$        20,469	$        22,500	$        24,500	$ 18,469
F07-988-45	ADI Ltd	$        18,752	$        11,592	$        12,980	$ 17,364
W40-860-91	Sky link	$        12,287	$        14,765	$        14,000	$ 13,052
Totals		$        51,508	$        48,857	$        51,480	$ 48,885

Ending Balance = Beginning balance + Sales - Collection

Allowance for doubtful debts = $ 17,364 i.e. ending balance of ADI Ltd

Average Net Accounts Receivable = (Opening Accounts Receivable + Closing Accounts Receivable) / 2

Average Collection Period = 365 / (Net Sales / Average Net Accounts Receivable)

Further calculations to be made as under:

Qantas Accounts receivable
06/01/2020
Customer ID	Customer Name	Beginning balance	Sales	Collection	Ending Balance
F01-235-00	Shell	$        20,469	$        22,500	$        24,500	$ 18,469
F07-988-45	ADI Ltd	$        18,752	$        11,592	$        12,980	$ 17,364
W40-860-91	Sky link	$        12,287	$        14,765	$        14,000	$ 13,052
Totals		$        51,508	$        48,857	$        51,480	$ 48,885
Allowance for doubtful debts	$        17,364
Average Net Accounts Receivable	$ 50,196.50
Total Net Sales	$        48,857
Average Collection Period	375.01	days

Formula pasted below:

B Qantas Accounts receivable 2 =TODAY() Ending Balance |=C5+D5-E5 Beginning balance 20469 Sales Collection 4 Customer ID Customer Name 22500 Shell 24500 5 F01-235-00 6 F07-988-45 =C6+D6-E6 18752 12980 ADI Ltd 11592 Sky link 7 W40-860-91 |=C7+D7-E7 12287 14765 14000 8 Totals =SUM(D5:D7) |=SUM(E5:E7) =C8+D8-E8 =SUM(C5:C7) 10 Allowance for doubtful debts =F6 11 12 Average Net Accounts Receivable =(C8+F8)/2 13 Total Net Sales =D8 =365/(B13/B12) 14 Average Collection Period days 15