Skill Building Exercise: Build Base-case, Upside and Downside Price Targets
- For at least two stocks, identify the base-rate (“outside view”) historical EPS CAGR for the past 5 to 10 years and compare it to the consensus expected EPS over the next year. Why do they differ?
- Follow the instructions in the related “Review Historical Relative Valuation Levels” exercise to obtain the chart “Rel P/E & EPS Grwth.”
- Estimate where you expect the overall market’s P/E multiple (on forward earnings) to be in one year (use the current market multiple if you don’t have a strong view)
- Using the data in the chart:
- Based on the company’s historical growth rate and your understanding of the company’s fundamentals, create base-case, upside and downside forward EPS estimates for the next 12 months and the 12 months after (these will be for “Year 2”)
- Based on the historical relative valuation levels in the chart, create base-case, upside and downside relative P/E levels for the stock
- Multiply your three relative P/E ratios by your estimate of the market multiple to derive three absolute P/E ratio scenarios for the stock
- Apply the absolute P/E ratios to your three EPS estimate scenarios for “Year 2” to derive your base-case, upside and downside price targets
- After downloading the files below, watch the video below to use Excel’s CHOOSE function to automate your EPS and price target scenarios
| NAME | USAGE | DOWNLOAD |
Setting Price Target Scenarios Using CHOOSE Function EXERCISE
Setting Price Target Scenarios Using CHOOSE Function EXAMPLE
Use this file if you intend to watch the video (about 60 minutes) to build price target scenarios using Excel’s CHOOSE function
Use this file to see the final product that is built in the video below