Answered step by step
Verified Expert Solution
Link Copied!

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
image text in transcribed
image text in transcribed
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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Finance questions

Question

What are the challenges associated with tunneling in urban areas?

Answered: 1 week ago

Question

What are the main differences between rigid and flexible pavements?

Answered: 1 week ago

Question

What is the purpose of a retaining wall, and how is it designed?

Answered: 1 week ago

Question

How do you determine the load-bearing capacity of a soil?

Answered: 1 week ago

Question

what is Edward Lemieux effect / Anomeric effect ?

Answered: 1 week ago

Question

=+For a different audience? In another tone of voice?

Answered: 1 week ago

Question

=+Can it illicit audience participation?

Answered: 1 week ago

Question

=+Create an open dialogue among users?

Answered: 1 week ago