Directions:
- Excel file work: You must use cell referencing for the input data. You must use Excel for all calculations. You will be graded on accuracy of your calculations, Excel use in calculations, formatting, and appearance. Be sure to remove grid lines.
- Financial Statements: Use the data sheet tab to create the following three statements.
- Income Statement for Year ended 2018. *See Table 2.1 in text for example. Proper form excludes row numbers and all text is in black. See rubric for details. Check figure for Earnings Available to Common = $16,590
- Statement of Retained Earnings for Year ended 2018. *See Table 2.2 in text for example.
- Balance Sheet as of December 31, 2018. *See Table 2.4 in text for example
Check figure for Total Assets = $384,985
- Financial Ratios: Use the 2017 and 2018 financial statements to calculate the ratios specified on the Ratio Analysis tab.
Assets $ 5 Current Assets: 6 Cash 7 Accounts receivable 8 Inventory 9 Prepaid expenses 10 Total current assets 11 Fixed Assets: 12 Plant & equipment 13 Less: Accum. depreciation 14 Net plant & equipment 15 Total Assets D & H Enterprises Balance Sheet for Year Ended December 31, 2017 Liabilities Current Liabilities: $60,000 Accounts payable 21,000 Notes payable 31,000 Total current liabilities 12,600 Notes payable 124,600 Bonds payable Total liabilites 256,000 Stockholder's Equity 51,200 Preferred stock 204,800 Common stock $329,400 Paid in capital in excess of par Retained earnings Total Equity Total Liab. & Equity 18,000 26.000 44,000 12,000 88,000 144,000 26,000 61,000 31,000 67,400 185,400 329,400 $ $ A D & H Enterprises Income Statement for year ended December 31, 2017 4 Sales $ 230,000 5 Cost of goods sold 126,500 6 Gross profit $ 103,500 7 Selling and administrative expense 24,500 8 Depreciation expense 26,000 9 Operating profit 53,000 10 Interest expense 20,580 11 Earnings before taxes 32,420 Taxes 8,105 Earnings after taxes $ 24,315 Preferred stock dividends 2,700 Earnings available to common stockholder $ 21,615 17 Shares outstanding 18 Earnings per share 10000 2.16 $ $ 215,000 62% $ 21,000 9% 5% 8% 11% 25% 2,700 4,085 1 Sales for 2018 2 Cost of goods sold as percent of sales 3 Selling and administration expenses 4 Depreciation expense as percent of plant & equipment at beginning of the year 5 Interest expense on Notes payable - based on December 31, 2017, balances 6 Interest rate on Notes payable (5 year) - based on Dec 31, 2017, balances 7 Interest rate on Bonds payable - based on December 31, 2017, balances 8 Tax rate 9 Preferred dividends paid 10 Common stock dividends paid 11 During 2018, prepaid expenses balance was unchanged. 12 Accounts receivable decreased in 2018 by 13 Inventory increased in 2018 by 14 New machine was purchased on December 31, 2018, at a cost of 15 Accounts payable increased in 2018 by 16 Notes payable (6 month) decreased at end of the year by 17 Notes payable (5 year) increased at end of the year by 18 Bonds payable increased at end of the year by 19 Preferred stock 20 Common stock increased by 21 Paid in capital in excess of par 22 Number of shares outstanding 23 During 2018, cash balance increased by 24 Market price per share of common as of December 31, 2017, was 25 Market price per share of common as of December 31, 2018, was 3% 15% $ 65,000 6% $ 2,000 $ 9,000 $ 30,000 No change $ 5,000 No change 15000 9605 1 57.45! 51.93 Company Name Statement Title Time Period 16 Earnings available to common stockholder 18 Shares outstanding 19 Earnings per share 20 Round earnings per share to two decimal places Company Name Statement Title Time Period Company Name Statement Title Time Period Liabilities Current Liabilities: Assets Current Assets: 11 Fixed Assets: Stockholder's Equity Total Assets Total Equity Total Liab. & Equity Ratio Name Current Quick Ratio Analysis - Liquidity 2017 Value 2018 Value Industry Average 2.15 1.43 Ratio Analysis - Asset Utilization/Efficiency 7 Ratio Name 2017 Value 2018 Value Industry Average 8 Inventory Turnover 9 Days Sales in Inventory 52 10 Receivables Turnover 12.33 11 Days Sales Outstanding 45 12 Fixed Assets Turnover 1.84 13 Total Assets Turnover 1.09 Ratio Analysis - Debt Management/Leverage 16 Ratio Name 2017 Value 2018 Value Industry Average 17 Total Debt Ratio Times Interest Earned 21 Ratio Name 22 Net Profit Margin 23 Return on Assets 24 Return on Equity Ratio Analysis - Profitability 2017 Value 2018 Value Industry Average 8.60% 5.33% 10.50% 27 Ratio Name 28 Price/Earnings (PE) 29 Market/Book JU Ratio Analysis - Profitability 2017 Value 2018 Value Industry Average 23 Assets $ 5 Current Assets: 6 Cash 7 Accounts receivable 8 Inventory 9 Prepaid expenses 10 Total current assets 11 Fixed Assets: 12 Plant & equipment 13 Less: Accum. depreciation 14 Net plant & equipment 15 Total Assets D & H Enterprises Balance Sheet for Year Ended December 31, 2017 Liabilities Current Liabilities: $60,000 Accounts payable 21,000 Notes payable 31,000 Total current liabilities 12,600 Notes payable 124,600 Bonds payable Total liabilites 256,000 Stockholder's Equity 51,200 Preferred stock 204,800 Common stock $329,400 Paid in capital in excess of par Retained earnings Total Equity Total Liab. & Equity 18,000 26.000 44,000 12,000 88,000 144,000 26,000 61,000 31,000 67,400 185,400 329,400 $ $ A D & H Enterprises Income Statement for year ended December 31, 2017 4 Sales $ 230,000 5 Cost of goods sold 126,500 6 Gross profit $ 103,500 7 Selling and administrative expense 24,500 8 Depreciation expense 26,000 9 Operating profit 53,000 10 Interest expense 20,580 11 Earnings before taxes 32,420 Taxes 8,105 Earnings after taxes $ 24,315 Preferred stock dividends 2,700 Earnings available to common stockholder $ 21,615 17 Shares outstanding 18 Earnings per share 10000 2.16 $ $ 215,000 62% $ 21,000 9% 5% 8% 11% 25% 2,700 4,085 1 Sales for 2018 2 Cost of goods sold as percent of sales 3 Selling and administration expenses 4 Depreciation expense as percent of plant & equipment at beginning of the year 5 Interest expense on Notes payable - based on December 31, 2017, balances 6 Interest rate on Notes payable (5 year) - based on Dec 31, 2017, balances 7 Interest rate on Bonds payable - based on December 31, 2017, balances 8 Tax rate 9 Preferred dividends paid 10 Common stock dividends paid 11 During 2018, prepaid expenses balance was unchanged. 12 Accounts receivable decreased in 2018 by 13 Inventory increased in 2018 by 14 New machine was purchased on December 31, 2018, at a cost of 15 Accounts payable increased in 2018 by 16 Notes payable (6 month) decreased at end of the year by 17 Notes payable (5 year) increased at end of the year by 18 Bonds payable increased at end of the year by 19 Preferred stock 20 Common stock increased by 21 Paid in capital in excess of par 22 Number of shares outstanding 23 During 2018, cash balance increased by 24 Market price per share of common as of December 31, 2017, was 25 Market price per share of common as of December 31, 2018, was 3% 15% $ 65,000 6% $ 2,000 $ 9,000 $ 30,000 No change $ 5,000 No change 15000 9605 1 57.45! 51.93 Company Name Statement Title Time Period 16 Earnings available to common stockholder 18 Shares outstanding 19 Earnings per share 20 Round earnings per share to two decimal places Company Name Statement Title Time Period Company Name Statement Title Time Period Liabilities Current Liabilities: Assets Current Assets: 11 Fixed Assets: Stockholder's Equity Total Assets Total Equity Total Liab. & Equity Ratio Name Current Quick Ratio Analysis - Liquidity 2017 Value 2018 Value Industry Average 2.15 1.43 Ratio Analysis - Asset Utilization/Efficiency 7 Ratio Name 2017 Value 2018 Value Industry Average 8 Inventory Turnover 9 Days Sales in Inventory 52 10 Receivables Turnover 12.33 11 Days Sales Outstanding 45 12 Fixed Assets Turnover 1.84 13 Total Assets Turnover 1.09 Ratio Analysis - Debt Management/Leverage 16 Ratio Name 2017 Value 2018 Value Industry Average 17 Total Debt Ratio Times Interest Earned 21 Ratio Name 22 Net Profit Margin 23 Return on Assets 24 Return on Equity Ratio Analysis - Profitability 2017 Value 2018 Value Industry Average 8.60% 5.33% 10.50% 27 Ratio Name 28 Price/Earnings (PE) 29 Market/Book JU Ratio Analysis - Profitability 2017 Value 2018 Value Industry Average 23
