please show calculation

D Spreadsheet Exercise: Problem 8.32 Nabor Industries is considering going public but is unsure of a fait offering price for the company. Before hiring an investment banker to assist in making the public offering, managers at Nabor have decided to make their own estimate of the firm's common stock value. The firm's CFO has gathered data for performing the valuation using the free cash flow valuation model. The firm's weighted average cost of capital is 11%, and it has $1,500,000 of debt and $100,000 of preferred stock in terms of matket value. The estimated free cash flows over the next five years, 2020 through 2024, are given. Beyond 2024 to infinity, the firm expects its free cash flow to grow by 3% annually Complete the steps below using cell references to given date or previous caleslations. In some cases, a simple cell reference is all you need to copy paste a formula across a row or down a colum, an absolute cell reference or a mixed cell reference may be preferred If a specific Excel function is to be used the directions will preth the me of that function. Do not type in numerical data into a call or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the bow calls highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the date in your formulas, usually the Gover Dakt section 2 Year (1) 2020 2021 2022 2023 2024 Free Cash Flow (FCT) $200,000 $250,000 $310.000 $350,000 $390,000 TO 11 13 14 To Do a. Estimate the value of Nabor Industriesentire company by using the free cash flow valuation model b. Use your finding in partaalong with the data provided previously to find Nabor Industries' common stock value c. If the firm plans to issue 200,000 shares of common stock, what is its estimated value per shite? 15 16 17 Solution a. Estimate the value of Nabor Industries' entire company by using the free cash flow valuation model 20 31 Rate of return Growth rate after 2024 11% 396 23 24 Present Value 25 200 22 2 Year Sum of discounted CF Stock value at the end of 2024 PV of fire stock value Current value of Nabor Industries 8.32 16 Solution TH 19 a. Estimate the value of Nabor Industries' entire company by using the free cash flow valuation model. Rate of return Growth rate after 2024 22 23 11% 3% Present Value Year Sum of discounted CFS Stock value at the end of 2024 PV of future stock value Current value of Nabor Industries 27 28 29 b. Use your finding in part a along with the data provided previously to find Nabor Industries' common stock value. 30 31 32 33 34 35 36 37 Debt Preferred Stock Common stock value 1.500,000 400,000 c. If the firm plans to issue 200.000 shares of common stock, what is its estimated value per share? IR 200,000 Number of shares Estimated value per share 39 40 41 4 40 12 Requirements 1 In cell D25, by using cell references and the function NPV, calculate the sum of discounted cash flows for 2020-2021 44 2 In cell 026 by using cell references, calculate the value of the stock at the end of 2024 3 In cell D27, by using cell references and the function PV. calculate the present value of the future stock value Note: The output of the expression or function you typed in this cell is expected as a positive number 4 In cell 128, by using cell references, calculate the current total value of Nabor Industries Note: Refer to the values from Steps 1 and 3 in your calculations, $ In cell D34, by using cell references, calculate the Nabor Industries' common stock value Note: Refer to the current value of Nabor Industries from Stop 4 in your calculations 6 In cell D39, by using cell references, calculate the firm's estimated value per share 7 Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed 41