Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need step by step by how to complete these calculations in excel Part A Betzy Inc. is a relatively new company. You first need
I need step by step by how to complete these calculations in excel
Part A Betzy Inc. is a relatively new company. You first need to find the company's WACC. You gathered the following data to solve for Betzy Inc.'s WACC. The firm has a weight of debt of 35%, 20% in preferred stock, and 45% in cquity and faces a corporate tax rate of 40%. The expected market return is 13%, the risk free rate is 4% and the firm's Beta is 1.2. The preferred stock has a current price of $22 and pays a $1.5 dividend. The firm's debt consists of bonds with a coupon ratc of 11% that makes payments semi-annually and matures in 12 years. The current price of the bond is $960. The company's prior year's (starting) free cash flow was found to be $185,000. The company currently has $35,000 in debt and 250,000 shares outstanding. Given that it is a relatively new company, you believe that Betzy Inc. will have strong growth over the next 5 years. After 5 years, you assume that the company's growth will stabilize to a normal rate and continue that rate of growth forever. For now, assume a growth rate of 12% for the first 5 years FCF and a terminal growth rate of 3% (rate after year 5). Note: assume annual end-of-year data Use the above information to create a model to find the intrinsic value of Betzy Inc. Be sure that if the user changes any inputs, the model appropriately updates. Betzy Inc. Inputs Weight of Debt Preferred Stock Equity Corporate Tax Rate Expected Market Return Risk Free Rate Beta Current Stock Price Dividend Bond Coupon Rate Bond Maturity (years) # of Bond Payments per year Current Bond Price Prior Year Starting Cash Flow Debt Shares Outstanding Assumed Growth Rate First 5 Years FCF Assumed Terminal Growth Rate After 5 Years 35% 20% 45% 40% 13% 4% 1.20 $22.00 $1.50 11% 12 2 $960.00 $185,000 $35,000 250,000 12% 3% Bet Part A Betzy Inc. is a relatively new company. You first need to find the company's WACC. You gathered the following data to solve for Betzy Inc.'s WACC. The firm has a weight of debt of 35%, 20% in preferred stock, and 45% in cquity and faces a corporate tax rate of 40%. The expected market return is 13%, the risk free rate is 4% and the firm's Beta is 1.2. The preferred stock has a current price of $22 and pays a $1.5 dividend. The firm's debt consists of bonds with a coupon ratc of 11% that makes payments semi-annually and matures in 12 years. The current price of the bond is $960. The company's prior year's (starting) free cash flow was found to be $185,000. The company currently has $35,000 in debt and 250,000 shares outstanding. Given that it is a relatively new company, you believe that Betzy Inc. will have strong growth over the next 5 years. After 5 years, you assume that the company's growth will stabilize to a normal rate and continue that rate of growth forever. For now, assume a growth rate of 12% for the first 5 years FCF and a terminal growth rate of 3% (rate after year 5). Note: assume annual end-of-year data Use the above information to create a model to find the intrinsic value of Betzy Inc. Be sure that if the user changes any inputs, the model appropriately updates. Betzy Inc. Inputs Weight of Debt Preferred Stock Equity Corporate Tax Rate Expected Market Return Risk Free Rate Beta Current Stock Price Dividend Bond Coupon Rate Bond Maturity (years) # of Bond Payments per year Current Bond Price Prior Year Starting Cash Flow Debt Shares Outstanding Assumed Growth Rate First 5 Years FCF Assumed Terminal Growth Rate After 5 Years 35% 20% 45% 40% 13% 4% 1.20 $22.00 $1.50 11% 12 2 $960.00 $185,000 $35,000 250,000 12% 3% Bet Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started