PLEASE HELP ME WITH THIS!!

Spreadsheet Project 1: Calculating the Cost of Capital Due Dec 4, 2015

This project has two parts. In Part One, you will be estimating the betas of three firms using downloaded data and the regression module included in the data analysis tool pack in Excel for this project. In Part Two, you will be calculating the weighted average cost of capital of capital by finding the costs of the individual financing options and the relative weights.

Part One: For part one, you will be calculating the betas of three different: YHOO, HON, D firms using data downloaded from the Yahoo Finance web site. Use data for the 3-year period ending October 31, 2015.

Using Yahoo:

• Log into the Yahoo website: www.yahoo.com
• Click on Finance
• Enter the ticker symbol of the firm in the search box.

• Select Historical Prices under Quotes.
• Use the Start and End Date boxes to enter your custom date range (Nov. 1, 2012 to Oct. 31, 2015).
• Clink on the link Download to Spreadsheet. Click on this link and select Save. Repeat this process for your other firms and the index ^GSPC.

Using the regression analysis module to get the beta:

• Get the data for your firms (they were assigned on the class roll) from the Yahoo Finance website.
• Calculate the daily holding period returns using the formula:

HPR = (Adj Close stock price previous day Adj Close stock price) / (previous day Adj Close stock price)

Ex: Oct 23 HPR = (Oct 23 Adj Close stock price Oct 22 Adj Close stock price) / (Oct 22 Adj Close stock price)

Note 1: Be careful, Yahoo formats the data so that the first observation is the most recent observation!

Note 2: You will have a HPR for each day except for the last observation.

Note 3: Calculate holding period returns for each of your firms and for the S&P 500 index.

Note 4: Use the adjusted close prices.

Note 5: Make sure you are using the closing prices (close to close) so that you are including any information released during the night.

Once you have the HPRs for each of your firms and for the S&P 500 index:

• Select the Data Analysis option under the Tools menu.
• Select the Regression option.
• The y-range will be the HPRs for the firm that you are currently examining.
• The xrange will be the HPRs for the S&P 500 index.
• Select the new worksheet ply option for your output.
• Repeat this analysis for your other firms.

Reading your regression output:

• We are estimating the characteristic line. This line is represented by the formula:
• R-squared is a measure of how well your models fits the data. It is shown in the output as R square.
• The coefficient on the intercept is alpha (a).
• The coefficient on the x-variable is beta (b).
• The t-statistics evaluate the statistical significance of the model components. (A general rule of thumb is that a t of 1.98 or greater implies statistical significance.)

Using Zacks analysis website to find beta:

• Log into Zacks website at http://www.zacks.com/
• At the top of the page, there is an option where you can input the ticker symbol of one of your firms in order to view the Zacks company report on that firm.
• Choose the full company report option. Under the price / volume section of the report, the beta for the firm is provided. Record this in an Excel table along with your regression results (see below.)

Completing part one:

• Create a table in Excel similar in format to the one below and fill in your regression results: (watch your formatting: Show alpha and beta to four decimal places, and r-squared and Zacks beta to two decimal places)

Company	Alpha	Beta	R-squared	Zacks Beta

• For comparison, find the betas calculated by Zacks Investment Research for your companies and enter these into the spreadsheet. Using Word, write the following analysis: How do Zacks numbers compare to yours? Why might they be different (i.e., what may we have done differently than Zacks? What assumptions did we make?)?

Part Two: For part two, you will need to create an Excel spreadsheet to calculate the cost of capital for the Gamma Corporation. What is Gammas WACC (assuming that it does not want to change its capital structure)? You will need to use the following assumptions in your analysis.

There are 5M shares of Gamma common stock outstanding at $40 each. Gamma has a beta of 1.3. Use the CAPM to find the cost of common stock.

The risk-free rate is 5% per year.

Gamma has 2m shares of preferred stock outstanding, carrying a dividend of $1.75 per share, currently trading at $18.

There are currently 200,000 bonds, each with a face value of $1,000, with a coupon rate of 5%. These bonds are trading at 102% of par value ($1,020.) These bonds mature in 10 years. Assume that interest is paid annually.

Gamma's marginal tax rate is 40%

I suggest that you use the function for the cost of debt, not . We are concerned with the REMAINING yield to maturity, that is, the rate of return an investor receives if he purchases the bond today. YIELDMAT calculates the overall YTM from issue date to maturity.

Remember: weights are based on market-value not book-value.

Using the YIELD function in Excel:

