You are the assistant to the CFO for Bento Lobo Corporation (BLC) and have been asked to estimate the firm's weighted average cost of capital and then use this cost of capital in conducting a corporate valuation analysis of the company. BLC stock sells for $100 per share and there are 60,000 shares outstanding. The risk free rate is 2.25%, the required return on the market is 12.25% and BLC's beta is 1.20. The flotation cost on new common stock adds 1.45% to the required return from CAPM. BLC's preferred stock pays a dividend of $4.00 per share and sells for $41.50 per share. There are 20,000 preferred shares outstanding. The flotation cost on new preferred stock is 1.25%. The company has outstanding bonds with a face value of $1,000 and 23 years to maturity. The bonds have an annual coupon rate of 10.2%, a current price of $1,375.50, and pay coupons semi- annually. The total market value of BLC's existing debt is $2.0 million. The company's combined federal and state tax rate is 28%. The flotation cost on new debt is 2%. Complete the excel template worksheet WACC' to provide the following: a. What is BLC's market value capital structure and the values for the weights in the WACC equation? b. Calculate the cost of each capital component, that is, the before-tax cost of debt, the cost of preferred stock, and the cost of equity. c. If BLC continues to use the same market value capital structure, what is the firm's WACC? You next turn to a corporate valuation of BLC (see pages 319-322 (8th ed) 327-330 (9th ed) in your textbook). After talking with the CFO and Chief Marketing Officer you have made the following assumptions regarding future growth and investments: 2022 12.0% 88.0% 2023 9.2% 86.0% 2024 8.0% 82.0% 2025 6.8% 80.0% 2026 4.8% 80.0% Sales Growth Rate Non depreciation operating costs as a % of sales Growth in fixed assets Growth in NOWC Deprec as a % of Fixed Assets 8.0% 5.0% 8.0% 7.8% 5.0% 8.0% 6.0% 5.0% 7.5% 6.0% 5.0% 7.0% 4.6% 5.0% 7.0% After 2026 you will use the company's expected long run annual growth forecast of 3.75% for the free cash flows of the company. d. Complete the Corp Valuation' worksheet in the template provided. Data in green shaded areas are provided. Only enter formulas or cell references (no typed numbers) in the blue shaded areas. e. Based on your analysis, is the company over-, under-, or fairly-valued? f. BLC is unsure of its long-run growth rate of FCF. What long-run growth rate would cause the intrinsic value per share estimate to equal the current market price? Use excel's goal seek function. Amortization You are planning to buy a house with a purchase price of $1,575,000. The mortgage lender requires that you provide a down payment of 20% of the purchase price. You will take out a 30 year mortgage loan for the remaining balance of the purchase price. The nominal annual interest rate is 3.25%. You will repay the loan by making equal end-of-month payments over the 30 year life of the loan. Complete the Amortization' worksheet in the provided spreadsheet. Use only formulas and cell references in the blue areas. Use the pmt() function in excel to compute the monthly mortgage payment. Answer the two questions in the worksheet. NPV Profile On the worksheet tab labeled 'NPV Profile' calculate the IRRs of each project and also the incremental project S-L. Then complete the table that indicates the NPVs of each of the two projects, S and L, for discount rates going from 0% to 24% in increments of 1%. Then graph these data and create the NPV profile. See the example in the capital budgeting handout. You are the assistant to the CFO for Bento Lobo Corporation (BLC) and have been asked to estimate the firm's weighted average cost of capital and then use this cost of capital in conducting a corporate valuation analysis of the company. BLC stock sells for $100 per share and there are 60,000 shares outstanding. The risk free rate is 2.25%, the required return on the market is 12.25% and BLC's beta is 1.20. The flotation cost on new common stock adds 1.45% to the required return from CAPM. BLC's preferred stock pays a dividend of $4.00 per share and sells for $41.50 per share. There are 20,000 preferred shares outstanding. The flotation cost on new preferred stock is 1.25%. The company has outstanding bonds with a face value of $1,000 and 23 years to maturity. The bonds have an annual coupon rate of 10.2%, a current price of $1,375.50, and pay coupons semi- annually. The total market value of BLC's existing debt is $2.0 million. The company's combined federal and state tax rate is 28%. The flotation cost on new debt is 2%. Complete the excel template worksheet WACC' to provide the following: a. What is BLC's market value capital structure and the values for the weights in the WACC equation? b. Calculate the cost of each capital component, that is, the before-tax cost of debt, the cost of preferred stock, and the cost of equity. c. If BLC continues to use the same market value capital structure, what is the firm's WACC? You next turn to a corporate valuation of BLC (see pages 319-322 (8th ed) 327-330 (9th ed) in your textbook). After talking with the CFO and Chief Marketing Officer you have made the following assumptions regarding future growth and investments: 2022 12.0% 88.0% 2023 9.2% 86.0% 2024 8.0% 82.0% 2025 6.8% 80.0% 2026 4.8% 80.0% Sales Growth Rate Non depreciation operating costs as a % of sales Growth in fixed assets Growth in NOWC Deprec as a % of Fixed Assets 8.0% 5.0% 8.0% 7.8% 5.0% 8.0% 6.0% 5.0% 7.5% 6.0% 5.0% 7.0% 4.6% 5.0% 7.0% After 2026 you will use the company's expected long run annual growth forecast of 3.75% for the free cash flows of the company. d. Complete the Corp Valuation' worksheet in the template provided. Data in green shaded areas are provided. Only enter formulas or cell references (no typed numbers) in the blue shaded areas. e. Based on your analysis, is the company over-, under-, or fairly-valued? f. BLC is unsure of its long-run growth rate of FCF. What long-run growth rate would cause the intrinsic value per share estimate to equal the current market price? Use excel's goal seek function. Amortization You are planning to buy a house with a purchase price of $1,575,000. The mortgage lender requires that you provide a down payment of 20% of the purchase price. You will take out a 30 year mortgage loan for the remaining balance of the purchase price. The nominal annual interest rate is 3.25%. You will repay the loan by making equal end-of-month payments over the 30 year life of the loan. Complete the Amortization' worksheet in the provided spreadsheet. Use only formulas and cell references in the blue areas. Use the pmt() function in excel to compute the monthly mortgage payment. Answer the two questions in the worksheet. NPV Profile On the worksheet tab labeled 'NPV Profile' calculate the IRRs of each project and also the incremental project S-L. Then complete the table that indicates the NPVs of each of the two projects, S and L, for discount rates going from 0% to 24% in increments of 1%. Then graph these data and create the NPV profile. See the example in the capital budgeting handout