Question

Goal: Create a worksheet that shows accounts receivable activity for a company. Scenario: You have a summer holiday job with Qantas headquarters in Sydney. Assume that shortly into your employment your supervisor asks you to prepare an Excel spreadsheet that breaks down the month's accounts receivable by customer. She gives you the partial data: Customer ID F01-235-00 F07-988-45 W40-860-91 Customer name Balance of Accounts Receivable at 30 November Shell $20,469 ADI Ltd $18,752 Sky Link $12,287 December sales December collections $22,500 $24,500 $11,592 $12,980 $14,765 $14,000 Assume Qantas's credit terms are net 30 i.e., all customers are required to pay the full amount of accounts receivable within 30 days. Required When you have completed your worksheet, answer the following questions: 1. How much cash did Qantas collect from these three customers during December? 2. How much do these three customers still owe Qantas at the end of December? (Hint: you are required to calculate the closing balance of Accounts receivable). 3. Considering that Qantas's credit terms are net 30, calculate the total amount that should be allocated to allowance for doubtful debts. 4. Considering that Qantas's credit terms are net 30, calculate the average number of days that it takes for Qantas to collect payment from customers after a sale is made. Hint: To calculate this you are required to use the following formula: Average collection period = Average net accounts receivable Net sales x 30 days

Answer 1

Please find attached worksheet showing AR schedule "customer wise and totals" for the month of December:

Accounts Receivable Statement for the month of December Cumulative Balance (Debit plus Debit minus Credit) Debit Credit Description Shell Opening Balance as of December, 01 A Sales Made During December Collections made during December C 20,469.00 22,500.00 20,469.00 Dr 42,969.00 Dr 18,469.00 Dr 24,500.00 ADI Ltd Opening Balance as of December, 01 D Sales Made During December E Collections made during December F 18,752.00 11,592.00 18,752.00 Dr 30,344.00 Dr 17,364.00 Dr 12,980.00 Sky Link Opening Balance as of December, 01 G Sales Made During December Collections made during December 12,287.00 14,765.00 12,287.00 Dr 27,052.00 Dr 13,052.00 Dr 14,000.00 Total Accounts Receivable Opening Balance as of December, 01 A+D+G 51,508.00 Sales Made During December B+E+H 48,857.00 Collections made during December C+F+I 51,508.00 Dr 1,00,365.00 Dr 48,885.00 Dr 51,480.00

1) Total Cash Collected from three customers during month of December = $ 51,480 (i.e.) C+F+I as per above table

2) AR as of December 31 = $ 48,885 (i.e.) Opening Balance Plus Total Sales Minus Total Collections = $ 51,508+$48,857-$ 51,480. In other words closing balance of all 3 customers (i.e.) $ 18,469+$ 17,364 + $ 13,052 = $ 48,885

3) Allowance for Doubtful debts = Opening balance in AR minus Collected during the month pertains to AR: Total Collections from customers Shell and Skylink are more than opening balance and thus no allowance required. But customer ADI Ltd has paid less (i.e.) Paid only $ 12,980 as against opening balance of $18,752. Thus allowance has to be accounted for $ 5,772 ($ 18,752 minus $ 12,980). If Allowance has to be assumed on TOTAL AR basis, and not customer-wise, then allowance for doubtful accounts would be $ 28 only (i.e.) Total opening balance in AR of $ 51,508 minus Total collected from all customers of $ 51,480.

4) Average collection period = (Average net accounts receivable / net sales)*30 days

Average net accounts receivable = (Opening AR Balance plus Closing AR balance) / 2 = ($ 51,508 + 48,885) / 2 = $ 50,196.50

Net sales = $ 48,857

Average collection period = ($ 50,196.50 / $48,857)* 30 days = 31 days.