Pls include excel references in solution!

Stock & Bond Excel Assignment Advanced Financial Management 26 27 Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas...only use cell references to the input data. I will change the input data in your problem to check alternate solutions. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. 1) a. What is the price of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.85% and is yielding 5.25%? 28 29 30 31 32 33 34 35 361 37 b. What is the YTM of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.85% and price of 973.47? 2) Coffee shop chain Java the Hut just paid a $2.70 dividend. There are two possible pricing scenarios: a. Scenario 1: You expect the stock will grow at 7% per year forever. Assuming 13.5% required return, what is the value of Java's stock? b. Scenario 2: For the next two years, you think it will grow by 12% a year, getting it back to its pre-recession level. After that, you expect it to grow at 5% a year forever. Assuming 13.5% required return, what is the value Java's stock? 1) Par Years Coupon YTM Price Price YTM 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 SC DIO) G Reg Rtn Value 2) a) b) DIO) G1 G2 Reg Ret Value Value