Problem #1 (30 marks) Prepare a ratio analysis for Halifax Sports Inc. using an excel spreadsheet. The firm has 2 million shares authorized and has 1 million shares issued and outstanding. Halifax Sports Inc. Balance Sheet($000) 2019 125,000 2020 160,000 96,000 64,000 75,000 50,000 Sales Revenues Cost of Goods Sold Contribution Margin Operating Expenses Salaries Wages General/Admin Amort/Depr. Interest Earnings B4 Taxes Halifax Sports Inc Balance Sheet($000) ASSETS 2020 2019 Current Assets Cash 485 9,000 Short-term Investments 10 Cert. of Deposit 5 Accounts Receivable 16,000 12,500 Inventory 45,500 29,000 Total Current Assets 62,000 50,500 Land 26,000 20,000 Buildings and Equipment 100,000 70,000 Accumulated Amortization 38,000 28,000 Net Plant & Equipment 88,000 62,000 TOTAL ASSETS 150,000 112,500 LIABILITIES and SHAREHOLDERS' EQUITY Current Liabilities Accounts Payable 22,000 10,500 Bank Note 40,000 10,000 Taxes Payable 2,500 2,000 Accrued Expenses 4,500 5,000 Total Current Liabilities 69,000 27,500 Long Term Debt First Mortgage Bonds 20,000 22,000 Debenture Bonds 2,950 6,750 Total Liabilities 22,950 28,750 11,000 16,000 10,000 10,000 6,100 10,900 5,450 11,000 12,500 10,000 4,500 3,000 Taxes 9,000 4,500 4,500 Earnings After tax 5,450 Equity Common Shares Retained Earnings 31,500 26,550 58,050 31,500 24,750 56,250 Total Equity a) Using an excel spreadsheet calculate the following for both years: 30 marks NOTE: the use of the excel spreadsheet is mandatory and the excel spreadsheet must be embedded in the word document. Submitting a separate file to the spreadsheet is not acceptable. If the spreadsheet is not used, a mark of 0 will be applied to the question. Formula Values Industry Calculation Average 4.20 2.10 9.86 Ratio Current Ratio Quick Ratio Receivables turnover Day's Sales in Recievables Inventory Turnover Day's Sales in Inventory Debt to Total Assets Times Interest Earned Total Asset Turnover Fixed Asset Turnover Return on Total Assets Return on Equity Gross Profit Margin Operating Profit Margin Profit Margin 37.00 2.50 146.00 58.00% 3.80 1.14 1.40 4.00% 15.00% 38.00% 10.00% 3.50% You need to show your work for your calculations. For example Equity Multiplier Assets 150,000 Equity 58,050 2.584 b) Complete the cash flow statement for 2020 using an excel spreadsheet. (5 marks) c) Complete the sources and Uses Statement for 2020 using an excel spreadsheet (5 marks) d) Complete the Cash Flow Identity Statement for 2020 using an excel spreadsheet. (5 marks) e) Complete common size statements for both the income statement and balance sheets for both years using an excel spreadsheet. (10 marks) f) Use the Dupont ratio to calculate the ROE and which ratio should the firm try to improve? (4 marks)
