Question:

1. Please fill in the highlighted portions of the excel spreadsheet and explain how each cell was calculated.

A D E F G H I 1 Case report 4 2 3 4 5 6 Columbia Sportswear Company, a manufacturer and distributer of sports equipment, has grown until it has become a stable, mature company. Now Columbia is planning its first distribution to shareholders. Shown below are the most recent year's financial statements and projections for the next year, 2019 (Columbia has a fiscal year ending on June 30). Columbia plans to liquidate $500 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 decided whether to distribute with dividends or with stock repurchases. 7 $500 40% 11.0% 1,000 6.0% 8 9 Inputs 10 Amount of distribution 11 Tax rate 12 WACC 13 Number of shares 14 FCF constant growth rate 15 16 17 Income Statement (Millions of Dollars) 18 Net Sales 19 Costs (except depreciation) 20 Depreciation 21 Earning before int tax 22 Interest expense 23 Earnings before taxes 24 Taxes Actual 6/30/2018 $20,000.00 $16,000.00 $1,300.00 $2,70 $150.00 $2,550.00 $1,020.00 $1,530.00 Projected 6/30/2019 $21,200.00 $16,960.00 $1,378.00 $2,862.00 $152.82 $2,709.18 $1,083.67 $1,625.51 Net income 25 26 27 a. Assume first that Columbia distributes the $500 million as dividends. Fill in the missing values in the balance sheet column for July 1, 2019, that is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets balance after you fill in the missing items. Also, assume Columbia did not have to establish an account for dividends payable prior to the distribution.) 28 29 30 31 32 33 34 Use below table for calculations. b. Now assume that Columbia distributes the $500 million through stock repurchases. Fill in the missing values in the balance sheet column for July 1, 2019, that is labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) B D E F G H 31 Use below table for calculations. 32 b. Now assume that Columbia distributes the $500 million through stock repurchases. Fill in the missing values in the balance sheet column for July 1, 2019, that is 33 34 labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) 35 a. Distribute as Dividend 7/1/2019 $169.60 b. Distribute as Repurchase 7/2/2019 $169.60 36 37 38 Balance Sheets (Millions of Dollars) 39 Assets 40 Cash 41 Short-term investments 42 Accounts receivable 43 Inventories 44 Total current assets 45 Net plant and equipment 46 Total assets 47 Liabilities & Equity 48 Accounts payable 49 Accruals 50 Short-term debt Projected: Prior Actual to Distribution 6/30/2018 6/30/2019 $160.00 $169.60 $200.00 $640.00 $2,000.00 $2,120.00 $3,000.00 $3,180.00 $5,360.00 $6,109.60 $13,000.00 $13,780.00 $18,360.00 $19,889.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 $1,000.00 $2,000.00 $400.00 $1,060.00 $2,120.00 $0.00 $1,060.00 $2,120.00 $0.00 $1,060.00 $2,120.00 $0.00 $3,180.00 $2,192.27 $5,372.27 $5.851.82 $3,180.00 $2,192.27 $5,372.27 $5,851.82 51 Total current liabilities 52 Long-term debt 53 Total liabilities 54 Common stock 55 Treasury stock 56 Retained earnings 57 Total common equity 58 Total liabilities & equity 59 60 Check for balance: 61 62 c. Caculate Columbia's projected free cash flow; the tax rate is 40%. 63 $3,400.00 $2,068.18 $5,468.18 $5,851.82 ($400.00) $7,440.00 $12,891.82 $18,360.00 $3,180.00 $2,192.27 $5,372.27 $5,851.82 ($400.00) $9,065.51 $14,517.33 $19,889.60 $5,851.82 $11,224.09 $5,851.82 $11,224.09 NOT BALANCED! NOT BALANCED! c. Projected 6/30/2019 62 c. Caculate Columbia's projected free cash flow; the tax rate is 40%. 63 64 65 Calculation of Free Cash Flow 66 Operating current assets 67 Operating current liabilities 68 Net operating working capital 69 Net plant & equipment 70 Total net operating capital 71 Net operating profit after taxes 72 Inv. in operating capital 6/30/2018 $5,160.00 3,000.00 $2,160.00 13,000.00 $15,160.00 $1,620.00 Free cash flow (FCF) 73 74 75 d. Caculate Columbia's horizon value for 6/30/2019. FCF is expected to grow at a constant rate of 6% and Columbia's WACC is 11%. Calculate Columbia's value of 76 operations for 6/30/2018 and 6/30/2019. (Hint: Columbia's value of operations on 6/30/2019 is equal to the horizon value.) 77 78 d. 79 Valuation 6/30/2018 6/30/2019 80 Horizon value 81 Value of operations 82 83 84 e. What is Columbia's current intrinsic stock price (the price on 6/30/2018)? What is the projected intrinsic stock price for 6/30/2019? Use below table for calculations. 85 86 f. What is the projected intrinsic stock price on 7/1/2019 if Columbia distributes the cash as dividends? Use below table for calculations. 87 88 g. What is the projected intrinsic stock price on 7/1/2019 if Columbia distributes the cash through stock repurchases? How many shares will remain outstanding after 89 the repurchase? Use below table for calculations. 90 91 92 e. 93 94 f. Distribute as Dividend 7/1/2019 g. Distribute as Repurchase 7/1/2019 6/30/2018 6/30/2019 95 96 97 98 99 100 101 Value of operations + Value of nonoperating assets Total intrinsic value of firm - Debt Intrinsic value of equity - Number of shares Intrinsic price per share A D E F G H I 1 Case report 4 2 3 4 5 6 Columbia Sportswear Company, a manufacturer and distributer of sports equipment, has grown until it has become a stable, mature company. Now Columbia is planning its first distribution to shareholders. Shown below are the most recent year's financial statements and projections for the next year, 2019 (Columbia has a fiscal year ending on June 30). Columbia plans to liquidate $500 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 decided whether to distribute with dividends or with stock repurchases. 7 $500 40% 11.0% 1,000 6.0% 8 9 Inputs 10 Amount of distribution 11 Tax rate 12 WACC 13 Number of shares 14 FCF constant growth rate 15 16 17 Income Statement (Millions of Dollars) 18 Net Sales 19 Costs (except depreciation) 20 Depreciation 21 Earning before int tax 22 Interest expense 23 Earnings before taxes 24 Taxes Actual 6/30/2018 $20,000.00 $16,000.00 $1,300.00 $2,70 $150.00 $2,550.00 $1,020.00 $1,530.00 Projected 6/30/2019 $21,200.00 $16,960.00 $1,378.00 $2,862.00 $152.82 $2,709.18 $1,083.67 $1,625.51 Net income 25 26 27 a. Assume first that Columbia distributes the $500 million as dividends. Fill in the missing values in the balance sheet column for July 1, 2019, that is labeled "Distribute as Dividends." (Hint: Be sure that the balance sheets balance after you fill in the missing items. Also, assume Columbia did not have to establish an account for dividends payable prior to the distribution.) 28 29 30 31 32 33 34 Use below table for calculations. b. Now assume that Columbia distributes the $500 million through stock repurchases. Fill in the missing values in the balance sheet column for July 1, 2019, that is labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) B D E F G H 31 Use below table for calculations. 32 b. Now assume that Columbia distributes the $500 million through stock repurchases. Fill in the missing values in the balance sheet column for July 1, 2019, that is 33 34 labeled "Distribute as Repurchase." (Hint: Be sure that the balance sheets balance after you fill in the missing items.) 35 a. Distribute as Dividend 7/1/2019 $169.60 b. Distribute as Repurchase 7/2/2019 $169.60 36 37 38 Balance Sheets (Millions of Dollars) 39 Assets 40 Cash 41 Short-term investments 42 Accounts receivable 43 Inventories 44 Total current assets 45 Net plant and equipment 46 Total assets 47 Liabilities & Equity 48 Accounts payable 49 Accruals 50 Short-term debt Projected: Prior Actual to Distribution 6/30/2018 6/30/2019 $160.00 $169.60 $200.00 $640.00 $2,000.00 $2,120.00 $3,000.00 $3,180.00 $5,360.00 $6,109.60 $13,000.00 $13,780.00 $18,360.00 $19,889.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 $2,120.00 $3,180.00 $5,469.60 $13,780.00 $19,249.60 $1,000.00 $2,000.00 $400.00 $1,060.00 $2,120.00 $0.00 $1,060.00 $2,120.00 $0.00 $1,060.00 $2,120.00 $0.00 $3,180.00 $2,192.27 $5,372.27 $5.851.82 $3,180.00 $2,192.27 $5,372.27 $5,851.82 51 Total current liabilities 52 Long-term debt 53 Total liabilities 54 Common stock 55 Treasury stock 56 Retained earnings 57 Total common equity 58 Total liabilities & equity 59 60 Check for balance: 61 62 c. Caculate Columbia's projected free cash flow; the tax rate is 40%. 63 $3,400.00 $2,068.18 $5,468.18 $5,851.82 ($400.00) $7,440.00 $12,891.82 $18,360.00 $3,180.00 $2,192.27 $5,372.27 $5,851.82 ($400.00) $9,065.51 $14,517.33 $19,889.60 $5,851.82 $11,224.09 $5,851.82 $11,224.09 NOT BALANCED! NOT BALANCED! c. Projected 6/30/2019 62 c. Caculate Columbia's projected free cash flow; the tax rate is 40%. 63 64 65 Calculation of Free Cash Flow 66 Operating current assets 67 Operating current liabilities 68 Net operating working capital 69 Net plant & equipment 70 Total net operating capital 71 Net operating profit after taxes 72 Inv. in operating capital 6/30/2018 $5,160.00 3,000.00 $2,160.00 13,000.00 $15,160.00 $1,620.00 Free cash flow (FCF) 73 74 75 d. Caculate Columbia's horizon value for 6/30/2019. FCF is expected to grow at a constant rate of 6% and Columbia's WACC is 11%. Calculate Columbia's value of 76 operations for 6/30/2018 and 6/30/2019. (Hint: Columbia's value of operations on 6/30/2019 is equal to the horizon value.) 77 78 d. 79 Valuation 6/30/2018 6/30/2019 80 Horizon value 81 Value of operations 82 83 84 e. What is Columbia's current intrinsic stock price (the price on 6/30/2018)? What is the projected intrinsic stock price for 6/30/2019? Use below table for calculations. 85 86 f. What is the projected intrinsic stock price on 7/1/2019 if Columbia distributes the cash as dividends? Use below table for calculations. 87 88 g. What is the projected intrinsic stock price on 7/1/2019 if Columbia distributes the cash through stock repurchases? How many shares will remain outstanding after 89 the repurchase? Use below table for calculations. 90 91 92 e. 93 94 f. Distribute as Dividend 7/1/2019 g. Distribute as Repurchase 7/1/2019 6/30/2018 6/30/2019 95 96 97 98 99 100 101 Value of operations + Value of nonoperating assets Total intrinsic value of firm - Debt Intrinsic value of equity - Number of shares Intrinsic price per share