• Choose the function option on the insert menu and select the yield function under the financial functions. Note: Your version of Excel may not contain the yield function if it does not contain the data analysis tool pack.
• This function assumes a $100 par while most bonds have a $1,000 par so you will need to adjust your numbers. I.e., if the bond price were $2,000, it would be $200 in this formula: divide by 10.
• Fill in each of the boxes (descriptions are provided below.) Do not hard type numbers; use cell references.
  • Settlement is the bonds settlement date. Assume that this is December 31, 2013.
  • Maturity is the bonds maturity date, December 31, 2020.
  • Hint: When entering dates directly into formulas, use quotes: 12/31/13. Otherwise, Excel will try to divide these numbers. Alternately, use cell references in the formulas that point to cells with the dates in them.
  • Rate is the annual coupon rate.
  • Pr is the current trading price of the bond as if the face value were $100: a bond selling at $2,000 would have a $200 Pr in the Excel formula.
  • Redemption value is the face value of the bond as if the face value were $100: a $1,000 face value bond would be $100 in the Excel formula.
  • Frequency is the number of times payment occurs in a year. Use 1 for this bond.
• The yield which the formula will provide is the before-tax cost of debt.

Return on the market portfolio:

• Assume the return on the market portfolio is 12%.

Formatting:

• I have attached a copy of a weighted average cost of capital spreadsheet; you may use this as a template if you wish. Do not calculate everything in one large step.
• Your final output should be formatted as though you were submitting the information to your boss. Column headings should be aligned/formatted for easy reading. The number of decimal places should be set to an appropriate level. Show percentages as %s. Use the comma and $ formatting options. Widen your columns. Spell check. Unprofessional formatting will result in points being deducted.
• Use cell references in your spreadsheet. You should not have to type a number in more than once.

Printing:

• Turn in a copy of your regression output as well as the Excel tables requested for part one and part two. Turn in formulas for the tables for part two. (Use Ctrl-~ to show formulas.)
• Do NOT turn in print outs of the sheets containing the three years of data for part one.
• Either include a cover sheet with your name and section or place this information in a header/footer printed on each page. Staple your papers together.
• Individual printouts should not span more than one page.
• Failure to follow any of the above will result in a deduction of points.

Electronic copy:

Make sure that you turn in an electronic copy of this project (spreadsheet and Word document) on Blackboard under the assignments link. Failure to turn in an electronic copy will reduce your grade.

