The managers of Rocky Mountain Motors (RMM) are considering the purchase of a new tract of land that will be held for one year. The price of the land is $10,000. RMMs current capital structure in terms of market values is shown below. Any new financing will be raised in the same proportions. Source Price per Unit Units Debt $904.53 400 Preferred $100.00 1,000 Common 70.00 10,000 Total a. (Columns A C) Set this up in Excel. b. (Column D) Add a column showing the total market value for each source. c. (Column E) Calculate the % of the market value of each source with respect to the total market value in the column after that. (Dont forget to label the column.) d. (Column F) The After-tax Cost for the three sources are 7.00%, 10.00%, and 12.00%, respectively. Place this in the next column and at the bottom of that calculate the WACC as the weighted average of the After-tax costs. Use =SUMPRODUCT(E2:E4, F2:F4). e. Heres some additional data on the bonds. Enter this in Excel. Tax Rate = 40% Coupon Rate = 10% Face Value = $1,000 Maturity = 10 f. Based on the above data and the price, calculate the pre-tax cost of debt using the RATE function. g. Now calculate the after-tax cost of debt as the pre-tax cost of debt multiplied by 1-tax rate. It should be the same as the cost of debt given above. h. Heres some additional data on the preferred stock. Enter this. Dividend = $10.00 i. Using the dividend and the price, calculate the cost of preferred stock. This should match the cost given above. j. Heres some additional data on common stock. Enter this. D0 = $3.96 Growth rate = 6% k. Using the above information and the price, calculate the cost of common stock. It should match the cost given above. l. Suppose theres a flotation cost involved with the issue of any securities, i.e. if the company wants to sell bonds, preferred stock, or common stock in the market, it will have to pay an additional cost to hire an investment bank to help it through the process. Enter the following: Flotation cost for bonds = 1% Flotation cost for preferred stock = 2% Flotation cost for common stock = 5% m. Repeat steps g, i, and k. For each of those steps, reduce the price used by the corresponding flotation cost. For example, the price for bonds should be reduced by 1% in the rate formula and so on. (As always please do NOT hard code the 1% in the formula. Use a cell reference.)