Excel Activity: Distributions as Dividends or Repurchases Start with the partial model in the file Ch15 P13 Build a Model.xisx. ). Clark. Inc. (JCI), a manufacturer and distributor of sports equipment, has grown until it has become a stable, mature company. Now JCl is planning its first distribution to sharehoiders. (See the file for the most recent year's financial statements and projections for the next year, 2022; JCI's fiscal year ends on June 30.) JCI plans to liquidate and distribute $800 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. Downlead spreadsheet Ch15 P13 Bulld a Model-76e182.xisx a. Assume first that JCr distributes the $800 mittion 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 JCl 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. b. Now assume that JCI distributes the $800 million through stock repurchases. Fill in the missing values in the file's balance sheet Tieatury atse Felaried exminge: Totridomen equity Tariale Opevieg oumbet anvis Wel eperabing woning capes Neet gant and nowerivent Tobai net eperating castes Net eperentry proti aher bust inctivent nepecting captal wace. Wuserer want Howion vilie? 4 \begin{tabular}{l|l} \hline A & a \\ \hline \end{tabular} D. 2 F I J 05000 7,4200 5135500 315.57129 5106909 25 Actual Propetent Enem. \begin{tabular}{|c|c|} \hline o & II \\ \hline & \\ \hline BNA & anix \\ \hline ENiA. & ins \\ \hline th/h & Non \\ \hline \multirow[t]{3}{*}{ ards } & A.A \\ \hline & \\ \hline & \\ \hline Actual & Peojected \\ \hline mera1. & Esora \\ \hline WNa & NWh \\ \hline evis. & anks \\ \hline WWis & WNA \\ \hline Fin & wer. \\ \hline WNA & \\ \hline ank & inia \\ \hline & ANA \\ \hline & Stas \\ \hline \end{tabular} 90s too make 5cs Aetual Prejectue ciserz Datribise as Distribute as Orvifent Repurchase nition - Prazaza Achal Ackal Projetsed inem. \begin{tabular}{|c|c|c|c|} \hline Aetal & Projetsed & & \\ \hline siond & snera. & & \\ \hline & then & & \\ \hline aNA & the & 5 & \\ \hline & & Debibute as: & Olatribute an \\ \hline Actaial & Projected & Dividend & Napurshase \\ \hline moz1 & sin22 & manz & porata \\ \hline Whin & NNA & Fven & INA \\ \hline sun & ANA & man & ine \\ \hline rivh & & Win & NNA \\ \hline GNA & max & this & ena \\ \hline SNin & & TNA & Niva \\ \hline INA & sin & ons & anden \\ \hline & ans & cos & smeA \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline Aethal & Prejested & & \\ \hline sadons & snear. & & \\ \hline & this & & \\ \hline aNA & sich & 5 & \\ \hline & & Detribute as & Olatriaute as \\ \hline Actaial & Prolecied & Dividend & Napurshase \\ \hline sabzi & sinx22 & mang & riagen \\ \hline Whih & FNA & Fhen & INA \\ \hline sun & ANA & & WNA \\ \hline Finh & WNA & win & ENA \\ \hline GNA & & this & war \\ \hline SNin & & TNA & Niva \\ \hline WNA & this & & anch \\ \hline asin. & ANS & Fin & shes \\ \hline \end{tabular} (4) m b. Now assume that 3Cl distributes the $800 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 fitt 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. c. Calculate JCI'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/2022? FCF is expected to grow at a constant rate of 5%, and JCI's WACC is 9%. The firm has 900 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. $ c. Calculate JCI'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 JCl's current intrinsic stock price (the price on 6/30/2021)? What is the projected intrinsic stock price for 6/30/2022 ? FCF is expected to grow at a constant rate of 5%, and JCI's WACC is 9%. The firm has 900 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 JCl distributes the cash through stock repurchases? Round 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. million