Help needed! I have been really struggling with this project.

AutoSave . Off) H Project 1_2020 Fall - Saved to this PC O Search Andrew Warren AW X File Home Insert Draw Design Layout References Mailings Review View Help Share Comments & Cut Paste [ Copy Times New Rome ~ 14 ~ A" A Aa Ap AaBbCcDd AaBbCcDd AaBbCc AaBbCct Ad B AaBbCCD AaBbCcDd Find BIU ab X2 X' A LA 1 Normal 1 No Spac.. Heading 1 Heading 2 Title Subtitle Subtle Em.. Gc Replace Select Dictate Sensitivity Editor Format Painter Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor The t-statistics evaluate the statistical significance of the model components. Part Two: For part two, you will need to create an Excel spreadsheet that calculates the Completing part one: weighted average cost of capital for the Alpha Corporation. I have attached a copy of a weighted average cost of capital spreadsheet; you may use this as a template if you wish. . Create a table in Excel formatted similar to the one below and fill in your Do not calculate everything in one large step. You will need to use the following regression results: (watch your formatting: Show alpha and beta to four decimal assumptions in your analysis. places, and r-squared and Yahoo Finance's beta to two decimal places) There are 9M shares of Alpha common stock outstanding at $55 each. Alpha has a beta of 1.25. Use the CAPM to find the cost of common stock. Company Alpha Beta R-squared Yahoo Finance's Beta The risk-free rate is 2.10%, which is the current yield on a 10-year Treasury. o The expected return on the market portfolio is 10%. Alpha has 2m shares of preferred stock outstanding, carrying a dividend of $2.25 per share, currently trading at $27. There are currently 200,000 bonds, each with a face value of $1,000, with a coupon rate of 6.75%. These bonds are trading at 109.5% of par value (i.e., For comparison, find the betas calculated by Yahoo Finance for your companies $1,095). The bonds were originally issued with a 10 year maturity. Assume and enter these into the spreadsheet. Please provide formal answers to the that interest is paid annually. following questions in your excel file: . Alpha's marginal tax rate is 40% (1) How do Yahoo Finance's numbers compare to yours? (2) Please interpret your results. (e.g., systematic risk exposures of the stocks) . I suggest that you use the =YIELD function for the cost of debt, not =YIELDMAT. We are concerned with the REMAINING yield to maturity, Required Output for Part 1: 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. Excel file you will need to submit via Canvas (Assignments, Project one): Remember: weights are based on market-values, not book-values. . You will need to provide the daily raw data for each firm and the index. In addition, make sure to provide the monthly HPRs (with formulas) that you used in your beta calculations and the regression output. You can separate this data Using the YIELD function in Excel into tabs within the same Excel file, or formally combine the HPRs and . Choose the "function" option on the "insert" menu and select the "yield" function regression results in to one large file. However, if you combine the data please under the financial functions. Alternatively, you can simply code "=Yield(...)" make sure that it will be easy to navigate. I should be able to easily verify the within the cell. calculations for your HPRs and replicate your regression results. If I'm unable to replicate your results then you will lose a significant amount of points. . Note: Your version of Excel may not contain the yield function if it does not contain the data analysis tool pack. Refer to the end of this document if you Include a cover page with your name, assignment description (i.e., "Project 1"), currently don't have the "Data Analysis" add-in installed. date, and section number. . The Yield function assumes a $100 par while most bonds have a $1,ooo par so Your file name should be: projecti_your last name.your section number.xsv. you will need to adjust your numbers accordingly (i.e., if the bond price were e.g., for Weike Xu, my file name should be projecti_xu_oo4.xls. $2,000, it would be $200 in this formula). . Fill in each of the boxes (descriptions are provided below). Do not hard type numbers; use cell references. Page 3 of 6 1666 words DX LO Focus - + 70% Type here to search W X A 11:33 AM 10/26/2020File Home Insert Draw Page Layout Formulas D Data Review View Developer Help Fuzzy Lookup Share Comments B12 :X Y CO E G H M N Q R S T U V W X Y Z AA AB AC AD AE AF AG AH C O -J O CA WN - Weighted Average Cost of Captial Calculations Share of Common Stock Outstanding Price Per Share Shares of Preferred Stock Outstanding 7 Price Per Share Bonds Outstanding 10 Price Per Bond 11 12 13 Common Stock 14 Preferred Stock 15 Debt 16 Total 17 18 Weights: 19 Common Stock 20 Preferred Stock 21 Debt 22 24 Cost of: 25 26 Risk Free Rate 27 Beta 28 Return on Market Portfolio 29 Cost of Common Stock 30 31 Preferred Stock: Dividend Per Share 34 83 Cost of Preferred Stock 35 Debt: 36 Today's Date 37 Maturity Date 38 Price 39 Par 40 Coupon Rate 41 Frequency of Coupon Payments Per Year 42 Cost of Debt [ Pretax) 43 Tax Rate 4 Cost of Debt ( After Tax) 45 46 WACC 47 48 49 50 53 Sheet1 + D LO + 60% Type here to search x 1:49 AM 10/26/2020AutoSave . Off) H Project 1_2020 Fall - Saved to this PC O Search Andrew Warren AW X File Home Insert Draw Design Layout References Mailings Review View Help Share Comments & Cut AaBbCcDd AaBbCcDd AaBbC( AaBbCct Ad B AaBbCCD AaBbCcDd Paste [ Copy Times New Rome ~ 14 ~ A" A Aa Ap Find Format Painter BIUvab X X A LA 1 Normal 1 No Spac.. Heading 1 Heading 2 S'c Replace Title Subtitle Subtle Em... Select Dictate Sensitivity Editor Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor FIN 3120, Fall 2020 HPR = (Ending stock price - beginning stock price) / (beginning stock price) Spreadsheet Project 1: Calculating the Cost of Capital Note 1: Be careful, Yahoo Finance formats the data so that the first observation listed is the most recent, so you will have to filter the data in Due November 1, 2020 Midnight order to start with the earliest date first. Note 2: Calculate holding period returns for each of your firms and for the This project has two parts. In Part One, you will be estimating the betas of two firms using S&P 500 index. downloaded data and the regression module included in the data analysis tool pack in Note 3: Make sure you calculate monthly HPR. For instance, the Excel. In Part Two, you will be calculating the weighted average cost of capital by finding HPR for January, 2017 is calculated using the following formula: (the the costs of the individual financing options and the relative weights. Note that your final closing price on January 31, 2017 - the closing price on December 31, output should be formatted as though you were submitting the information to your boss. 2016)/ the closing price on December 31, 2016. Do not use the closing Column headings should be aligned/formatted for easy reading. The number of decimal price adjusted for dividends and splits ("Adj Close"), the opening price places should be set to an appropriate level. Make sure to show percentages as %'s, use the comma and $ formatting options, widen your columns, and spell check. ("Open"), intraday high ("High") or intraday low ("Low") prices. Unprofessional formatting will result in points being deducted. Part One: For part one, you will be calculating the betas of three different firms using Once you have the HPRs for each of your firms and for the S&P 500 index: prior 36 months historical price data downloaded from Yahoo Finance. Use data for the 3-year period ending September 30, 2020. In addition, compare your betas with Select the "Data Analysis" option under the "Tools" menu. Refer to the end of this these from Yahoo Finance. document if you currently don't have the "Data Analysis" add-in installed . Select the "Regression" option. Using Yahoo: . The y-range will be the HPRs for the firm that you are currently examining. . Log into the Yahoo Finance website: www.finance.yahoo.com . The x-range will be the HPRs for the S&P 500 index. . Enter the ticker symbol of the firm in the "Quote Lookup" box . Select the "new worksheet ply" option for your output. o Note that I will be providing the tickers of your assigned firms via . Repeat this analysis for your other firms Blackboard shortly. Note that if you have 2008, 2009, or 2011 Excel for Mac then you may not Select "Historical Data". have access to the "Data Analysis" add-in. In this case you may have to use Windows based computer (or newer version of Excel) to complete this . Use the Start and End Date boxes to enter your custom date range. portion of the project . Scroll to the bottom of the page and click on the link "Download to Spreadsheet". Click on this link and select "Save". Repeat this process for your other firms and the market index (S&P 500). Reading your regression output: We are estimating the characteristic line. This line is represented by the formula: * =a +Br_+ Using the regression analysis module to get the beta: Obtain the data for your firms (I will be listing firm assignments on Canvas R-squared is a measure of how well your models fits the data. It is shown in the output as "R square". shortly). Calculate the monthly holding period returns using the formula: . The coefficient on the intercept is alpha, which is the abnormal return. . The coefficient on the x-variable is beta. Page 1 of 6 1666 words CX FO " Focus 70% Type here to search O W X A 1:32 AM 10/26/2020AutoSave . Off) H Project 1_2020 Fall - Saved to this PC O Search Andrew Warren AW X File Home Insert Draw Design Layout References Mailings Review View Help Share Comments & Cut [ Copy Times New Rome ~ 14 ~ A" A Aa Ap AaBbCcDd AaBbCcDd AaBbCc AaBbCct Ad B AaBbCCD AaBbCcDd Find Paste BIUvab X x A LA 1 Normal 1 No Spac... Heading 1 Heading 2 Title Subtitle Subtle Em.. S' Replace Select Dictate Sensitivity Editor Format Painter Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor Settlement is the bond's settlement date (i.e., the date after the issue date when the security is traded to the buyer). Assume that this is September 30, 2020. Other versions of Excel: o Maturity is the bond's maturity date, December 31, 2022. Follow a similar method to what is described above in order to install the Analysis ToolPack o Hint: When entering dates directly into formulas, use quotes (i.e., "12/31/15"). Otherwise, Excel will try to divide these numbers. Alternately, use cell references in the formulas that point to cells with the dates (in standard format without quotations) 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 (e.g., 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 (e.g., 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. . Note that the yield which the formula will provide is the before-tax cost of debt. Required Output for Part 2: . Excel file you will need to submit via Canvas You will need to provide the Excel file containing your WACC calculation table. Make sure to add this to the Excel file from Part 1 so that you are only uploading one Excel file to Canvas. I will deduct points if you upload more than one Excel file to Canvas. Use cell references in your spreadsheet. I should easily be able verify the validity of your WACC calculation and quickly solve for the WACC when making small changes to the assumptions of the model. I should not have to type a number in more than once in order to solve for the WACC. For example, if I make a change to the firm's beta then your template should be able to automatically calculate the new WACC. If missing the "Data Analysis" add-in, which contains the "regression" tool: For Excel 2013: Click on the "File" tab, click "Options", click the "Add-Ins" category . In the "Manage" box, choose "Excel Add-ins" and click "Go..." In the "Add-ins" box, select the "Analysis ToolPack" and click "OK" Page 5 of 6 1666 words X FO Focus 70% Type here to search O 8 0 9 W X A 11:34 AM 10/26/2020C clemson.instructure.com/courses/106255/files/8199831?module_item_id=1781274 h. D Q A S2008-FIN-3120-001-81471 > Files > Stocks_2020_Fall.docx Fall 2020 Account Home Stocks_2020_Fall.docx Announcements Download Stocks_2020_Fall.docx (17.2 KB) Dashboard Grades Page of 2 O - ZOOM + 9 Courses Modules Zoom Calendar BABA JPM Quizzes Sewell, Caleb Steven NFLX Shipman, Andrew W BABA NFLX JPM Eh Collaborations Small, Jim BABA NFLX JPM Inbox Smith, Bryce L BABA NFLX JPM Chat Smith, Emma BABA NFLX JPM Syllabus Wallace, Kevin BABA NFLX JPM History Warren, Andrew C BABA NFLX JPM ? Assignments Wells, Noah Scott BABA NFLX JPM Help People Westmoreland, Jason BABA NFLX JPM Wood, Harrison BABA NFLX JPM Course Evaluation Wood, Jackson T BABA NFLX JPM Wood, James David BABA NFLX JPM Previous Next K Type here to search O 9 W X 11:46 AM 10/26/2020 E