AutoSave O OFF ~ A . G ... 32ndFSA_Assignment_Q - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Share Comments Arial v 10 AA ab Wrap Text v Number V Z Paste BIUV DVAv E Merge & Center v $ ~ % " 00 20 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data D16 X V fx =D14*22% A B C D E F G H I J K L M N O P Q R S T U Do Netincome 69 + Depreciation 70 + (increase) decrease in A.R. 71 + (increase) decrease in inventory 72 + (increase) decrease in prepaid exp. 73 + increase (decrease) in A.P 74 + increase (decrease) in accrued taxes 75 + increase (decrease) in deferred taxes 76 77 =Cash Flow from operations 78 79 + (increase) decrease in marketable sec 80 + (increase) decrease in PPE sale price of 81 82 =Cash Flow from investing 83 84 + increase (decrease) in loans and notes. 85 + increase (decrease) in LTD 86 + increase (decrease) in common stock 87 - dividends 88 - treasury stock 89 90 =Cash flow from financing 91 92 Beginning cash 93 +Change in cash 94 Ending cash 95 96 97 98 99 100 101 102 103 104 105 106 107 108 Sheet1 + Ready Accessibility: Good to go + 120%AutoSave O OFF ~ A . G ... 32ndFSA_Assignment_Q - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Share Comments Arial v 10 AA ab Wrap Text Number ZY AY - O. Paste BIUVV DV Av Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data D16 4 X V fx =D14*22% B C D E F G H 1 J K L M N O P Q R S T U Pro-forma financial statements Create pro forma financial statements from the information provided below W N Income Statement Year 1 4 Year 0 Year 1 Year 2 Sales revenues increase 3.5% Revenues 17,000 17,595 17,243 Gross margin is 50% 6 Cost of goods sold 9,200 (8,798) (8,966) SG&A increa 7 Gross profit 7,800 8,798 8,277 $2000 of PP&E is purchased on January 1, 8 SG&A 4,790 4,847 4,799 New PP&E is depreciated over 10 years 9 Depreciation 1,700 (1,900) (1,820) Inventory grows in line with COGS 10 Operating Profit 1,310 2,050 1,658 Assume that all other asset accounts grow in line with sales (3.5%). 11 Interest expense 155 (152) (168) Accounts Payable grow in line with COGS 12 Income before taxes 1,155 1,898 1,490 Accrued and deferred income taxes grows in line with taxes. 13 Taxes @35% 404 (664) (521) Long-term debt declines by $200 14 Net Income 751 1,234 968 Unless otherwise stated, liability accounts grow in line with sales (3.5%) 15 Treasury Stock purchases equal $30 16 Dividends 225 271 242 Average interest cost of all interest bearing debt is 1.6% 17 Dividend payout ratio is 22% 18 Addition to retained earnings 526 962 726 Tax rate is 35% 19 Funding requirements should be financed with short-term debt 20 Balance Sheet 21 22 Assets 23 Year 0 Year 1 Year 2 Y2 24 Cash and cash equivalents 540 Sales revenue decline by 2.0% 25 Marketable securities 28 Gross margin declinesto 48% 26 Accounts Receivables 8,200 Inventory grows in line with COGS 27 Inventory 3,142 SG&A declines by 1% 28 Prepaid expen. & other assets 1,323 $800of PP&E is sold on January 1 for $600 cash. (Gross =$800, Accumulated depreciation = $200) 29 Total Current Assets 13,333 Annual depreciation expense declines by $ 80 30 Assume that all other asset accounts grow in line with sales. (-2.0%) 31 Plant property and equipment (gross) 7,607 Accounts Payable grow in line with COGS 32 Accumulated Depreciation 3,000 Long-term debt declines by $150 33 PP&E (net) 4,607 Accrued and deferred income taxes grows in line with taxes. 34 Unless otherwise stated, liability accounts grow in line with sales (-2.0%) 35 Total Assets 17,940 Treasury Stock purchase is $100. 36 Average interest cost of all interest bearing debt is 1.8% 37 Liabilities & Shareholders' Equity Dividend payout ratio changes to 25% 38 Year 0 Year 1 Year 2 Tax rate is 35% 39 Accounts payable 3,14 Funding requirements should be financed with short-term debt 40 Loans & notes payable (plug) 2,923 Excess cash is used to retire short-term debt 41 Accrued income taxes 100 shares of $1 nar value common stock is issued for $300 Sheet1 Ready 1 Accessibility: Good to go + 120%ZOOM + G H K M N O 2 Create pro forma financial statements from the information provided below 3 Year 1 4 Sales revenues increase 3.5% 5 Gross margin is 50% 6 SG&A increases 1.2% 7 $2000 of PP&E is purchased on January 1, 8 New PP&E is depreciated over 10 years 9 Inventory grows in line with COGS 10 Assume that all other asset accounts grow in line with sales (3.5%). 11 Accounts Payable grow in line with COGS 12 Accrued and deferred income taxes grows in line with taxes. 13 Long-term debt declines by $200 14 Unless otherwise stated, liability accounts grow in line with sales (3.5%) 15 Treasury Stock purchases equal $30 16 Average interest cost of all interest bearing debt is 1.6% 17 Dividend payout ratio is 22% 18 Tax rate is 35% 19 Funding requirements should be financed with short-term debt 20 21 22 23 Y2 24 Sales revenue decline by 2.0% 25 Gross margin declinesto 48% 26 Inventory grows in line with COGS 27 SG&A declines by 1% 28 $800of PP&E is sold on January 1 for $600 cash. (Gross =$800, Accumulated depreciation = $200) 29 Annual depreciation expense declines by $ 80 30 Assume that all other asset accounts grow in line with sales. (-2.0%) 31 Accounts Payable grow in line with COG 32 Long-term debt declines by $150 33 Accrued and deferred income taxes grows in line with taxes. 34 Unless otherwise stated, liability accounts grow in line with sales (-2.0%) 35 Treasury Stock purchase is $100 36 Average interest cost of all interest bearing debt is 1.8% 37 Dividend payout ratio changes to 25% 38 Tax rate is 35% 39 Funding requirements should be financed with short-term debt 40 Excess cash is used to retire short-term debt 41 100 shares of $1 par value common stock is issued for $300. 42 Do not add significant amounts to cash unless Loans & notes payable is drawn down to zero. 43AutoSave O OFF ~ A . G ... 32ndFSA_Assignment_Q - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Share Comments Arial v 10 AA ab Wrap Text Number LY AY - O. V Z Paste BIUV MVAv E Merge & Center $ ~ % 9 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data D16 X V fx =D14*22% A B C D E F G H J K L M N O P Q R S T U 37 Liabilities & Shareholders' Equity Dividend payout ratio changes to 25% 38 Year 0 Year 1 Year 2 Tax rate is 35% 39 Accounts payable 3,148 Funding requirements should be financed with short-term debt 40 Loans & notes payable (plug) 2,923 Excess cash is used to retire short-term debt 41 Accrued income taxes 1,322 100 shares of $1 par value common stock is issued for $300. 42 Total Current Liabilities 7,393 Do not add significant amounts to cash unless Loans & notes payable is drawn down to zero. 43 44 Long-term debt 2,300 45 Defered income taxes 195 46 47 Shareholders' Equity 48 49 Common Stock at par 860 50 Capital Surplus 863 51 Retained earnings 6.42 52 Less treasury stock (100) 53 Total equity 8,052 54 55 Total liabilities & shareholder equity 17,940 56 57 58 59 60 Statement of Retained Earnings Year 1 Year 2 61 Beginning retained earnings 62 +Net Income 63 -dividends 64 Ending retained earnings 65 66 Statement of Cash Flows 67 Year 1 Year 2 68 Net Income 69 + Depreciation 70 + (increase) decrease in A.R 71 + (increase) decrease in inventory 72 + (increase) decrease in prepaid exp. 73 + increase (decrease) in A.P. 74 + increase (decrease) in accrued taxes 75 + increase (decrease) in deferred taxes 76 77 =Cash Flow from operations Sheet1 + Ready 12 Accessibility: Good to go + 120%