• # Stock Valuation

Stock Valuation with Fair Value Calculation

Description
Welcome to this excel sheet!

It puts you in the position to perform a stock valuation according to various aspects (price, fundamental, dividends) within a few minutes and to determine the fair value using different methods (P/E Price/Earnings, DCF Discounted Cash Flow, Estimation, Ten Cap, Payback Time).

The information is compiled from various worksheets that can be easily added or updated via copy & paste or imports. Below I will explain the individual worksheets.

Stock valuation of key figures incl. Fair value in less than 10 minutes:

Stock valuation key figures - update quarterly results in less than 3 minutes

Stock Valuation Criteria
"The shares are rated according to 3 criteria:
1) Price
2) Fundamental
3) Dividends"

Regarding price rating
a maximum of 20 points can be achieved here - the individual possible points are:
- 2 points: current price < fair value according to P/E method (15% return expectation, 25% margin of safety)
- 2 points: current price < fair value according to DCF method (15% return expectation, 25% margin of safety)
- 2 points: actual P/E < P/E 5 Year Median (price / earnings ratio)"
- 1 point: current price < fair value according to P/E method (15% return expectation, without margin of safety)
- 1 point: current price < fair value according to DCF method (15% return expectation, without margin of safety)
- 1 point: current price < fair value according to analysts estimate for 5 years (15% return expectation)
- 1 point: current price < fair value according to analysts estimate for 5 years (10% return expectation)
- 1 point: current price < MA200 moving average for 200 days """
- 1 point: analyst estimate on"" ""Buy"" ""
- 1 point: Price / Owner Earnings <10
- 1 point: current P/B < P/B 5 year median (price / book value ratio)
- 1 point: current P/S < P/S 5 year median (prics / sales ratio)
- 1 point: current P/C < P/C 5 year median (price / cash flow ratio)
- 1 point: current EV / EBIT < EV / EBIT 5 year median (Enterprise Value / EBIT Ratio)
- 1 point: PEG ratio < 1 (price-earning-growth ratio)
- 1 point: Price To Graham Number <= 2
- 1 point: Price To Peter Lynch Fair Value <= 2"

Regarding fundamental valuation
it can be reached a maximum of 26 points - below the individual possible points:
- 2 points: earnings per share growth 1 year > 10%
- 2 points: earnings per share growth 5 year median > 10%
- 2 points: EV / EBITDA <15 (Enterprise Value to EBITDA)
- 2 points: ROIC return on invested capita l>= 10"
- 1 point: book value per share growth 5 year median > 10%
- 1 point: earnings per share > 0
- 1 point: free cash flow > 0
- 1 point: free cash flow growth 5 year median > 10%
- 1 point: Piotroski F-Score >= 6
- 1 point: sales growth 1 year > 5%
- 1 point: sales growth 5 year median > 5%
- 1 point: net income growth 5 year median >= 10%
- 1 point: operating profit growth 5 year median> = 10%"
- 1 point: share buybacks last year (hence >= 0)
- 1 point: liabilities to equity ratio <= 2
- 1 point: ROIC Return on Invested Capital Growth 5 year median >= 10%
- 1 point: earnings per share growth estimate >= 5%
- 1 point: sales growth estimate for the next 5 years >= 5%
- 1 point: EBIT growth estimate for the next 5 years >= 5%
- 1 point: profit growth estimate for the next 5 years >= 5%
- 1 point: Free Cash Flow growth estimate for the next 5 years >= 5%
- 1 point: earnings per share decline <-50% in the last 10 years"

Regarding dividend valuation
it can be reached a maximum of 3 points - below the individual possible points:
- 1 point: dividend per share growth estimate 5 years >= 10%
- 1 point: payout ratio <= 35%
- 1 point: dividend per share - stability 10 years (no decline >50% in the last 10 years)

This Best Practice includes
1 Excel Sheet for Stock Valuation

Discuss

## Reviews

• Be the first to review this Downloadable Best Practice

See all

See all

## Discussion feed for Stock Valuation

The user community and author are here to help. Go ahead!

• Hello, first of all thank you very much for the fantastic Stock Valuation model. It saves a lot of time since all important things under the same roof.
Anyway I have noticed some problem in the model (I could be wrong) which are as follows
1. In the video you showed example of SAP. In your portfolio, the currency of SAP is in USD. That means all the value from row 26 (MA200 moving average in USD) to row 98 (Capital Expenditure) are in USD.
But from row 99 (Revenue / NetSales Growth Future) to row 105 (Free Cash Flow Growth Future), the values are taken which are in EUR. I think the currency should be same in all cases, isn’t it? A mismatch in currency may give you wrong result as well as wrong calculation of Fair values.
2. Row 109 (EPS 10Y Rückgang >50%) is not clear, although there is an instruction available in the ‘How To’ sheet. Would you please explain a bit more?
Thanks again and hope to get an answer soon

Regards,

regarding 1
You are correct this is an error in the video
In the available Excel on Eloquens anything should be in EUR for SAP
You are right - the currency must be the same for one stock

regarding 2
yes - was a little bit misleading - changed this to:

Declining in profits in the last 10 years