Question

Spreadsheet Problems 2-18 Start with the partial model in the file Ch 02 Build a Model.xlsx at the textbook's website. Build a Model: Cumberland Industries' most recent balance sheets (in thousands of dollars) are shown Financial Statements, below and in the partial model in the file: EVA, and MVA 2015 2014 Cash Short-term investments Accounts receivable Inventories Total current assets Net fixed assets Total assets Accounts payable Accruals Notes payable Total current liabilities Long-term debt Total liabilities Common steck $ 91A50 11.400 103,365 38,444 244,659 67,165 $311 824 $ 30,761 30,477 16,717 77.955 76,264 154,219 $ 74,625 15,100 85,527 34,982 210,234 42,436 $252,670 $ 23,109 22,656 14,217 59,982 63,914 123,896 1000 20.000

Answer 1

Please give positive rating your feedback is valuable to me.

In case , any problem please leave a comment.

Note:- We are supposed to answer first 4 sub part of question under Chegg policy, Thanku!!!

Answer-

a. The company's 2015 sales were $455,150,000, and EBITDA was 15 percent of sales. Furthermore, depreciation amounted to 11 percent of net fixed assets, interest charges were $8,575,000, the state-plus-federal corporate tax rate was 40 percent, and Cumberland pays 40 percent of its net income out in dividends. Given this information, construct Cumberland's 2015 income statement. The input information required for the problem is outlined in the "Key Input Data" section below. Using this data and the balance sheet above, we constructed the income statement shown below. Key Input Data for Cumberland Industries Sales Revenue EBITDA as a percent of sales Depr. as a % of Fixed Assets Tax rate Interest Expense Dividend Payout Ratio $4,55,150 15% 11% 40% $8,575 40% Sales Expenses excluding depreciation and amortization EBITDA Depreciation (Cumberland has no amortization charges) EBIT Interest Expense EBT Taxes (40%) Net Income 2015 $4,55,150 $3,86,878 $68,273 $7,388 $60,884 $8,575 $52,309 $20,924 $31.386 2014 $3,64,120 $3,21,109 $43,011 $6,752 $36,259 $7,829 $28,430 $11,372 $17.058 Common dividends Addition to retained earnings $12,554 $18,831 $6,823 $10,235

b. Next, construct the firm's statement of retained earnings for the year ending December 31, 2015, and then its 2015 statement of cash flows. Statement of Retained Earnings (in thousands of dollars) Balance of Retained Earnings, December 31, 2014 Add: Net Income, 2015 Less: Common dividends paid, 2015 Balance of Retained Earnings, December 31, 2015 $38,774 $31,386 ($12.554) $57.605 Statement of Cash Flows (in thousands of dollars) $31,386 $7,388 Operating Activities Net Income Adjustments: Noncash adjustment: Depreciation Due to changes in working capital: Increase in accounts receivable Increase in inventories Increase in accounts payable Increase in accruals Net cash provided by operating activities -$17,838 -$3,462 $7,652 $7,821 $32,947 Investing Activities Cash used to acquire gross fixed assets Decrease in short-term investments Net cash provided by investing activities -$32,117 $3,700 -$28,417 = Financing Activities Increase in notes payable Increase in long-term debt Increase in common stock Payment of common dividends Net cash provided by financing activities Net increase/decrease in cash Add: Cash balance at the beginning of the year Cash balance at the end of the year $2,500 $12,350 $10,000 -$12.554 $12.295 $16,825 $74,625 $91.450

c. Calculate net operating working capital, total net operating capital, net operating profit after taxes, operating cash flow, and free cash flow for 2015. Net Operating Working Capital NOWC07= Operating current assets $2,33,259 $1,72,021 Operating current liabilities $61,238 = Operating NOWC06= current assets Operating current liabilities $45,765 $1,95,134 $1,49,369 Fixed assets Total Net Operating Capital TOC07 = NOWC $1,72,021 $2,39,186 $67,165 TOC06 = Fixed assets NOWC $1,49,369 $1,91,805 $42,436 X Net Operating Profit After Taxes NOPAT07= EBIT $60,884 $36,531 (1-1) 60% X Operating Cash Flow OCF07= NOPAT $36,531 $43,919 Depreciation $7,388 Free Cash Flow FCF07= OCF Gross investment in operating capital $54,769 $43,919 -$10,850 01 FCF07= NOPAT $36,531 -$10,850 Net investment in operating capital $47,381

d. Calculate the firm's EVA and MVA for 2015. Assume that Cumberland had 10 million shares outstanding, that the year-end closing stock price was $17.25 per share, and its after-tax cost of capital was 12 percent. Additional Input Data Stock price # of shares (in thousands) A-T cost of capital $17.25 10,000 12% х Market Value Added MVA = Stock price $17.25 $14,895 # of shares 10,000 Total common equity $1,57,605 X Economic Value Added EVA = NOPAT $36,531 $7,828 Operating Capital $2,39,186 After-tax cost of capital 12%