Part A - RECKON: The following Reports are to be produced using Reckon Accounting Software and you must upload the two Reports in PDF format to Turnitin: 1. Adjusted Trial Balance: (Reports Accountant) The date range must be from 30/11/2021 to 30/11/2021. Report to be a maximum of 1 page wide and long. 2. Audit Trail Report: (Reports Accountant Audit Trail) . The date range must be from 7/12/2021 to the date you print the Audit Trail Report. Where the Audit Trail Report is not submitted in the appropriate format (including your User Name printed on the report) investigations into plagiarism may be commenced. o If an Audit Trail Report is not submitted, your assignment will not be marked. Make sure that your Reckon User Name (student number and surname/family name e.g. 87654321Jones) are included in the Audit Trail Report generated by Reckon (they may not be handwritten on afterwards). Report to be a maximum of 1 page wide and 2 pages long. Work in excess of two pages may not be marked. PART A - RECKON Background Information Nancy and Will, two former QUT students, recently started Hawkins Advertising Pty Ltd. The company specialises in high quality advertising services to a wide range of international clients. Their services include print, digital and social media advertising in multiple languages. The unadjusted trial balance for Hawkins Advertising Pty Ltd as at 30 November 2021 is below. Unadjusted Trial Balance as at 30 November Cash at Bank Accounts Receivable Debit Credit 104,687 11,605 Office Supplies Prepaid Insurance Office Equipment - Original Cost 1,380 2,088 45,012 Office Equipment - Accumulated Depreciation 19,490 Accounts Payable 4,705 Loan Payable Current 4,915 PAYG Withholding * 2,470 Revenue Received in Advance 10,000 Tax Payable** 5,161 Loan Payable - Non Current Retained Earnings Share Capital 27,255 15,881 50,000 Fees Revenue 48,650 Catering Expense Interest Expense Rent Expense Security Expense Staff Training Expense Storage Expense 3.505 120 2,940 1,200 850 370 Wages Expense 14,770 188,527 188,527 PAYG withholding is tax withheld from staff wages and is owed to the government. **Tax Payable is the GST Clearing account set up for the GST owing. This is a Reckon account and GST amounts will be taken to Tax Payable as you process transactions with GST. You do not need to create any other Tax or GST accounts. Accounts Receivable Details as at 30 November Accounts Payable Details as at 30 November $ $ Byers Lighting 8,675 Hopper Security 1,200 Eleven Moving Services 2,930 Dustin's Discount Catering 3,505 Total 11,605 Total 4,705 Nancy and Will have provided the following transactions to you, the Graduate Accountant at Hawkins Advertising Ltd Pty. For each transaction you need to determine the accounting implications for the month of November 2021. Hawkins Advertising uses accrual accounting and prepares adjusting entries and financial statements on a monthly basis. You need to decide for each transaction below whether it involves: a normal Reckon transaction, an adjusting entry, or does not require any transaction to be recorded for the month of November 2021. List of Transactions 1. 30 November: Received an invoice from Dustin's Discount Catering for $4,785 (including GST $435) in relation to catering expenses incurred during November. 2. 30 November: The manager of Mews Pet Supplies advised by email that they wish to proceed with the quote provided by Hawkins Advertising. This is expected to generate $9,350 (including GST $850) in revenue. The services will be provided in December 2021. 3. 30 November: Agreed to rent a new printer from 1 January 2022 with an annual rental payment of $5,280 (including GST $480). 4. 30 November: Paid the monthly internet expense with cheque number 168 for $1,320 (including GST $120). 5. 30 November: Prepared a quote for a new customer for advertising services; Quote number Q754 for $4,180 (including GST $380). 6. 30 November: Invoiced Eleven Moving Services for advertising services provided; Invoice number R374 for $4,565 (including GST $415). 7. 30 November: Received cash of $7,000 as payment on account from Byers Lighting. 8. 30 November: Received cash of $3,245 (including GST $295) from a client for advertising services provided. (Ensure the 'Deposit To' shows the 'Cash at Bank' account!). 9. 30 November: Paid Hopper Security $1,200 with cheque number 169. 10. As at 30 November: $7,500 worth of services have been performed which relate to a client who paid $10,000 cash upfront in October, and the receipt of the cash was processed in Reckon at that time. (The GST has already been recorded and can be ignored) (refer Trial Balance). 11. 30 November: Placed an order for Office Supplies of $2,310 (including GST $210). The supplies and associated invoice do not arrive until 15 December 2021. 12. 30 November: Hired a new staff member with an annual salary is $57,500 and who will commence work on 20 December 2021. 13. Prepaid for 12 months of insurance on 1 November (refer Trial Balance). 14. Wages owing to staff as at 30 November are $7,335. 15. Depreciation on Office Equipment is calculated using the Straight Line Method over 7 years with $0 estimated residual value. Round the amount of depreciation to the nearest whole dollar. Note that the calculation is for one month only (i.e. November depreciation only). Reckon Transaction Summary Table Transaction Section in Reckon Entering invoices received from Accounts Payable (Suppliers) To pay existing suppliers (Accounts Payable) Entering invoices issued (Sales on Credit - Accounts Receivable) To enter money received from Accounts Receivable To enter cash receipts from a customer (NOT Accounts Receivable receipts) To enter cash payments (NOT Accounts Payable payments) To enter Adjusting Entries Suppliers section - Enter Bills Suppliers section - Pay Bills Customers section - Create Invoices Customers section - Receive payments Ensure the 'Deposit To' shows the 'Cash at Bank' account Customers section - Create Sales Receipts Ensure the 'Deposit To' shows the 'Cash at Bank' account Banking section - Write Cheques Click on Accountant in Menu bar, Choose Make General Journal entries To create reports Click on Reports in the Menu bar, Choose Accountant and the desired Report To view transactions Click on Reports in the Menu bar, Choose Accountant and Transaction Detail by Account OR Transaction List by Date Transactions can be viewed by using the 'Previous' button on transaction entry screens. Transactions can also be viewed from the Reports menu, place the cursor over the amount to be checked and double click. To correct errors Return to the transaction by using the 'Previous' button and adjust the entry as required. Reckon Tips GST codes: where payments have GST included, use the tax code NCG for expenses. Bank Accounts: check each receipt and payment is processed to the Cash at Bank account, not Undeposited Funds. Dates: ensure that dates are entered correctly for the month of November 2021. Names: are required if the transaction involves an Accounts Receivable or Accounts Payable but for other transactions such as Cash Sales and Cash Cheques, you can simply enter "Cash Sale" or "Cash" respectively. If a name is not given, do not create an Account Receivable or Account Payable. Some Basic Reminders When Using Reckon If you have problems you should refer back to the Reckon Practice Exercise and Instructions document on Blackboard to find the answer, as everything you need to know and be able to do in Reckon is covered in those instructions. Check all transaction dates carefully - both in this document and when entering transactions into Reckon (remember it will default to today's date so you will need to change it). Ensure that dates are entered correctly for the month of November 2021. Check all account names and account types carefully in Reckon. If the transaction involves Accounts Receivable or Accounts Payable the individual name of the customer or supplier must be used. For other transactions such as Cash Sales and Cash Cheques, you can simply enter "Cash Sale" or "Cash" respectively. Check the GST (tax) amount and totals very carefully (where applicable). Where payments have GST included, use the tax code NCG for expenses. Check each receipt and payment is processed to the Cash at Bank account, not Undeposited Funds. Double and triple check the entry made before you hit Save and Close in Reckon. Dates for Reports can be changed manually at the top (dates 'From' and 'To'). Your Audit Trail Report must show your username (e.g. 87654321Jones) in the column "Last modified by". If this column shows "Admin" you have not completed the assessment requirements correctly and this will negatively impact on the marks you receive for this part of the assignment. If your Adjusted Trial Balance shows accounts with balances of 0 in columns there is no need to do anything. Some accounts will show that you may not have used, you do not need to try to remove or adjust these in any way. Your Adjusted Trial Balance should not show accounts called "Undeposited Funds", "Uncategorised Expenses" or "Uncategorised Revenues". Part B - EXCEL: The Excel worksheet provided is to be completed with your ratio calculations and analysis. Do not copy and paste data out of the Excel file provided into another Excel document. You are to use only the Excel file provided to complete this assessment item. The Excel worksheet with your completed ratio calculations and analysis must be uploaded in Excel (.xlsx) format to Turnitin. Do not PDF your Excel worksheet or save as a different file type, it must be submitted in Excel (.xlsx) format only. PDF worksheets or other files types will not be marked. CoEmployees Customers Suppliers Home Sales Orders Enter Bills Estimates Invoices Pay Bills Company Type a help question Chart of Accounts +TAX Manage Tax Items & Services Create Sales Receipts Banking Receive Payments EB Audit Company Record Deposits Reconcile Statement Charges Statements Refunds & Adjustments Write Cheques Cheque Register Payroll Centre Pay Employees Pay Liabilities Print Cheques PART B - EXCEL Background Information For the Excel Part of the assignment the data source is the Annual Report (including the Audited Financial Statements) of Australian publicly listed company Super Retail Group. Super Retail Group owns four brands: Supercheap Auto, rebel, BCF and Macpac, and is one of Australia and New Zealand's largest retailers. Each of their brands have established market leading positions in growing high involvement lifestyle categories of auto, sports and outdoor leisure. Super Retail Group is headquartered in Brisbane with a network of over 697 retail stores and more than 13,000 team members across Australia, New Zealand and China. You have been provided with select extracted Financial Information for Super Retail Group in an Excel file. Please use only the financial information provided to you in this file. There is no need to find or refer to any other information in the Financial Statements, Annual Report or online. Required: Using the designated cells in the Excel file provided, you must clearly identify six ratios (by name) for three categories. For each ratio identified you are to use an Excel formula to calculate that ratio. All ratio formulas used must be from the current set textbook for the subject. No other formulas will be accepted. Marks are allocated for the correct use of formulas in Excel. Not complying with this requirement will have an impact on the marks you receive for this part of the assignment. You will not have successfully completed the assignment requirements if you simply type in or hard code your ratio answers, they must all be calculated using Excel formulas. 1. Using the select extracted financial information of Super Retail Group provided in the Excel file, use appropriate Excel formulas to calculate ratios that provide information about the following: a. Two (2) ratios in relation to the Liquidity of the company for the 2021 year. b. Two (2) ratios in relation to the Solvency of the company for the 2021 year (note: Free Cash Flow is not an accepted ratio calculation for this assessment piece). c. Two (2) ratios in relation to the profitability of the company for the 2021 year. You must clearly identify the ratios selected (i.e. clearly name the ratio that has been calculated) for each of the above categories, in addition to performing the calculation in the area provided in the Excel file. All ratios must be displayed to two (2) decimal places only. 2. Using the ratios you have calculated for each of the three categories, evaluate the liquidity, solvency and profitability of Super Retail Group. Use only the area provided in the Excel worksheet for your analysis. The word limit is 150 words per category. Support Provided Materials are available on Blackboard to assist with using Excel, including videos and demonstration exercises showing how to use and format an Excel worksheet, save an Excel file in the appropriate format etc. Using Excel formulas to calculate ratios was covered in the Topic 3 tutorial. You can access the recording on Blackboard. A Discussion Board is available on Blackboard where you can post questions. Make sure you check the Discussion Board before asking your question, as it may have already been answered. Answers to Common Questions Does it matter what Excel version I use? Yes! You need to have a version of Excel from 2013 or later for the functionality in the file to work properly. There is no difference in using Excel on Windows or Mac, as long as the version is compatible. Office 365 Excel is compatible. If a warning comes up when you open the Excel file, click on Enable Content to allow full functionality of the file. How strict is the word count for the ratio evaluation? Like any other piece of assessment the 150 word limit per category for your evaluation needs to be closely adhered to. This should be ample words to allow you to perform a meaningful evaluation. Grading will be based upon the text that is within the word limit, text outside the word limit will not be marked. Should I check my ratio calculations? It is recommended that you double check the calculation of ratios that you have produced in Excel, particularly if you are not proficient at using Excel. You can calculate the ratios manually using a calculator and use this to double check the answers that you have calculated in Excel. Be particularly careful and vigilant with the use of brackets in Excel. Is the select extracted Financial Information the only information I am supposed to use to calculate the ratios? Do I need to get more information from the Financial Statements in the Annual Report? You are to use only the financial information provided in the Excel spreadsheet. There is no need to find or refer to any other information from the Financial Statements, Annual Report or online. Does it matter which ratios I choose? No, you can choose any two ratios under each category, provided that the ratios chosen and their formulas used are from the current set textbook for the subject. No other formulas for ratio calculations will be accepted. Note that Free Cash Flow is not an accepted ratio calculation for this assessment piece. Do I need to explain why I chose the ratios that I calculated? No, you do not need to explain why you chose the ratios that you calculated. Are certain ratios worth more marks than others? No, all ratios are worth the same amount of marks, provided that the ratios chosen and their formulas used are from the current set textbook for the subject. Note that Free Cash Flow is not an accepted ratio calculation for this assessment piece and should not be used. Is revenue the same as net sales? Revenue and Sales are sometimes used interchangeably. Net Sales is Sales less Sales Returns and Allowances, if Sales Returns and Allowances is not listed in the information provided, you can take Sales/Revenue as being the same as Net Sales/Net Revenue. What if I cannot find the information I need to calculate the ratio I have chosen? The selected financial information provided in the Excel file allows for a wide range of ratios to be calculated. There is a very small number of ratios that cannot be calculated due the required information not being available, in which case you should choose a different ratio to calculate. Note that Free Cash Flow is not an accepted ratio calculation for this assessment piece and should not be used. Where can I find Profit Available to Ordinary Shareholders? Profit available to ordinary shareholders is the same as Profit after Tax and Net Profit. How many decimal places do the ratios need to be calculated to? All ratios must be displayed to two (2) decimal places only. How can I reduce the number of decimal places in Excel? You can reduce the number of decimal places in Excel quite easily. In the cell, right click on the mouse, select Format Cells and under Number you can select the number of decimal places you want to display to. How can I express the ratios in the correct format? Ratios should be expressed in the correct format where possible, such as %. To do this, in the cell, right click on the mouse, select Format Cells and select the appropriate category (i.e. Percentage) and select the number of decimal places you want to display to. Expressions of ratios as 'times' or ':1' do not require the additional wording to be added (i.e. you do not need to put in 'times' or ':1' after the figure) and the ratio should be expressed simply as a number to two (2) decimal places. Do I need to write out the ratio formula? No, you only need to put in the name of the ratio (e.g. Current Ratio) and perform the calculation using Excel formula. As part of your evaluation you may wish to provide further information about the ratios including explaining the financial statement components used and how the ratio is calculated. This explanation should be relevant to your evaluation. Marks are not awarded for simply providing the ratio formula in your evaluation without any meaningful analysis which is relevant to your work. Do I need to create a new Excel file? No, only the Excel worksheet provided is to be completed with your ratio calculations and analysis. Do not copy and paste data out of the Excel file provided into another Excel document. You are to use only the Excel file provided to complete this assessment item