Please show the excel formulas and funcations to solve this problem

J. Clark Inc. (JCI), a manufacturer and distributer of sports equipment, has grown until it has become a stable, matu its first distribution to shareholders. Shown below are the most recent year's financial statements and projections f fiscal year ending on June 30). JCI plans to liquidate $500 million of its short-term securities and distribute them on 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 $500 40% 11.0% 1,000 6.0% Income Statement (Millions of Dollars) Net Sales Costs (except depreciation) Depreciation Earning before int. & tax Interest expense Earnings before taxes Taxes Net income Actual 6/30/2016 $20,000.00 $16,000.00 $1,300.00 $2,700.00 $150.00 $2,550.00 $1,020.00 $1,530.00 Projected 6/30/2017 $21,200.00 $16,960.00 $1,378.00 $2,862.00 $152.82 $2,709.18 $1,083.67 $1,625.51 a. Assume first that JCI distributes the $500 million as dividends. Fill in the missing values in the balance sheet col that is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets balance after you fill in the missing i 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 ba for July 1, 2017, that is labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you items.) Distribute as Repurchase 7/2/2017 $169.60 Projected: Prior Distribute as Actual to Distribution Dividend 6/30/2016 6/30/2017 7/1/2017 $160.00 $169.60 $169.60 $200.00 $640.00 $2,000.00 $2,120.00 $2,120.00 $3,000.00 $3.180.00 $3,180.00 $5,360.00 $6,109.60 $5,469.60 $13,000.00 $13,780.00 $13,780.00 $18,360.00 $19,889.60 $19,249.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 Balance Sheets (Millions Assets Cash Short-term investments Accounts receivable Inventories Total current assets Net plant and equipment Total assets Liabilities & Equity Accounts payable Accruals Short-term debt Total current liabilities Long-term debt Total liabilities Common stock Treasury stock Retained earnings Total common equity Total liabilities & equity $1,000.00 $2,000.00 $400.00 $3,400.00 $2,068.18 $5,468.18 $5,851.82 ($400.00) $7.440.00 $12,891.82 $18,360.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 $1,060.00 $2,120.00 $0.00 $3,180.00 $2,192.27 $5,372.27 $5,851.82 $1,060.00 $2,120.00 $0.00 $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! Check for balance: c. Caculate JCI's projected free cash flow; the tax rate is 40%. Projected 6/30/2017 Calculation of Free Cash Flow Operating current assets Operating current liabiliti Net operating working ca Net plant & equipment Total net operating capiti Net operating profit after Inv. in operating capital Free cash flow (FCF) 6/30/2016 $5,160.00 3,000.00 $2,160.00 13,000.00 $15,160.00 $1,620.00 c. Caculate JCI's horizon value for 6/30/2017. 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/2016 and 6/30/2017. (Hint: JCI's value of operatic on 6/30/2017 is equal to the horizon value.) 6/30/2016 6/30/2017 Valuation Horizon value Value of operations d. What is JCI's current intrinsic stock price (the price on 6/30/2016)? What is the projected intrinsic stock price for 6/30/2017? See below for calculations. e. What is the projected intrinsic stock price on 7/1/2017 if JCI distributes the cash as dividends? See below for calculations. f. What is the projected intrinsic stock price on 7/1/2017 if JCI distributes the cash athrough stock repurchases? How many shares will remain outstanding after the repurchase? See below for calculations. Distribute as Dividend 7/1/2017 Distribute as Repurchase 7/1/2017 6/30/2016 6/30/2017