the excel link
Start with the partial model in the file. Ch15 P13 Build a Model.xisx. .. Clark inc. (WCI), a manufacturer and distributor of sports equlpment, has grown until it has become a stable, mature company, Now JCl is planning its first di stribution to shareholders. (See the file for the most recent year's Ainancial statements and projections for the next year, 2022; )CI's fiscal year ends on June 30.) JCl plans to liquidate and distribute $700 million of its short-term securities on luly 1 , 2022, the fist day of the next fiscal year, but it has not yet decided whether to distrbute with dividends or with stock repurchases. The data has been collected in the Microsof Excel fle beiow. Download the spreadsheet and perform the required analysis to answer the questicos below. Do not round intermediate calculations. Use a minus sign to enter a negative value, if any. X Download spreadshect Ch15 P13 Euild a Madel-a33739.xisx a. Assume first that 3Cl distributes the $700 milion as dividends. Fill in the missing values in the fie's baiance sheet column for July 1 , 2022, which is labeled "Distribute as Dividends," (Hint: Be sure that the bolance sheets balance after you fill in the missing items.) Assume that XCI did not have to establish an account for dividends peyable prior to the distribution. Enter your answers in millions. For example, an answer of $1,23 milion should be entered as 1.23 , not 1,230,000. Round your answers to two decimal places. b. Now aswume that JCt distrbutes the $700 milion through stock repurchases. Fill in the missing values in the file's balance sheet column for July 1, 2022, which is labeled "Distribute as kepurchase." (Hint: Be sure that the balance sheets bolance after you fil in the miswing agems.) Enter your answers in milions. For examble, an answer of \$1.23 milion should be entered as 1.23, not 1,230,000. Mound your anwwers to two decimal places. c. Calculate JCl's projected free cash fow; 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. $ mition d. What is JCl'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 JCI's WACC i5 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 JCl distributes the cash as dividends? Round your answer to the nearest cent. 5 f. What is the projected intrinsic stock price on 7/1/2022 if 3Cl distributes the cash through stock repurchases? Reund your answer to the nearest cent. 5 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. Distributiont as Dividends or Repurehases Aliount of datribution 5700tillon a. Filling in the mising value in the balanee shet celumn for July 1,2022 , which is labeled "Distribute as Dividesds," If JCl distibutes its short term securitets as dividends; JCl did not have to establish an account for dividende payable prlor to the distribution b. Fulling in the misuing values in the balance sheat column for July 1,2022 , whieh is lebeled "Distribute as Repurehase," If JCl dithibutes its short term wecurities through abock repurchases 10 11. Projectng JCre Finanelai Statements 12 (Mitilions of Dollars) 14. Income Stalements 14. Net sines 16. Costs (erongt depeciation) 17 Depreciation is (17) EAIT is intomst expente 20 EBT 21 Tanes 22. Net inowe (2) te mumber of sharos of maining oute tanding atier the ispurchase Start with the partial model in the file. Ch15 P13 Build a Model.xisx. .. Clark inc. (WCI), a manufacturer and distributor of sports equlpment, has grown until it has become a stable, mature company, Now JCl is planning its first di stribution to shareholders. (See the file for the most recent year's Ainancial statements and projections for the next year, 2022; )CI's fiscal year ends on June 30.) JCl plans to liquidate and distribute $700 million of its short-term securities on luly 1 , 2022, the fist day of the next fiscal year, but it has not yet decided whether to distrbute with dividends or with stock repurchases. The data has been collected in the Microsof Excel fle beiow. Download the spreadsheet and perform the required analysis to answer the questicos below. Do not round intermediate calculations. Use a minus sign to enter a negative value, if any. X Download spreadshect Ch15 P13 Euild a Madel-a33739.xisx a. Assume first that 3Cl distributes the $700 milion as dividends. Fill in the missing values in the fie's baiance sheet column for July 1 , 2022, which is labeled "Distribute as Dividends," (Hint: Be sure that the bolance sheets balance after you fill in the missing items.) Assume that XCI did not have to establish an account for dividends peyable prior to the distribution. Enter your answers in millions. For example, an answer of $1,23 milion should be entered as 1.23 , not 1,230,000. Round your answers to two decimal places. b. Now aswume that JCt distrbutes the $700 milion through stock repurchases. Fill in the missing values in the file's balance sheet column for July 1, 2022, which is labeled "Distribute as kepurchase." (Hint: Be sure that the balance sheets bolance after you fil in the miswing agems.) Enter your answers in milions. For examble, an answer of \$1.23 milion should be entered as 1.23, not 1,230,000. Mound your anwwers to two decimal places. c. Calculate JCl's projected free cash fow; 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. $ mition d. What is JCl'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 JCI's WACC i5 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 JCl distributes the cash as dividends? Round your answer to the nearest cent. 5 f. What is the projected intrinsic stock price on 7/1/2022 if 3Cl distributes the cash through stock repurchases? Reund your answer to the nearest cent. 5 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. Distributiont as Dividends or Repurehases Aliount of datribution 5700tillon a. Filling in the mising value in the balanee shet celumn for July 1,2022 , which is labeled "Distribute as Dividesds," If JCl distibutes its short term securitets as dividends; JCl did not have to establish an account for dividende payable prlor to the distribution b. Fulling in the misuing values in the balance sheat column for July 1,2022 , whieh is lebeled "Distribute as Repurehase," If JCl dithibutes its short term wecurities through abock repurchases 10 11. Projectng JCre Finanelai Statements 12 (Mitilions of Dollars) 14. Income Stalements 14. Net sines 16. Costs (erongt depeciation) 17 Depreciation is (17) EAIT is intomst expente 20 EBT 21 Tanes 22. Net inowe (2) te mumber of sharos of maining oute tanding atier the ispurchase
