Learning Objectives: Class Project Financial Analysis and Data Analytics Assignment 1. Understand key financial ratios and calculations in analyzing a company's financial data. 2. Compare financial ratios across industries and calculate industry averages. 3. Compare one company's financial ratio to industry benchmark averages. Using Excel data analytics tools, create calculations, summarizations of data, and visualizations. Datasets and Deliverables: Your team must upload your files into the Submissions Folder in D2L. For the data analytics part, you are expected to use formulas and Excel functions to analyze the data, and guidance is provided on each part of the assignment. You must upload your Excel file so that I can check the formulas used in the analysis. You only need one team member to submit the files. If you do not respond promptly to your team member's communication, especially to start the project, you will be removed from your team and have to perform the project on an individual basis. If your team member does not respond within a timely manner, you can email your professor and you will be reassigned to another team to work in a productive team environment. Each Team must submit the following files to your submission folder: For Part One: 1. Summary Table (in Word format) For Part Two: 1. One-Page Summary of Findings (in Word format) 2. Supporting Excel File including separate worksheets (in Excel format) 1 Part One: Financial Ratio Summary (20% of your Project Grade) Review and think through each of the following twelve financial ratios (formulas provided in Appendix 1). Create a summary that provides the benefit of analyzing each ratio and whether a higher or lower ratio amount is better for a company's financial position. The first item is shown as an example. Complete the information for the other ratios. No. Ratio 1 Current Ratio 2 Debt to Equity Ratio Accounts Receivable 3 Turnover 4 Inventory Turnover 5 Asset Turnover Fixed Asset 6 Turnover Profit Margin on 7 Sales Gross Profit 8 Percentage 9 Return on Assets Basic Earnings per 10 Share 11 Diluted Earnings per Share Quality of earnings 12 Ratio Summary Table Explanation – the benefit for analyzing Provides information about a company's ability to pay short-term liabilities. 2 Which is better - a higher or lower ratio? Higher Part Two: Use the "ACC 306 Class Project Data” file to answer the following questions. (80% of your Project Grade) In the assignment, you will use the Excel file called “ACC 306 Class Project Data”. This Excel assignment will utilize your data analytic skills. Please focus your analysis on the most recent year. The Excel file consists of several worksheets: • • • Balance Sheet: 2023 and 2022 Balance Sheet Information for Clorso Income Statement: 2023 and 2022 Income Statement Information for Clorson Code: this file includes a group of companies along with their specific names, ticker symbols and industry classifcation. It serves as a reference for analyzing and understanding company data within a specific industry. Data: this dataset consists of a group of companies along with selected financial information. The first column contains the ticker symbols (TIC) representing each company. The following five columns provide the prior year data, specifically the figures ending on December 31, 2022. The remaining columns in the dataset contain the current year data, specifically the figures ending on December 31, 2023. Formula: this file includes financial ratio formula information. The objective of this project is to engage you in the analysis of a dataset in order to evaluate the financial performance of Clorsol, a manufacturing company. You will be required to compare the financial ratios of Clorsol with the industry averages for the manufacturing sector and provide an assessment of whether Clorsol performs better or worse. To fulfill this objective, you will need to conduct a thorough analysis of various financial ratios such as liquidity ratios, profitability ratios, and solvency ratios. By scrutinizing Clorsol's ratios in comparison to the industry averages, you will be able to ascertain the company's strengths and weaknesses, ultimately determining whether it outperforms or underperforms its industry peers. You are requird to submit a word file and an excel file to the submission folder I. Word File As part of this project, students are required to provide a one-page summary of findings based on your analysis. The summary should 1) provide a clear evaluation of how its financial ratios compare to the manufacturing industry averages 2) highlight the areas where Clorsol excels 3) identify weakness and potential areas for improvement. Students should ensure that their summary accurately reflects the findings and presents a coherent and logical narrative of the analysis conducted. Your summary should include relevant data presented through tables, charts, or graphs to address the data analysis questions provided below. Ensure that you incorporate these visual aids from your Excel analysis to support your discussion of Clorsol's performance in relation to the industry average. Both the content accuracy and professionalism of your summary and 3 supporting data will be evaluated during the grading process. Each team is only required to submit one set of solutions. II. Excel File 1) Caclulate eight ratios below for Clorsol using balalnce sheet and income statement information. Liquidity & Financial Leverage Ratios: Current Ratio = Current Assets / Current Liabilities Debt to Equity Ratio = total liabilities / total shareholder equity Activity Ratios: Accounts Receivable Turnover = Net Sales / Average net Accounts Receivable Inventory Turnover = Cost of Goods Sold / Average Inventory Asset Turnover = Net Sales / Average Total Assets Profitability/Earnings Ratios: Profit Margin on Sales = Net Income / Net Sales Return on Assets = Net Income / Average Total Assets Retrun on Equity = (net income – preferred dividends) / Average common stockholders' equity. a. Use excel formulas to calculate the ratios b. Insert a new worksheet, and label it as “Ratio". Include the ratio calculations in this sheet. 2) Calculate ratios for all companies and generate a merged file a. Compute the financial ratios for each company using the provided data in the "Data" file. (Note: you may add additional columns to the right side of the worksheet. Label each column with the appropriate ratios. To efficiently calculate the ratios for all companies, utilize the drag and fill feature available in Excel.) b. Once the ratios have been calculated for each company, you need to link each company with its associated industry information. Once you establish a clear link between each company (identified as TIC symbol) and its respective industry, make sure the "Data" file contains TIC, the industry, and the ratios for each company. i. In the "Data" file, add a new column adjacent to the "TIC" column and name it "Industry Category." Populate this column with the industry category information found in the 4th column (Column D) of the "Code" sheet. ii. Use VLOOPUP function to merge the industry information from the "Code" worksheet with the company names in the "Data" worksheet. You must use the VLOOKUP function in order to receive credit for this part of the assignment. It is not permissible to sort the data and merge it by copying and pasting. By correctly applying the 4 VLOOKUP function, you will be able to link the industry information from the "Code" worksheet with the respective company (TIC) in the "Data" worksheet, enabling the calculation of industry averages. 3) Create a pivot table to display the averages of the ratios by Industry. It is essential to use a pivot table to receive credit for this section of the assignment. a. Ensure that the pivot table displays the averages of the ratios for different industries, inlcudig the Manufacturing industry. b. Insert a new worksheet and label it as “Industry". Include the pitvot table in this sheet. 4) Utilize graphical representations to compare Clorsol's ratios with those of the manufacturing industry. a. Insert a new worksheet, and label it as "Graph." You need to copy the Clorsol' ratio and the average ratios for the manufacturing industry to this sheet. b. Utilize bar charts to visually represent the comparisons. You can either create single graph to encompass all ratios or generate multiple graphs, each focusing on one specific ratio. C. Ensure each ratio on the bar charts is clearly labeled to enable users to identify the specific ratio being represented. To summarize, your Excel file should, at the very least, incorporate the revised "Data" sheet, along with new sheets named "Ratio," "Industry," and "Graph." 5