Excel File Edit Window Help 8 Sun Nov 7 8:06 PM View Insert Format Tools Data APC ... AutoSave OFF Chapter 15 spreadsheet assignment Home Insert Draw Page Layout Formulas Data Review View Tell me Share - Comments Arial v 10 = Insert AA A V O General $ IL Peste TU OA = + $ V % 3 . Dx Delete Format Conditional Formet Formatting as Table Cell Styles Sort & Filter Find & Select Analyze Data Sensitvity I I J K L M N N 0 P Q R R S S N17 x fx A B D E F G H H 65 C. Caculate JCI's projected free cash flow; the tax rate is 40%. 66 67 Projected Calculation of Free Cash 68 Flow 6/30/2018 6/30/2019 69 Operating current assels $5,160.00 70 Operating current liabilities 3,000.00 71 Net operating working capital $2,160.00 72 Net plant & equipment 13,000.00 73 Total net operating capital $15, 160.00 74 Net operating profit after taxes $1,620.00 75 Inv. In operating capital 76 Free cash flow (FCF) 77 78 C. Caculate JCI's horizon value for 6/30/2019. FCF is expected to grow at a constant rate of 6% and JCl's 79 WACC is 11%. Calculate JCI's value of operations for 6/30/2018 and 6/30/2019. (Hint: JCI's value of operations 80 on 6/30/2019 is equal to the horizon value.) 81 82 Valuation 6/30/2018 6/30/2019 83 Horizon value 84 Value of operations 85 86 d. What Is JCI's current Intrinsic stock price (the price on 6/30/2018)? What is the projected intrinsic stock 87 price for 6/30/20197 See below for calculations. 88 e. What is the projected intrinsic stock price on 7/1/2019 if JCI distributes the cash as dividends? See below for calculations. 90 91 f. What is the projected intrinsic stock price on 7/1/2019 of JCI distributes the cash athrough stock Build a Model + Ready + 125% NOV 168 . A 7 w P G Excel File Edit Window Help 8 Sun Nov 7 8:06 PM View Insert Format Tools Data APO... AutoSave OFF Chapter 15 spreadsheet assignment Home Insert Draw Page Layout Formulas Data Review View Tell me Share - Comments Arial 10 "A AA = Insert O $ General IL D D Conditional Formet Cell Formatting as Table Styles Peste OA + = = $ Dk Delete Dx Formal V % . Sort & Filter IN Analyze Data Find & Select Sensity TU B1 Uw 3 3 x fx N17 A B D E F F G H I 1 J K L M M N N 0 P Q R R S 91 f. What is the projected intrinsic stock price on 7/1/2019 if JCI distributes the cash athrough stock 92 repurchases? How many shares will remain outstanding after the repurchase? See below for calculations. 93 94 95 Distribute Distribute as | 96 as Dividend Repurchase 97 6/30/2018 6/30/2019 7/1/2019 7/1/2019 98 Value of operations 99 + Value of nonoperating assets 100 Total Intrinsic value of firm 101 - Debt 102 Intrinsic value of equity 1013 Number of shares 1014 Intrinsic price per share 105 106 107 108 109 110 111 112 113 1141 115 116 117 118 119 120 121 Build a Model + Ready B 168 .. ( HOV 7 A A AA w P va G Excel File Edit Window Help 8 Sun Nov 7 8:06 PM View Insert Format Tools Data APC ... AutoSave OFF Chapter 15 spreadsheet assignment Home Insert Draw Page Layout Formulas Data Review View Tell me Share - Comments Arial v 10 = Insert AA A V O General $ IL Peste TU OA = + $ V % 3 . Dx Delete Format Conditional Formet Formatting as Table Cell Styles Sort & Filter Find & Select Analyze Data Sensitvity I I J K L M N N 0 P Q R R S S N17 x fx A B D E F G H H 65 C. Caculate JCI's projected free cash flow; the tax rate is 40%. 66 67 Projected Calculation of Free Cash 68 Flow 6/30/2018 6/30/2019 69 Operating current assels $5,160.00 70 Operating current liabilities 3,000.00 71 Net operating working capital $2,160.00 72 Net plant & equipment 13,000.00 73 Total net operating capital $15, 160.00 74 Net operating profit after taxes $1,620.00 75 Inv. In operating capital 76 Free cash flow (FCF) 77 78 C. Caculate JCI's horizon value for 6/30/2019. FCF is expected to grow at a constant rate of 6% and JCl's 79 WACC is 11%. Calculate JCI's value of operations for 6/30/2018 and 6/30/2019. (Hint: JCI's value of operations 80 on 6/30/2019 is equal to the horizon value.) 81 82 Valuation 6/30/2018 6/30/2019 83 Horizon value 84 Value of operations 85 86 d. What Is JCI's current Intrinsic stock price (the price on 6/30/2018)? What is the projected intrinsic stock 87 price for 6/30/20197 See below for calculations. 88 e. What is the projected intrinsic stock price on 7/1/2019 if JCI distributes the cash as dividends? See below for calculations. 90 91 f. What is the projected intrinsic stock price on 7/1/2019 of JCI distributes the cash athrough stock Build a Model + Ready + 125% NOV 168 . A 7 w P G Excel File Edit Window Help 8 Sun Nov 7 8:06 PM View Insert Format Tools Data APO... AutoSave OFF Chapter 15 spreadsheet assignment Home Insert Draw Page Layout Formulas Data Review View Tell me Share - Comments Arial 10 "A AA = Insert O $ General IL D D Conditional Formet Cell Formatting as Table Styles Peste OA + = = $ Dk Delete Dx Formal V % . Sort & Filter IN Analyze Data Find & Select Sensity TU B1 Uw 3 3 x fx N17 A B D E F F G H I 1 J K L M M N N 0 P Q R R S 91 f. What is the projected intrinsic stock price on 7/1/2019 if JCI distributes the cash athrough stock 92 repurchases? How many shares will remain outstanding after the repurchase? See below for calculations. 93 94 95 Distribute Distribute as | 96 as Dividend Repurchase 97 6/30/2018 6/30/2019 7/1/2019 7/1/2019 98 Value of operations 99 + Value of nonoperating assets 100 Total Intrinsic value of firm 101 - Debt 102 Intrinsic value of equity 1013 Number of shares 1014 Intrinsic price per share 105 106 107 108 109 110 111 112 113 1141 115 116 117 118 119 120 121 Build a Model + Ready B 168 .. ( HOV 7 A A AA w P va G