Technical analysis

Today I want to look at technical analysis of precious metals and other commodities.

Orange picture of graph with text about technical analysis

Technical analysis of crude oil, September 14, 2017

This is a weekly chart of crude oil:

Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 1. Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is caught between two opposing trends: One descending trend line above and one lateral trend line below. Prices are now pushing up agains a rising 50-week moving average which makes a break-through likely.

 

The bullish scenario:

In this scenario prices go through both the 50-week moving average as well as the descending trend line above.

This scenario is fairly likely. I would give it a probability of 70 per cent.

 

The bullish/bearish scenario:

In this scenario prices falter at either the 50-week moving average or the the descending trend line. Given the chart pattern I don’t believe it being very likely. I would give it a probability of 15 per cent.

 

The bearish scenario:

This is where prices go south from here. I would also give such a scenario a probability of 15 per cent.

 

 

Technical analysis of gold, September 9, 2017

This is what a gold chart looks like:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 2. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

We have a breakout!

Now there is nothing holding gold back and all the resistance is gone.

But would I be buyer at this stage?

Probably not. I would prefer to wait for a pull-back down at the descending trend line.

But essentially this is good news if you are bullish on gold.

 

Technical analysis of crude oil, September 3, 2017

This is what a chart of crude oil looks like:

Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 3. Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

We are still caught between the 50-week moving average and the 100-week. It looks as though the chart hasn’t really decided for which trend to follow.

The bullish case:

In this scenario prices bouncing off the 100-week MA and continue up through the 50-week and the descending trend line.

The bearish case:

In this scenario prices are falling through the 100-week MA and continue through the lateral trend line that is drawn in Figure 1.

I would give both these scenarios a probability of 50 percent at this stage.

Technical analysis of gold, August 24, 2017

This is what a chart of gold looks like:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 4. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is squeezed between two opposing trends. The 100-week moving average is acting as support.

The bearish scenario:

In the bearish scenario prices falling down from here and then go through the rising 100-week moving average. I would give such a scenario a probability of 10 per cent.

The bearish/bullish scenario:

In this scenario prices are falling down to the 100-week moving average, but then they rebound and go higher. I would give such a scenario a probability of 30 per cent.

The bullish scenario:

In this scenario prices go through the descending trend line that is acting as resistance in the chart. I would give such a scenario a probability of 60 per cent.

Technical analysis of crude oil, August 17, 2017

This is what a chart of crude oil looks like:

Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 3. Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

Looking at the chart it becomes clearer to me that it is in a head-and-shoulders-pattern. The chart looks heavy.

The bullish scenario:

In the bullish scenario prices find their footing at the 100-week moving average and then move higher. Given the current chart pattern I would give such a scenario a probability of 15 per cent.

The bearish scenario:

In the bearish scenario prices along their current path and go lower. That means that they will penetrate through the 100-week moving average without any resistance. Given the chart pattern, I would give such a scenario a high probability of 85 per cent.

Technical analysis of crude oil, August 5, 2017

This is a weekly chart of crude oil:

Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 4. Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is getting squeezed between one supporting trend line (below) and another putting pressure on prices (above). Furthermore, there may be head-and-shoulders pattern building up in the chart.

The bullish scenario:

In the bullish scenario prices are supported by the 50-week moving average and move up through the descending trend line.

At this point I would put a ten per cent probability on that happening.

The bullish/bearish scenario:

This scenario is where prices move up and kiss the descending trend line but then move down.

I would give such a scenario a probability of ten per cent.

The bearish/bullish scenario:

This is where prices simply fall down and plunge through both the 50-week and the 100-week moving average. When prices finally hit the horizontal trend line then they rebound and continue within the trading range.

I would give such a scenario a probability of 75 per cent.

The bearish scenario:

In this scenario prices are simply plunging through both moving averages and then also the lateral trend line in the chart.

I would give such a scenario a low probability of 5 per cent.

Technical analysis of gold, July 31, 2017

As usual, this is a weekly chart of gold:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 2. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is caught in a pennant like pattern with prices edging up against a declining trend line.

Prices are further lifted by the rising 100-week moving average (dotted turquoise line).

The bearish scenario:

In this scenario prices rediscover gravity and fall down from here.

That happens despite the rising 100-week moving average.

Because this is not very likely I give it a probability of 15 percent.

The bullish scenario:

In this scenario prices are moving up and through the declining trend line.

This is a likely scenario given the current chart pattern. I would give it a probability of 40 per cent.

The bullish/bearish scenario:

In this scenario prices move up and touch the declining trend line, but then fall down.

This is also a likely scenario and I would give it a probability of 45 per cent.

Technical analysis of gold, July 24, 2017

This is a weekly chart of gold:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 3. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is in a classic pennant where it is pushing up on the descending trend line above.

 

The bullish scenario:

In this scenario prices go up from here, but they don’t stop at the upper trend line but continue through.

I would give such a scenario a probability of 50 per cent.

The bullish/bearish scenario:

This scenario is very similar to the one above.

The only difference is that prices stop at the declining trend line and continue down.

I would give such a scenario a probability of 40 per cent.

The bearish scenario

In the bearish scenario prices are rediscovering gravity and go down from here.

Because it is not very likely I give it a probability of 10 per cent.

Technical analysis of crude oil, July 19, 2017

This is a weekly chart of crude oil:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 4. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is extending its right shoulder in a head-and-shoulders pattern. The chart looks heavy and I believe it will go lower from here.

The bullish scenario:

In the bullish scenario prices defy gravity and go up from here. I would give this a low probability of 5 per cent.

The bearish scenario:

In the bearish scenario prices go down from here. Given that the chart looks heavy I would give this a high probability: 95 per cent.

Conclusion:

The chart looks poised to go lower with a head-and-shoulders pattern developing.

Technical analysis of crude oil, July 13, 2017

Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 4. Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart looks heavy with a left shoulder, a head and a right shoulder. This is usually a sign of the chart going lower from here.

Furthermore, prices are being pushed down by the 100-week moving average.

The bullish scenario:

In the bullish scenario prices defy gravity and edge higher from here.

I would give this a probability of 15 per cent.

The bearish scenario:

In this scenario prices are indeed being pushed down by the 100-week moving average.

Given the current shape of the chart, I would give this a probability of 85 per cent.

Conclusion:

My reading of the crude oil chart is that it is likely to go lower from here.

Technical analysis of gold, July 6, 2017

This is a weekly chart of gold:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 5. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is caught between a descending trend line and a rising 100-week moving average.

The bullish scenario:

In this scenario prices are pushing up against the descending trend line and then through it.

Given the chart pattern I give this a probability of 65 per cent.

The bullish/bearish scenario:

Here prices are advancing up but then they go down at the trend line.

I would give this scenario a probability of 20 per cent.

The bearish scenario:

In this scenario prices are inexplicably going down from here.

I would give this a probability of 5 per cent.

The bearish/bullish scenario:

Here prices fall just like above, but when they reach the ascending trend line they change direction and head up.

I would give this a probability of 10 per cent.

Technical analysis of crude oil, July 1, 2017

This is a weekly chart of crude:

Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white.

Figure 3. Weekly chart of crude oil (XOIL.X) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is getting squeezed between two opposing trend lines. The one above is descending while the one below is lateral.

If we wait a couple of weeks we may see head-and-shoulders pattern forming.

The bullish scenario:

In the bullish scenario prices continue on their upward path that began this week (I didn’t believe that it would).

I would give such a scenario a low probability of 10 per cent.

The bearish scenario:

In this scenario prices are being pushed down the 100-week moving average.

I would give such a scenario a high a probability: 90 per cent.

