What is the Tool to Measure Relative Changes in Financial Statements

Dear Friend!

Simple MS Excel is the best tool to measure relative changes in the item heads in financial statements. I use this tool regularly and quite effectively.

You have to physically enter the financial data for many years as columns and data items/ heads as rows. Then you can perform absolute changes and percentage changes quite easily.

After the data is ready, you can use many powerful features of MS Excel like “IF” function, “Conditional Formatting” to highlight results in the most useful and visually powerful forms.

Please see the example Excel Sheet containing NMDC’s operating results for various years, below. By using conditional formatting, the EBT% figures are highlighted in the following ways:

1.     If EBT% is below 50% the cell is filled with light pink and fonts are in dark red.

2.     If EBT% is above 50% but below 75% the cells are highlighted in light green and the font colour is black.

3.     If EBT% is above 75% the cells are highlighted in dark green and the font colour is white.

I hope you will use the MS Excel and also master a few powerful techniques they possess.

Thank you,

With Best Regards

Anand

What Stocks to Invest in April 2017?

A puzzled investor wondering which stocks to buy in April 2017

This month we have further tightened the rules for buying stocks. Besides being a member of our ‘Portfolio 2K15’, the stocks shall satisfy the three criteria prescribed below:

1. The Price to Book Value (P2BV) Ratio shall be less than 1.00. This means we will allocate money to a stock under this rule to only those stocks that are available at or at a discount to the book value.
2. The Price to Earnings (PE) Ratio shall be below 10.
3. The Dividend Yield shall be 4.5% or above. However, a mere good dividend yield is not sufficient. In addition, the scrip must have passed at least one of the two previous tests. Meaning if the share proves expensive under both the P2BV and PE criteria, it is ineligible for allocation merely on the grounds of an attractive dividend yield.

The total investible sum is taken as multiples of 12,000, that is Rs.12,000, 24000 or 120,000 and so on, depending on the investible surplus available with the investor.

The basic unit of 12,000 is equally distributed among the three criteria at Rs.4,000 or multiples thereof.

The stocks that made the cut are:

Serial Number	Stock	Total Allocation	CMP on 4th April 2017	Quantity to Buy
1	NHPC	541	32.25	17
2	PFC	4542	147.7	31
3	REC	4456	179.8	25
4	ONGC	871	185.8	5
5	OIL	934	330.3	3
6	GE SHIP	656	430.05	2
	Total	12000

Now let us examine the eligible candidates separately under each of the three categories in detail.

Price to Book Value (P2BV) Ratio:

Criterion: P2BV ≤ 1.00; Allocable sum: Multiples of Rs.4000;
Serial Number	Stock		Discount available in P2BV	Weightage	Allocation of Investible Amount
1	NHPC	1.13	-0.13	FALSE	0
2	PFC	0.54	0.46	35.94%	1438
3	REC	0.62	0.38	29.69%	1188
4	NMDC	1.92	-0.92	FALSE	0
5	CAIRN	1.17	-0.17	FALSE	0
6	SJVN	1.27	-0.27	FALSE	0
7	Neyveli Lignite	1.18	-0.18	FALSE	0
8	ONGC	0.89	0.11	8.59%	344
9	NALCO	1.5	-0.5	FALSE	0
10	MOIL	1.53	-0.53	FALSE	0
11	HZL	3.45	-2.45	FALSE	0
12	OIL	0.88	0.12	9.38%	375
13	GE SHIP	0.79	0.21	16.41%	656
14	VEDANTA	1.81	-0.81	FALSE	0
15	SCI	0.52	0	0.00%	0
Total	Sum of Positive Values		1.28	1	4000

Shipping Corporation of India (SCI) is an exception. Even though the stock is available at a significant discount of 48% to the book value of its assets, the company is continuously in losses and hence no allocation is made.

Price to Earnings (PE) Ratio:

Serial Number	Stock		Weightage	Allocation of Investible Amount
1	NHPC	13.06	FALSE	0
2	PFC	5.74	50.53%	2021
3	REC	5.83	49.47%	1979
4	NMDC	16.1	FALSE	0
5	CAIRN	66.03	FALSE	0
6	SJVN	10	0.00%	0
7	Neyveli Lignite	13.13	FALSE	0
8	ONGC	13.62	FALSE	0
9	NALCO	23.78	FALSE	0
10	MOIL	19.76	FALSE	0
11	HZL	17.38	FALSE	0
12	OIL	13.24	FALSE	0
13	GE SHIP	11.93	FALSE	0
14	VEDANTA	18.07	FALSE	0
15	SCI	98.27	FALSE	0
Total	Sum of Positive Values		1	4000

SJVN Limited has a PE Ratio of exactly 10 and not being below 10 it had not been given any allocation for this month.

Dividend Yield:

Serial Number	Stock	Dividend Yield	Weightage	Allocation of Investible Amount
1	NHPC	4.69%	13.52%	540.95
2	PFC	9.39%	27.08%	1083.04
3	REC	11.18%	32.24%	1289.50
4	NMDC	8.21%	FALSE	0.00
5	CAIRN	0.99%	FALSE	0.00
6	SJVN	3.44%	FALSE	0.00
7	Neyveli Lignite	2.78%	FALSE	0.00
8	ONGC	4.57%	13.18%	527.10
9	NALCO	2.67%	FALSE	0.00
10	MOIL	1.59%	FALSE	0.00
11	HZL	9.52%	FALSE	0.00
12	OIL	4.85%	13.99%	559.40
13	GE SHIP	3.13%	FALSE	0.00
14	VEDANTA	1.28%	FALSE	0.00
15	SCI	0.00%	FALSE	0.00
Total	Sum of Positive Values	8.43	1	4000

NMDC and Hindustan Zinc Limited (HZL) are again exceptions. Even though their dividend yields are significantly higher than the threshold 4.5%, they fail to pass the tests in both P2BV and PE Ratio hurdles we had set. Basically in April 2017 they are expensive and hence shall not be bought, even though dividend yield is high.

To conclude, NHPC Ltd., Power Finance Corporation Ltd. (PFC), Rural Electrification Corporation Ltd. (REC), Oil and Natural Gas Corporation Ltd. (ONGC), Oil India Ltd. (OIL), and Great Eastern Shipping (GE Ship) are the stocks to buy this April 2017 in the extent of the amount allocated in the table.