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