Conclusion:

The chart looks bearish. We’ve tested the lateral trend line several times the past six months and in my opinion it will fall. The question is how deep.

Technical analysis of gold, June 26, 2017

This is what a weekly chart of gold looks like:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 4. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description of chart:

Prices are now sitting on a declining 50-week moving average. If they go up from here they will face overhead resistance in the form of a descending trend line and if they fall they are likely to be caught by the ascending 100-week moving average below.

The bullish scenario:

In this scenario prices are being saved by the 50-week moving average that will act as support. After that they will then continue through the descending trend line.

This is an unlikely scenario because of the nature of the chart pattern.

I would only give this scenario a probability of 10 per cent.

The bullish/bearish scenario:

This scenario is as the one above with the difference that prices stop at the descending trend line and go down from there.

I give that scenario a probability of 30 per cent.

The bearish scenario:

In this scenario prices go down all the way through the 100-week moving average and the ascending trend line.

This is unlikely to happen but nevertheless I give it a probability of 5 per cent.

The bearish/bullish scenario:

In this scenario prices first fall but are then caught either by the ascending 100-week moving average or the ascending trend line below in the chart.

I would give such a scenario a probability of 55 per cent.

Technical analysis of crude oil, June 21, 2017

This is a weekly chart of crude oil:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 5. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart has been in a pennant until a few weeks ago. Now all the resistance in the chart is gone.

The bullish scenario:

In this scenario prices neglect gravity and head up from here without sensing any downward pressure.

While this scenario is not impossible, I don’t consider it likely.

I would give such a scenario a probability of 1 per cent.

The bearish scenario:

In this scenario prices go down because there is no resistance left in the chart.

At this juncture this is the likely scenario.

I would give such a scenario a probability of 99 per cent.

Conclusion:

It seems likely that we are going to fill up our cars cheaply this summer.

Technical analysis of gold, June 19, 2017

This is a weekly chart of gold:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 6. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Description:

The chart is in a pennant, currently on its way down, and once it breaks out of the resistance or the support, the move will be violent.

The bullish scenario:

In this scenario prices head up from here and break out of the resistance that is weighing on the upside.

Its not an unlikely scenario, but I would only give it a probability of 20 per cent.

The bearish scenario:

In this scenario prices go down from here. This is the more likely scenario given how prices have moved lately.

I would give such a scenario a probability of 80 per cent.

Conclusion:

In the short-term prices are likely to continue down, but in the medium-term it looks as though they are moving up. The reason why I say this is because prices have been knocking on the upper resistance zone at least twice recently. It would surprise me if they did not succeed to go through at some point.

Technical analysis of gold, June 12, 2017

This is what a weekly chart of gold looks like:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 7. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

Prices sit just at the descending trend line and depending upon where they move from here will determine their movement for a long time.

The bullish scenario

In this scenario prices are slowly edging their way through the descending trend line.

If the resistance is gone prices have no immediate thing stopping them from much higher.

In favor of this is the fact that we are above both the 50-week and the 100-week moving averages.

Given the lower high made in February, I’d still give such a scenario a probability of 40 per cent.

The bearish scenario

In this scenario prices are headed lower from here.

The arguments for lower prices are the same as above.

I would give such a scenario a probability of 60 per cent.

Technical analysis of crude oil, June 09, 2017

This is a weekly chart of crude oil:

Daily chart of the crude oil index (XOIL.X). 50-day moving average is in blue and 100-day is in turquoise. Ascending and descending straight trend line are in white.

Figure 8. Daily chart of the crude oil index (XOIL.X). 50-day moving average is in blue and 100-day is in turquoise. Ascending and descending straight trend line are in white. Chart: FreeStockCharts.com

Summary:

Prices have now come down again and are now pushing against the ascending trend in the Figure 1.

The bearish scenario:

I will begin with the bearish scenario. This is where prices fall down through the ascending trend line and then continue down. At this point I would give such a scenario a probability of 65 per cent.

The bearish/bullish scenario:

In this scenario prices first go down but then rebound once they hit the trend line below. This is not implausible and I give such a scenario a probability of 30 per cent.

The bullish scenario

This is where prices shoot straight up from here. Given recent trends I don’t consider it very likely and I would give it a probability of 5 per cent.

Technical analysis of gold, June 05, 2017

This is what a weekly chart of gold looks like:

Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white

Figure 9. Weekly chart of Gold (XGLD) from the end of 2015 until now. 50-week moving average in blue and 100-week moving average in turquoise. Ascending and descending trend lines are in white. Chart: FreeStockCharts.com

What we are seeing is that prices are coming up towards the descending trend line.

The bullish scenario

In this scenario prices are going through the declining trend line and then continue up beyond.

Given that the 100-week moving average is slightly ascending, I would give such a scenario a probability of 70 per cent.

 

The bearish scenario

In this scenario prices are going down from here.

While a distinct possibility, I only give such a scenario a probability of 30 per cent.

If you want to learn the basics of technical analysis you can do that here.

Why is the Free cash flow yield important?

Today I want to talk about the Free cash flow yield and discuss why it is important for value investors.

Blue figure with cash flow icon and text about Free cash flow yield.

In theory value investing is easy.

You buy an undervalued stock, you reinvest the dividends and you repeat for a long time.

The trouble is that you cannot be certain when the stocks that you are looking at are really undervalued – perhaps the company had just one year of good earnings that skews the Price to earnings ratio.

That is why I prefer to look at the Free cash flow. The Free cash flow is the amount of money that the company is making after paying off costs to maintain or expand the company’s asset base.

The Free cash flow is the money that the company can spend to pay dividends or to buy back shares.

OK, I get it, but what has this got to do with value?

Quite a lot, it turns out.

The Free cash flow per share complements the earnings per share and gives a broader picture of the money made.

Because it cannot be as easily manipulated as the earnings per share, it gives a fairer picture of how much money that flows into the company.

How do you calculate the Free cash flow yield?

First of all you need to find the Free cash flow. Go to the company’s web site and download the most recent annual report.

In there you will find something that is called Comprehensive cash flow statement.

You take the Cash flow from operating activities and you enter it into Excel. Then you will find something called Investment in plant, property and equipment. Then finally you add the two together, like so:

Screenshot of Microsoft Excel showing how to calculate the Free cash flow from the Operational cash flow and the Capital expenditures.

Figure 1. Screenshot of Microsoft Excel showing how to calculate the Free cash flow from the Operational cash flow and the Capital expenditures.

Then you need to find the number of shares that the company has outstanding and you divide the Free cash flow with this number.

Then it turns out that the Free cash flow yield is easy:

You divide the Free cash flow per share with the price per share, like so:

Formula for calculataing the Free cash flow yield by dividing the Free cash flow per share by the Share price.

Figure 2. Formula for calculataing the Free cash flow yield by dividing the Free cash flow per share by the Share price.

What you end up with is a percentage that you can use as a valuation metric.

In my experience, a free cash flow yield above 5 per cent indicates that the share is undervalued, if it is between 3 and 5 per cent it is worth considering and if it is below 3 per cent the share is outright expensive.

That is the reason why you want to consider the Free cash flow yield as another value metric.

Why not give it a go?

 

Pepsico, Inc.

Fundamental analysis of Pepsico, July 10, 2017

Blue figure with Pepsi icon and text about Pepsico

Update, September 12, 2017

Pepsico is valued at 26.5 times 2016 earnings which is expensive. If we assume that the company will be making $4.50 this year, which is in line with what they’ve been earning so far in 2017, the P/E ratio comes in at 25.7 which is still very expensive. The P/E over the past three years’ earnings is 28.2.

