Homework answers / question archive / Instructions This project requires you to apply knowledge you obtain during the course of the semester

Instructions This project requires you to apply knowledge you obtain during the course of the semester

Finance

Share With

Instructions This project requires you to apply knowledge you obtain during the course of the semester. It allows you to examine an actual company by applying the analytical methods discussed in class. There are three parts to the project. In part one, you collect and calculate risk and return measures for your company. In part two, you complete a basic financial statement analysis of the company. In part three, you value the stock. The project is unlike standard problems found in a book since it does not have an “exact” answer. Much of your analysis requires you to make assumptions about the firm and its prospects. In addition, since you are an outsider, you often must try to infer management’s intentions from their actions. I do not expect your output to be “perfect.” I simply expect you to produce an analysis that is logical and free from “obvious mistakes.” To assist you I have provided some sample projects from previous semesters that made 100 on the project. Additionally, I have created three videos that demonstrate how to do the required calculations, and how to react to possible data problems. The final output from the project is a written analysis. It should be organized into three distinct sections corresponding to the three parts of the project. Copies of the completed spreadsheet used in the project should be uploaded with the written report. Note, I am mostly grading your written report, but will use the spreadsheet to verify your analysis. You are to submit two files in D2L. The first is a Word or .pdf file with your written analysis. The second is an Excel (or .xlsx compatible) file with your statistical analysis. You will have a second excel file with your financial analysis, you do not need to upload this file. Please verify that your files are uploaded by the due date. You can choose from the following companies, AAP, CAG, EMR, HPE, LUMN, OLN, SMG, and WAB. You will choose one of the two companies whose first letter is closest to the first letter in your last name. For instance, someone named Flock must choose between EMR and HPE. If your last name begins with a letter that matches the first letter of a ticker symbol, then you can choose among three companies. For instance, a person named Hodges could choose between EMR, HPE, and LUMN. If your last name begins between X and Z, you choose between WAB and AAP. If you should turn in a project not permitted based on your last name, you project will be graded, then half of your score will be deducted. Thus, a score for a permitted project that would be 100 will be a 50 for you. This project is due by April 13. The project will not be accepted after the due date. After the due date, a 0 will be assigned. Part One - Risk and Return To gather needed data, see the tabs in the provided spreadsheet in CourseDen (our Learning Management System). You will be provided with a template EXCEL file. You are to calculate expected return, standard deviation, coefficient of variation, and beta for the firm for which you enter monthly data. PLEASE NOTE THERE IS A VIDEO THAT DEMONSTRATES HOW TO DO THE REQUIRED ANALYSIS. THE VIDEO IS AVAILABLE AT THE COURSE WEBPAGE IN D2L. First, open the template file and then save it under a new name. Next, from the stock price data in the spreadsheet, copy your firm’s closing stock prices into the template file in the column “Stock price (Column B).” Also, from the spreadsheet, copy the closing data for the index you are using into the column “Index value (Column C).” The worksheet will automatically calculate the stock and index monthly returns and will calculate the monthly standard deviation, and coefficient of variation below the columns of returns. To calculate the beta for the firm you must use the Regression function of EXCEL. After copying the new data into the spreadsheet, click on Tools and then on Data Analysis. If you do not see Data Analysis under tools, you must “add-in” Data Analysis (you can find instructions under Help). A list of analysis alternative should appear. Go down the list and click on Regression (this will highlight Regression) and then click on OK. A window should appear for the regression analysis technique. If the regression does not automatically include your data, you will need to list the stock returns as the y variable (Column D), the index returns as the x variable (Column E), and cell “a72” as the output range. Alternately, and a preferred method, you choose a separate Excel sheet as the output range. If using this method, name your new tabs based on the regression such as “HPE versus SP500.” Before printing the worksheet, change the title line at the top of the worksheet to indicate the name of the firm you are analyzing and the index used in this calculation. To print the worksheet, click on File, then click on Print, and then click on OK. The print settings have been preset to ensure that all of the information will print, but this is sometimes lost in the downloading process. You must create a worksheet (and calculate beta) for your company with each of the three indices you choose to use (select from S&P 500, NYSE Composite, NASDAQ COMPOSITE, Russell 1000, Russell 2000, and NYSE World Leaders). Also calculate a beta for the NASDAQ Composite relative to the S&P500 by using the template and putting the NASDAQ values in under “Stock price” and the S&P500 values in under “Index value.” Ultimately, you will create four regressions (your company versus three indices, and NASDAQ versus S&P 500). Report (Items that must be discussed in Part 1) In one paragraph, briefly describe your company. Then, briefly outline your Section 1 results. I prefer you create tables and/or graphs to present your results, then discuss the results. What betas did you calculate using the various indices? Why are they different? Which index do you feel is most appropriate to use when calculating beta for the stocks you are considering (explain your answer)? How do your beta calculations compare with those of the published sources (hint, should be similar to your S&P500, as Yahoo uses 5 years monthly versus the S&P500 in their calculations)? Comparing your company to the NASDAQ index, which has higher: (a) total risk? (b) risk per unit of return? (c) systematic risk? Does your company have a higher expected return than the overall stock market? Why? Be sure to list numbers in your comparisons. Part Two - Financial Analysis Perform a financial analysis of your firm. First, gather financial information and ratios for the firm using the provided spreadsheet. This information is provided in the relevant EXCEL files are located in CourseDen. There is a separate EXCEL file for each company, you need only use the EXCEL file for your company. Report (Items that must be discussed in Part 2) Do a brief market value to book value analysis. Then, include in your analysis a trend analysis using at least three years of data from the balance sheet, income statement, ratios, and statement of cash flows. For the ratios, do both a trend/historical and benchmark/competitive ratio analysis for each of the major ratio categories, commenting on specific ratios. When reviewing your firm’s statement of cash flows, with a brief discussion of their major sources and uses of funds. In this section, include at least four graphs and four tables. I prefer you present numbers in tables or graphs, then discuss these numbers. I do not like having many numbers embedded in paragraphs. The graphs should present multiple items (e.g. include multiple ratios per graph). If you have access to capitaliq.com, then I suggest creating the tables and graphs in capitaliq and exporting to your paper. Otherwise, use the provided spreadsheet to create your tables and graphs. PLEASE Part Three – Stock Valuation Use the constant growth model and supernormal growth model to obtain stock values for your company. Determine the discount rates (expected returns) using the CAPM model (Hint: you’ve already calculated the firms’ betas in part one of the project). For this project, assume the risk free rate is 3.5% and the market risk premium is 5.5% (NOTE, THIS IS DIFFERENT THAN THE NUMBERS USED IN THE SAMPLE PROJECTS, YOU WILL LOSE POINTS IF YOU DO NOT USE 3.5% AND 5.5%). Use analyst’s earnings forecasts from a reputable source (e.g. most likely Yahoo but possibly CapitalIQ, Zacks, Value Line, Etrade, etc.) to determine expected growth rates. (You may use the simplifying assumption that the dividend payout ratio will remain constant. This means that the expected growth rate for earnings will equal the expected growth rate for dividends.) For the constant growth model, use the 5-year earnings growth rate (I suggest you chose between Past 5 years, Next 5 Years, or an average of the two, if using Yahoo Analyst Forecasts) as your proxy for the dividend growth rate. For the supernormal growth model, use the projected growth rate for current and next fiscal year earnings to estimate your growth rates for years one and two and then use the same 5-year historical earnings growth rate you used for the constant growth rate. In the event that both of the 5-year earnings growth rates are higher than required rate of return on the stock, review the various growth rates in your ratio analysis and choose a rate that is lower than the required return. Be sure to list the source of the expected growth rate. Report (Items that must be discussed in Part 3) You must show the inputs and compute a required rate of return using the Capital Asset Pricing Model. You must show your inputs and compute the value of the stock using the Constant Growth Model. You must show your inputs and compute the value of the stock using the Non-Constant Growth Model. After computing the values, compare them to the recent price of the stock. You must state clearly whether your calculations suggest that the company is under-valued or over-valued. You must state an opinion about over or under valued; you will lose points if you do not state a valuation opinion with a brief discussion of your logic. Also, provide a brief discussion regarding your confidence in using these models to value “real-world” companies.