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. Decision Making in Accounting Assignment Part B - EXCEL SUPER RETAIL GROUP Read all of the information below to assist you with the completion of the assessment item for Part B - Excel. Only after you have read the information below should you move to the sheet entitled RATIOS. Use the RATIOS sheet to complete the assessment item for Part B - Excel. Background 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 this Excel file. Please use only the financial information as provided 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 this Excel file 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 provided in this file (RATIOS sheet), 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. 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. A word count indicator can be seen to the right of each evaluation section. Designated Cells These cells show the category, you do not need to do anything in these cells. LIQUIDITY RATIOS INSERT NAME OF RATIO HERE Use these cells to type in the name of the ratio you are calculating. Only the name is required, nothing else. CALCULATE RATIO HERE Use these cells to perform your ratio calculation using an Excel formula. WRITE EVALUATION HERE FORMULA WILL SHOW HERE These cells will show the formula used to calculate your ratio, you do not need to do anything in these cells. Use these cells to write your evaluation for the specified category, you must make reference to the ratios you have calculated for that category. Additional Information Further information on the Excel Part of this assessment item, such as support provided and common questions, can be found in the Decision Making in Accounting Assignment document on Blackboard (under Assessment - Decision Making in Accounting Assignment). A Discussion Board is also available on Blackboard (under Discussion Board - Assignment Excel Part) which you can post questions and responses to. Saving Remember to save your work regularly and back up to a secondary source, such as a USB, external drive or cloud drive. Acknowledgement In moving to the RATIOS sheet you acknowledge that you have read and understand the instructions provided above. Use the RATIOS sheet to complete the assessment item. 123 456 SUPER RETAIL GROUP Excel Worksheet - Ratios Selected Comparative Financial Information from the Financial Statements for the year ended 26 JUNE 2021 Selected Statement of Financial Position Information 2021 2020 8 ($'m) ($'m) 9 Cash and Cash Equivalents 242.3 285.1 10 Trade Receivables 38.4 26.3 11 Inventories 696.4 502.4 12 Total Current Assets 980.7 813.8 13 Total Assets 2,972.6 2,775.1 14 Total Current Liabilities 923.8 750.8 15 Total Liabilities 1,746.1 1,783.8 16 Retained Earnings 468.2 285.7 17 Shareholders Equity 740.7 698.1 18 19 Selected Statement of Financial Performance Information 2021 21 ($'m) 22 Revenue from Sale of Goods 3,453.1 23 Income Tax Expense 126.6 26 28 2222222 24 Interest Expense (Finance Costs) 41.0 25 Cost of Sales 1,797.2 Gross Profit 1,655.9 27 Operating Expenses 3,025.9 Profit After Tax 301.0 29 30 Selected Other Financial Information (including Cash Flow Information) 2021 32 ($'m) 2021 34 32 33 34 Net Cash Provided by Operating Activities Dividends paid 35 Weighted Average Number of Ordinary Shares 37 Market Price per share ($) as at 25 June 2021 ($'m) 600.0 118.5 222.8 12.95 38 39 40 IDENTIFY, CALCULATE AND EVALUATE YOUR RATIOS IN THE APPROPRIATE CATEGORY BELOW: 41 42 43 INSERT NAME OF RATIO HERE 44 INSERT NAME OF RATIO HERE 44 45 LIQUIDITY RATIOS CALCULATE RATIO HERE CALCULATE RATIO HERE FORMULA WILL SHOW HERE FORMULA WILL SHOW HERE 46 47 EVALUATE THE LIQUIDITY OF SUPER RETAIL GROUP USING THE RATIOS YOU CALCULATED ABOVE WRITE EVALUATION HERE (MAX 150 WORDS) 48 49 50 51 52 INSERT NAME OF RATIO HERE 53 INSERT NAME OF RATIO HERE 54 55 SOLVENCY RATIOS CALCULATE RATIO HERE CALCULATE RATIO HERE FORMULA WILL SHOW HERE FORMULA WILL SHOW HERE EVALUATE THE SOLVENCY OF SUPER RETAIL GROUP USING THE RATIOS YOU CALCULATED ABOVE WRITE EVALUATION HERE (MAX 150 WORDS) WORD COUNT 6 55 596 57 58 59 EVALUATE THE SOLVENCY OF SUPER RETAIL GROUP USING THE RATIOS YOU CALCULATED ABOVE WRITE EVALUATION HERE (MAX 150 WORDS) 60 61 INSERT NAME OF RATIO HERE 62 INSERT NAME OF RATIO HERE 63 64 65 66 67 68 PROFITABILITY RATIOS CALCULATE RATIO HERE CALCULATE RATIO HERE FORMULA WILL SHOW HERE FORMULA WILL SHOW HERE EVALUATE THE PROFITABILITY OF SUPER RETAIL GROUP USING THE RATIOS YOU CALCULATED ABOVE WRITE EVALUATION HERE (MAX 150 WORDS) WORD COUNT 6 WORD COUNT 6