The Free cash flow yield comes in at 4.4 per cent which qualifies the stock in the “watch” category.

The only reason why you would want to own such a stock is the dividend of $3.06 per share (2.6 per cent yield). In an environment where you are getting 2.17% on a 10-year note this may be interesting for some people. However, as it stands the stock is too expensive for me.

 

 

Update:

The day after publishing this we figured out that the company presented their earnings.

The earnings came in better than anticipated and we can now make estimated guesses for next year’s earnings.

We think they will come in around $4.50 per share which equates to a forward P/E of 25.3.

That is still far too expensive for my taste.

Yesterday interestingly the share went down 0.5 per cent to $113.74.

Valuation:

At $115 and a trailing P/E value of 26.5 the PepsiCo share is expensive. Over a period of three years, the P/E ratio is even higher at 28.2. Because of their high degree of intangible assets the Book value is negative so a measure like Price to book does not make sense.

 

Balance sheet:

PepsiCo has a Working capital of $6 billion but the Working capital to debt is low at only 0.1. The Debt to equity ratio is extraordinarily high at 5.6.

In all, PepsiCo’s balance sheet could look better.

 

Free cash flow and dividend:

The company has a Free cash flow of $7.4 billion which equates to $5 per share. Of this they both buy back outstanding shares and pay a good dividend of $2.96 (2.6 per cent). The earnings look stable.

PepsiCo has been paying out uninterrupted and increasing dividends for more than 25 consecutive years.

 

Conclusion:

At 26.5 times trailing earnings PepsiCo is too expensive for my taste. At these prices I would call it a SELL.

 

If you would like to learn more about fundamental analysis you can do that here.

Discipline is having the strength to say no

What does it take to say no?

Green picture of note book with text about to say no

As an investor, you are more likely than not to have been in a situation like this:

You are listening to a presentation of a company that you are interested in.

The CEO is making his pitch and it is tempting. The business model sounds fool proof and you are on the verge of buying equity in the company.

But is it a good idea to buy just when you’ve listened to a presentation with a CEO?

Of course it is not. As always, you owe yourself some due diligence before making any investment decisions.

That is why I advice you to wait a week before buying if you’re tempted. If by then, it’s still a good proposition then buy. Otherwise you say no.

Why is it so difficult to say no?

As famous investor Benjamin Graham once said:

Every investment decision should be taken with safety of principal and a good rate of return in mind.

In the example above, the safety of principal is to say the least dubious.

There is simply no guarantee that you will be able to get your money back if you invest in an “interesting startup”. Additionally, the rate of return will almost certainly be non-existent in a speculation like this.

You don’t want to go there.

 

What you should do instead

You need to take an ice cold look at the prospect and not letting your feelings run high.

You need to carefully analyze the investment proposition looking at different key numbers from both the company in question and competing companies.

One thing that is extremely important is the earnings where a good investment is characterized by solid earnings. It makes no sense to invest in a company that is loss making.

If you after careful analysis come up with a negative outlook for the company, then you owe yourself to say no.

 

To say no is not a sign of weakness – it’s a sign of strength.

The more you look at different companies, the more experience you will acquire.

What that means is that you will look at many investment propositions before finally acting on one or two.

You will then be more secure in your decisions and not waste any money on useless propositions.

 

 

Target Corporation (TGT)

Fundamental analysis of Target Corporation (TGT), July 17, 2017

Update, August 28, 2017

Target is valued at 11.6 times 2016 earnings which is cheap enough to make it interesting. If we assume that the company will make $4.50 for the whole of 2017 – which is in line with the earnings reported so far – the P/E ratio comes in at 12.1. This number is not cheap, but not extremely expensive either.

The problem is of course that it is a retailer – a business model that is under heavy attack from e-commerce competitors. However, for the time being Target is making real money which potentially makes it an interesting value proposition.

 

Description:

Target Corporation operates a household retail business in the United States. It is based in Minneapolis, MN.

 

Valuation:

Given the strength of its business, the company is reasonably priced at 11.5 times earnings. Average earnings over the past three years are low with one year of loss. Price to forward earnings comes in at 11.8.

Price to book value is high at 2.9.

 

Income items:

The earnings history seems a little bit erratic with 2014 being a year with a loss. They actually lost $1.6 billion that year which equates to a loss of $2.56 per share. Hopefully, Target Corporation will stay away from those years in the future.

 

Balance sheet:

The company’s current liabilities are greater than its current assets so the net working capital is negative.

The Debt to equity ratio is 2.4, a number which usually is associated with high risk.

 

Free cash flow and dividends:

The company last year had a Free cash flow of $3.9 billion which equates to $6.70 per share. Of this they are paying out a dividend of $2.36 (2.8%).

 

Conclusion:

Because the company is reasonably priced, I’m tempted to dip my toes in the company. The only problem is the high debt levels.

 If you would like to learn more about fundamental analysis you can do that here.

 

Walgreen Boots Alliance (WBA)

Fundamental analysis of Walgreen Boots Alliance (WBA), July 7, 2017

Update, August 25, 2017

Walgreen Boots is valued at 21 times last year’s earnings. If we assume that the company will make $4.00 in 2017, the forward P/E ratio comes in at 20.

Because the company has a lot of intangible assets the Price to Book comes in at 19.7, which is very high.

In summary, I would not buy shares in Walgreen Boots at this time.

 

 

Description:

Walgreen Boots Alliance is an American pharmacy chain with many business areas in the health sector.

 

Valuation:

At $78 and a trailing P/E of 20.3, the company is expensive. Looking at an average of the past three years’ earnings, the P/E comes in at 23.7 which is not better. Because of their intangible assets the Price to Book value is also very high at 19.0.

 

Balance sheet:

The company has a Debt to equity ratio of 1.4 and a Working capital to debt ratio of 0.2 which is OK, but not extraordinary. The Net working capital is $8.9 bn which of course is a lot of cash.

Last year, the Return on equity was 14 per cent which was OK, but not extraordinary. A high Return on equity usually correlates with a high Free cash flow.

 

Free cash flow and dividend:

Last year Walgreen Boots had a Free cash flow of $6.5 bn which allows them to buy back a lot of the expensive shares that they have issued.

It also allows them to pay a dividend of 1.46 (1.9 per cent). The dividend has been uninterrupted and increasing for at least 25 years.

 

Conclusion:

The company is too expensive at these prices. Ideally I would like to see them fall by 50 per cent before dipping my toes.

There is nothing wrong with the company, but it is simply too expensive.

 

 If you would like to learn more about fundamental analysis you can do that here.

Leggett & Platt (LEG)

Fundamental analysis of Leggett & Platt Inc., June 2, 2017

Update, August 21, 2017:

Leggett & Platt is neither expensive nor cheap at 16.8 times the earnings of 2016. Assuming forward earnings of $2.50 for 2017, the forward P/E ratio is 18.6 which is on the high side. I would not be a buyer of the stock at these prices.

Description:

Leggett & Platt is a designer and manufacturer of home and office products. It has its headquarters in Carthage, Missouri.

Valuation:

Leggett & Platt seems expensive at a trailing P/E of 19.0. Over the average past three years the P/E is even worse at 27.6. The Shiller P/E is very high at 46.7. Given the high amount of intangible assets the company’s Price to book ratio is very high at 53.3.

Balance sheet:

With a Working capital of $620m seems well capitalized. At least they don’t have any problems with paying their short-term bills. However the company has a Debt to equity ratio of 1.7, a figure associated with high risk. Net earnings to sales in 2016 came in at 10.3 per cent which is good for a manufacturing company. Last year’s Return on equity was very high at 35 per cent.

