In this post, I will be sharing what I have learn about fundamental analysis of value stocks by demonstrating how fundamental analysis can be conducted using a watch list created from excel. I will not be going into specifics with regards to the analysis process, but rather, I will be focusing on how the watch list is used to assist me with my analysis process. My purpose for creating this watch list is to hasten this otherwise grueling analysis process. Therefore, all calculations that are required in the analysis process will be computed automatically after keying in the required values into the watch list. Also, whenever a stock passes a certain criteria in the analysis process, the cell will automatically be colored green to indicate pass. Similarly, whenever a stock fails to pass a certain criteria in the analysis process, the cell will automatically be colored red.
Business and Industrial Analysis
The first step of fundamental analysis is to conduct business and industrial analysis of the stock. To pass the business and industrial analysis, a company must be able to produce stable income throughout the year. In order for a company to produce stable income however, the nature of the company's businesses must be that either it's services are being utilized daily, the company revenue are generated from subscriptions or the company has businesses diversified enough that allows it to earn income from various sources throughout the year. For this post, I will be using Singpost as my example stock for conducting fundamental analysis on. To begin with the business and industrial analysis, I will be looking at Singpost's company description on SGX's stock facts for more information on the nature of Singpost's business.
Based on the company description, Singpost provide services that are utilized daily and they also have diversified business. As a result, we have basis to believe that Singpost is capable of producing stable income and has thus pass our business and industrial analysis. As shown in the screenshot below, I will thus mark "Y" in my spreadsheet under "Utilized Daily", "Diversified Business" and "Stable Income", which indicates that Singpost has pass the business and industrial analysis.
Financial Statement Analysis
The next analysis to perform is the financial statement analysis. For Singpost to pass this analysis test, it must be producing stable revenue and profit. I will thus look up stock facts again to determine if Singpost has been producing stable revenue and profit.
As shown in the screenshot taken above, Singpost is indeed producing stable revenue and profit. In fact, both revenue and profit seems to be moving in an upward trend for the past few years. Singpost has thus pass my financial statement analysis test and as shown below, I will be indicating a "Y" in my spreadsheet under both "Stable Revenue" and "Stable Profit".
The third analysis to perform, is the pricing analysis. Under pricing analysis, I will need to determine the fair value of the stock and there are actually multiple methods for me to determine the fair value. The first method to determine the fair value, is to use the Dividend Discount Model. In order for me to use the dividend discount model however, I will first require a few values. The values needed are Previous Year's Dividend, the Required Returns on the Stock and the Dividend Growth Rate. To find the previous year dividend, you can look it up from stock facts once again as shown in the screenshot below.
For both the required return and the growth rate of the dividend however, we will be using the assumed value of 8% and 3% respectively. Now that we have the values we need, we can just key in the values into the spreadsheet and it will automatically calculate the fair value for Singpost's stock.
As shown in the screenshot above, the fair value that is calculated using the dividend discount model, is $1.545. This is higher than the current price of $1.43, Singpost is therefore, according to the dividend discount model currently below fair value.
Another important variable that we will have to look at under pricing analysis, is the Dividend Yield. The dividend yield must at least be 4% to pass our pricing analysis and in order for us to find the dividend yield of Singpost, we will once again be looking it up from SGX's stock facts.
As shown in the screenshot above, dividend yield is at 4.9%. We will thus enter this value into our spreadsheet.
As shown in the screenshot above, since Singpost's dividend yield is above 4%, the cell will automatically be colored green to indicate the meeting of our dividend yield criteria.
The next variable to look at, is the P/E ratio of the stock. To pass our pricing analysis, the stock's P/E ratio must be lower than 20. The P/E ratio can also be found on stock facts. It is be found at the exact same location where you can find dividend yield as shown in the screenshot from before.
Once again, we will enter the P/E ratio of 13.969 into the spreadsheet and since it is lower than 20, the cell will be colored green to indicate a pass.
If there are 6 to 8 comparable companies to Singpost, we can actually use these comparable companies' P/E ratio to calculate the fair value of Singpost stock. Unfortunately, Singpost does not have that many comparable companies, therefore any fair value calculated using this method will not be accurate. However, for demonstration purposes, I will be demonstrating how you can calculate the fair value of a stock using the watch list. We will first need the estimated earning per share for Singpost, which can be found on bloomberg markets watchlist as shown below.
As shown above, the estimated earnings per share is 0.07 and so we will enter this value into our spreadsheet, which we will see further down below. We still need another value besides estimated earnings per share, this value is the Fair P/E Ratio. To calculate the fair P/E ratio, we will need to do quite a bit of work. First, we will need to obtain the P/E ratio of companies that are comparable to Singpost. Next we will have to take the average of these P/E ratio, which will then be our fair P/E ratio. As shown in the screenshot below, the P/E ratio of companies comparable to Singpost are actually readily available from SGX's stock facts.
As shown in the screenshot below, once we have obtained the estimated earnings per share for Singpost and calculated the fair P/E ratio from the P/E ratios of companies comparable to Singpost, we can enter them into the watch list and it will automatically compute the fair value of Singpost's stock based on the P/E ratio of companies comparable to Singpost. In this case, the fair value calculated is at $0.912534, which is lower than the current price of Singpost's stock. This signals to us that based on the P/E ratio of comparable companies to Singpost, the price of Singpost is currently overvalued. However as mentioned before, since we do not have enough comparable companies, this fair value calculated will not be accurate. Therefore, it is okay for us to disregard this result here.
The last test to perform under pricing analysis, will be the price to book ratio. To pass this test, the price to book ratio of the stock must be lower than 1. This criteria however, is incredibly difficult to meet and most stocks will most likely not be able to meet this criteria. If a stock managed to pass this test however, it will indicate that the stock is currently really cheap. The price to book ratio can be found in the same location as both the dividend yield and the P/E ratio. We will then enter the price to book ratio of 1.955 this time however, it did not managed to pass the test.
As stated before though, it is incredibly rare for a stock's price to book ratio to be lower than 1. There is therefore, no need to disregard Singpost completely due to it failing to meet the price to book ratio criteria of below 1. With this, we have therefore completed our pricing analysis and we can move on to the final analysis, which is the risk analysis.
Under risk analysis, there are four important test to pass. If the stock fail any of the test, it will mean that the stock has failed risk analysis and it is therefore risky for you to purchase this stock. The first test to perform is to check whether there are any company specific risk for the stock you are analyzing. In this case, there are no company specific risk to note for Singpost. We will therefore indicate a "N" in the spreadsheet below.
As shown in the screenshot above, the next three test are the Current Ratio, the Debt Equity Ratio and the Interest Cover. To pass these tests, current ratio must be greater than 1, debt equity ratio be lower than 300% and interest cover be greater than 3. All these values can be found on SGX stock facts, under Ratios.
As shown in the screenshot above, Singpost has unfortunately failed the current ratio test under risk analysis. It is therefore risky to purchase Singpost at the moment.
In conclusion, apart for failing to meet the current ratio criteria, Singpost has mostly passed our fundamental analysis for value stocks. It might be a good idea therefore, to keep Singpost in your watch list and continue to monitor it first, rather than rushing to buy it now.
For those who are interested in downloading and using this watch list, I have made it possible for you to download it for free below. Fill in your email in the submission form below and the watch list will then be sent directly to you through email. I hope that my watch list will be helpful to you in your stock selection process. I have plans to create more watch lists for other financial instruments in the future, so if you are interested in them, do subscribe to our mailing list so that you can be notified through mail when I post them.