Trend Analysis in a Spreadsheet

Trend Analysis in a Spreadsheet          

The following is an example of the various financial metrics and ratios I use when evaluating a company. I get the raw figures from TD Waterhouse. If you don’t have access to TD Waterhouse you can use Morningstar or maybe Google financial or you can even get the raw figures right from the annual reports found on sedar.

1		SIS	2014	2013	2012
2		market cap +	150,730,500	92,941,670	66,668,100
3	bs	debt -	12,521,000	12,731,000	15,464,000
4	bs	cash	16,280,000	1,968,000	2,629,000
5
6		ev	146,971,500	103,704,670	79,503,100
7	is	ebit	9,000,000	8,000,000	3,000,000
8		ev/ebit	16.33	12.96	26.50
9		ebit/ev (cap rate or yield)	0.06	0.08	0.04
10
11	is	net income	6,391,000	5,299,000	1,578,000
12	is	sales	82,909,000	75,739,000	66,734,000
13	bs	total assets	71,420,000	49,013,000	49,380,000
14	bs	total equity	36,456,000	20,233,000	19,224,000
15
16		net income / sales	0.08	0.07	0.02
17		sales / assets	1.16	1.55	1.35
18		assets / equity	1.96	2.42	2.57
19		roe	0.18	0.26	0.08
20	ocs	operating cash flow	9,000,000	7,000,000	5,000,000
21	ocs	deferred taxes	0	0	0
22	icf	capital expenditures	1,000,000	2,000,000	11,000,000
23	icf	acquisitions	3,000,000	0	0
24	icf	purchase of investments	0	0	0
25		free cash flow	8,000,000	5,000,000	-6,000,000
26	bs	common shares	29,555,000	23,411,000	22,910,000
27		fcf per share	0.27	0.21	-0.26
28		share price	5.10	3.97	2.91
29		fcf yield	0.05	0.05	-0.09
30		equity/share	1.23	0.86	0.84
31	bs	(goodwill +	7,253,000	4,051,000	4,051,000
32	bs	other intangibles) /	2,661,000	1,679,000	2,138,000
33	bs	total assets =	71,420,000	49,013,000	49,380,000
34		intangible asset ratio	0.14	0.12	0.13
35		cash earnings per share	0.30	0.30	0.22
36		price/cash earnings	16.75	13.28	13.33
37		debt/equity ratio	0.34	0.63	0.80
38
39		Cash per Share	0.55	0.08	0.11
40		Quality of Income	1.41	1.32	3.17
41	bs	Retained Earnings	4,710,000	5,499,000	3,464,000

Column 2...Market Cap

Multiply common shares by share price.

Column 3 and 4...Debt and Cash

Observing trends in cash and long-term debt can be enlightening.

Column 6...Enterprise Value

market cap + long-term debt - cash

Column 8…ev/ebit

Enterprise value divided by operating income

This is a valuation ratio that private equity investors often use to value a company. Okay they usually use EBITDA (earnings before interest, taxes, depreciation and amortization) but I just stick with operating income, the lower the number the cheaper the stock. If you’re ambitious you could compare this figure with others in the industry.

Column 9…ebit/ev

Cap rate, a yield based indicator

If you reverse the two inputs you can turn the ratio into a yield based ratio which you can then compare to the risk free rate (I like to use the yield of the 10 yr bond as my risk freed rate).

Columns 11 to 14

Simply the needed inputs to compute the Dupont formula of ROE (see previous post on April 20)

Columns 16 to 19

The Dupont formula of ROE with its separate components. Remember ROE is a profitability ratio and one of the most useful of all financial ratios.

Columns 20 to 25

Cash Flow stuff

I subtract capital expenditures and deferred taxes from operating cash flow to get my free cash flow number. I also track acquisitions and purchase of investments to get an idea of how management is investing their capital to grow the company. Free Cash Flow is a key number. It indicates how much actual cash the company has after expenses. In other words can the company fund itself comfortably.

Columns 27

Free Cash Flow per Share

The more the better but if the company is making a lot of acquisitions, free cash flow can be spotty and up and down.

Columns 30

Book Value per share

A key metric, hopefully it is increasing every year. That way you know that the equity you have invested in the company is growing every year thus adding value.

Columns 31 to 34

Goodwill and Intangible Assets

If the company makes a lot of acquisitions they will be building up a lot of goodwill on their balance sheet. To make a long story short the intangible asset ratio indicates how much of the company’s assets are intangible in nature. If it’s over 30 percent it means there are a lot of intangibles involved which could be a red flag. If the company you are looking at is in a knowledge based industry you need to give them some leeway here as the nature of intangible assets could be inherent in their business model.

Column 35 and 36
Operating Cash Flow per Share and Price divided by Cash Flow per Share

Cash Flow per share is one of my favorite valuation ratios.

Column 37
Debt to Equity Ratio

How leveraged is the company. Under 30 debt to equity is preferred but there is no magic level.

Column 39
Cash per Share

A useful metric. How much money does the company have on its books on a per share basis.

Column 40
Operating Cash Flow divided by Net Income

A quick gauge to measure the quality of income.

Column 41
Retained Earnings

A company`s long term track record at generating profits. A cumulative figure.

Once I have done the work in the spreadsheet it gives me a good foundation if and when I decide to go to the M,D&A and understand things a little better. What’s M,D&A…see previous post on April 20.

If all of this looks complicated, fear not, to compute the above figures the only thing needed is simple arithmetic which is good cause I only have grade 11 math.

Now you don’t have to do all of this. Whatever I have written about in these posts I am just passing along the information as I have learned it. You are the final word on how you want to approach all of this good stuff. The point is if you can track the trends of these various metrics over time you can gain added insight into the company you are investigating.

One final word, don't rush to learn everything at once if you are just starting out. Take your time. Learn only what you are interested in and go from there. Take small bites then if you feel like it, take another small bite, don't wolf your food, you might bring it up.

Where to find the information

bs    = balance sheet
is     = income statement
ocs  = operating cash flow statement