Finance final homework | Business & Finance homework help
Our final objective is to take the information we analyzed in the first homework assignment and attempt to value Proctor & Gamble (PG) to determine whether it would make sense to invest in the stock today (2/26/16). Use the excel template provided to assess any historical and forecasted data. 99% of your quantitative information will come from your calculations but if you have downloaded anything, i.e. ERP, it must be sourced appropriately. Print screens can be downloaded directly into your excel worksheet. Lack of sourcing will result in point deductions.
Quantitative Analysis (65 pts)
a. Fill in all highlighted items on all tabs starting with the historical data through the forecasted data (most necessary items are highlighted in yellow.
i. Do NOT assume “other assets/liabilities” are operating. You must look into the 10k and see exactly what these values represent. This is one of your assumptions and print screens can be used to back up your opinion. I have included some for you but if you need additional, please add them.
ii. For the WACC, calculate all three methods of cost of debt
iii. In addition to revenue and margins, you also need to forecast the components of invested capital and lastly data such as acquisitions for the FCF calculation
iv. For changes in NWC, use your forecasts of operating CA – operating CL calculating AR, Inventory and AP using DSO, DIO, DPO
b. Fill in the Forecast tab. Revenue growth and profit margins determine the value of the investment according to this model. Forecast revenue growth, gross and operating margin (all operating expenses as a % of sales except depreciation which is a % of PP&E and amortization as a % of intangibles). In making your forecasts, take into account both management’s assessment of the future, analyst projections and your evaluation of the economy over the next five years. (Source all expectations unless they are your own opinion) In assumptions explain your reasoning for all forecasts.
i. Analyst evaluations are good to read but remember you have to understand their thought process. If you are using data from a private source, provide a print out of the report you are utilizing.
ii. You CANNOT just take averages nor should you assume stable growth until at least five years from now.
iii. You must show several valuation approaches for the CV/TV – the continuing value formula using NOPLAT*(1-g/ROIC), the exit multiple approach showing the implied growth rate, using the implied growth rate for FCF*(1+g) and the 0% excess approach
iv. Calculate a sensitivity analysis for your exit multiple, growth rate and WACC
c. You MUST make sure all your valuations connect and are not just inputs that do not connect to the other sections of the balance sheet, income statement, FCF. I have connected most data points in the model.
d. Fill in all remaining values on the excel template and calculate your equity value. Based on this calculation, should you buy, sell or hold PG stock.
e. As we discussed in class, capital is historically cheap today and given our WACC includes yield to maturity and the risk free rate, the calculation may underestimate the Company’s required rate of return. I have spoken with some current PG employees, and they say their “hurdle rate” for the company varies between 7% based on the risk of the investment they are making. Include in your valuation this rates regardless of your calculations of WACC. (or you can do a separate analysis at this rate)
Qualitative Assessment in a WORD document (35 pts)
1) Start your paper with a buy, sell or hold recommendation stating the level of valuation as compared to the current market price as of 2/26/16. Include both the current and forecasted growth strategy of PG and how these plans have been incorporated into your forecasts. This should incorporate both domestic and international plans. (1-2 pages)
a. You do NOT need to reiterate what you already wrote in your homework assignment but should evaluate the first two quarters for PG as we did not include that in our original assignment. Did these results impact your forecasts?
b. How did the sensitivity analysis impact your overall decision? Did the re-calculation of equity value at 7% as compared to your WACC impact your decision?
c. How did your implied multiple compare to the historical average of the Company? Is this a realistic assumption on your part?
2) OUTLINE FORMAT: Explain the inputs into your DCF model. When I say “explain” I do not want a reiteration of the number or the formula but rather what determined the number in your analysis. You must have support for your thought process and cannot just look at the history of the company and assume that will continue. Explain the full five year time frame. DO NOT just assume everything remains the same b/c that is completely unreasonable.
a. Revenue growth, gross and operating profit margin projections over the time period. Even if you read an analyst report you still need to explain what is behind their evaluation of PG’s growth. This includes your thoughts about all operating expenses. Include how the macro -economic environment impacted your estimates. Keep in mind PG is a global company.
b. What did you decide about the “other” assets and liability categories including LT investments, Equity investments, Minority Interest?
c. Growth rate, ROIC and Exit multiple, you used in the continuing value.
d. Invested capital projections – include your assessment of operating cash cycle (meaning DSO, DIO, DPO), PP&E, goodwill and intangible assets – For example, did you include continued acquisition as part of PG ongoing growth strategy?
e. Did operating leases have a big impact on your valuation? How important is it to consider this type of off-balance sheet financing for a company like SBUX?