Free cash flow and dividend:

Leggett & Platt has a Free cash flow of $430m which equates to $3 per share. Of this the company pays out a dividend of $1.12 (2.5%) which seems reasonable.

Conclusion:

Leggett & Platt is a well run company with solid earnings and good cash flow. However, at these prices I would not buy new stock.

 

 If you would like to learn more about fundamental analysis you can do that here.

How to use Microsoft Excel in finance – Part 3

This time we will look a bit more in depth on how to use Microsoft Excel for financial calculations.

Green Excel icon with text about using Microsoft Excel in finance

What we will look at today is Financial statements and how to use them in order to understand the financial health of a company.

In particular we will look at:

  • Financial statements
    • Balance sheet
    • Income statement
  • The basic difference between accounting (or book) value and market value.
  • The difference between accounting income and cash flow.
  • How to determine a firm’s cash flow from its financial statements.
    • Calculate cash flow.
  • The difference between average and marginal tax rates.
    • Calculate taxes

Finance not accounting

The first thing to grasp is that we will look at the numbers and look at them through the eyes of people in finance.

They use the numbers differently than the people in accounting and we will show you how.

Balance sheet

In the last article we talked about this formula:

Assets = Liabilities + Equity

Everyone in finance is using this equation and not just in finance but also in accounting.

What the Balance sheet does is that it reflects the equation.

The Balance sheet is a snapshot of the Firm’s account balances at the last day of the reporting period.

Hypothetical Balance Sheet of ABC Corporation (Current assets, Non-current assets and Total assets).

Figure 1. Hypothetical Balance Sheet of ABC Corporation (Current assets, Non-current assets and Total assets).

Assets

The assets are divided into Current assets and Non-current assets where the Current portion is assets that can be turned into cash within 12 months.

Then per definition Cash is a Current asset. Then we Accounts receivable which is accounts that will be cash soon. Inventory is another Current asset. The whole point of inventory is so that you can sell it and get cash.

The Current assets are important not only in finance, but also in accounting, auditing and banking. It’s very important to see a business’ Current assets, because if they don’t have very many current assets perhaps they cannot pay their bills.

The Non-current portion are fixed assets that cannot be easily transformed into cash. These are your buildings, your trucks or patents or the long-term assets that actually define your business.

This is what you’ve invested in because you think you can make a profit from this.

In a financial statement you will see different periods. That is because you want to compare one year’s numbers to another.

 

Liabilities

We will also talk about the other side of the equation, which are the liabilities. These are the funds that the company have at its disposal.

The company either goes out and get debt (current), it borrows money long-term (bonds) or it issues equity to get its funds which means the cash it is going to use to buy its assets.

Current liabilities are liabilities that need to be reimbursed within a year – much like the current assets which are assets that can be converted into cash within a year.

Current liabilities are the bills the company need to pay within one year.

As you can see, within the current liabilities there are two items Accounts payable and Notes payable.

Accounts payable is when the company goes out and buys products that it has to pay for. Notes payable is when the company borrows money that it has to reimburse within a year.

The combined current liabilities and non-current liabilities represent debt on the balance sheet.

