PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 X (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e4b7890371975ead3bd x + X C dbb715huzqn8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex.. G This PC c80cad62e467890371975ead3bd8a00f8a1db09a 1 / 8 C: Excel Exercise 1 Network Goal: Create an Excel worksheet to prepare an income statement, a balance sheet and a statement of changes in equity for a start-up business. Scenario: You have started a valet parking business called First Class Valet. You have tracked your cash, supplies, revenues, expenses, and other items in a spreadsheet. The totals for each account at the end of your first month are as follows (ignore GST): Recycle Bin 1. Sales Revenues, $400 2. Supplies on hand (asset account), $175 3. Cash, $300 4. Supplies expense, $225 5. Capital, 1 July 2019: $250 Control Pane 6. Salary payable, & Salary expense, $50. 7. Drawings: $20 8. Unearned revenue: $75 9. Accounts receivable: $5 Adobe Required Reader XI a) In a new worksheet, prepare an income statement, a balance sheet, and a statement of changes in equity for 31 July 2019. Your statements should show the result of the following: 1. How well did your business do in its first month of operations? 2. How did this affect your capital account? Google b) In a different worksheet draw a graph to visualise the financial performance of your company in an Chrome Excel spreadsheet. + Hint: Your graphs could visualise the following ratios: 1. Expense to sales ratio 2. profit margin ratio Zoom c) provide a brief explanation about the financial performance of your company. Exercises -.. vald gum... dald Gerider Type here to search O ^ 6 ()) ENG 04:06 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 X (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e4b7890371975ead3bd x + X C dbb715huzqn8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex. G This PC c80cad62e467890371975ead3bd8a00f8a1db09a 2 / 8 C: Suggested Format Excel Exercise 1 Week 7 Excel Application Exercises Network First Class Valet 04/09/2019 First Class Valet First Class Valet Income Statement Statement of Changes in Equity for the month ended 31 July 2019 for the month ended 31 July 2019 Revenue Capital, 1 July 2019 Recycle Bin Sales Revenue Add: Net profit Expenses Less: Drawings Salary Expense Capital, 31 July 2019 Supplies Expense Total Expenses Net profit Control Pane First Class Valet Balance Sheet as at 31 July 2019 Adobe Assets Liabilities Reader XI Cash at Bank Accounts Payable Accounts Receivable Unearned Revenue Supplies on Hand Total liabilities Owners' Equity Capital Total Assets Total liabilities & Owners' Equity Google + Chrome Zoom Exercises -.. wald gum... uala Gender Type here to search O X ^ 6 () ENG 04:06 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 x (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e467890371975ead3bd x + O X C A dbb715huzan8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex. G This PC c80cad62e467890371975ead3bd8a00f8a1db09a 3 / 8 C: Goal: Create a worksheet that shows accounts receivable activity for a company. Network 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 Recycle Bin Receivable at 30 November collections FO1-235-00 Shell $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 Control Pane 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? Adobe 2. How much do these three customers still owe Qantas at the end of December? (Hint: you are Reader XI 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 Google takes for Qantas to collect payment from customers after a sale is made. Chrome Hint: To calculate this you are required to use the following formula: Average collection period = Average net accounts receivable Net sales - x 30 days Zoom Exercises -.. vald gum... aald Gender Type here to search O X ^ 6 ()) ENG 04:06 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 x (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e4b7890371975ead3bd x + X C A dbb715huzqn8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex.. G c80cad62e467890371975ead3bd8a00f8a1db09a 4 / 8 C This PC Instruction - Excel Exercise 2 Network 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 Recycle Bin 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 Control Pane c. Beginning balance d. Sales e. Collections f. Ending balance Adobe Reader XI 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. Google Chrome Zoom Exercises -.. bald gum... adid Gender Type here to search O 9 X ^ 6 ()) ENG 04:06 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 x (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e467890371975ead3bd x + X C dbb715huzqn8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex... G This PC c80cad62e467890371975ead3bd8a00f8a1db09a 5 / 8 Suggested Format Excel Exercise 2 Network Excel Exercise 2 Qantas Accounts Receivable 04/09/2019 Customer ID Customer Name |Beginning Balance Sales Collection |Ending Balance F01-235-00 Shell 20,469 22,500 $ 24,500 Recycle Bin F07-988-45 ADI Ltd 18,752 11,592 $ 12,980 W40-860-91 Sky Link 2,287 14,765 $ 14,000 Totals 51,508 $ 18,857 $ 51,480 Note: Credit terms are net 30 i.e., customers are required to pay the full amount within 30 days. The only customer who did not pay the full amount within 30 days is ADI Ltd. This is because the Beginning Balance is Control Pane $18,752, and December Sales is $11,592. However the amount collected is only $12,980. Considering the credit terms of net 30, we can calculate the allowance for doubtful debt. Calculation of Allowance for Doubtful Debts Allowance for Doubtful Debts Adobe Reader XI Average Collection Period Average Net Accounts Receivable Total Net Sales Google Average Collection Period Idays Chrome Zoom Exercises -. wald yum. uala Gender Type here to search O 9 X ^ 6 ()) ENG 04:07 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 x (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | P X c80cad62e467890371975ead3bd x + X C dbb715huzqn8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex. G This PC c80cad62e467890371975ead3bd8a00f8a1db09a 6 / 8 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. Network 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 Morningsta DatAnalysis Premium. You can find this database here: https://datanalysis-morningstar-com- au.ez.library.latrobe.edu.au/af/dathome?xtm-licensee=datpremium Recycle Bin 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. Control Pane 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 Adobe Reader XI Inventory turnover =- Cost of sales Average inventory Days in inventory = 365 days Google Inventory turnover ratio Chrome Current ratio = Total current assets Total current liabilities Zoom Exercises -.. vald gum... uala Gender Type here to search O X ^ 6 ()) ENG 04:07 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 x (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e4b7890371975ead3bd x O X + C A dbb715huzan8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex. G 7 1 8 C: This PC c80cad62e467890371975ead3bd8a00f8a1db09a Instruction - Excel Exercise 3 Network 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 Recycle Bin 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) Control Pane 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 Adobe Reader XI b. Current liabilities c. Inventory Note: You are required to obtain the 2015 inventory to be able to calculate the 2016 inventory turnover. Google 6. Calculate the ratios. Chrome 7. draw the graphs in a new worksheet. Zoom Exercises -.. vald gum... uala Gender Type here to search O 9 X ^ 6 ()) ENG 04:07 PM 24-09-2020PDF PDF PDF WE PDF WE DELL (369) Samvidhaan - Episode 2/10 x (369) pradhanmantri series - You x (370) Nai Jaana | Neha Bhasin | PI X c80cad62e467890371975ead3bd x X C dbb715huzan8k.cloudfront.net/c8/0c/c80cad62e467890371975ead3bd8a00f8a1db09a?response-content-disposition=inline%3Bfilename%3D"ACC1AIS%20Homework%20Ex.. G c80cad62e467890371975ead3bd8a00f8a1db09a 8 / 8 C: This PC Excel Exercise 3 Evaluating Woolworths' ratios Network 04/09/2019 2015 2016 2017 2018 Sales Recycle Bin Cost of Sales Gross Profit Current assets Current liabilities Inventory Control Pane Adobe Calculation of ratios Reader XI 2016 2017 2018 Inventory turnover ratio #DIV/O! #DIV/O! #DIV/O! Days in inventory #DIV/O! #DIV/O! #DIV/O! 2016 2017 2018 Google Gross profit ratio Chrome #DIV/O! #DIV/0! #DIV/O! Current ratio #DIV/O! #DIV/O! #DIV/O! Zoom Exercises Data gum uald Gender Type here to search O 9 X A 'S() ENG 04:07 PM 24-09-2020
