answers are provided. however I need you to put them in an excel spreadsheet with the correct formulas.

Thank you

Question 6 ($40 x 80 million shares) + $3.8 billion = 6.38 billion. Question 8. In March of 2016, ABC Corp. considered an acquisition of XYZ, Inc., a privately held educational software firm. As a first step in deciding what price to bid for XYZ, ABC's CFO, Ryan Lewis, has prepared a five-year financial projection for the company assuming the acquisition takes place. The WACC of the acquiring firm (ABC) is 8.0 percent, XYZ's WACC is 12 percent, and the average of the two companies' WACCs, weighted by sales, is 10 percent. a) What is XYZ's projected free cash flow for 2016->2020? (2 points) b) Estimate the present value of XYZ's free cash flow for the years 2016 to 2020 (2 points) $1,241.0 PV@ 12% {FCF, 2015-2019} = 2 c) Estimate XYZ's value at the end of 2015 assuming it is worth the book value of its assets at the end of 2020. What is the maximum acquisition price ABC Inc. should pay to acquire XYZ's equity? (2 points) d) Estimate XYZ's value at the end of 2015 assuming that at year-end 2020 the company's equity is worth 15 times earnings after tax and its debt is worth book value. What is the maximum acquisition price ABC Inc. should pay to acquire XYZ's equity? (2 points) Estimated value of equity $ 2 ,754.53 d. Estim XYZ's valueat thee o 2 5 ass ingthat at ye nd 2020 theco pany's e ate nd f 01 um ar-e m quity isworth 15 Value of equity in 2020 9,502.50 Value of debt in 2020 1,359.00 Value of firm in 2020 10,861.50 Present value of firm in 2020 at 12% 6,163.11 Estimated firm value $ 7 ,404.13 Base on your ans r above what isthem d we , axim acquisitio price um n ? Estimated firm value 7,404.13 Existing interest-bearing debt 2,080.00 Estimated value of equity $ 5 ,324.13 e) Estimate XYZ's value at the end of 2015 assuming that in the years after 2020 the company's free cash flow grows 6 percent per year in perpetuity. What is the maximum acquisition price ABC Inc. should pay to acquire XYZ's equity? (2 points) f) Estimate XYZ's value at the end of 2015 assuming that in the years after 2020 the company's free cash flow will equal the FCF at the end of 2020 and stay in perpetuity (no-growth perpetuity). What is the maximum acquisition price ABC Inc. should pay to acquire XYZ's equity? (2 points) XYZ Systems, Inc. 5-year Financial projection ($ millions) Actual 2015 2016 Income Statement Net sales Cost of sales Gross Income Depreciation Interest expense Operating expenses Net income before tax Provision for taxes Net income after tax 2017 2018 2019 2020 2712 830 1882 1060 191 270 361 108 253 2895 959 1936 1301 175 306 154 46 108 3210 1087 2123 1009 142 334 638 191 447 3547 1241 2306 917 110 374 905 272 634 2017 2018 2019 2020 413 266 463 122 1264 5149 1654 3495 1069 5828 107 482 140 97 826 1554 344 3104 5828 462 287 541 142 1432 6410 2714 3696 1069 6197 111 842 165 120 1238 1389 370 3200 6197 545 339 629 167 1680 7449 4015 3434 1069 6183 140 814 200 134 1288 1189 454 3252 6183 618 374 739 212 1943 8200 5024 3176 1069 6188 137 585 223 174 1119 966 505 3598 6188 704 415 864 206 2189 9016 5941 3075 1069 6333 145 393 267 168 973 699 496 4165 6333 2016 2017 2018 2019 2020 2045 644 1401 786 171 212 232 70 162 2567 742 1825 1060 178 239 348 104 244 2712 830 1882 1060 191 270 361 108 253 2895 959 1936 1301 175 306 154 46 108 3210 1087 2123 1009 142 334 638 191 447 3547 1241 2306 917 110 374 905 272 634 358 228 399 156 1141 4180 868 3312 1069 5522 114 335 51 86 586 1694 335 2907 5522 413 266 463 122 1264 5149 1654 3495 1069 5828 107 482 140 97 826 1554 344 3104 5828 462 287 541 142 1432 6410 2714 3696 1069 6197 164 842 165 120 1291 1389 370 3200 6250 545 339 629 167 1680 7449 4015 3434 1069 6183 186 814 200 134 1334 1189 454 3252 6229 618 374 739 212 1943 8200 5024 3176 1069 6188 137 585 223 174 1119 966 505 3598 6188 704 415 864 206 2189 9016 5941 3075 1069 6333 145 393 267 168 973 699 496 4165 6333 526 30% 368 1060 969 1060 119 552 30% 386 1060 1261 1148 88 329 30% 230 1301 1039 1360 212 780 30% 546 1009 751 1632 272 1015 30% 711 917 816 1876 244 2045 644 1401 786 171 212 232 70 162 2567 742 1825 1060 178 239 348 104 244 XYZ Systems, Inc. 5-year Financial projection ($ millions) Actual 2015 2016 Balance Sheet Cash and securities Accounts receivable Inventory Other current assets Total current assets Gross property and equipment Accumulated Depreciation Net property and equipment Goodwill Total Assets Accounts payable Short-term debt Current portion long term debt Accrued expenses Total Current liabilities Long term debt Deferred taxes Shareholders' Equity Total liabilities and equity 358 228 399 156 1141 4180 868 3312 1069 5522 114 335 51 86 586 1694 335 2907 5522 a) What is XYZ's projected free cash flow for 2016->2020? Actual 2015 Income Statement Net sales Cost of sales Gross Income Depreciation Interest expense Operating expenses Net income before tax Provision for taxes Net income after tax Balance Sheet Cash and securities Accounts receivable Inventory Other current assets Total current assets Gross property and equipment Accumulated Depreciation Net property and equipment Goodwill Total Assets Accounts payable Short-term debt Current portion long term debt Accrued expenses Total Current liabilities Long term debt Deferred taxes Shareholders' Equity Total liabilities and equity a) Estimate XYZ's free cash flow from 2016 through 2020 Earnings before interest and taxes Tax rate EBIT*(1- tax rate) Depreciation Capital expenditures Working capital 941 Change in working capital Free cash flow b) PV of XYZ'S free cashflows for the year 2016 to 2020 = 340 97 $1,241.02 280 532 568 XYZ Systems, Inc. 5-year Financial projection ($ millions) Actual 2015 2016 Income Statement Net sales Cost of sales Gross Income Depreciation Interest expense Operating expenses Net income before tax Provision for taxes Net income after tax 2017 2018 2019 2020 2712 830 1882 1060 191 270 361 108 253 2895 959 1936 1301 175 306 154 46 108 3210 1087 2123 1009 142 334 638 191 447 3547 1241 2306 917 110 374 905 272 634 2017 2018 2019 2020 413 266 463 122 1264 5149 1654 3495 1069 5828 107 482 140 97 826 1554 344 3104 5828 462 287 541 142 1432 6410 2714 3696 1069 6197 111 842 165 120 1238 1389 370 3200 6197 545 339 629 167 1680 7449 4015 3434 1069 6183 140 814 200 134 1288 1189 454 3252 6183 618 374 739 212 1943 8200 5024 3176 1069 6188 137 585 223 174 1119 966 505 3598 6188 704 415 864 206 2189 9016 5941 3075 1069 6333 145 393 267 168 973 699 496 4165 6333 2016 2017 2018 2019 2020 2045 644 1401 786 171 212 232 70 162 2567 742 1825 1060 178 239 348 104 244 2712 830 1882 1060 191 270 361 108 253 2895 959 1936 1301 175 306 154 46 108 3210 1087 2123 1009 142 334 638 191 447 3547 1241 2306 917 110 374 905 272 634 358 228 399 156 1141 4180 868 3312 1069 5522 114 335 51 86 586 1694 335 2907 5522 413 266 463 122 1264 5149 1654 3495 1069 5828 107 482 140 97 826 1554 344 3104 5828 462 287 541 142 1432 6410 2714 3696 1069 6197 164 842 165 120 1291 1389 370 3200 6250 545 339 629 167 1680 7449 4015 3434 1069 6183 186 814 200 134 1334 1189 454 3252 6229 618 374 739 212 1943 8200 5024 3176 1069 6188 137 585 223 174 1119 966 505 3598 6188 704 415 864 206 2189 9016 5941 3075 1069 6333 145 393 267 168 973 699 496 4165 6333 526 30% 368 1060 969 1060 119 552 30% 386 1060 1261 1148 88 329 30% 230 1301 1039 1360 212 780 30% 546 1009 751 1632 272 1015 30% 711 917 816 1876 244 2045 644 1401 786 171 212 232 70 162 2567 742 1825 1060 178 239 348 104 244 XYZ Systems, Inc. 5-year Financial projection ($ millions) Actual 2015 2016 Balance Sheet Cash and securities Accounts receivable Inventory Other current assets Total current assets Gross property and equipment Accumulated Depreciation Net property and equipment Goodwill Total Assets Accounts payable Short-term debt Current portion long term debt Accrued expenses Total Current liabilities Long term debt Deferred taxes Shareholders' Equity Total liabilities and equity 358 228 399 156 1141 4180 868 3312 1069 5522 114 335 51 86 586 1694 335 2907 5522 a) What is XYZ's projected free cash flow for 2016->2020? Actual 2015 Income Statement Net sales Cost of sales Gross Income Depreciation Interest expense Operating expenses Net income before tax Provision for taxes Net income after tax Balance Sheet Cash and securities Accounts receivable Inventory Other current assets Total current assets Gross property and equipment Accumulated Depreciation Net property and equipment Goodwill Total Assets Accounts payable Short-term debt Current portion long term debt Accrued expenses Total Current liabilities Long term debt Deferred taxes Shareholders' Equity Total liabilities and equity a) Estimate XYZ's free cash flow from 2016 through 2020 Earnings before interest and taxes Tax rate EBIT*(1- tax rate) Depreciation Capital expenditures Working capital 941 Change in working capital Free cash flow b) PV of XYZ'S free cashflows for the year 2016 to 2020 = 340 97 $1,241.02 280 532 568