Attached are a set of Financial Statements (Balance Sheet and Income Statement) for a sample corporation. The assigment is to analyze and document what has transpired over this period both in terms of actions taken and any resulted that have occurred. While you should examine financial ratios over the period, do not just comment on individual ratio changes. Your submission needs to critically examine the interrelationship of outcomes. Are they the result of discrete actions undertaken by management and if so, is there an indication that the intended results were achieved? For example: - - Were major capital investments made during the period? If so, how were they finance and how did this impact the statements? Has the financing of capital assets introduced additional risk to any investor group? Did the investment in new capital assets generate enhanced profits and to which group of investors? What decisions did the owners (equity investors) make regarding net income generated each year? 1 2 3 4 5 Return on Equity 8.18% 7.07% 8.01% 8.31% 10.05% Balance Sheet ($000) Year Year 1 2 3 4 5 1 2 3 4 5 Current Assets Current Liabilities Cash and Equivants 4,520 9,500 5,100 5,900 6,600 Accounts Payable 2,910 3,010 3,960 5,250 6,420 Accounts Receivable 3,700 4,200 5,300 6,120 7,100 Taxes Payable 4,000 4,100 4,300 4,900 5,100 Inventory 2,200 2,330 3,100 4,400 5,125 Notes Payable 2,000 2,000 2,000 2,000 2,000 Total Current Assets 10,420 16,030 13,500 16,420 18,825 Total Current Liabilities 8,910 9,110 10,260 12,150 13,520 Long Term Assets Long Term Liabilities Property Plant & Equip. 120,450 Less: Depreciation Net Prop./ Pl. & Equip Reserves 135,000 141,500 27,000 28,000 29,000 93,450 107,000 112,500 146,000 151,000 Long Term Debt and Fin. Leases 25,000 40,000 38,000 36,000 34,000 31,000 33,000 Deferred Pension Liab. 2,760 2,800 3,100 3,500 3,700 115,000 118,000 Other Long Term Liab. 2,100 1,950 2,200 2,600 2,910 2,352 4,466 7,042 9,920 13,709 Total Long Term Liabilities 29,860 44,750 43,300 42,100 40,610 Intang. Assets & Good Will 4,451 Total Long Term Assets 100,253 3,900 115,366 3,740 123,282 2,900 127,820 2,000 Total Assets 110,673 131,396 136,782 133,709 144,240 152,534 Total Liabilities Shareholder's Equity Share Capital Retained Earnings 38,770 53,860 53,560 54,250 54,130 25,000 30,000 30,000 30,000 30,000 Income Statement ($ 000) Total Equity Year 1 Total Liabilities and Equity 46,903 47,536 53,222 59,990 71,903 77,536 83,222 89,990 110,673 131,396 136,782 144,240 68,404 98,404 152,534 2 3 4 5 Sales 40,000 40,000 44,000 49,000 55,000 Less: Cost of Goods Sold 19,000 19,000 21,500 23,000 24,300 Linked Excel Instructions Admin. Costs 8,700 8,700 9,100 10,000 11,550 Deprec. & Amortiz. 1,900 1,551 1,160 2,840 Operating Profit 10,400 10,749 12,240 13,160 2,900 16,250 Less: Interest Expense 2,000 3,200 3,040 2,880 Taxes (30%) 2,520 2,265 2,760 3,084 2,720 4,059 Net Income 5,880 5,284 6,440 7,196 9,471 Cash Dividend 3,528 3,171 3,864 3,318 4,183 Stock Repurchase ($25/share) 1,000 1,500 Retained Earnings 2,352 2,114 2,576 2,878 3,788 Total Income 5,880 5,284 6,440 7,196 9,471 Shares Outstanding Initial Shares: $25,000/$25 1,000 1,000 1,000 1,000 1,000 Add'n from Invest: $5,000 / $25 200 200 200 200 Share Buyback Total Yr 4: $1,000/$25 (40) (40) Yr 5: $1,500 / $25) (60) 1,000 1,200 1,200 1,160 1,100 Freeze Pane First off, in the spreadsheet you will be using (Spreadsheet 3 - Financials) I have used the Freeze Pane feature (8th icon in the Customized Ribbon) to allow for the title in line 1 to be frozen as you move down the rows of the spreadsheet. It may be useful to extend this frozen area as a place to display any of the financial ratio information you would like to use. On this demonstration spreadsheet, I have added several extra lines in the frozen area for this purpose. First, I accessed the far left cell directly under frozen line (cell A2) and hit the Freeze Pane icon to unfreeze the current setting. Still in Row 2, I inserted several additional lines to the spreadsheet using the Row Insert, the 14th icon. Now in Cell A4, I refroze the rows to give me space to insert my financial ratios. Set Up for Information Transfer In Cell E2 to 12, I have inserted the years for the information being transferred and in Cell D3 have printed the ratio to be calculated. In Cell E3, I initially insert = then go to E32 (Year 1 Net Income) then insert/then go to E24(Year 1 Sales) and press Enter. The ROE is therefore 0.147. To express this as a percent, press the % key in the top ribbon and increase the number of decimals showing to 2. There is no need to repeat this for years 2 to 4. Simply place your cursor on the dot in the bottom right corner of Cell E3 and holding down the mouse draw the cell across to Cell 15. This will fill in the data for the complete five years. Checking Your Calculations Finally, you should check all of your links to make sure the calculations are using the correct inputs. For example, to make sure that the ROE for years 1 is calculated correctly, go to Cell E3 and hit the last icon on the custom ribbon (Trace Precedence). This will show you the inputs used in the E3 cell. To remove Trace Precedence, simply hit the second icon in from the far right. Balance Sheet ($000) Year Year 1 2 3 4 5 1 2 3 4 5 Current Assets Current Liabilities Cash and Equivants 4,520 9,500 4,850 5,100 5,500 Accounts Receivable 3,700 4,150 4,355 4,661 4,925 Inventory 2,200 2,330 3,100 4,400 5,125 Accounts Payable Taxes Payable Notes Payable 2,910 3,010 3,960 5,250 6,420 4,000 4,100 4,300 4,900 5,100 2,000 2,000 2,000 2,000 2,000 Total Current Assets 10,420 15,980 12,305 14,161 15,550 Total Current Liabilities 8,910 9,110 10,260 12,150 13,520 Long Term Assets Long Term Liabilities Property Plant & Equip. 120,450 130,000 136,500 139,500 Less: Depreciation 27,000 28,000 Net Prop./ Pl. & Equip 93,450 102,000 29,000 107,500 31,000 108,500 142,600 33,000 109,600 Long Term Debt and Fin. Leases 25,000 40,000 38,000 36,000 34,000 Deferred Pension Liab. 2,800 Other Long Term Liab. 1,950 Reserves 2,886 5,044 5,967 Reserves/Investments 3,675 9,100 8,740 Total Long Term Assets 100,011 116,144 122,207 7,442 7,900 123,842 9,305 7,000 125,905 Total Assets 110,431 132,124 134,512 138,003 141,455 Total Long Term Liabilities Total Liabilities Shareholder's Equity Share Capital Retained Earnings 2,760 2,100 29,860 44,750 43,300 42,100 3,100 3,500 3,700 2,200 2,600 2,910 40,610 38,770 53,860 53,560 54,250 54,130 25,000 30,000 30,000 30,000 30,000 Income Statement ($ 000) Total Equity Year 1 Total Liabilities and Equity 46,661 48,264 71,661 78,264 80,952 110,431 132,124 134,512 138,003 50,952 53,753 57,325 83,753 87,325 141,455 2 3 4 5 Sales 40,000 42,000 44,000 49,000 57,000 Less: Cost of Goods Sold 19,000 19,950 20,900 23,275 27,075 Admin. Costs 8,700 8,700 9,100 10,000 11,550 Deprec. & Amortiz. 4,125 4,425 1,360 2,840 2,900 Operating Profit 8,175 8,925 12,640 12,885 15,475 Less: Interest Expense 2,000 3,200 3,040 2,880 2,720 Taxes (30%) 1,853 1,718 2,880 3,002 3,827 Net Income 4,323 4,008 6,720 7,004 8,929 Cash Dividend Stock Repurchase Retained Earnings 2,594 2,405 4,032 3,202 3,857 1,000 1,500 1,729 1,603 2,688 2,801 3,571 4,323 4,008 6,720 7,004 8,929 Total Income Shares Outstanding Initial Shares: $25,000/$25 1,000 1,000 1,000 1,000 1,000 Add'n from Invest: $5,000/$25 200 200 200 200 Share Buyback- Total Yr 4: $1,000/$29 (34) (34) Yr 5: $1,500/$30) (50) 1,000 1,200 1,200 1,166 1,116