use excel

Use the following information to answer the questions below. Show all work in your Excel worksheet. Place the company name at the top of the worksheet. Midland Energy, a private firm, has hired you to estimate their company's beta estimate to use when developing project discount rates. From Google Finance you have obtained the following equity betas for publicly traded firms in the same line of business. SHOW ALL OF YOUR WORK, not just the answer. Market Value of Market value Company Equity beta Debt equity Pioneer Petroleum 2.31 $ 5,700 $ 9,350 Anadarko Energy 1.86 $ 13,525 $ 11,300 1 Estimate the asset beta for each of the peer firms. What is the asset beta of Anadark? SHOW ALL STEPS in your calculation. 2 To account for size differences, Midland Energy instructs you to calculate a weighted average asset beta by using weights equal to the % market value of equity. SHOW ALL STEPS in your calculation. 3 Midland Energy has a target D/E ratio of 1.0, which does not match either of the peer firms given differences in business maturity. Estimate Midland's equity beta. SHOW ALL STEPS in your calculation. 4 You estimate from current market data that the Duff & Phelps market return is 8%, the 20 year Treasury bond is yielding 2.3%. What is your estimate for the required return on equity