MAKE A BALANCE SHEET LIKE THIS FOR LEONS FURNITURE
3 Vertical Analysis - Balance Sheet A condensed balance sheet can be analyzed using common size percentages. Total assets are the usual basis of companson for a balance shoet in the balance sheet below, current assets and fixed assets have been divided by total assets to calculate their percentages AB D E F G H 1 2 3 XYZ Corporation Inc. Condensed Balance Sheet December 31 4 5 6 Formula 2016 Amount 2015 Amount 7 % % in cell G10-F10/8F512 9 10 Assets Current assets Capital assets (net) Total assets $1,020,000 56.6% $ 945,000 59.2% 815 000 44 45 650.000 40.8% 1.835.000 100.0% 1,595.000 100.0% 11 12 13 14 Liabilities Current liabilities Long-term liabilities Total liabilities 15 344 500 487500 832 000 18.8% 28 89% 453% 303,000 497000 800,000 19.0% 31.296 50 2% E15015/50511 16 17 18 19 16 9% 20 21 275 400 727,600 1 003 000 150% 39.7% 54.79 Shareholders' Equity Common shares 270,000 Retained earnings 525,000 32.9% Total shareholders' equity 795.000 49.8% Total liabilities and shareholders' equity $1,835,000 100.0% 51,595.000 100.000 Remember to use the percent format and an absolute cell reference for the base number, which is total assets in this example 22 June 10,000 1 Preferred Stock Common Stock Conversion of 1000 510 stated value preferred shaten to 4000 comenshas 10,000 Excel Sheet 3: Vertical Analysis a) Using a spreadsheet, prepare a vertical analysis of the most current year's balance sheet and the previous year's balance sheet. Calculate component % for each B/S account, compute as a % of total assets. b) Using a spreadsheet, prepare a vertical analysis of the most current year's income statement and the previous year's income statement. Calculate component % for each V/S account, compute as a % of net sales. 3 Vertical Analysis - Balance Sheet A condensed balance sheet can be analyzed using common size percentages. Total assets are the usual basis of companson for a balance shoet in the balance sheet below, current assets and fixed assets have been divided by total assets to calculate their percentages AB D E F G H 1 2 3 XYZ Corporation Inc. Condensed Balance Sheet December 31 4 5 6 Formula 2016 Amount 2015 Amount 7 % % in cell G10-F10/8F512 9 10 Assets Current assets Capital assets (net) Total assets $1,020,000 56.6% $ 945,000 59.2% 815 000 44 45 650.000 40.8% 1.835.000 100.0% 1,595.000 100.0% 11 12 13 14 Liabilities Current liabilities Long-term liabilities Total liabilities 15 344 500 487500 832 000 18.8% 28 89% 453% 303,000 497000 800,000 19.0% 31.296 50 2% E15015/50511 16 17 18 19 16 9% 20 21 275 400 727,600 1 003 000 150% 39.7% 54.79 Shareholders' Equity Common shares 270,000 Retained earnings 525,000 32.9% Total shareholders' equity 795.000 49.8% Total liabilities and shareholders' equity $1,835,000 100.0% 51,595.000 100.000 Remember to use the percent format and an absolute cell reference for the base number, which is total assets in this example 22 June 10,000 1 Preferred Stock Common Stock Conversion of 1000 510 stated value preferred shaten to 4000 comenshas 10,000 Excel Sheet 3: Vertical Analysis a) Using a spreadsheet, prepare a vertical analysis of the most current year's balance sheet and the previous year's balance sheet. Calculate component % for each B/S account, compute as a % of total assets. b) Using a spreadsheet, prepare a vertical analysis of the most current year's income statement and the previous year's income statement. Calculate component % for each V/S account, compute as a % of net sales
