Please use excel spreadsheet and write formulas that are needed to get answers, thank you!
4 Higgs Bassoon Corporation is a custom manufacturer of bassoons and other 5 wind instruments. Its current value of operations, which is also its value of debt 6 plus equity, is estimated to be $200 million. Higgs has $110 million face value, 7 zero coupon debt that is due in 3 years. The risk-free rate is 5%, and the 8 standard deviation of returns for similar companies is 60%. The owners of Higgs 9 Bassoon view their equity investment as an option and would like to know the 10 value of their investment. 11 12 a. Using the Black-Scholes Option Pricing Model, how much is the equity worth? 13 14 Black-Scholes Option Pricing Model 15 Total Value of Firm 200.00 this is the current value of operations 16 Face Value of Debt 110.00 17 Risk Free rate 5% 18 Maturity of debt (years) 19 Standard Dev. 60% this is sigma-also known as volatility 20 d1 use the formula from the text 21 d2 use the formula from the text 22 N(d) use the Normsdist function in the function wizard 23 N(D2) 24 Call Price = Equity Value million 25 26 b. How much is the debt worth today? What is its yield? 27 28 Debt value = Total Value - Equity Value = million 3 29 Debt yield - 30 31 c. How much would the equity value and the yield on the debt change if Fethe's 32 management were able to use risk management techniques to reduce its 33 volatility to 45 percent? Can you explain this? 34 35 Equity value at 60% volatility million 36 Equity value at 45% volatility million 37 Percent change million 38 39 40 d. Graph the cost of debt versus the face value of debt for values of the face value from $0.5 to $8 million. 41 Cost of Debt 42 Face Value of Debt hint: use a data table 43 10 20 45 30 46 40 47 50 48 60 49 70 50 80 51 90 52 100 53 110 44 54 120 55 130 56 140 57 150 58 160 59 60 61 62 b. Graph the values of debt and equity for volatilities from 0.10 to 0.90 when the face value of the debt is $2 million. 63 Value of Debt Value of Equity 64 Volatility Face Value of Debt Volatility Face Value of Debt 65 110.00 110.00 66 0.1 67 0.2 0.2 68 0.3 0.3 69 0.4 0.4 70 0.5 0.5 71 0.6 0.6 72 0.7 0.7 73 0.8 0.8 74 0.9 0.9 75 76 77 78 0.1 4 Higgs Bassoon Corporation is a custom manufacturer of bassoons and other 5 wind instruments. Its current value of operations, which is also its value of debt 6 plus equity, is estimated to be $200 million. Higgs has $110 million face value, 7 zero coupon debt that is due in 3 years. The risk-free rate is 5%, and the 8 standard deviation of returns for similar companies is 60%. The owners of Higgs 9 Bassoon view their equity investment as an option and would like to know the 10 value of their investment. 11 12 a. Using the Black-Scholes Option Pricing Model, how much is the equity worth? 13 14 Black-Scholes Option Pricing Model 15 Total Value of Firm 200.00 this is the current value of operations 16 Face Value of Debt 110.00 17 Risk Free rate 5% 18 Maturity of debt (years) 19 Standard Dev. 60% this is sigma-also known as volatility 20 d1 use the formula from the text 21 d2 use the formula from the text 22 N(d) use the Normsdist function in the function wizard 23 N(D2) 24 Call Price = Equity Value million 25 26 b. How much is the debt worth today? What is its yield? 27 28 Debt value = Total Value - Equity Value = million 3 29 Debt yield - 30 31 c. How much would the equity value and the yield on the debt change if Fethe's 32 management were able to use risk management techniques to reduce its 33 volatility to 45 percent? Can you explain this? 34 35 Equity value at 60% volatility million 36 Equity value at 45% volatility million 37 Percent change million 38 39 40 d. Graph the cost of debt versus the face value of debt for values of the face value from $0.5 to $8 million. 41 Cost of Debt 42 Face Value of Debt hint: use a data table 43 10 20 45 30 46 40 47 50 48 60 49 70 50 80 51 90 52 100 53 110 44 54 120 55 130 56 140 57 150 58 160 59 60 61 62 b. Graph the values of debt and equity for volatilities from 0.10 to 0.90 when the face value of the debt is $2 million. 63 Value of Debt Value of Equity 64 Volatility Face Value of Debt Volatility Face Value of Debt 65 110.00 110.00 66 0.1 67 0.2 0.2 68 0.3 0.3 69 0.4 0.4 70 0.5 0.5 71 0.6 0.6 72 0.7 0.7 73 0.8 0.8 74 0.9 0.9 75 76 77 78 0.1
