Start with the partial model in the file Ch15 P13 Build a Model.xlsx. 3. Clark Inc. (DCI), a manufacturer and distributor of sports equipment, has grown until it has become a stable, mature company. Now JCI is planning its first distribution to shareholders. (See the file for the most recent year's financial statements and projections for the next year, 2022; JC's fiscal year ends on June 30.) JCI plans to liquidate and distribute $700 million of its short-term securities on July 1, 2022, the first day of the next fiscal year, but it has not yet decided whether to distribute with dividends or with stock repurchases. The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Use a minus sign to enter a negative value, if any. Download spreadsheet Ch15 P13 Build a Model-a33739.xlsx a. Assume first that JCI distributes the $700 million as dividends. Fill in the missing values in the file's balance sheet column for July 1, 2022, which is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) Assume that JCI did not have to establish an account for dividends payable prior to the distribution. Enter your answers in millions. For example, an answer of $1.23 million should be entered as 1.23, not 1,230,000. Round your answers to two decimal places. Short-term investments Treasury stock Retained earnings Distribute as Dividend $ $ 7/1/2022 million millio million b. Now assume that JCI distributes the $700 million through stock repurchases. Fill in the missing values in the file's balance sheet column for July 1, 2022, which is labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) Enter your answers in millions. For example, an answer of $1.23 million should be entered as 1.23, not 1,230,000. Round your answers to two decimal places. Distribute as Repurchase 7/1/2022 Short-term investments $ million Treasury stock $ million Retained earnings $ million c. Calculate JC's projected free cash flow; the tax rate is 25%. Enter your answer in millions. For example, an answer of $1.23 million should be entered as 1.23, not 1,230,000. Round your answer to two decimal places. million d. What is JCI's current intrinsic stock price (the price on 6/30/2021)? What is the projected intrinsic stock price for 6/30/20227 FCF is expected to grow at a constant rate of 5%, and CI's WACC is 9%. The firm has 1,000 million shares outstanding. Round your answers to the nearest cent Intrinsic stock price on 6/30/2021: $ Intrinsic stock price on 6/30/2022: $ e. What is the projected intrinsic stock price on 7/1/2022 if JCI distributes the cash as dividends? Round your answer to the nearest cent f. What is the projected intrinsic stock price on 7/1/2022 if JCI distributes the cash through stock repurchases? Round your answer to the nearest cent $ How many shares will remain outstanding after the repurchase? Enter your answer in millions. For example, an answer of 1.23 million should be entered as 1.23, not 1,230,000. Round your answer to two decimal places. million Distributions as Dividends or Repurchases Amount of distribution $700 million a. Filling in the missing values in the balance sheet column for July 1, 2022, which is labeled "Distribute as Dividends," if JCI distributes its short-term securities as dividends; JCI did not have to establish an account for dividends payable prior to the distribution b. Filling in the missing values in the balance sheet column for July 1, 2022, which is labeled "Distribute as Repurchase," If JCI distributes its short-term securities through stock repurchases Projecting JCI's Financial Statements (Millions of Dollars) Income Statements Net sales Costs (except depreciation) Depreciation EBIT Interest expense EBT Taxes Net income 1,200.00 $2,400.00 Actual 6/30/21 Projected 6/30/22 $18,000.00 $18,900.00 14,400.00 15,120.00 1,260.00 $2,520.00 150.00 163.60 $2,250.00 562.50 $1,687.50 $2,356.40 589.10 $1,767.30 Projected: Prior to Distribute as Actual Distribution Dividend 6/30/21 6/30/22 7/1/2022 Distribute as Repurchase 7/1/2022 Balance Sheets Assets Cash Short-term investments $180.00 300.00 Accounts receivable 1,800.00 $189.00 960.00 1,890.00 Inventories 3,250.00 3,412 50 Total current assets $5,530.00 $6,451.50 Net plant and equipment i 12,000.00 12,600.00 3 Total assets $17,530.00 $19,051.50 4 5 Liabilities & Equity #6 Accounts payable $900.00 $945.00 7 Accruals 2,300.00 2,415.00 58 Short-term debt 500.00 0.00 Total cument liabilities $3,700.00 $3,360.00 40 Long-term debt 1,884.00 1,978.20 41 Total abilities $5,584.00 $5,338.20 42 Common stock 43 Treasury stock 5,948.00 5,948.00 350.00 44 Retained eamings 6,348.00 45 Total common equity $11,946.00 -350.00 8,115.30 $13,713.30 46 Total labilities & equity $17,530.00 $19.051.50 47 48 c. Calculating JCT's projected free cash flow 49 Tax rate 25% 50 51 52 (Milions of Dollars) Actual 6/30/21 Projected 6/30/22 53 Operating cument assets 54 Operating cument labilities 55 Net operating working capital 56 Net plant and equipment 57 Total net operating capital 58 Net operating profit after taxes 59 Investment in operating capital 60 Free cash flow (FCF) 61 62 d. Calculating JCE's current intrinsic stock price (the price on 6000020 47 48 c. Calculating JCI's projected free cash flow 49 Tax rate 50 51 52 (Millions of Dollars) 53 Operating current assets 54 Operating current liabilities 55 Net operating working capital 56 Net plant and equipment 57 Total net operating capital 58 Net operating profit after taxes 59 Investment in operating capital 60 Free cash flow (FCF) 61 25% Actual 6/30/21 Projected 6/30/22 62 d. Calculating JCI's current intrinsic stock price (the price on 6/30/2021) and the projected intrinsic stock price for 6/30/2022 63. Calculating the projected intrinsic stock price on 7/1/2022 if JCI distributes the cash as dividends 64 f. Calculating the projected intrinsic stock price on 7/1/2022 if JCI distributes the cash through stock repurchases and 65 the number of shares remaining outstanding after the repurchase 66 WACC 67 Number of shares 68 FCF constant growth rate 69 70 71 (Millions of Dollars) 72 Horizon value 73 Value of operations 9.0% 1,000 million 5.0% Actual 6/30/21 Projected 6/30/22 74 75 Actual 76 (in millions, except intrinsic stock price) 6/30/21 Projected 6/30/22 Distribute as Dividend Distribute as Repurchase 7/1/2022 7/1/2022 77 Value of operations 78 Value of nonoperating assets 79 Total intrinsic value of firm 80-Debt 81 Intrinsic value of equity 82 Number of shares 83 Intrinsic price per share 84 85