Question Content Area Excel Activity: Distributions as Dividends or Repurchases Start with the partial model in the file Ch$15$ P$13$ Build a Model.xlsx$.$ J$.$ Clark Inc. $($JCI$),$ 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$; JCI's fiscal year ends on June $30.)$ JCI plans to liquidate and distribute $$500$ 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 Ch$15$ P$13$ Build a Model$-9$a$1203.$xlsx Assume first that JCI distributes the $$500$ 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\mathrm{.}23$ million should be entered as $1\mathrm{.}23,$ not $1,230,000.$ Round your answers to two decimal places. Distribute as Dividend$7/1/2022$Short$-$term investments$$$fill in the blank $2$ millionTreasury stock$$$fill in the blank $3$ millionRetained earnings$$$fill in the blank $4$ million Now assume that JCI distributes the $$500$ 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\mathrm{.}23$ million should be entered as $1\mathrm{.}23,$ not $1,230,000.$ Round your answers to two decimal places. Distribute as Repurchase$7/1/2022$Short$-$term investments$$$fill in the blank $5$ millionTreasury stock$$$fill in the blank $6$ millionRetained earnings$$$fill in the blank $7$ million Calculate JCI's projected free cash flow; the tax rate is $25\%.$ Enter your answer in millions. For example, an answer of $$1\mathrm{.}23$ million should be entered as $1\mathrm{.}23,$ not $1,230,000.$ Round your answer to two decimal places. $$$fill in the blank $8$ million 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$: $$$fill in the blank $9$ Intrinsic stock price on $6/30/2022$: $$$fill in the blank $10$ 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. $$$fill in the blank $11$ 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. $$$fill in the blank $12$ How many shares will remain outstanding after the repurchase? Enter your answer in millions. For example, an answer of $1\mathrm{.}23$ million should be entered as $1\mathrm{.}23,$ not $1,230,000.$ Round your answer to two decimal places. fill in the blank $13$ million

Projecting JCl$\text{'}$s Financial Statements $($Millions of Dollars$)$

Income Statements

Net sales

Costs $($except depreciation$)$

Depreciation

EBIT

Interest expense

EBT

Taxes

B

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

c$.$ Calculating JCI's projected free cash flow Tax rate

$50$

$51$

$7/1/2022$

c$.$ Calculate JCI's projected free cash flow; the tax rate is $25\%.$ Enter your answer in millions.

For example, an answer of $$1\mathrm{.}23$ million should be entered as $1\mathrm{.}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 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 neare