Valuation Exercise

For this project you will value and analyze one of the stocks in your portfolio using a DCF analysis. I will give you the opportunity to analyze the stock of your choice. The stock should be within the S&P 500 universe. I advise that you analyze a company to which you have an investment interest.

DCF Analysis

Perform a DCF valuation on your company using the DCF spreadsheet I presented in class as a template. Please keep to my template because I find it easier to read after you hand it in when the format is just as I have specified (using a spreadsheet that is worse or different than what I did in class is not a good idea). Some notes/hints/reminders on the spreadsheet and on this valuation assignment:

First off, be sure to reproduce my results for Walmart in my spreadsheet. I have separate tabs for the separate analysis that I have performed, so you should be able to reproduce my results before your forge ahead with your analysis. When you are reproducing my spreadsheet dont touch any cells in the spreadsheet Inputs. You would need to enter all formulae into the relevant cells in spreadsheet DCF to make the model working.

Once you replicate my spreadsheet, you will download the stock report from Value Line for your stock when building your valuation. Value Line is no longer available through UConns library website. Hence, I relied on a free source roic.ai which creates a Value Line like stock report. When you construct the spreadsheet for your stock, be sure to make it flexible so that you can change the major inputs and have everything recalculate.

Enter the last financial year into spreadsheet Inputs for which you have historical data on Value Line. In my spreadsheet for WMT, it is 2022 as Walmarts 2022 fiscal year ended in January, 2023.[1] Enter values for all items under Historical Info on spreadsheet Inputs from the Value Line report. If an item is not available on the report, use data from SEC Edgar or the Bloomberg terminal. Enter assumptions for the Forecast Period such as Revenue Growth, EBIT margin, Capital expenditure as % of revenue, Working capital as % of revenue, and Depreciation & amortization as % of revenue. You can use Value Line estimates or the Historical Info to form your assumptions.

Calculate WACC as we learnt in class. Use the CAPM and an estimate of beta (from Value Line) to get your cost of equity. There are a number of ways to get a cost of debt. A simple way is to use the ratio of LT Interest to LT debt from Value Line.

You must include some sensitivity analysis, and you may want to examine the effect of varying the other inputs to see the impact on the valuation. The table that I did in class shows the value per share as a function of discount rate and terminal growth rate.

For some firms, the EBIT margins going forward are difficult to estimate, and if this is the case then you should show the effect of changing EBIT margin on your valuations.

Include the Value Line report, Income Statement, Balance Sheet, and Statement of Cash Flows in your spreadsheet so I can review them when you hand it in.

Deliverable

A short summary write-up with your conclusion as to whether or not the stock you analyzed is under-valued or over-valued, and a buy or sell recommendation (if you think it is generally fairly priced I want you to give me a hold recommendation). You will, of course, cite to your analysis in making your argument.

Short write-ups for each of the major input variables, in which you explain the reasoning behind your choices. The majors input variables are:

Discount rate- what you ultimately decided on if your calculated WACC just didnt seem right to you for some reason;

Revenue growth rate;

Terminal revenue growth rate;

EBIT margins;

Sensitivity analysis

Your write-up should not be more than two pages. I show what I think is a great report. You will notice how it presents the results and what recommendations it makes.

[1] Walmarts annual results for fiscal year ending in January 2022 were available on SEC Edgar on 4/3/2022 when I was working on my spreadsheet.