Hypothetical Balance Sheet of ABC Corporation (Current liabilities, Non-current liabilities, Total liabilities and Shareholders' equity).

Figure 2. Hypothetical Balance Sheet of ABC Corporation (Current liabilities, Non-current liabilities, Total liabilities and Shareholders’ equity).

Shareholders’ equity

In cell A19 you can see that we’ve written Common stock and paid-in surplus. What that means is that if the company issues common stock and they are being priced at $22 but were supposed only to be worth $20, the paid-in surplus is the $2 that the stockholders pay in order to own the company.

Retained earnings belong to the shareholders and they are to be paid back to the shareholders in the form of dividends, but sometimes they are not.

If they are not paid out to the shareholders they can be used within the company in the form of investment.

The way to account for such a situation is to label the item Retained earnings.

Adding it all up

Finally we add it all up. First we calculate total liabilities which is the sum between current and non-current liabilities.

Then we calculate total liabilities plus shareholders’ equity which is just what it sounds like.

Why is it called the Balance sheet?

That’s because there’s an equal sign in the formula Asset = Liabilities + Equity which means that the two sides have to balance each other.

So what I do in cell B24 (Figure 2) is that I add the total assets from Figure 1 in cell B18 and in cell C24 (Figure 2) I add the Total liabilities and Shareholders’ equity from cell B22:

Comparison between Total assets for 2016 (B24) and Total liabilities and Shareholders' equity for 2016 (C24).

Figure 3. Comparison between Total assets for 2016 (B24) and Total liabilities and Shareholders’ equity for 2016 (C24).

The result that we get in D24 is then TRUE.

 

Working capital

Remember that Current assets are assets that the firm easily can convert into cash and that the Current liabilities are the bills that the company needs to pay within 12 months.

If your current liabilities are greater than your current assets it means you’re in trouble and you need to find cash somehow.

The Net working capital is the term that is used and it is defined as Current assets – Current liabilities.

The Net working capital is defined as the difference between the Current assets and the Current liabilities.

Figure 4. The Net working capital is defined as the difference between the Current assets and the Current liabilities.

The Net working capital is the short-term capital that the firm has to work with.

We will use the Net working capital when we do our cash flow calculations and we will also use it in the next chapter when we do analysis of financial statements.

In accounting you will see that the term Net working capital is used but in finance the term Capital is used more broadly for all assets.

If we now have our Current assets and Current liabilities on different sheets like this:

Screenshot of Microsoft Excel showing Assets, Liabilities and Working capital on different sheets.

Figure 5. Screenshot of Microsoft Excel showing Assets, Liabilities and Working capital on different sheets.

If we now want to calculate the Net working capital we do it like this:

In the sheet Working capital we type an equal sign in cell D16.

Screenshot of Microsoft Excel showing how to calculate a Net working capital: Step 1.

Figure 6.Screenshot of Microsoft Excel showing how to calculate a Net working capital: Step 1.

We then click on the sheet Assets in Figure 5. and we click on Current assets (cell B13):

Screenshot of Excel showing how to activate cell B13 in the sheet called Assets.

Figure 7. Screenshot of Excel showing how to calculate a Net working capital: Step 2.

If we look in the formula bar in Figure 7 we see that we have now activated cell B13 in the sheet called Assets. The exclamation sign means that we are using a different sheet for our data.

We then type a “-“-sign (1) and click on the “liabilities”-sheet (2) in Figure 5:

Then we click in cell B16 and hit Enter and we are immediately brought back to the Working capital sheet.

Screenshot of Microsoft Excel showing how to calculate the Net working capital from different working sheets in the same Excel document.

Figure 8. Screenshot of Microsoft Excel showing how to calculate the Net working capital from different working sheets in the same Excel document.

What’s important to remember here is not to click on Working capital sheet but rather hit Enter (if you don’t hit Enter your formula will be ruined).

If we then go back to the Working capital sheet and hit the F2 key, this will appear:

Screenshot of Microsoft Excel showing how to calculate the Net working capital in cell D16.

Figure 9. Screenshot of Microsoft Excel showing how to calculate the Net working capital in cell D16.

Of course, different businesses have different values for their Net working capital, but in general, the number should be positive.

Liquidity

We will then turn our attention to Liquidity:

Different concepts of liquidity and how it can be used.

Figure 10. Different concepts of liquidity and how it can be used.

Liquidity is important because if you run out of it you’re in trouble.

If your working capital is getting too small then maybe you have to sell assets to get cash for the company.

Liquidity is defined as:

How quickly an asset can be converted into cash.

Furthermore, liquidity has two dimensions:

  1. Ease of conversion into cash.
  2. Loss of value because you have to sell your asset quickly.

There are highly liquid assets which can be sold quickly without loss of value. (This can be inventory or a short-term investment).

How liquid is cash? That is the most liquid.

How liquid is accounts receivable? You can quite easily convert accounts receivable into cash. In fact you can sell those assets to bank and get cash in return.

The we have illiquid assets which are assets that cannot be sold quickly without significant price reduction. Examples of this are machinery and buildings.

You can almost sell anything if you reduce the price enough.

On the Balance sheet the items are usually listed in decreasing liquidity so that the most liquid assets come first.

Another aspect of liquidity is that businesses that have it can go out and get a loan easily.

A firm needs a positive working capital in order to pay its bills, but there is another aspect to it:

The reason why a lot of companies are keeping a lot of cash on their balance sheets is so that they can go out and buy other businesses quickly.

The last point is that you probably don’t want to have too much cash on your balance sheet because it doesn’t earn any return.

Building a balance sheet

We will then turn our attention to building a balance sheet:

Screenshot of Microsoft Excel showing how to build a balance sheet.

Figure 11. Screenshot of Microsoft Excel showing how to build a balance sheet.

The first thing that we want to look at is assets. What I do is that I type en equal sign in cell B10 and then I write SUM( and I highlight cells B7 and B8:

Screenshot of Microsoft Excel showing how to calculate Total assets by adding Current assets and Fixed assets.

Figure 12. Screenshot of Microsoft Excel showing how to calculate Total assets by adding Current assets and Fixed assets.

Then we do the same thing for the liabilities in cell E7 and E8:

Screenshot of Microsoft Excel showing how to calculate Total liabilities by adding Current liabilities and Long-term debt.

Figure 13. Screenshot of Microsoft Excel showing how to calculate Total liabilities by adding Current liabilities and Long-term debt.

The result in E10 will of course be $550.

How are we then going to calculate the equity? If you remember the fundamental accounting equation:

Assets = Liabilities + Equity

In other words:

Equity = Assets – Liabilities

So what we do is that we take the assets and we subtract the liabilities:

Screenshot of Microsoft Excel showing how to calculate Shareholders' equity by subtracting Total liabilities from Total assets

Figure 14. Screenshot of Microsoft Excel showing how to calculate Shareholders’ equity by subtracting Total liabilities from Total assets.

Then finally we can check that liabilities and equity equal assets, like so:

Screenshot of Microsoft Excel showing how to calculate Total liabilities and Shareholders' equity by adding cells E10 and E11 together

Figure 15. Screenshot of Microsoft Excel showing how to calculate Total liabilities and Shareholders’ equity by adding cells E10 and E11 together.

And the result in cell E12 is of course $1650.

Debt vs. equity

In finance we have Assets which are the use of the funds and on the other side of the equation we have Debt and Equity. Debt and Equity represent the source of funds:

Fundamental accounting equation where the Assets represent the use of funds and Debt and Equity the source of funds

Figure 16. Fundamental accounting equation where the Assets represent the use of funds and Debt and Equity the source of funds.

If we look at the Source of funds we will see that there are two different items: Debt and Equity. What is the difference between the two?

The debt is a Fixed claim and is something that you must pay back to the lender at some point in the future + interest.

The Equity on the other hand is Residual claim which means that you as a company does not have to pay the holder back

If I go out and buy a stock of ABC Corp. in the stock market for $50, the company does not have to pay me back if the stock loses value.

If the company goes bankrupt and there isn’t enough money around to pay off the creditors I get nothing. Residual means “left over”.

Then we have dividends which are only paid out once there is something left over.

Why then would anybody like to do equity if it’s always residual?

It’s because of the upside. If you invest in a well run business with equity that is steadily growing then you can make a lot of money.

Interest expenses (cash out) are tax deductible. This means that when you are paying your taxes you will pay a little bit less if you have interest payments.

Let’s say that you are paying $2500 in interest then if you deduct the interest payments you will less than that (let’s say $2300).

On the other hand dividends (cash out) are not tax deductible which means that there is a slight advantage of using debt.

The creditors are also paid first during bankruptcy while if you have equity you will get whatever is left over.

Debt

We will now turn our attention to debt:

Description of the expressions "Capital structure" and "Financial leverage"

Figure 17. Description of the expressions “Capital structure” and “Financial leverage”.

The question of whether to use debt or equity to raise funds is called Capital structure.

The term Financial leverage is used when the firm has debt. If you are using debt wisely you can reap the benefits of having your debt tax deductible and put the capital to productive use.

The more debt you have, the higher your leverage.

Leverage can magnify both gains and losses.

Market value vs. Book value

This will be the last topic that we will cover about the Balance sheet:

Description of the Market value and the Book value of an asset.

Figure 18. Description of the Market value and the Book value of an asset.

Market value

The Market value is the amount that you would get if you sold your equity stake.

For financial assets like stocks and debt you can go out and see what the value is every day.

But for a lot of equity this is not really possible so we need to estimate the market value.

Otherwise you do not know for sure until you sell your asset.

Book value

For fixed assets like machines and trucks finding a market value becomes much more difficult.

That is why accountants have invented the Book Value.

When I buy the business outright, the machines, the buildings and the inventory, that receipt is telling me what it is and will be the sum that I’m going to record.

That is also called the Historical cost principle and it is required by the Generally Accepted Accounting Principles (GAAP).

Then the Book value of the company often does not take into account the company’s most valuable assets such as:

  • Talented employees and managers
  • Customer lists
  • Reputation

These are all intangible assets, but it can be tangible assets also where the value of the assets can change radically from the price that you paid.

The Market value of an asset is almost always different from the Book value.

The goal of the financial management is to maximize the market value of the stock. At least in theory this is a good thing.

That means that the financial manager is more interested in the market value than he or she is of the book value.

We are given these numbers so we don’t have to research them:

Screenshot of Microsoft Excel showing Book value vs. Market value for a number of Balance sheet items.

Figure 19.Screenshot of Microsoft Excel showing Book value vs. Market value for a number of Balance sheet items.

Now we will calculate the Book value of the assets and we begin by adding cell B5 to the Book value in cell B16:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Book value of the New working capital to cell B16.

Figure 20. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Book value of the New working capital to cell B16.

We then add the Book value of the Fixed assets to cell B17:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Book value of the Fixed assets to cell B17

Figure 21. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Book value of the Fixed assets to cell B17.

Then we do the same thing for the Market value. We begin by adding the Net working capital to cell C16:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Market value of the Net Working Capital to cell C16

Figure 22. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Market value of the Net Working Capital to cell C16.

We then continue the Market value of the Fixed assets (cell B4):

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Market value of the Fixed Assets to cell C17.

Figure 23. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are adding the Market value of the Fixed Assets to cell C17.

Finally we highlight cells B18 and C18 and use the keyboard shortcut Alt  + =.

What that does is that it calculates an auto sum of the numbers above it:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are using the autosum option (Alt + =) in cells B18 and B19.

Figure 24. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are using the auto sum option (Alt + =) in cells B18 and B19.

We can then see that we have different numbers for Book and Market Value.

Finally we do the same thing for liabilities.

First we assume that the Book and Market value for the Long-term debt is the same:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are using the Long-term debt from cell B7 in B25 and C25.

Figure 25. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of different assets. Here we are using the Long-term debt from cell B7 in B25 and C25.

So what is then Shareholders’ equity?

Remember the fundamental accounting equation in Figure 16:

Assets = Debt + Equity

That means if we fill in Total assets from cells B19 and C19 into B27 and C27:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of Shareholders' equity and Total liabilities. Here we are using Total assets from cells B18 and C18 for Total liabilities and Shareholders' equity in cells B27 and C27.

Figure 26. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of Shareholders’ equity and Total liabilities. Here we are using Total assets from cells B18 and C18 for Total liabilities and Shareholders’ equity in cells B27 and C27.

we can then calculate Shareholders’ equity by subtraction:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of Shareholders equity and Total liabilities. Here we are calculating the Shareholders' equity in cell B26 by subtracting B25 from B27.

Figure 27. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of Shareholders equity and Total liabilities. Here we are calculating the Shareholders’ equity in cell B26 by subtracting B25 from B27.

Then we do the same thing for the Market value in cell C26.

The final result is like this:

Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of Shareholders' equity and Total liabilities. Here we are showing the final result.

Figure 28. Screenshot of Microsoft Excel where we are calculating the Book value and the Market value of Shareholders’ equity and Total liabilities. Here we are showing the final result.

Income statement

In this section we will talk about the Income statement.

The Income statement is different from the Balance sheet in that it shows revenues, expenses and net income for the whole period.

On the other hand, what the Balance sheet showed us was valid just for a particular day.

The first thing that we are going to look at is the Revenue:

 Picture showing Total revenue, Total sales and Net sales.

Figure 29. Picture showing Total revenue, Total sales and Net sales.

Total Revenue is the accounting term used for the Total sales of the business during the period.

Then we have Net sales which is the Amount earned by the business from delivering products or services.

You get this from taking the Total revenue and then subtracting any expenses.

Another thing that is important to recognize is that the company cannot record revenue before the product is delivered to the customer.

In accounting that is called accrual accounting.

The definition of accrual accounting is this:

Generally Accepted Accounting Principles (GAAP) definition of Accrual.

Figure 30. Generally Accepted Accounting Principles (GAAP) definition of Accrual.

As you can see there is both an Expense and Revenue meaning of the word.

If the business receives a bill and it isn’t due until 30 days later, they have to record the expense at the earlier date.

This is then an example of a hypothetical Income statement:

As you can see the statement is valid for the whole period ending on December 31, 2016.

For publicly traded stocks the reporting periods are either annual or by quarter.

In Figure 31 you will find a made up Income statement:

Screenshot of Microsoft Excel showing a hypothetical Income statement.

Figure 31. Screenshot of Microsoft Excel showing a hypothetical Income statement.

An Income statement is profit or loss for the whole period ( in this case the period is a whole year.)

We have our Total revenue and Cost of goods sold.

If you sell a widget for $100 and you paid $50 for it, you will record $100 as revenue and $50 for cost of goods sold.

If you look in cell C6 you will find an item called Depreciation and this is where accrual accounting comes in.

This is where the cash may be spent at at a different period from where it is received. We will look into when to book an expense like that.

It could come as cash, or it could come later as accounts receivables as we saw in the balance sheet.

Then we have Earnings before interest and tax which we get by subtracting Cost of goods sold and Depreciation from the Total revenue.

We calculate this by adding a formula. We first take Total revenue in cell C4 and subtract the sum between the Cost of goods sold in cell C5 and Depreciation in cell C6:

Screenshot of Microsoft Excel showing how to calculate Earnings before interest and tax by subtracting the sum of C5 (Cost of goods sold (COGS)) and C6 (Depreciation) from C4 (Total revenue).

Figure 32. Screenshot of Microsoft Excel showing how to calculate Earnings before interest and tax by subtracting the sum of C5 (Cost of goods sold (COGS)) and C6 (Depreciation) from C4 (Total revenue).

The result in cell C7 is then of course $1,928,000. We then do the same thing when we calculate the Taxable income by subtracting Interest paid (C8) from Earnings before interest and tax (C7):

Screenshot of Microsoft Excel showing how to calculate Taxable income by subtracting Interest paid (C8) from Earnings before interest and tax (C7)

Figure 33. Screenshot of Microsoft Excel showing how to calculate Taxable income by subtracting Interest paid (C8) from Earnings before interest and tax (C7).

The result in cell C9 is of course $1,856,000. Finally we are doing the same thing when we are calculating the Net income in cell C11 by subtracting taxes in cell C10 from Taxable income in cell C10:

Screenshot of Microsoft Excel showing how to calculate Net income by subtracting Taxes (C10) from Taxable income (C9).

Figure 34. Screenshot of Microsoft Excel showing how to calculate Net income by subtracting Taxes (C10) from Taxable income (C9).

The final Net income in cell C11 is then $1,536,00. The Net income (or the Earnings) can go in one of two places. Because technically they belong to the shareholders they can either be paid out in dividends or they can be plowed back into the business to buy more assets.

Generally if the company keeps a lot of retained earnings, it’s because they have good ideas of to make the company grow.

If we go back to the Balance sheet (Figure 8) and take a look at the item that is called Retained earnings, we now understand where that item comes from.

So in order to calculate how much money ABC Corp is keeping to plow back into the company we need to take the Dividend (cell C13) and subtract from the Net income (cell C11):

Screenshot of Microsoft Excel showing how to calculate Retained earnings (cell C14) by subtracting Dividends (cell C13) from Net income (cell C11).

Figure 35. Screenshot of Microsoft Excel showing how to calculate Retained earnings (cell C14) by subtracting Dividends (cell C13) from Net income (cell C11).

We will also consider the total number of shares outstanding. This means that at this particular date, the 31 December 2016, there were 210,000,000 shares outstanding.

Then we can calculate Earnings per share by dividing Net income (cell C11) x 1000 (because our numbers are divided by 1000 to begin with) by Shares outstanding (cell C15).

The formula looks like this:

Screenshot of Microsoft Excel showing how to calculate Earnings per share (cell C16) by dividing Net income (cell C11) times 1000 and the total number of shares outstanding (cell C15).

Figure 36. Screenshot of Microsoft Excel showing how to calculate Earnings per share (cell C16) by dividing Net income (cell C11) times 1000 and the total number of shares outstanding (cell C15).

The result in cell C16 will then be $7.31.

After that we continue with Dividend per share where we divide the Total dividend paid out in cell C13 (x 1000) with the total number of shares outstanding (cell C15):

Screenshot of Microsoft Excel showing how to calculate Dividend per share (cell C17) by dividing Dividends (cell C13) times 1000 and the total number of shares outstanding (cell C15).

Figure 37. Screenshot of Microsoft Excel showing how to calculate Dividend per share (cell C17) by dividing Dividends (cell C13) times 1000 and the total number of shares outstanding (cell C15).

The result in cell C17 will then be $0.31 which means that in this case the company keeps a lot of the earnings.

Depreciation

Then we come into the subject of depreciation:

Green picture with text Depreciation is a non-cash expense that shows up on the income statement

Figure 38. Explanation of the word Depreciation: Depreciation is a non-cash expense that shows up on the income statement.

In the following example we are using trucks for FedEx for $10 million with a Salvage value of 500,000 and an estimated time in use of 7 years:

The salvage value is what you would get out of your investment in a fire sale:

Screenshot of Microsoft Excel showing Linear depreciation of a $10,000,000 investment, $500,00 slavage value and 7 years of use.

Figure 39. Screenshot of Microsoft Excel showing Linear depreciation of a $10,000,000 investment, $500,00 salvage value and 7 years of use.

What this means is that if the cash goes out the first year, but that the Depreciation event is accounted for every year.

That means that there is no cash associated with it because it all went out the first year.

To give you an example of what I mean we first need to discuss the Matching principle:

Definition of the Matching principle in accounting.

Figure 40. Definition of the Matching principle in accounting.

This is part of the concept of accrual accounting that we discussed earlier in this chapter. What it means is basically that we need to add revenue and expenses in the right period so that they finally add up.

If we then look at Figure 39 again we see that we have a linear depreciation for our purchase of the assets over 7 years. What that means is that we assume that the trucks are going to last for 7 years.

But if we have the trucks they are going to generate revenue during that whole period and this we have to account for.

We do thisby matching the revenue that we are going to get from the trucks with the depreciation.

If we put this information into Excel it will look like this:

Screenshot of Microsoft Excel showing how to account for depreciation to match the related revenue for the same period.

Figure 41. Screenshot of Microsoft Excel showing how to account for depreciation to match the related revenue for the same period.

This is then the proper way of accounting for depreciation.

Another example of depreciation comes when we calculate Net capital spending or NCS:

Screenshot of Microsoft Excel showing how to calculate a Net capital spending or NCS.

Figure 42. Screenshot of Microsoft Excel showing how to calculate a Net capital spending or NCS.

What we are going to do is to calculate a Net cash flow from our accounting numbers.

The first thing that we need to look at is the begin number. The begin number we find in the Balance sheet item of Net fixed assets of the 31 December 2016 (cell B8).

Then we need to find the end number which is the Net fixed assets on the 31 December 2017 (cell B9).

That means that we have more cash at the end of the period than in the beginning (which is a good thing).

Then we need to look at the depreciation and because these items have already taken into account the depreciation we need to add it back.

The calculation therefore becomes B9 – B8 + B10. In Excel it looks like this:

Screenshot of Microsoft Excel showing the formula for calculating Net capital spending in cell B12.

Figure 43 Screenshot of Microsoft Excel showing the formula for calculating Net capital spending in cell B12.

The result in cell B12 is then of course $265,000.

Cash flow from accounting information

We previously discussed the concept of accrual accounting.

There is a fundamental problem with it and that is that it doesn’t consider cash flow.

That is what financial managers are interested in, cash.

We therefore have to take the financial numbers of the balance sheet and the income statement and convert them into cash:

Screenshot of Microsoft Excel showing a hypothetical balance sheet from which we are going to calculate cash flow.

Figure 44 Screenshot of Microsoft Excel showing a hypothetical balance sheet from which we are going to calculate cash flow.

On the balance sheet accrual accounting is for instance affecting Net fixed assets. Similarly, on the Income statement, Sales are recorded when they are earned and expenses are recorded when they are paid out:

Screenshot of Microsoft Excel showing a hypothetical Income statement from which we are going to calculate cash flow.

Figure 45. Screenshot of Microsoft Excel showing a hypothetical Income statement from which we are going to calculate cash flow.

The next figure is again the fundamental accounting equation:

The fundamental accounting equation where Assets equal Debt plus Equity.

Figure 46. The fundamental accounting equation where Assets equal Debt plus Equity.

In finance we think of the equation like this:

When FedEx buys trucks or Coca-Cola buys buildings they are acquiring assets which in other words is use of cash or funds. The reason why they buy these assets is because they think that they are going to make a profit from them.

Where are we then going to get the cash from? The cash is coming from either Debt or Equity which are the source of funds.

This equation, where we have Use of cash and Source of cash, will be our starting point when we calculate cash flow.

So the first question to answer is “What is cash flow?”

Figure explaining the concept of Cash flow.

Figure 47. Figure explaining the concept of Cash flow.

In finance people care about cash in and cash out.

Cash flow is not the same thing as Net earnings.

We will therefore have to derive cash flow information from the Balance sheet and the Income statement.

We will look at how cash is generated from utilizing assets and how it’s paid to those that finance the purchase.

Figure explaining how Cash flow from assets equals Cash flow to creditors and Cash flow to stockholders.

Figure 47. Figure explaining how Cash flow from assets equals Cash flow to creditors and Cash flow to stockholders.

What this means is that Cash flow from assets can either go to the Bondholders or the Stockholders.

In the next figure all the calculations we are going to do are summarized:

Summary of all the Cash flows that we are going to calculate.

Figure 48. Summary of all the Cash flows that we are going to calculate.

If we begin with the second box we see that it says Cash flow from assets and the way to calculate it is by taking the Operational cash flow and subtracting Net capital spending and Changes in net working capital.

In order to calculate the Operational cash flow we need to take the EBIT (Earnings Before Interest and Taxes) and add back the Depreciation minus Taxes.

To understand this we need to take another look at our Income statment (Figure 45). There we see that we have an item called EBIT and below that we have Interest. Where does the interest go? To the creditors. This means that EBIT should belong to the Operational cash flow.

In the end we need to subtract the Taxes from EBIT and add back the Depreciation, because it’s a non-cash item.

To calculate the Net capital spending we need to take the end value of the Net fixed assets then subtract the value of the Net fixed assets at the beginning of the period and finally add back the non-cash Depreciation on the Income statement. We do this because we’ve already taken this into account when we calculate the value of the assets.

Changes in Working capital is all change calculated by taking something at the end of the period minus the beginning. In this particular case we do it by taking the working capital for the end of the period minus working capital for the beginning. How do we calculate the working capital? We do it by subtracting current liabilities from current assets.

Then we have the Cash flow that go out to our creditors. The first thing that we do is that we identify the Interest paid and then we subtract the Net new borrowing. The Net new borrowing is always the change of the Long-term debt, calculated as the long-term debt at the end of the period minus the long-term debt at the beginning.

Of course the net new borrowing can be both positive or negative. If the company takes on more debt then the Net new borrowing is positive and we will say Interest paid minus the new debt that we’ve issued will be our Cash flow to creditors.

At last we have Cash flow to shareholders which is similar to Cash flow to creditors. We begin by taking the dividends paid and then subtracting the Net new equity that we’ve raised.

The way that we calculate the Net new equity is that we begin by looking at value of the common stock and paid-in surplus at the end of the period minus the common stock and paid-in surplus at the beginning of the period.

Then we have defined all the cash flow calculations that we are going to do.

Income statement, Balance sheet and Cash flow problem

We are then going to take on a comprehensive problem. We will call it Exercise 1. In Figure 49 we have a lot of information that we are going to use:

We have information about the corporation, the dates and the tax rates and for a number of accounts we have a begin of a period and an end of the same period.

What we are going to do is to calculate an income statement, a balance sheet and then a cash flow.

Screenshot of Microsoft Excel showing assumptions, requirements and accounts for the two years 2016 and 2017 that we are going to use for the exercise (1).

Figure 49. Screenshot of Microsoft Excel showing assumptions, requirements and accounts for the two years 2016 and 2017 that we are going to use for Exercise 1.

This is then the income statement that we are going to fill out:

Screenshot of Microsoft Excel showing the hypothetical income statement that we are going to fill out in Exercise 1.

Figure 50. Screenshot of Microsoft Excel showing the hypothetical income statement that we are going to fill out in Exercise 1.

The first thing that we notice in the exercise is that the income statement is for the year 2017. That means that we only need to take the values for 2017. In cell C28 we therefore type =C15, like so:

Screenshot of Microsoft Excel showing how we take the value for Sales and 2017 (C15) and put it in the income statement.

Figure 51. Screenshot of Microsoft Excel showing how we take the value for Sales and 2017 (C15) and put it in the income statement.

Next we take the Cost of goods sold for the year 2017 which we find in cell C16:

Screenshot of Microsoft Excel showing how we take the value for the Cost of goods sold and 2017 (C16) and put it in the income statement.

Figure 52. Screenshot of Microsoft Excel showing how we take the value for the Cost of goods sold and 2017 (C16) and put it in the income statement.

Then the Depreciation for the year 2017 is found in cell C17:

Screenshot of Microsoft Excel showing how we take the value for the Depreciation and 2017 (C17) and put it in the income statement below in the figure.

Figure 53. Screenshot of Microsoft Excel showing how we take the value for the Depreciation and 2017 (C17) and put it in the income statement below in the figure.

Then we want to calculate the earnings before interest and tax and the way we do that is to take our Net sales and then subtract the Cost of goods sold and the Depreciation. In Excel it looks like this:

Screenshot of Microsoft Excel showing how we to calculate the Earnings before interest and taxes by subtracting the Cost of goods sold and Depreciation from Net sales.

Figure 54. Screenshot of Microsoft Excel showing how we to calculate the Earnings before interest and taxes by subtracting the Cost of goods sold and Depreciation from Net sales.

The result in cell C31 is then of course $930.

Then we want to find the interest which is given in cell C18:

Screenshot of Microsoft Excel showing how we take the value for the Interest and 2017 (C18) and put it in the income statement below in the figure.

Figure 55. Screenshot of Microsoft Excel showing how we take the value for the Interest and 2017 (C18) and put it in the income statement below in the figure.

Then we want to calculate the Taxable income. How do we do that?

We take our earnings before interest and tax (C31) minus the interest (C32), like so:

Screenshot of Microsoft Excel showing how to calculate the Taxable income by subtracting Interest from Earnings Before Interest and Tax (EBIT).

Figure 56. Screenshot of Microsoft Excel showing how to calculate the Taxable income by subtracting Interest from Earnings Before Interest and Tax (EBIT).

Then we are going to calculate the taxes. How much are we to pay in taxes?

To calculate that we first have to take the taxable income (C33) and multiply with the Tax rate (B5):

Screenshot of Microsoft Excel showing how to calculate Taxes by multiplying the Taxable income (cell C34) with the Tax rate (cell B5).

Figure 57. Screenshot of Microsoft Excel showing how to calculate Taxes by multiplying the Taxable income (cell C34) with the Tax rate (cell B5).

If we then hit enter we will get two decimals like this in cell C34:

Screenshot of Microsoft Excel showing taxes with two decimals.

Figure 58. Screenshot of Microsoft Excel showing taxes with two decimals.

What this then means is that we usually have to round to the nearest dollar (but in this case we don’t have to). So how do we do that?

In the same way that we can use the sum function for summing or the average function for calculating averages, there is a round function that we can use. It is used like this:

Screenshot of Microsoft Excel showing how to use the <i>round</i> function.

Figure 59. Screenshot of Microsoft Excel showing how to use the round function.

What do we do in Figure 59? We are rounding to the nearest dollar.

Finally to calculate the Net income we take the taxable income in cell C33 minus the taxes in cell C34, like so:

Screenshot of Microsoft Excel showing how to calculate Net income by subtracting Taxes (cell C34) from Taxable income (cell C33).

Figure 60. Screenshot of Microsoft Excel showing how to calculate Net income by subtracting Taxes (cell C34) from Taxable income (cell C33).

Then we have dividends which is given in cell C19:

Screenshot of Microsoft Excel showing how to transfer Dividends from cell C19 to C37.

Figure 61. Screenshot of Microsoft Excel showing how to transfer Dividends from cell C19 to C37.

Now the dividends can go in either of two ways. Either it goes out of the company to the shareholders or its kept within.

If it’s kept within the company, the dividends are to be used for purchase of new productive assets. Thus they are added to the retained earnings or subtracted from the net income, like so:

Screenshot of Microsoft Excel showing how to add the Dividends to the Retained earnings by subtracting them from the Net income.

Figure 62. Screenshot of Microsoft Excel showing how to add the Dividends to the Retained earnings by subtracting them from the Net income.

We then want to build our balance sheet.

First, we look at the current assets for 2016:

 

This article is part of a series. You can read Part 1 here and Part 2 here. This article is highly influenced by the excellent work of Michael Girvin (Excelisfun) on Youtube.

Embed This Image On Your Site (copy code below):

Nucor, Inc.

Fundamental analys of Nucor, July 15, 2017

Green picture of steel beam with text about Nucor, Inc.

Update, August 14, 2017

Nucor is valued at 22.2 times its earnings in 2016. This is in my opinion too pricey.

If we then estimate the earnings for this year to $3.50 the P/E multiple comes in at 15.8 which is still too high, but slightly better.

Balance sheet, earnings and dividend history are all outstanding.

 

In summary I would not buy Nucor at these prices.

 

Description:

Nucor is an American steel producer that sells steel and steel products in the United States and internationally. Their headquarters are in Charlotte, North Carolina.

 

Valuation:

At $60 and 24 times trailing earnings, Nucor is expensive. When looking at an average of the past three years’ earnings the P/E ratio comes in at 37 which is a lot of money.

The Price to book value is also high at 3.6.

 

Balance sheet:

The Balance sheet of course looks good. In the end, this is what the market is paying for. Their Debt to equity ratio is 0.8 which is considered low risk and their Working capital is $4.1 billion which at least means that they can pay their short-term bills.

 

Free cash flow and dividend:

Last year Nucor had a Free cash flow of $1.1 billion which equates to $3.50 a share. Of this they are paying a dividend of $1.49 which means that the current yield is 2.5%.

Nucor is part of The Dividend Aristocrats which means that they have been paying out uninterrupted and increasing dividends for more than 25 years.

 

Conclusion:

If the share had been 30 per cent cheaper I would have been a buyer. Now it is too expensive for my taste.

 

 If you would like to learn more about fundamental analysis you can do that here.

Johnson & Johnson

Fundamental analysis of Johnson & Johnson, June 30, 2017

Blue picture with red soap bottle icon and text about Johnson & Johnson

Update, August 13, 2017

At 22.4 times earnings the Johnson & Johnson stock is too expensive for my taste. In the first quarter they earned $1.61 which allows us to say that they will be making at least $5.50 for the year.

This gives a forward P/E multiple of 24.2 which is way to high for my taste.

Otherwise it’s a well-managed company with solid earnings and a good dividend history.

 

Description:

Johnson & Johnson is an American healthcare company that researches, manufactures and sells various products in the health care field.

 

Valuation:

The company is expensive at a cool 22.4 times trailing earnings. When looking at an average over the past three years’ earnings, the P/E ratio is almost the same at 23.2. Because the company has a lot of intangible assets the Book value is only $7.50 a share which obviously makes the Price to Book value very high.

 

Balance sheet:

The Balance sheet looks very stable with a Working capital of $38.7 bn and a Working capital to Debt ratio of 0.5. The Debt to Equity ratio is 1.0 and its current Return on Equity is 23 per cent which are solid numbers.

 

Free cash flow and dividend:

Johnson & Johnson has a Free cash flow of $15.5 bn which allows it to pay out a dividend of $2.95 which equates to a yield of 2.2 per cent. The company has been paying out uninterrupted and increasing dividends for 25 years.

 

Conclusion:

Johnson & Johnson is a very well run business with steady earnings and a good cash flow. The only problem is the valuation where you are paying too much for what you get. Had the company been 30 per cent cheaper I would be a buyer. Now it’s a HOLD.

 

If you would like to learn more about fundamental analysis you can do that here.