Hello my dear,

Here is another finance spreadsheet for you.

Solution Chapter: Problem: 12/8/2012 14 13 J. Clark Inc. (JCI), a manufacturer and distributer of sports equipment, has grown until it has become a stable, mature company. Now JCI is planning its first distribution to shareholders. Shown below are the most recent year's financial statements and projections for the next year, 2014 (JCI has a fiscal year ending on June 30). JCI plans to liquidate $500 million of its short-term securities and distribute them on July 1, 2014, the first day of the next fiscal year, but has not yet decided whether to distribute with dividends or with stock repurchases. Inputs Amount of distribution Tax rate WACC Number of shares FCF constant growth rate Income Statement (Millions of Dollars) Net Sales Costs (except depreciation) Depreciation Earning before int. & tax Interest expense Earnings before taxes Taxes Net income $500 40% 11.0% 1,000 6.0% Actual 6/30/2013 $20,000.00 $16,000.00 Projected 6/30/2014 $21,200.00 $16,960.00 $1,300.00 $2,700.00 $150.00 $2,550.00 $1,020.00 $1,378.00 $2,862.00 $152.82 $2,709.18 $1,083.67 $1,530.00 $1,625.51 a. Assume first that JCI distributes the $500 million as dividends. Fill in the missing values in the balance sheet column for July 1, 2014, that is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets balance after you fill in the missing items. Also, assume JCI did not have to establish an account for dividends payable prior to the distribution.) See below for calculations. b. Now assume that JCI distributes the $500 million through stock repurchases. Fill in the missing values in the balance sheet column for July 1, 2014, that is labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) Balance Sheets (Millions of Dollars) Assets Cash Short-term investments Accounts receivable Inventories l current assets Net plant and equipment Total assets Liabilities & Equity Accounts payable Accruals Short-term debt Actual 6/30/2013 $160.00 $200.00 $2,000.00 $3,000.00 $5,360.00 $13,000.00 $18,360.00 Projected: Prior to Distribution 6/30/2014 $169.60 $640.00 $2,120.00 $3,180.00 $6,109.60 $13,780.00 $19,889.60 $1,000.00 $2,000.00 $400.00 rrent liabilities Long-term debt Total liabilities Common stock Treasury stock Retained earnings common equity bilities & equity $3,400.00 $2,068.18 $5,468.18 $5,851.82 ($400.00) $7,440.00 $12,891.82 $18,360.00 Distribute as Dividend 7/1/2014 $169.60 Distribute as Repurchase 7/2/2014 $169.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 $1,060.00 $2,120.00 $0.00 $1,060.00 $2,120.00 $0.00 $1,060.00 $2,120.00 $0.00 $3,180.00 $2,192.27 $5,372.27 $5,851.82 ($400.00) $9,065.51 $14,517.33 $19,889.60 $3,180.00 $2,192.27 $5,372.27 $5,851.82 $3,180.00 $2,192.27 $5,372.27 $5,851.82 $5,851.82 $11,224.09 $5,851.82 $11,224.09 NOT BALANCED! NOT BALANCED! ck for balance: c. Caculate JCI's projected free cash flow; the tax rate is 40%. Projected Calculation of Free Cash Flow Operating current assets 6/30/2013 $5,160.00 Operating current liabilities Net operating working capital 3,000.00 $2,160.00 Net plant & equipment Total net operating capital Net operating profit after taxes 13,000.00 $15,160.00 $1,620.00 6/30/2014 Inv. in operating capital Free cash flow (FCF) c. Caculate JCI's horizon value for 6/30/2014. FCF is expected to grow at a constant rate of 6% and JCI's WACC is 11%. Calculate JCI's value of operations for 6/30/2013 and 6/30/2014. (Hint: JCI's value of operations on 6/30/2014 is equal to the horizon value.) Valuation Horizon value Value of operations 6/30/2013 6/30/2014 d. What is JCI's current intrinsic stock price (the price on 6/30/2013)? What is the projected intrinsic stock price for 6/30/2014? See below for calculations. e. What is the projected intrinsic stock price on 7/1/2014 if JCI distributes the cash as dividends? See below for calculations. f. What is the projected intrinsic stock price on 7/1/2014 if JCI distributes the cash athrough stock repurchases? How many shares will remain outstanding after the repurchase? See below for calculations. 6/30/2013 Value of operations + Value of nonoperating assets Total intrinsic value of firm Debt Intrinsic value of equity Number of shares Intrinsic price per share 6/30/2014 Distribute as Dividend 7/1/2014 Distribute as Repurchase 7/1/2014