Principles of finance i spreadsheet problem 2-14 grantham university

Spreadsheet Problem

(2-14) Begin with the partial model in the file Ch02 P14 Build a Model.xls on the textbook’s            Web site.

a.       Cumberland Industries’s 2010 sales were $455,000,000; operating costs (excluding 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 Cumberland’s 2010 income statement.  Also calculate total dividends and the addition to retained earnings.  (Hint: Start with the partial model in the file and report all dollar figures in thousands to reduce clutter.)

b.      Cumberland Industries’s partial balance sheets are shown below.  Cumberland issued $10,000,000 of new common stock in 2010.  Using this information and the results from a part a, fill in the missing values for common stock, retained earnings, total common equity, and total liabilities and equality.

Cumberland Industries: Balance Sheets as of December 31 (Thousands of Dollars)

                                                                                    2010                                        2009

Assets

Cash                                                                            $91,450                                   $74,625

Short-term investments                                               11,400                                    15,100

Accounts receivable                                                    108,470                                   85,527

Inventories                                                                    38,450                                   34,982

            Total current assets                                       $249,770                                $210,234

Net fixed assets                                                            67,000                                   42,436

Total assets                                                                $316,770                                $252,670

2010                                        2009

Liabilities and Equity             

Accounts payable                                                        $30,761                                   $23,109

Accruals                                                                      30,405                                    22,656

Notes payable                                                             12,717                                    14,217

            Total current liabilities                                    $73,883                                   $59,982

Long-term debt                                                           80,263                                    63,914

            Total liabilities                                  $154,146                                 $123,896

Common stock                                                                      ?                                               $90,000

Retained earnings                                                       _____?                                                38,774

            Total common equity                                                ?                                             $128,774

Total liabilities and equity                                           _____?                                             $252,670

c.        Construct the statement of cash flows for 2010.