Excel File Edit Window Help 8 + Sun Nov 7 8:04 PM View Insert Format Tools Data B... AutoSave OFF Chapter 15 spreadsheet assignment 8 Home Insert Draw Page Layout Formulas Data Review View Tell me Share 4- Comments Arial v 10 VA AA General FH insart 2 Delete Et Format 27-0 Peste Sort & Filter Find & Select Analyze Data Sensity L L M N N 0 P P Q R S S # TU OA $ v % Conditional Format Cell Formatting as Table Styles N17 Xfx # x A B D ) E E F G H H I K J. Clark Inc. (JCI), a manufacturer and distributer of sports equipment, has grown until it has become a stable, mature ? company. Now JCI is planning its first distribution to shareholders. Shown below are the most recent year's financial # statements and projections for the next year, 2019 (JCI has a fiscal year ending on June 30). JCI plans to liquidate $500 9 million of its short-term securities and distribute them on July 1, 2019, the first day of the next fiscal year, but has not yet 10 decided whether to distribute with dividends or with stock repurchases. 11 12 Inputs 13 Amount of distribution $500 14 Tax rate 40% 15 WACC 11.0% 16 Number of shares 1,000 17 FCF constant growth rate 6.0% 18 19 Actual Projected 20 Income Statement (Millions of Dollars 6/30/18 6/30/19 21 Net Sales $20,000.00 $21,200.00 22 Costs (except depreciation) $16,000.00 $16,960.00 23 Depreciation $1,300.00 $1,378.00 24 Earning before int. & tax $2,700.00 $2,862.00 25 Interest expense $150.00 $152.82 26 Earnings before taxes $2,550.00 $2,709.18 27 Taxes $1,020.00 $1,083.67 28 Net income $1,530.00 $1,625.51 29 30 a. Assume first that JCI distributes the $500 million as dividends. Fill in the missing values in the balance 31 sheet column for July 1, 2019, that is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets 32 balance after you fill in the missing items. Also, assume JCI did not have to establish an account for dividends 33 payable prior to the distribution.) See below for calculations. 34 35 b. Now assume that JCI distributes the $500 million through stock repurchases. Fill in the missing values in 36 the balance sheet column for July 1, 2019, that is labeled "Distribute as Repurchase." (Hint: Be sure that the 37 balance sheets balance after you fill in the missing items.) Build a Model Enter 125% NOV 168 A AA 7 w P va Excel File Edit Window Help 8 Sun Nov 7 8:05 PM View Insert Format Tools Data APO... AutoSave OFF Chapter 15 spreadsheet assignment a 8 Home Insert Draw Page Layout Formulas Data Review View Tell me Share - Comments Arial v 10 . AA = O sy General IL DUD Conditional Format Cell Formatting as Table Styles Insert Dx Delete Format Peste 7 U w OA 3 = = += $ V % . Sort & Filter Find & Select Analyze Data Sensity N17 x I 1 J J K L M N 0 P Q R S S A B C D E E F H calculations. 34 35 b. Now assume that JCI distributes the $500 million through stock repurchases. Fill in the missing values in 36 the balance sheet column for July 1, 2019, that is labeled "Distribute as Repurchase." (Hint: Be sure that the 37 balance sheets balance after you fill in the missing Items.) 38 39 Projected: 401 Prior to Distribute Distribute as 41 Balance Sheets (Millions of dollars) Actual Distribution as Dividend Repurchase 42 Assets 6/30/2018 6/30/2019 7/1/2019 7/2/2019 43 Cash $160.00 $169.60 $169.60 $169.60 44 Short-term investments $200.00 $140.00 45 Accounts receivable $2,000.00 $2,120.00 $2,120.00 $2,120.00 46 Inventories $3,000.00 $3,180.00 $3.180.00 $3,180.00 47 current assets $5,300.00 $6,109.60 $5,469.60 $5,469.60 48 Net plant and equipment $13,000.00 $13,780.00 $13,780.00 $13,780.00 49 Total assets $18,360.00 $19,889.60 $19,249.60 $19.249.60 so Liabilities & Equity 51 Accounts payable $1,000.00 $1,060.00 $1,060.00 $1,060.00 52 Accruals $2,000.00 $2,120.00 $2,120.00 $2,120.00 S3 Short-term debt $400.00 $0.00 $0.00 $0.00 54 rrent liabilities $3,400.00 $3,180.00 $3,180.00 $3,180.00 ss Long-term debt rollillah $2,068.18 $2,192.27 $2,192.27 $2,192.27 56 Total liabilities $5,468.18 $5,372.27 $5,372.27 $5,372.27 S? Common stock $5,851.82 $5,851.82 $5,851.82 $5,851.82 58 Treasury stock ($400.00) ($400.00) 59 Retained earnings $7.440.00 $9,065.51 60 bmmon equity $12,891.82 $14,517.33 $5,851.82 $5,851.82 61 ilities & equity $18,360.00 $19,889.60 $11,224.09 $11,224.09 62 NOT NOT 63 ck for balance: BALANCEDI BALANCEDI 64 65 C. Caculate JCI's projected free cash flow, the tax rate is 40%. 66 Build a Model + Ready 125% 168 .. NOV 7 A . w P va G Excel File Edit Window Help 8 + Sun Nov 7 8:04 PM View Insert Format Tools Data B... AutoSave OFF Chapter 15 spreadsheet assignment 8 Home Insert Draw Page Layout Formulas Data Review View Tell me Share 4- Comments Arial v 10 VA AA General FH insart 2 Delete Et Format 27-0 Peste Sort & Filter Find & Select Analyze Data Sensity L L M N N 0 P P Q R S S # TU OA $ v % Conditional Format Cell Formatting as Table Styles N17 Xfx # x A B D ) E E F G H H I K J. Clark Inc. (JCI), a manufacturer and distributer of sports equipment, has grown until it has become a stable, mature ? company. Now JCI is planning its first distribution to shareholders. Shown below are the most recent year's financial # statements and projections for the next year, 2019 (JCI has a fiscal year ending on June 30). JCI plans to liquidate $500 9 million of its short-term securities and distribute them on July 1, 2019, the first day of the next fiscal year, but has not yet 10 decided whether to distribute with dividends or with stock repurchases. 11 12 Inputs 13 Amount of distribution $500 14 Tax rate 40% 15 WACC 11.0% 16 Number of shares 1,000 17 FCF constant growth rate 6.0% 18 19 Actual Projected 20 Income Statement (Millions of Dollars 6/30/18 6/30/19 21 Net Sales $20,000.00 $21,200.00 22 Costs (except depreciation) $16,000.00 $16,960.00 23 Depreciation $1,300.00 $1,378.00 24 Earning before int. & tax $2,700.00 $2,862.00 25 Interest expense $150.00 $152.82 26 Earnings before taxes $2,550.00 $2,709.18 27 Taxes $1,020.00 $1,083.67 28 Net income $1,530.00 $1,625.51 29 30 a. Assume first that JCI distributes the $500 million as dividends. Fill in the missing values in the balance 31 sheet column for July 1, 2019, that is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets 32 balance after you fill in the missing items. Also, assume JCI did not have to establish an account for dividends 33 payable prior to the distribution.) See below for calculations. 34 35 b. Now assume that JCI distributes the $500 million through stock repurchases. Fill in the missing values in 36 the balance sheet column for July 1, 2019, that is labeled "Distribute as Repurchase." (Hint: Be sure that the 37 balance sheets balance after you fill in the missing items.) Build a Model Enter 125% NOV 168 A AA 7 w P va Excel File Edit Window Help 8 Sun Nov 7 8:05 PM View Insert Format Tools Data APO... AutoSave OFF Chapter 15 spreadsheet assignment a 8 Home Insert Draw Page Layout Formulas Data Review View Tell me Share - Comments Arial v 10 . AA = O sy General IL DUD Conditional Format Cell Formatting as Table Styles Insert Dx Delete Format Peste 7 U w OA 3 = = += $ V % . Sort & Filter Find & Select Analyze Data Sensity N17 x I 1 J J K L M N 0 P Q R S S A B C D E E F H calculations. 34 35 b. Now assume that JCI distributes the $500 million through stock repurchases. Fill in the missing values in 36 the balance sheet column for July 1, 2019, that is labeled "Distribute as Repurchase." (Hint: Be sure that the 37 balance sheets balance after you fill in the missing Items.) 38 39 Projected: 401 Prior to Distribute Distribute as 41 Balance Sheets (Millions of dollars) Actual Distribution as Dividend Repurchase 42 Assets 6/30/2018 6/30/2019 7/1/2019 7/2/2019 43 Cash $160.00 $169.60 $169.60 $169.60 44 Short-term investments $200.00 $140.00 45 Accounts receivable $2,000.00 $2,120.00 $2,120.00 $2,120.00 46 Inventories $3,000.00 $3,180.00 $3.180.00 $3,180.00 47 current assets $5,300.00 $6,109.60 $5,469.60 $5,469.60 48 Net plant and equipment $13,000.00 $13,780.00 $13,780.00 $13,780.00 49 Total assets $18,360.00 $19,889.60 $19,249.60 $19.249.60 so Liabilities & Equity 51 Accounts payable $1,000.00 $1,060.00 $1,060.00 $1,060.00 52 Accruals $2,000.00 $2,120.00 $2,120.00 $2,120.00 S3 Short-term debt $400.00 $0.00 $0.00 $0.00 54 rrent liabilities $3,400.00 $3,180.00 $3,180.00 $3,180.00 ss Long-term debt rollillah $2,068.18 $2,192.27 $2,192.27 $2,192.27 56 Total liabilities $5,468.18 $5,372.27 $5,372.27 $5,372.27 S? Common stock $5,851.82 $5,851.82 $5,851.82 $5,851.82 58 Treasury stock ($400.00) ($400.00) 59 Retained earnings $7.440.00 $9,065.51 60 bmmon equity $12,891.82 $14,517.33 $5,851.82 $5,851.82 61 ilities & equity $18,360.00 $19,889.60 $11,224.09 $11,224.09 62 NOT NOT 63 ck for balance: BALANCEDI BALANCEDI 64 65 C. Caculate JCI's projected free cash flow, the tax rate is 40%. 66 Build a Model + Ready 125% 168 .. NOV 7 A . w P va G