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