PLEASE SHOW ALL WORK ON XL( how you plug in evreything).

2/1/2012 2 3 4 Module 7. Student Ch 7-14 Build a Model 5 8 6 a. Cumberland Industries' most recent sales were $455,000,000; operating costs (excluding 7 depreciation) were equal to 85% of sales; net fixed assets were $67,000,000; depreciation amounted to 10% of net fixed assets; interest expenses were $8,550,000; the state-plus-federal corporate tax rate was 40% and Cumberland paid 25% of its net income out in dividends. Given this information, construct 9 Cumberland's income statement. Also calculate total dividends and the addition to retained earnings. 10 11 The input information required for the problem is outlined in the "Key Input Data" section below. Using this data 12 and the balance sheet above, we constructed the income statement shown below. 13 14 Key Input Data for Cumberland Industries 2012 15 (Thousands of dollars) 16 Sales Revenue $455,000 17 Expenses (excluding depreciation) as a percent of sales 85.0% 18 Net fixed assest $67,000 19 Depr, as a % of net fixed assets 10.0% 20 Tax rate 40.0% 21 Interest expense $8,550 22 Dividend Payout Ratio 25% 23 24 Cumberland Industries: Income Statement (Thousands of dol 2012 25 Sales 26 Operating costs excluding depreciation 27 EBITDA 28 Depreciation (Cumberland has no amortization charges) 29 EBIT 30 Interest expense 31 EBT 32 Taxes (40%) 33 Net income Build a Model Solution Sheet2 Sheet3 + 22 Dividend Payout Ratio 25% 23 24 Cumberland Industries: Income Statement (Thousands of dol 2012 25 Sales 26 Operating costs excluding depreciation 27 EBITDA 28 Depreciation (Cumberland has no amortization charges) 29 EBIT 30 Interest expense 31 EBT 32 Taxes (40%) 33 Net income 34 35 Common dividends 36 Addition to retained earnings 37 38 b. Cumberland Industries' partial balance sheets are shown below. Cumberland issued $10,000,000 of 39 new common stock in the most recent year. Using this information and the results from part a, fill in the 40 missing values for common stock, retained earnings, total common equity, and total liabilities and 41 42 Dollar value of common stock issued (in thousands of dollars) $10,000 43 44 Cumberland Industries December 31 Balance Sheets 45 (in thousands of dollars) 46 2012 2011 47 Assets 48 Cash and cash equivalents $91.450 $74.625 49 Short-term investments 11.400 15.100 50 Accounts Receivable 108,470 85,527 51 Inventories 38 450 34.982 52 Total current assets S249.770 $210.234 53 Net fixed assets 67 000 42436 54 Total assets $316,770 $252.670 55 56 Liabilities and equity Ruldad Shoc Paste BIUDA Merge & Center $ % 98% Conditional For Formatting Tal Style Cipboard Font Alignment Number 3 fo Module 7. Student Ch 7-14 Build a Model E G H $30,761 30,405 12.717 $73,883 80.263 $154,146 $23,109 22,656 14,217 $59,982 63.914 $123,896 $90,000 38.774 $128.774 $252,670 B D 55 56 Liabilities and equity 57 Accounts payable 58 Accruals 59 Notes payable 60 Total current liabilities 61 Long-term debt 62 Total liabilities 63 Common stock 64 Retained earnings 65 Total common equity 66 Total liabilities and equity 67 68 Check for balancing this should be zero), 69 70 c. Construct the statement of cash flows for the most recent year. 71 72 Statement of Cash Flows 73 (in thousands of dollars) 74 75 Operating Activities 76 Net Income 77 Adjustments 78 Noncash adjustment 79 Depreciation 80 Due to changes in working capital 81 Luu to change in accounts receivable 82 Due to change in inventories 83 Due to change in accounts payable 84 Due to change in accruals 85 Net cash provided (used) by operating activities 86 87 Investing Activities 88 Cash used to acquire gross fixed assets 89 Due to change in short-term investments Build a Model Solution Sheet2 Sheet3 70 C. Construct the statement of cash flows for the most recent year. 71 72 Statement of Cash Flows 73 (in thousands of dollars) 74 75 Operating Activities 76 Net Income 77 Adjustments 78 Noncash adjustment: 79 Depreciation 80 Due to changes in working capital: 81 Due to change in accounts receivable 82 Due to change in inventories 83 Due to change in accounts payable 84 Due to. hange in accruals 85 Net cash provided (used) by operating activities 86 87 Investing Activities 88 Cash used to acquire gross fixed assets 89 Due to change in short-term investments 90 Net cash provided (used) by investing activities 91 92 Financing Activities 93 Due to change in notes payable 94 Due to change in long-term debt 95 Due to change in common stock 96 Payment of common dividends 97 Net cash provided (used) by financing activities 98 99 Net increase/decrease in cash 100 Add: Cash balance at the beginning of the year 101 Cash balance at the end of the year 102 103 Check: cash balance in statement of cash flows should equal the 104 cash on balance sheets as shown here. Build a Model Solution Sheet2 Sheet3 S91.450 AutoSave module 7a (2) Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help X Aria - A A General BTU Wrap Text 3. Merge & Center Alignment $ % 9 38 - Conditional Format Formatting Table Styles pboard Font Number fx "Module 7. Student Ch 7-14 Build a Model H D 85 Net cash provided (used) by operating activities 86 87 Investing Activities 88 Cash used to acquire gross fixed assets 89 Due to change in short-term investments 90 Net cash provided (used) by investing activities 91 92 Financing Activities 93 Due to change in notes payable 94 Due to change in long-term debt 95 Due to change in common stock 96 Payment of common dividends 97 Net cash provided (used) by financing activities 98 99 Net increme/decrease in cash 100 Add Cash balance at the beginning of the year 101 Cash balance at the end of the year 102 103 Check: cash balance in statement of cash flows should equal the 104 cash on balance sheets as shown here: 105 106 107 108 $91,450 111 112 113 114 115 116 118 119 Build a Model Solution Sheet2 Sheet