Pleaseanswerthefollowing12questionsinexcel
Please answer all questions and show work in excel. Thank you.
1. Federated Investorsâ€™ beta is 1.25, and it has paid a $1.00 dividend for many years. If its current price is $26.52, is it over or under valued?
2. Use the information below to find Connecticut Waterâ€™s intrinsic value. If its current price is $57.00, is it over or under valued?
Beta 
0.65 
2017 dividend 
$2.20 
Growth rate 
0.045 
3. Use the information below to find Connecticut Waterâ€™s intrinsic value. Forecast its cash flows for 2018 to 2020. If its current price is $57.00, is it over or under valued?
2017 
2016 
2015 
2014 

EPS 
$2.20 

DPO 
0.53 
0.54 
0.54 

ROE 
0.11 

Beta 
0.65 

P/E 
23.3 
17.6 
17.5 
4. Use the twostage growth model and the information below to determine whether Brooks Automation is over or under valued if its current price is $20.88. Forecast the companyâ€™s cash flows for 2018 to 2020.
2017 
2018 
2019+ 

Beta 
1.20 

ROE 
0.13 
0.12 

DPO 
0.36 
0.37 

Dividend 
$0.40 
5. Federated Investorsâ€™ has a 2017 EPS of $2.00. Its most recent P/Es are 14.5, 20.0, and 20.8. Use the P/E as an earnings multiplier model to find the stockâ€™s intrinsic value. If it is currently selling for $26.52, is it under or over valued?
6. Federated Investors has a beta of 1.25, and it has paid a $1.00 dividend for many years. If its current P/E is 13.1, is it over or under valued?
7. Connecticut Waterâ€™s beta is 0.65, its DPO is 0.54, and it has a 0.045 growth rate. If it has a variable dividend and its current P/E is 25.9, is it over or under valued?
8. Valuing Bonds: Microhard has issued a bond with the following characteristics:
Par: $1,000
Time to maturity: 23 years
Coupon Rate: 7 percent
Semiannual payments
Calculate the price of this bond if the YTM is:
a. 7 percent
b. 9 percent
c. 5 percent
9. Bond Yields: Skolits Corp. issued 15year bonds two years ago at a coupon rate of 5.1 percent. The bonds make semiannual payments. If these bonds currently sell for 105 percent of par value, what is the YTM?
10. Valuing Bonds: Even though most corporate bonds in the United States make coupon payments semiannually, bonds issued elsewhere often have annual coupon payments. Suppose a German company has a bond outstanding with a par value of â‚¬1,000, 16 years to maturity, and a coupon rate of 4.7 percent paid annually. If the yield to maturity is 3.4 percent, what is the current price of the bond?
11. Even though most corporate bonds in the united states make coupon payments semiannually, bonds issued elsewhere often have annual coupon payments. Suppose a German company issues a bond with a par value of â‚¬1,000, 16 years to maturity, and a coupon rate of 4.7 percent paid annually. If the yield maturity 3.4%, what is the current price of the bond?
12. Bond Yields: Bonino Software has 6.4% coupon bonds on the market with 11 years to maturity. The bonds make semiannual payments and currently sell for 108 percent of par. What is the current yield on the bonds? The YTM? The effective annual yield?