I need help on Module III, I need to to fill out the Ratio Analysis excel sheet, from liquify ratio to book value per share.
Income Statement Spreadsheet Example 4 Module Name: Date: Jacob Corporation Income Statement 2015-2018 11 2017 $ 40,000.00 18000 $ 22,000.00 2018 $ 42,000.00 19500 $ 22,500.00 7,951 193 7,800 195 3,150 $ 11,145.00 3.002 $ 11,146.00 2015 2016 12 Sales $ 33,000.00 $ 36,000.00 13 Cost Of Goods Sold 14000 16000 14 Gross Profit $ 19,000.00 $ 20,000.00 15 16 Selling & Administrative Expenses 7,075 7,200 17 Depreciation 170 184 18 Other Operating Expenses 2,871 2,904 19 Total Operating Expenses $ 10,116.00 $ 10,288.00 20 21 Earnings Before Interest & Taxes(EBIT) $ 8,884.00 $ 9,712.00 Interest Expense 628 628 23 Earnings Before Taxes(EBT) 8256 9084 24 Taxes@40% 3302 3634 25 Net Income $ 4,953.60 $ 5,450.40 26 27 Common Dividends 750 860 28 Addition to Retained Earnings $ 4,203.60 $ 4,590.40 29 30 Common Shares Outstanding 1,000 1,000 31 Earnings Per Share(EPS) 4.95 5.45 32 Dividends Per Share 0.75 0.86 33 34 Jacob Corporation Common-Size Income Statement 2015-2018 $ 10,854.00 595 10259 4104 $ 6,155.40 $ 11.355.00 615 10740 4296 $ 6,444.00 $ 920 5,235.40 1020 $ 5,424.00 1,000 6.16 0.92 1,200 5.37 0.85 38 39 2015 100.00% 42.42% 57.58% 2016 100.00% 44.44% 55.56% 2017 100.00% 45.00% 55.00% 2018 100.00% 46.43% 53.57% 40 Sales 41 Cost Of Goods Sold 42 Gross Profit 43 44 Selling & Administrative Expenses 45 Depreciation 46 Other Operating Expenses 21.44% 0.52% 8.70% 20.00% 0.51% 8.07% 19.88% 0.48% 7.51% 18.57% 0.46% 7.50% 36 37 38 Jacob Corporation Common-Size Income Statement 2015-2018 40 Sales 41 Cost Of Goods Sold 42 Gross Profit 2015 100.00% 42.42% 57.58% 2016 100.00% 44.44% 55.56% 2017 100.00% 45.00% 55.00% 2018 100.00% 46.43% 53.57% 43 21.44% 0.52% 8.70% 30.65% 20.00% 0.51% 8.07% 28.58% 19.88% 0.48% 7.51% 27.87% 18.57% 0.46% 7.50% 26.54% 44 Selling & Administrative Expenses 45 Depreciation 46 Other Operating Expenses 47 Total Operating Expenses 48 49 Earnings Before Interest & Taxes(EBIT) 50 Interest Expense 51 Earnings Before Taxes(EBT) 52 Taxes@40% Net Income 26.92% 1.90% 25.02% 26.98% 1.74% 25.23% 27.14% 1.49% 25.65% 27.04% 1.46% 25.57% 10.01% 10.09% 10.26% 10.23% 2.27% 12.74% 2.39% 12.75% 2.30% 13.09% 2.43% 12.91% 55 Common Dividends 56 Addition to Retained Earnings(NET) 57 58 59 Balance Sheet Spreadsheet Example 60 61 62 63 Module 1 64 Name: Date: 65 66 67 Jacob Corporation Balance Sheet 2015-2018 68 69 70 2015 3,000 330 2,534 2184 $8,048.00 2016 3,600 1,220 3,418 2704 $10,942.00 2017 4,380 1,582 3,902 3099 $12,963.00 2018 5,308 1,385 4,200 3400 $14,293.00 71 72 Cash 73 Marketable Securities 74 Accounts Receivable 75 Inventories 76 Total Current Assets 77 78 Gross Fixed Assets 79 Less: Accumulated Depreciation 80 Net Fixed Assets 81 Intangible Assets Total Assets 83 84 Notes Payable 85 Accounts Payable 86 Accruals 46,659 7179 39480 200 $47,728.00 47.750 7358 40392 195 $51,529.00 51,785 7542 44243 186 $57,392.00 55,689 7731 47958 180 $62,431.00 200 82 185 $1,200 3,125 922 CEI Statement of CF $1,000 $900 $700 3,185 3,622 3,800 619 643 484 CLOZAPEZIO DI COLA Financial Ratios Time Financial Statements Enter BCDEFG JK Ratio Analysis Spreadsheet Example AWN 4 Module III Name: Date: 8 Jacob Corporation Ratio Analysis 2015 2016 2017 2018 10 11 Liquidity Ratios 12 13 Current Ratio 14 Quick Ratio 15 16 Asset Management Ratios 17 18 Inventory Turnover 19 Days Sales Outstanding 20 Total Assets Turnover 21 22 23 Debt Management 24 25 Debt Ratio 26 Equity Multiplier 27 Times Interest Eamed Ratio 28 29 Profitablitlity Ratios 30 31 Gorss Profit Margin 32 Operationg Profit Margin 33 Net Profit Margin 34 Return On Total Assets (ROA) 35 Return On Equity (ROE) 36 Basic Eaming Power 37 Earings Per Share 38 Book Value Per Share 39 40 Graph Current Ratio over 2015-2018 41 2015 0.00 2016 0.00 2017 0.00 2018 0.00 42 43 Current Ratio 44 45 46 47 Financial Statements Statement of CF Financial Ratios Time Value Risk & Return is is to graph ratios. ou would like to try average. You should set u An easy way to conduct trend analysis You can produce graphs easily using graph the current ratios of the firm and following table containing the needed informati Iduct trend analysis or comparative ratio analysis is to or graphs easily using Excel. For example, suppose you would the needed information: | | D | E 2012 2013 1.5 2011 1.3 1.4 142 / 2013 2010 1.2 1.1 Firm 2 1.6 1.3 1.3 1.1 Industry Next, follow the steps below: dimit Block the input range A1:E3 Click Insert Select Chart (or click the Chart Wizard icon) In the Chart Wizard box, click the Line Chart with dots Financial Ratios Exercise: This assignment is divided into two parts. The first part will be devoted to typing the ratio analysis format and creation of formulas, followed by a comparison of Jacob's Corp.'s 2010 through 2013 ratios. In the second part of the assignment, you are required to create a graph for Current Ratio. You need to begin this assignment by combining both the income statement and the balance sheet files into a single spreadsheet. After you have combined both files, go to the bottom of the Income Statement to begin preparing the ratio analysis worksheet. Refer to your lab manual for the necessary formulas. Be careful when entering formulas that contain more than one mathematical operator. Just like a basic algebraic formula, parentheses always take precedence over any other mathematical operator. The proper sequence of the mathematical operators to be utilized is of utmost importance to determine the correct answers to the formulas. Income Statement Spreadsheet Example 4 Module Name: Date: Jacob Corporation Income Statement 2015-2018 11 2017 $ 40,000.00 18000 $ 22,000.00 2018 $ 42,000.00 19500 $ 22,500.00 7,951 193 7,800 195 3,150 $ 11,145.00 3.002 $ 11,146.00 2015 2016 12 Sales $ 33,000.00 $ 36,000.00 13 Cost Of Goods Sold 14000 16000 14 Gross Profit $ 19,000.00 $ 20,000.00 15 16 Selling & Administrative Expenses 7,075 7,200 17 Depreciation 170 184 18 Other Operating Expenses 2,871 2,904 19 Total Operating Expenses $ 10,116.00 $ 10,288.00 20 21 Earnings Before Interest & Taxes(EBIT) $ 8,884.00 $ 9,712.00 Interest Expense 628 628 23 Earnings Before Taxes(EBT) 8256 9084 24 Taxes@40% 3302 3634 25 Net Income $ 4,953.60 $ 5,450.40 26 27 Common Dividends 750 860 28 Addition to Retained Earnings $ 4,203.60 $ 4,590.40 29 30 Common Shares Outstanding 1,000 1,000 31 Earnings Per Share(EPS) 4.95 5.45 32 Dividends Per Share 0.75 0.86 33 34 Jacob Corporation Common-Size Income Statement 2015-2018 $ 10,854.00 595 10259 4104 $ 6,155.40 $ 11.355.00 615 10740 4296 $ 6,444.00 $ 920 5,235.40 1020 $ 5,424.00 1,000 6.16 0.92 1,200 5.37 0.85 38 39 2015 100.00% 42.42% 57.58% 2016 100.00% 44.44% 55.56% 2017 100.00% 45.00% 55.00% 2018 100.00% 46.43% 53.57% 40 Sales 41 Cost Of Goods Sold 42 Gross Profit 43 44 Selling & Administrative Expenses 45 Depreciation 46 Other Operating Expenses 21.44% 0.52% 8.70% 20.00% 0.51% 8.07% 19.88% 0.48% 7.51% 18.57% 0.46% 7.50% 36 37 38 Jacob Corporation Common-Size Income Statement 2015-2018 40 Sales 41 Cost Of Goods Sold 42 Gross Profit 2015 100.00% 42.42% 57.58% 2016 100.00% 44.44% 55.56% 2017 100.00% 45.00% 55.00% 2018 100.00% 46.43% 53.57% 43 21.44% 0.52% 8.70% 30.65% 20.00% 0.51% 8.07% 28.58% 19.88% 0.48% 7.51% 27.87% 18.57% 0.46% 7.50% 26.54% 44 Selling & Administrative Expenses 45 Depreciation 46 Other Operating Expenses 47 Total Operating Expenses 48 49 Earnings Before Interest & Taxes(EBIT) 50 Interest Expense 51 Earnings Before Taxes(EBT) 52 Taxes@40% Net Income 26.92% 1.90% 25.02% 26.98% 1.74% 25.23% 27.14% 1.49% 25.65% 27.04% 1.46% 25.57% 10.01% 10.09% 10.26% 10.23% 2.27% 12.74% 2.39% 12.75% 2.30% 13.09% 2.43% 12.91% 55 Common Dividends 56 Addition to Retained Earnings(NET) 57 58 59 Balance Sheet Spreadsheet Example 60 61 62 63 Module 1 64 Name: Date: 65 66 67 Jacob Corporation Balance Sheet 2015-2018 68 69 70 2015 3,000 330 2,534 2184 $8,048.00 2016 3,600 1,220 3,418 2704 $10,942.00 2017 4,380 1,582 3,902 3099 $12,963.00 2018 5,308 1,385 4,200 3400 $14,293.00 71 72 Cash 73 Marketable Securities 74 Accounts Receivable 75 Inventories 76 Total Current Assets 77 78 Gross Fixed Assets 79 Less: Accumulated Depreciation 80 Net Fixed Assets 81 Intangible Assets Total Assets 83 84 Notes Payable 85 Accounts Payable 86 Accruals 46,659 7179 39480 200 $47,728.00 47.750 7358 40392 195 $51,529.00 51,785 7542 44243 186 $57,392.00 55,689 7731 47958 180 $62,431.00 200 82 185 $1,200 3,125 922 CEI Statement of CF $1,000 $900 $700 3,185 3,622 3,800 619 643 484 CLOZAPEZIO DI COLA Financial Ratios Time Financial Statements Enter BCDEFG JK Ratio Analysis Spreadsheet Example AWN 4 Module III Name: Date: 8 Jacob Corporation Ratio Analysis 2015 2016 2017 2018 10 11 Liquidity Ratios 12 13 Current Ratio 14 Quick Ratio 15 16 Asset Management Ratios 17 18 Inventory Turnover 19 Days Sales Outstanding 20 Total Assets Turnover 21 22 23 Debt Management 24 25 Debt Ratio 26 Equity Multiplier 27 Times Interest Eamed Ratio 28 29 Profitablitlity Ratios 30 31 Gorss Profit Margin 32 Operationg Profit Margin 33 Net Profit Margin 34 Return On Total Assets (ROA) 35 Return On Equity (ROE) 36 Basic Eaming Power 37 Earings Per Share 38 Book Value Per Share 39 40 Graph Current Ratio over 2015-2018 41 2015 0.00 2016 0.00 2017 0.00 2018 0.00 42 43 Current Ratio 44 45 46 47 Financial Statements Statement of CF Financial Ratios Time Value Risk & Return is is to graph ratios. ou would like to try average. You should set u An easy way to conduct trend analysis You can produce graphs easily using graph the current ratios of the firm and following table containing the needed informati Iduct trend analysis or comparative ratio analysis is to or graphs easily using Excel. For example, suppose you would the needed information: | | D | E 2012 2013 1.5 2011 1.3 1.4 142 / 2013 2010 1.2 1.1 Firm 2 1.6 1.3 1.3 1.1 Industry Next, follow the steps below: dimit Block the input range A1:E3 Click Insert Select Chart (or click the Chart Wizard icon) In the Chart Wizard box, click the Line Chart with dots Financial Ratios Exercise: This assignment is divided into two parts. The first part will be devoted to typing the ratio analysis format and creation of formulas, followed by a comparison of Jacob's Corp.'s 2010 through 2013 ratios. In the second part of the assignment, you are required to create a graph for Current Ratio. You need to begin this assignment by combining both the income statement and the balance sheet files into a single spreadsheet. After you have combined both files, go to the bottom of the Income Statement to begin preparing the ratio analysis worksheet. Refer to your lab manual for the necessary formulas. Be careful when entering formulas that contain more than one mathematical operator. Just like a basic algebraic formula, parentheses always take precedence over any other mathematical operator. The proper sequence of the mathematical operators to be utilized is of utmost importance to determine the correct answers to the formulas
