Spreadsheet of a case study and short paper
Capital Budgeting Basics, Build a Model INSTRUCTIONS FINC 620 - FINANCIAL MANAGEMENT CASE STUDY PROJECT INSTRUCTIONS FOR THIS EXCEL FILE PLEASE ADHERE TO THESE INSTRUCTIONS TO COMPLETE THIS EXCEL FILE FOR THE CASE STUDY PROJECT: 1BE SURE TO PUT THE NAME OF THE PERSON COMPLETING THE TAB AT THE TOP OF THE TAB IN THE SPACE PROVIDED. 2EACH MEMBER OF THE TEAM MUST COMPLETE ONE FILE TO EARN UP TO THE 35 POINTS POSSIBLE. 3USE EXCEL FORMULAS TO OBTAIN INFORMATION FROM THE OTHER TABS 4COMPLETE ALL OF THE YELLOW HIGHLIGHTED CELLS WITH FORMULAS FROM OTHER PLACES IN THE EXCEL FILE. 5THERE ARE HIDDEN (RATHER THAN DELETED) ROWS TO MAINTAIN THE FORMULAS IN THE MODEL. PLEASE DO NOT OPEN THE HIDDEN ROWS AS THAT INFORMATION NEED NOT BE COMPLETED. 6WRITE YOUR FULL ESSAY (FIVE-PARAGRAPH FORMAT) IN A WORD FILE. IT CAN LATER BE ADDED TO THE TEAM REPORT AND THEN ALL SAVED AS AN ADOBE (.pdf) FILE. A. Ch 6 - Returns & EMH Chapter 6:CALCULATE ACTUAL AND EXPECTED RETURN ON YOUR COMPANY'S STOCK AND ANALYZE EFFICIENT MARKET HYPOTHESIS Completed by NaTasha Taylor POINTS POSSIBLEPOINTS EARNEDCOMMENTS 35 points total0.00 A. Obtain data from finance.yahoo.com for The Clorox Company (CLX) and the S & P 500 Index to calculate annual returns over the past five years for the company and the S & P 500 as the Market Index, and then calculate average returns over the five-year period. (NOTE: Six years of data are needed to result in five years of returns). 5 pointsInsert price data for your company and level data for the S & P 500 Index from finance.yahoo.com in the space below: The Clorox Company (CLX)Market Index YearStock PriceDividend(Includes Dividends) 2019$ 156.27$ 1.063,224.00 2018$ 143.62$ 0.962,704.50 2017$ 133.38$ 0.842,825.75 2016$ 110.11$ 0.802,274.50 2015$ 115.52$ 0.771,930.00 2014$ 92.99$ 0.741,988.50 NOTE 1: Use December 31 (or last trading day of the year) for the stock price and Market Index for each year. NOTE 2: Sum all of the dividends for the year to capture all dividends paid per share for the year. NOTE 3: To get annual stock prices (or levels for the S & P 500 Index, go to finance.yahoo.com and tyoe in the ticker symbol for your company. Once on the company's page, click on "Historical Prices". Set the terms to "Annual" and the dates for each year of the table. Select the Closing Price data. Select the Dividend data for each year. Repeat for the S & P 500 Index for each year and complete that portion of the table. Remember that the dividends are already captured in the Market Index levels. Using the data collected above, calculate the rates of return for your company and the index: (Hint: Remember, returns are calculated by subtracting the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the index. Also, you cannot calculate the rate of return for 2014 because you do not have 2013 data.) The Clorox Company (CLX)S & P 500 Index (i.e. Market Return) Actual ReturnExpected ReturnActual ReturnExpected Return 20199.5%19.2% 20188.4%-4.3% 201721.9%24.2% 2016-4.0%17.8% 201525.1%-2.9% 5 pointsAverage Actual Return12.2%4.3%Return on the Market 10.8%10.8% (From cell F122)(From cell F118) Note: To get the average, you could get the column sum and divide by 5, but you could also use the function wizard, fx. Click fx, then statistical, then Average, and then use the mouse to select the proper range. Do this for your company and then copy the cell for the Market Index. b. Calculate the standard deviation of the returns for Goodman, Landry, and the Market Index. (Hint: Use the sample standard deviation formula given in the chapter, which corresponds to the STDEV function in Excel.) Use the function wizard to calculate the standard deviations. GoodmanLandryIndex Standard deviation of returns13.3%ERROR:#DIV/0!14.5% On a stand-alone basis, it would appear that Goodman is the most risky, Landry the least risky. c. Construct a scatter diagram graph that shows Goodman’s and Landry’ returns on the vertical axis and the Market Index’s returns on the horizontal axis. It is easiest to make scatter diagrams with a data set that has the X-axis variable in the left column, so we reformat the returns data calculated above and show it just below. YearIndexGoodmanLandry 2018-4.3%8.4%0.0% 201724.2%21.9%0.0% 201617.8%-4.0%0.0% 2015-2.9%25.1%0.0% 2014ERROR:#REF!ERROR:#REF!ERROR:#REF! To make the graph, we first selected the range with the returns and the column heads, then clicked the chart wizard, then choose the scatter diagram without connected lines. That gave us the data points. We then used the drawing toolbar to make free-hand ("by eye") regression lines, and changed the lines color and weights to match the dots. It is clear that Goodman moves with the market and Landry moves counter to the market. So, Goodman has a positive beta and Landry a negative one. d. Estimate Goodman’s and Landry’s betas as the slopes of regression lines with stock returns on the vertical axis (y-axis) and market return on the horizontal axis (x-axis). (Hint: use Excel’s SLOPE function.) Are these betas consistent with your graph? Goodman's beta =ERROR:#REF! Landry' beta =ERROR:#REF! B. Calculate the Expected Return using the Capital Asset Pricing Model (CAPM): The risk-free rate on long-term Treasury bonds is 3.00% as the risk-free rate. Obtain the Return on the Market from Part A and your company's beta from finance.yahoo.com. 5 pointsReturn on the Market (Rm) = 10.812%Your company's beta = 0.17 Risk-free rate (Rrf) =3.000%Market beta = 1.00 5 pointsCalculate the Expected Return on the Market: Expected return on market =Risk-free rate (Rrf)+Beta xMarket risk premium(NOTE: Market risk premium = Return on the Market - Risk-free rate) =3.000%+1.007.800% Expected return on market =10.800% (To cell J44) 5 pointsCalculate the Expected Return on Equity for your company: The Clorox Company (CLX) Expected return on Equity = 3.000%7.800%x0.170 =4.326% (To cell F44) Summary: How do the actual returns for your company from Part A compare to the expected return (using CAPM) for your company from Part B? Does this "test" result in accepting or rejecting the Efficient Market Hypothesis? (NOTE: If actual return is greater than expected return, then we must reject the Efficient Market Hypothesis (EMH). 10 pointsWrite an essay to explain to the Board of Directors your conclusions about whether to accept or reject the Efficient Market Hypothesis. (NOTE: WRITE YOUR ESSAY IN A WORD FILE OR GOOGLE DOC TO BE INCLUDED IN THE TEAM REPORT). Stock Returns Vs. Index Goodman0.327609638389656071.2179751566247843E-20.349072898803757140.148466933700802660.19046148441130140.24762765476728427-4.1616161616161655E-20.627402355858648562.9308323563892159E-20.60926573426573416Landry0.327609638389656071.2179751566247843E-20.349072898803757140.148466933700802660.1904614844113014-1.045251752708741E-20.13223027485300159-0.10037261294829997-4.1111111111111617E-30.11658495755111807Index Returns Stocks' Returns B. Ch 9 - Cost of Capital Chapter 9:CALCULATE WACC FOR YOUR COMPANY POINTS POSSIBLEPOINTS EARNEDCOMMENTSCompleted by Ronisha Hammonds 35 points total0.00 Calculate WACC for The Clorox company INPUTS TO BE USED IN THE MODEL (NOTE: See the Income Statement on finance.yahoo.com for Sales and Tax Rate data) P0 = Price of common stock on 12/31/2019$156.27(NOTE: From Chapter 6 tab; Use Excel formula to obtain) Ppf = Price of preferred stock on 12/31/2019(NOTE: From Chapter 6 tab; Use Excel formula to obtain) 1 pointDpf = last dividend on preferred stock, if any 2019 Sales2018 Sales 2 pointsg = growth rate based on change in Sales1%Use the 2019 Income Statement on www.finance.yahoo.com to obtain data Total Revenue (or Sales) to calculate this growth. Input the data in the table to the right. Calculate growth rate in cell F15 as [(2019 Sales - 2018 Sales)/ 2018 Sales] using an Excel fomula in cell F16.$ 6,210,000,000,000.00$ 6,120,000,000,000.00 B-T rd10%(Given for simplicity) your company's beta0.17(NOTE: From Chapter 6 tab; Use Excel formula to obtain) Market risk premium, RPM7.8%(NOTE: From Chapter 6 tab; Use Excel formula to obtain) Risk free rate, rRF3.0% Capital Structure Table:Market Values of Capital 2 pointsTarget capital structure from debt (or Wd)Use the 2019 Balance Sheet data on www.finance.yahoo.com to obtain the data to complete the table to the right. Remember that the sum of the weights MUST total 100%. Use Excel formulas to calculate the weights from the table data in cells F22, F23, and F24.Total Long-term Debt$ 2,288,000.00 2 pointsTarget capital structure from preferred stock (or Wp)Total Preferred Stock$ 159,000.00 2 pointsTarget capital structure from common stock (or We)Total Common Stock$ 159,000.00 Total Capital Structure$ 2,606,000.00 1 pointTax rate of your company20%Use the 2018 Income Statement on www.finance.yahoo.com to calculate the tax rate. Input the data in the table to the right. Calculate the tax rate as: Income Tax Expense divided by Income before Taxes using an Excel formula in cell F27.Income Tax Expense$ 209,000.00 Income before Taxes$ 1,025,000.00 a. Calculate the cost of each capital component, that is, the after-tax cost of debt, the cost of preferred stock (ignoring flotation costs), and the cost of equity (ignoring flotation costs). Remember that the Cost of Equity was calculated by your teammate's Chapter 6 work. Cost of debt: B-T rd × (1 – T) =A-T rd 5 points10%0.00% b. Cost of preferred stock (including flotation costs): Dpf / Ppf =rpf 5 points$0.00$0.00ERROR:#DIV/0! rs c. Cost of common equity, CAPM:4.33%(NOTE: From Chapter 6 tab; Use Excel formula to obtain) d. Assuming that your company will not issue new equity and will continue to use the same capital structure, what is the company's WACC? wd0.0% wpf0.0% ws0.0% 0.0% wd × A-T rd