Excel Exercise 2 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 Customer name Balance of Accounts December sales December Receivable at 30 November collections FO1-235-00 Shel $20,469 $22,500 $24,500 F07-988-45 ADI Ltd $18,752 $11,592 $12,980 W40-860-91 Sky Link $12,287 $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 daysInstruction - Excel Exercise 2 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.Suggested Format Excel Exercise 2 Excel Exercise 2 Qantas Accounts Receivable DIOS/2019 Customer ID) + Customer Name + Collection Ending Balance FO1-235 00 shall 20,469 32.500 24,500 FOT-908 45 ADi Lid 14,752 11.592 12,930 W40 850 41 Sky Link 12,287 9 14.765 9 14,000 Totals 51.508 43.457 51,430 Note: Credit terms are net 30 Le., customers are required to pay the full amount within 10 days. The only customer who did not pay the full amount within 10 days is API Ud. This is because the Beginning Balance is $14,753, and December Sales Is $11,592, However the amount collected Is only $12,981, Comidering the credit berme of net 30, we can calculate the allowance for doubtful debt. Calculation of Allowance for Doubtful Debts Alowance for Doubtful Debts Average Collection Period Average Net Accounts Receivable Total Net Sales Avoyage Collection Period 1daysExcel Exercise 3 Goal: Create a spreadsheet to calculate Woolworths' gross profit ratio, inventory turnover, days in inventory ratio, and current ratio for the past three years i.e., 2018, 2017, and 2016. Scenario: Go to the website of Woolworths www.woolworthslimited.com.au . Their shareholder centre (Information for Investors) contains the data you need to complete this exercise. Hint: Easier way to find the financial information of Woolworths over the past three years is to refer to the La Trobe University Library and obtain the required information from the Morningstar DatAnalysis Premium. You can find this database here: https://datanalysis-morningstar-com- au.ez.library.latrobe.edu.au/af/dathome?xtm-licensee=datpremium 1. What has happened to Woolworths' gross profit ratio over the past three years? 2. What can you tell about Woolworths' inventory operations from its inventory ratio, and days in inventory ratio for the past three years? 3. What can you tell about Woolworths's current ratio for the past three years? 4. Draw graphs to visualise the ratios. 5. provide a brief explanation about the trends of ratios for the past three years. Hint: You can calculate the ratios by using the following formulas: Gross profit ratio = Gross profit Net sales revenue Inventory turnover = Cost of sales Average inventory Days in inventory = 365 days Inventory turnover ratio Current ratio Total current assets Total current liabilitiesInstruction - Excel Exercise 3 1. Open a new worksheet. 2. Create a bold-faced heading as follows: a. Excel Exercise 3 b. Evaluating Woolworths' ratios c. Today's date 3. Two rows down and two columns over, create bold and underlined headings for the most recent three years i.e., 2018, 2017, and 2016. 4. Locate the following information for Woolworths for 2018, 2017 and 2016 (found in the consolidated statement of profit or loss): a. Sales (in millions) b. Cost of sales c. Gross profit 5. Locate the following information for Woolworths for 2018, 2017, 2016 (found in the consolidated statement of financial position): a. Current assets b. Current liabilities c. Inventory Note: You are required to obtain the 2015 inventory to be able to calculate the 2016 inventory turnover. 6. Calculate the ratios. 7. draw the graphs in a new worksheet.Suggested Format Excel Exercise 3 Excel Exercise 3 Evaluating Woolworths' ratios 04/09/2019 2015 2016 2017 2018 Sales Cost of Sales Gross Profit Current assets Current liabilities Inventory Calculation of ratios 2016 2017 2018 Inventory turnover ratio #DIV/O! #DIV/O! #DIV/O! Days in inventory #DIV/O! #DIV/O! #DIV/O! 2016 2017 2018 Gross profit ratio #DIV/O! #DIV/O! #DIV/O! Current ratio #DIV/O! #DIV/O! #DIV/O