STEP 3 PROJECT OVERVIEW The goal of this project is to complete a comprehensive analysis of two companies in the same industry. We will then create a set of forecasted financial statements (essentially what financial analysts and many creditors do). Use the information below to complete the Project. You must select from the following companies for your analysis: Perform a horizontal analysis of the balance sheet and income statement comparing the most recent year's financial information to the prior year's financial information. Submit in Excel document in Excel as two tabs. One labeled Horizon. Analysis IS and one labeled Horizon. Analysis BS. Choice 1: Home Depot/Lowes Home Improvement Company Choice 2: Wal-Mart/Target Choice 3: Apple/Microsoft Choice 4: CocaCola Company/PepsiCo, Inc. Choice 5: BJs Wholesale Club/Costco Wholesale Corporation You can use the Excel PROJECT NAME as a template or you can create your own Excel document. Note, update your balance sheet and income statement financial lines to match your companies. For example, if you do not have depreciation expense broken out on a separate line delete the row from the template. If you have investment gain/loss, add a new row in the Excel template. See EXAMPLE EXCEL PROJECT ANALYSIS in the Project folder for additional guidance. STEP 1 Ensure your Assets = Liabilities + Stockholders' Equity prior to submitting. Download the most recent annual reports for each of your assigned companies, and review them. You can find the annual report by visiting the company's respective website and search under Investor Relations. PART 1 SUBMISSION Follow the steps above to complete the Project Part 1. You will utilize the Excel document file Project Excel YOUR NAME to complete the spreadsheet analysis. To receive full credit for the Project you must submit: 1. Both Excel interactive data downloads of your two companies' interactive data spreadsheets from the SEC website. Follow the document Accessing SEC Files located in Blackboard under the Project folder and access the SEC EDGAR Website to locate the recent Form 10-K (or other annual report such as 20-F or 40-F) (www.sec.gov). Download a spreadsheet version of financial statements from the interactive data section (see Accessing SEC Files guide). 2. 1 Excel document named Project Excel YOUR NAME Answers to STEP 2 and STEP 3 1 tab labeled Vertical Analysis IS 1 tab labeled Vertical Analysis BS 1 tab labeled Horizon. Analysis IS 1 tab labeled Horizon. Analysis BS STEP 2 1 Using the Excel spreadsheet financial statements perform a vertical analysis of the balance sheet and income statement for the most recent fiscal year. Perform your vertical analysis in Excel as a two tabs. One labeled Vertical Analysis IS and one labeled Vertical Analysis BS. Submit Excel document of vertical analysis. 1. Prepare a common-size balance sheet. Express each item as a percent of total assets. Do this for all years on the balance sheet. 2 Prepare common size income statement. Express each item on the income statement as a percent of total sales or revenue. Do this for all years on the income statement. You can use the EXAMPLE EXCEL PROJECT ANALYSIS in the Project folder for additional guidance. To gain an understanding of the assignment grading, view the grading rubric in Blackboard. You can use the Excel PROJECT NAME as a template or you can create your own Excel document. Note, update your balance sheet and income statement financial lines to match your companies. For example, if you do not have depreciation expense broken out on a separate line delete the row from the template. If you have investment gain/loss, add a new row in the Excel template. See EXAMPLE EXCEL PROJECT ANALYSIS in the Project folder for additional guidance. E F G H 1 M N o P R S T U V K COMPANY TWO NAME Balance Sheet December 31 NOTE - ADJUST YOUR BALANCE SHEET FINANCIAL STATEMENT LINES AS NECESSARY % % A D 1 COMPANY ONE NAME 2 Balance Sheet 3 XXXX 4 5 6 Assets 7 Current Assets: 8 Cash 9 Accounts receivable 10 Inventory 11 Prepaid rent 12 Long-Term Assets: 13 Investment in bonds 14 Land 15 Equipment 16 Less: Accumulated depreciation 17 Total Assets 18 19 Liabilities and Stockholders' Equity 20 Current Liabilities: 21 Accounts payable 22 Interest payable 23 Income tax payable 24 Long-Term Liabilities: 25 Notes payable 26 Stockholders' Equity: 27 Common stock 28 Retained earnings 29 Total Liabilities and Stockholders' Equity 30 31 32 33 34 35 Assets Current Assets: Cash Accounts receivable Inventory Prepaid rent Long-Term Assets: Long-term investment Land Equipment Less: Accumulated depreciation Total Assets Liabilities and Stockholders' Equity Current Liabilities: Accounts payable Interest payable Income tax payable Long-Term Liabilities: Notes payable Stockholders' Equity: Common stock Retained earnings Total Liabilities and Stockholders' Equity A B DE F G H 1 J K L M N Q R S T U V 1. COMPANY ONE NAME Income Statement For the Year Ended XXXX COMPANY TWO NAME Income Statements For the Year Ended XXXX 2 3 4 4 5 5 7 2018 2017 $ change % change 2018 2017 $ change % change 3 0 1 2 3 4 5 6 7 8 9 20 -1 1 2. -3 Net Sales Cost of goods sold Gross profit Expenses: Operating expenses Depreciation expense Loss on sale of land Interest expense Income tax expense Total expenses Net income Net Sales Cost of goods sold Gross profit Expenses: Operating expenses Depreciation expense Loss on sale of land Interest expense Income tax expense Total expenses Net income $ $ $ F G H 1 J K L o P R S T U V W Y Z MN COMPANY ONE NAME Balance Sheets December 31 NOTE - ADJUST YOUR BALANCE SHEET FINANCIAL STATEMENT LINES AS NECESSARY XXXX $ change % change XXXX XXXX $ change % change B D E 1 1 COMPANY ONE NAME 2 Balance Sheets 3 December 31 4 5 XXXX 6 Assets 7 Current Assets: 8 Cash 9 Accounts receivable 10 Inventory 11 Prepaid rent 12 Long-Term Assets: 13 Investment in bonds 14 Land 15 Equipment 16 Less: Accumulated depreciation 17 Total Assets 18 19 Liabilities and Stockholders' Equity 20 Current Liabilities: 21 Accounts payable 22 Interest payable 23 Income tax payable 24 Long-Term Liabilities: 25 Notes payable 26 Stockholders' Equity: 27 Common stock 28 Retained earnings 29 Total Liabilities and Stockholders' Equity $ 30 31 32 Assets Current Assets: Cash Accounts receivable Inventory Prepaid rent Long-Term Assets: Long-term investment Land Equipment Less: Accumulated depreciation Total Assets $ - $ Liabilities and Stockholders' Equity Current Liabilities: Accounts payable Interest payable Income tax payable Long-Term Liabilities: Notes payable Stockholders' Equity Common stock Retained earnings Total Liabilities and Stockholders' Equity - $ $ $ 33 34 35 STEP 3 PROJECT OVERVIEW The goal of this project is to complete a comprehensive analysis of two companies in the same industry. We will then create a set of forecasted financial statements (essentially what financial analysts and many creditors do). Use the information below to complete the Project. You must select from the following companies for your analysis: Perform a horizontal analysis of the balance sheet and income statement comparing the most recent year's financial information to the prior year's financial information. Submit in Excel document in Excel as two tabs. One labeled Horizon. Analysis IS and one labeled Horizon. Analysis BS. Choice 1: Home Depot/Lowes Home Improvement Company Choice 2: Wal-Mart/Target Choice 3: Apple/Microsoft Choice 4: CocaCola Company/PepsiCo, Inc. Choice 5: BJs Wholesale Club/Costco Wholesale Corporation You can use the Excel PROJECT NAME as a template or you can create your own Excel document. Note, update your balance sheet and income statement financial lines to match your companies. For example, if you do not have depreciation expense broken out on a separate line delete the row from the template. If you have investment gain/loss, add a new row in the Excel template. See EXAMPLE EXCEL PROJECT ANALYSIS in the Project folder for additional guidance. STEP 1 Ensure your Assets = Liabilities + Stockholders' Equity prior to submitting. Download the most recent annual reports for each of your assigned companies, and review them. You can find the annual report by visiting the company's respective website and search under Investor Relations. PART 1 SUBMISSION Follow the steps above to complete the Project Part 1. You will utilize the Excel document file Project Excel YOUR NAME to complete the spreadsheet analysis. To receive full credit for the Project you must submit: 1. Both Excel interactive data downloads of your two companies' interactive data spreadsheets from the SEC website. Follow the document Accessing SEC Files located in Blackboard under the Project folder and access the SEC EDGAR Website to locate the recent Form 10-K (or other annual report such as 20-F or 40-F) (www.sec.gov). Download a spreadsheet version of financial statements from the interactive data section (see Accessing SEC Files guide). 2. 1 Excel document named Project Excel YOUR NAME Answers to STEP 2 and STEP 3 1 tab labeled Vertical Analysis IS 1 tab labeled Vertical Analysis BS 1 tab labeled Horizon. Analysis IS 1 tab labeled Horizon. Analysis BS STEP 2 1 Using the Excel spreadsheet financial statements perform a vertical analysis of the balance sheet and income statement for the most recent fiscal year. Perform your vertical analysis in Excel as a two tabs. One labeled Vertical Analysis IS and one labeled Vertical Analysis BS. Submit Excel document of vertical analysis. 1. Prepare a common-size balance sheet. Express each item as a percent of total assets. Do this for all years on the balance sheet. 2 Prepare common size income statement. Express each item on the income statement as a percent of total sales or revenue. Do this for all years on the income statement. You can use the EXAMPLE EXCEL PROJECT ANALYSIS in the Project folder for additional guidance. To gain an understanding of the assignment grading, view the grading rubric in Blackboard. You can use the Excel PROJECT NAME as a template or you can create your own Excel document. Note, update your balance sheet and income statement financial lines to match your companies. For example, if you do not have depreciation expense broken out on a separate line delete the row from the template. If you have investment gain/loss, add a new row in the Excel template. See EXAMPLE EXCEL PROJECT ANALYSIS in the Project folder for additional guidance. E F G H 1 M N o P R S T U V K COMPANY TWO NAME Balance Sheet December 31 NOTE - ADJUST YOUR BALANCE SHEET FINANCIAL STATEMENT LINES AS NECESSARY % % A D 1 COMPANY ONE NAME 2 Balance Sheet 3 XXXX 4 5 6 Assets 7 Current Assets: 8 Cash 9 Accounts receivable 10 Inventory 11 Prepaid rent 12 Long-Term Assets: 13 Investment in bonds 14 Land 15 Equipment 16 Less: Accumulated depreciation 17 Total Assets 18 19 Liabilities and Stockholders' Equity 20 Current Liabilities: 21 Accounts payable 22 Interest payable 23 Income tax payable 24 Long-Term Liabilities: 25 Notes payable 26 Stockholders' Equity: 27 Common stock 28 Retained earnings 29 Total Liabilities and Stockholders' Equity 30 31 32 33 34 35 Assets Current Assets: Cash Accounts receivable Inventory Prepaid rent Long-Term Assets: Long-term investment Land Equipment Less: Accumulated depreciation Total Assets Liabilities and Stockholders' Equity Current Liabilities: Accounts payable Interest payable Income tax payable Long-Term Liabilities: Notes payable Stockholders' Equity: Common stock Retained earnings Total Liabilities and Stockholders' Equity A B DE F G H 1 J K L M N Q R S T U V 1. COMPANY ONE NAME Income Statement For the Year Ended XXXX COMPANY TWO NAME Income Statements For the Year Ended XXXX 2 3 4 4 5 5 7 2018 2017 $ change % change 2018 2017 $ change % change 3 0 1 2 3 4 5 6 7 8 9 20 -1 1 2. -3 Net Sales Cost of goods sold Gross profit Expenses: Operating expenses Depreciation expense Loss on sale of land Interest expense Income tax expense Total expenses Net income Net Sales Cost of goods sold Gross profit Expenses: Operating expenses Depreciation expense Loss on sale of land Interest expense Income tax expense Total expenses Net income $ $ $ F G H 1 J K L o P R S T U V W Y Z MN COMPANY ONE NAME Balance Sheets December 31 NOTE - ADJUST YOUR BALANCE SHEET FINANCIAL STATEMENT LINES AS NECESSARY XXXX $ change % change XXXX XXXX $ change % change B D E 1 1 COMPANY ONE NAME 2 Balance Sheets 3 December 31 4 5 XXXX 6 Assets 7 Current Assets: 8 Cash 9 Accounts receivable 10 Inventory 11 Prepaid rent 12 Long-Term Assets: 13 Investment in bonds 14 Land 15 Equipment 16 Less: Accumulated depreciation 17 Total Assets 18 19 Liabilities and Stockholders' Equity 20 Current Liabilities: 21 Accounts payable 22 Interest payable 23 Income tax payable 24 Long-Term Liabilities: 25 Notes payable 26 Stockholders' Equity: 27 Common stock 28 Retained earnings 29 Total Liabilities and Stockholders' Equity $ 30 31 32 Assets Current Assets: Cash Accounts receivable Inventory Prepaid rent Long-Term Assets: Long-term investment Land Equipment Less: Accumulated depreciation Total Assets $ - $ Liabilities and Stockholders' Equity Current Liabilities: Accounts payable Interest payable Income tax payable Long-Term Liabilities: Notes payable Stockholders' Equity Common stock Retained earnings Total Liabilities and Stockholders' Equity - $ $ $ 33 34 35