Finance 312 Spreadsheet Project 1: Calculating the Cost of Capital Fall 2015 Due Dec 4, 2015 This project has two parts. In Part One, you will be estimating the betas of three firms using downloaded data and the regression module included in the data analysis tool pack in Excel for this project. In Part Two, you will be calculating the weighted average cost of capital of capital by finding the costs of the individual financing options and the relative weights. Part One: For part one, you will be calculating the betas of three different: YHOO, HON, D firms using data downloaded from the Yahoo Finance web site. Use data for the 3-year period ending October 31, 2015. Using Yahoo: Log into the Yahoo website: www.yahoo.com Click on Finance Enter the ticker symbol of the firm in the search box. Select \"Historical Prices\" under \"Quotes\". Use the Start and End Date boxes to enter your custom date range (Nov. 1, 2012 to Oct. 31, 2015). Clink on the link \"Download to Spreadsheet\". Click on this link and select \"Save\". Repeat this process for your other firms and the index ^GSPC. Using the regression analysis module to get the beta: Get the data for your firms (they were assigned on the class roll) from the Yahoo Finance website. Calculate the daily holding period returns using the formula: HPR = (Adj Close stock price - previous day Adj Close stock price) / (previous day Adj Close stock price) Ex: Oct 23 HPR = (Oct 23 Adj Close stock price - Oct 22 Adj Close stock price) / (Oct 22 Adj Close stock price) Note 1: Be careful, Yahoo formats the data so that the first observation is the most recent observation! Note 2: You will have a HPR for each day except for the last observation. Note 3: Calculate holding period returns for each of your firms and for the S&P 500 index. Note 4: Use the adjusted close prices. Note 5: Make sure you are using the closing prices (close to close) so that you are including any information released during the night. Once you have the HPRs for each of your firms and for the S&P 500 index: Select the \"Data Analysis\" option under the \"Tools\" menu. Select the \"Regression\" option. The y-range will be the HPRs for the firm that you are currently examining. The x-range will be the HPRs for the S&P 500 index. Select the \"new worksheet ply\" option for your output. Repeat this analysis for your other firms. Reading your regression output: We are estimating the characteristic line. This line is represented by the formula: k j j j rm j R-squared is a measure of how well your models fits the data. It is shown in the output as \"R square\". The coefficient on the intercept is alpha (). The coefficient on the x-variable is beta (). The t-statistics evaluate the statistical significance of the model components. (A general rule of thumb is that a t of 1.98 or greater implies statistical significance.) Using Zack's analysis website to find beta: Log into Zack's website at http://www.zacks.com/ At the top of the page, there is an option where you can input the ticker symbol of one of your firms in order to view the Zack's company report on that firm. Choose the \"full company report\" option. Under the price / volume section of the report, the beta for the firm is provided. Record this in an Excel table along with your regression results (see below.) Completing part one: Create a table in Excel similar in format to the one below and fill in your regression results: (watch your formatting: Show alpha and beta to four decimal places, and r-squared and Zack's beta to two decimal places) Company Alpha Beta R-squared Zack's Beta For comparison, find the betas calculated by Zack's Investment Research for your companies and enter these into the spreadsheet. Using Word, write the following analysis: How do Zack's numbers compare to yours? Why might they be different (i.e., what may we have done differently than Zack's? What assumptions did we make?)? Part Two: For part two, you will need to create an Excel spreadsheet to calculate the cost of capital for the Gamma Corporation. What is Gamma's WACC (assuming that it does not want to change its capital structure)? You will need to use the following assumptions in your analysis. There are 5M shares of Gamma common stock outstanding at $40 each. Gamma has a beta of 1.3. Use the CAPM to find the cost of common stock. o The risk-free rate is 5% per year. Gamma has 2m shares of preferred stock outstanding, carrying a dividend of $1.75 per share, currently trading at $18. There are currently 200,000 bonds, each with a face value of $1,000, with a coupon rate of 5%. These bonds are trading at 102% of par value ($1,020.) These bonds mature in 10 years. Assume that interest is paid annually. Gamma's marginal tax rate is 40% I suggest that you use the =YIELD function for the cost of debt, not =YIELDMAT. We are concerned with the REMAINING yield to maturity, that is, the rate of return an investor receives if he purchases the bond today. YIELDMAT calculates the overall YTM from issue date to maturity. Remember: weights are based on market-value not book-value. Using the YIELD function in Excel: Choose the \"function\" option on the \"insert\" menu and select the \"yield\" function under the financial functions. Note: Your version of Excel may not contain the yield function if it does not contain the data analysis tool pack. This function assumes a $100 par while most bonds have a $1,000 par so you will need to adjust your numbers. I.e., if the bond price were $2,000, it would be $200 in this formula: divide by 10. Fill in each of the boxes (descriptions are provided below.) Do not hard type numbers; use cell references. o Settlement is the bond's settlement date. Assume that this is December 31, 2013. o Maturity is the bond's maturity date, December 31, 2020. o Hint: When entering dates directly into formulas, use quotes: \"12/31/13\". Otherwise, Excel will try to divide these numbers. Alternately, use cell references in the formulas that point to cells with the dates in them. o Rate is the annual coupon rate. o Pr is the current trading price of the bond as if the face value were $100: a bond selling at $2,000 would have a $200 Pr in the Excel formula. o Redemption value is the face value of the bond as if the face value were $100: a $1,000 face value bond would be $100 in the Excel formula. o Frequency is the number of times payment occurs in a year. Use 1 for this bond. The yield which the formula will provide is the before-tax cost of debt. Return on the market portfolio: Assume the return on the market portfolio is 12%. Formatting: I have attached a copy of a weighted average cost of capital spreadsheet; you may use this as a template if you wish. Do not calculate everything in one large step. Your final output should be formatted as though you were submitting the information to your boss. Column headings should be aligned/formatted for easy reading. The number of decimal places should be set to an appropriate level. Show percentages as %'s. Use the comma and $ formatting options. Widen your columns. Spell check. Unprofessional formatting will result in points being deducted. Use cell references in your spreadsheet. You should not have to type a number in more than once. Printing: Turn in a copy of your regression output as well as the Excel tables requested for part one and part two. Turn in formulas for the tables for part two. (Use Ctrl-~ to show formulas.) Do NOT turn in print outs of the sheets containing the three years of data for part one. Either include a cover sheet with your name and section or place this information in a header/footer printed on each page. Staple your papers together. Individual printouts should not span more than one page. Failure to follow any of the above will result in a deduction of points. Electronic copy: Make sure that you turn in an electronic copy of this project (spreadsheet and Word document) on Blackboard under the assignments link. Failure to turn in an electronic copy will reduce your grade. Weighted Average Cost of Capital Calculations Shares of Common Stock Outstanding Price Per Share 1,000,000 $75.00 Shares of Preferred Stock Outstanding Price Per Share 500,000 $20.00 Bonds Outstanding Price per Bond 100,000 $990.00 Value: Common Stock Preferred Stock Debt Total $75,000,000 10,000,000 99,000,000 $184,000,000 Weights: Common Stock Preferred Stock Debt Total 40.76% 5.43% 53.80% 100% Cost of: Common Stock: Risk free rate Beta Return on Market Portfolio Cost of Common Stock 8.00% 2.00 18.00% 28.00% Preferred Stock: Dividend per Share Cost of Preferred Stock $3.00 15.00% Debt: Today's Date Maturity Date Price Par Coupon Rate Frequency of Coupon Payments per year Cost of Debt (pretax) Tax Rate Cost of Debt (after tax) WACC 1/23/2012 12/31/2016 $99 $100 14.00% 2 14.28% 28.00% 10.28% 17.76% Fezanto@gmail.com That is my private email you can reach me we talk I can tutor you well you score A plus in all your Accounting, Economics and Finance courses. Thanks once again for trusting in me. Am familiar with all exams platforms kindly reach me in case you need any help. Kind Regards, Festus