Setp 1 Step 2 Step3 Step 4 Step 5 Step 6 Step 7 Step 8 Step 9 For Time 0, compute profit before tax, profit after tax, retained earnings on the Income Statement For Time 0, compute net fixed assets, total assets, total liabilities and equity on the Balance Sheet For Time 0, check does the Balance Sheet balance? Using the "Assumptions" provided by the statements, project the Income statement (hold off on interest and interest income) Using the assumptions provided above, project the Balance Sheet (hold off on cash) Project interest and interest income Create the Statement of Cash Flows (Time 1-5, NOT Time 0) using information from the Balance Sheet and Income Statement Link cash on the Balance Sheet to the Statement of Cash Flows (beginning Cash+Net increase in cash) Check: does the Balance Sheet balance? Year 2 3 4 Sales Income statement 5 Costs of goods sold 6 Interest payments on debt 7 Interest earned on cash and marketable securitie 8 Depreciation 9 Profit before tax 10 Taxes 11 Net income 12 Dividends 13 Retained earnings 14 15 Balance sheet 0 1 2 3 4 5 ASSUMPTIONS 10% Sales growth $ 1,000 500 32 6 100 150 90 16 Cash and marketable securities $ 80 17 Other current assets 150 18 Fixed assets 19 At cost 20 21 Depreciation (accumulated) Net fixed assets 22 Total assets 23 24 Current liabilities 25 Debt 1,070 300 26 Stock $ 80 320 450 150 27 Accumulated retained earnings 28 Total liabilities and equity 29 30 Check: Assets Liabilities? 31 32 Statement of cash flows 33 Cash flow from operating activities 34 Net income 35 Add back depreciation 36 37 38 Changes in working capital Other current assets Current liabilities 39 Cash flows from operations 40 41 Cash flow from investing activities 42 Purchases of fixed assets--capital expenditures 43 Net cash used in investing activities 44 45 Cash flow from financing activities 46 Change in debt 47 Change in stock 48 Dividends paid 49 Net cash from financing activities 50 51 Net increase in cash and cash equivalents 50% Costs of goods sold/sales (margin) 10% Depreciation/fixed assets (at cost) 6% Interest rate on average debt 2% Interest paid on average cash and marketable securities 40% Tax rate 40% Dividend payout ratio (dividends/Net Income) 15% Other current assets/sales 77% Net fixed assets/sales 8% Current liabilities/sales Hold at current value Debt Hold at current value Stock Use to close model Cash Note: (1) "Average" above means the average of the current and prior year only. (2) You may encounter an error message regarding "Circular Reference." Do research on what this is and how to "Enable iterative calculation" (3) You may not want to leave your computer in this setting, because it can slow the speed of Excel