Posts Tagged ‘intrinsic value’

Learn how to invest, read stock analysis, and find stock picks

Free Discounted Cash Flow DCF Valuation Spreadsheet

March 24th, 2008 by Jae Jun

Jae Jun

Free Excel Discounted Cash Flow Spreadsheet

Previously I put up a Ben Graham formula investment spreadsheet.

As with any intrinsic value model, there are shortcomings and disadvantages. Even with this version of the DCF spreadsheet, there are disadvantages but it is logical and reasonable.

This free DCF stock valuation spreadsheet utilizes the Discounted Cash Flow method, which I believe to be one of the most logical methods of valuing a business and estimating its fair value.

How to Value a Stock

The discounted cash flow intrinsic value calculation is based off FWallStreet’s method. The original spreadsheet can be downloaded from his post on JNJ.

This free version is just an enhanced version of the original spreadsheet. The underlying calculations are the same but many tweaks have been made to the formula and variables.

The full version of the stock valuation spreadsheet includes an entire suite of valuation models at great value. Buyers receive 1 year of free updates as well.

How to Use this Excel DCF Spreadsheet

If you have read the AAPL stock analysis post, you would have seen the screen shots of what it looks like. If you have personally used either the Benjamin Graham valuation spreadsheet or the portfolio tracking spreadsheet you will know how much time you save with the automated downloading of data.

Once you have the valuation spreadsheets working, just be sure to enter the ticker in the ticker box only. Everything starts from there.

Other Points

Since this stock valuation calculator is free, if you enjoy this spreadsheet, consider buying the best stock valuation spreadsheet available on the internet.

IMPORTANT!

Please read the installation guide and FAQ.

To date, I’ve been spending hours helping people with simple excel issues on a free product rather than anything spreadsheet related.

So for all excel and install problems, place all questions in the comment sections below. That way I won’t have to answer the same question again and again.

How to Download

The download has now moved.

To download the spreadsheet, you must go to the main page of Old School Value and sign up with your email to receive a list of free spreadsheets.

The list includes nine free spreadsheets, the add-in required to download data from the internet as well as all the installation help material.

Premium Spreadsheets

Feel free to check out the free version and then when ready, go to the stock valuation software page and review what you will get with the premium.

The premium version includes several valuation models as well as fundamental analysis data, historical data, charts and competitor comparison features. Just by entering one ticker, you can immediately get all that information on your favorite stock which will save you hours in your analysis.

Go now and see for yourself why people rave about the spreadsheets.

DCF Excel Spreadsheet Screenshot

Tags: excel, intrinsic value, spreadsheet
Posted in Investment Tools | 25 Comments »

American Eagle Outstanding (AEO) Valuation

March 11th, 2008 by Jae Jun

Seems like the current pull back clipped the wings of American Eagle. At its current price of $17.17 is it a superb deal or a value trap?

Recent News

On March 6th, AEO dropped 15%. Panic had set into the market. The reason? Same store sales fell 4% in February. Head over to any finance sites and people are asking about the drop and their panic is evident. Was I panicking? Sure was. I was panicking with excitement at the chance of picking up 50c dollars.

I’ll try going over a few things here.

Insider Transactions

Head over to the SEC website (direct link to AEO filings) and view the form 4’s. Form 4 is a filing that must be submitted to the SEC for insider transactions.

From the latest filings, we see that a little over 1 million shares were exercised or purchased (I believe they are stock options) at $21.28. None of which have been sold yet.

This tells me a couple of things:
  1. Insiders believe the price of $21.28 is underpricing the value.
  2. Insders are confident in their company and their business prospects.
The Industry

Frankly, retail and teen fashion are horrible industries. Retail is cyclical, fashion is just as cyclical and seasonal. One day the shirt you’re wearing is considered hot and the next, it’s out dated. Combine the two and you are looking at a difficult industry with little moat between competitors. However, make your mark, get brand awareness and apply some competitive advantage and you may just have what it takes for great growth in a highly competitive but profitable industry.

The Brand

On Feb 22, in a recent teen survey by Pali Research showed that “hands down” AEO won as the favorite brand and most dollars spent.

Currently, AEO are running the American Eagle, Aerie, Martin + Osa, 77kids.
Martin + Osa seems to be finding difficulty in engraving its place in retail but Aerie is continuing to increase sales along with America Eagle. The real important question you have to ask yourself is “do you believe that the company will grow in the next 5 years or so?”

The conclusion to the question above will eventually dictate your decision on AEO as a potential investment. Remember what Buffett said.

Growth and value are joined at the hip – Warren Buffett

The Management

This section I must leave to individuals to decide upon the competency of management. Don’t let my thoughts cloud or influence yours. There are many hints from annual reports and other publications that you should read for yourself as well seeing this interview from CNBC.
James O’Donnell the CEO speaks candidly in this interview, something that I always look for because it portrays the CEO as focusing on the business, operations and future growth, rather than focusing on reporting lovable earnings for the short term.
Part 1of video
Part 2 of video

The Intrinsic Value

AEO has been able to steadily increase its earnings, owner earnings for the past 10 years without any drastic volatility. I like to invest in companies where they have consistent growth rather than huge ups and downs. AEO fits that filter.

It seems like AEO has been able to grow FCF at 35% for the past 10 years. They have no long term debt, a nice lump of cash in their balance sheet and for every $1 invested into the company, they were able to generate $0.17 with it. Can AEO do this for the next 5 or 10 years? I don’t know. But by being reasonable, I do have an idea of a potential growth rate.

Can AEO keep up the 35% FCF growth for the next 5-10 years? I doubt it. Assuming that the company is able to grow at 15% for 3 years, slowed down 10% in years 4-7, slowed down another 10% in years 8-10 followed by 5% growth in years 11-20, the intrinsic value comes out to $37.37. 50% margin of safety applied for a small, fast growing company in a competitive industry gives a target buy price of $18.69.

Can the price go lower? Why not? The lowest any company can go is $0, but considering current economic situations, uncertainty and fear in the market, I’m sure there is a good chance the price may drop. But I see a huge gain compared to a short term possibility of 10% loss.

Price is what you pay, value is what you get – Warren Buffett

In answer to the first question. Is it a superb deal or a value trap? My analysis and research reveals that it is a deal. But then again, I’m not always right. Let me know what you think.

Disclaimer

I own shares of American Eagle Outstanding.

Tags: aeo, american eagle, intrinsic value, Stock Analysis, valuation
Posted in Stock Analysis | 5 Comments »

Benjamin Graham Formula Free Stock Valuation Spreadsheet

February 26th, 2008 by Jae Jun

Jae Jun

If you haven’t read The Intelligent Investor, you are missing out on timeless advice. One of which is to buy at a great margin of safety. I won’t be going through the details of the book but an explanation and how to is explained in the article titled “How to value a stock with Graham Formula“.

Instead, I’ve applied Benjamin Graham’s formula to a free stock valuation spreadsheet that allows anyone to quickly value the fair value of a company (quantitatively). There are sites that already do this but I wanted something where I could do it on my own for any company. (download link is down the bottom)

A quick quote to start things off.

Confronted with a like challenge to distill the secret of sound investment into three words, we venture the following motto, Margin of Safety. – Benjamin Graham

Benjamin Graham Formula Overview

Ben Graham’s formula is as follows:

Intrinsic Value = “normal” earnings x (8.5 + (2 x expected 5 yr growth)) x (4.4/20yr AA corp bond)

- Normal earnings refer to earnings over a period of years. Not just the previous year.
– 8.5 is the PE of a company with no growth.
– In the spreadsheet, growth rate is user defined. Check out a method to determine growth rate.
– Back when Graham wrote the book, he was using a 20 yr AAA corp bond rate of 4.4%. To apply the formula today, we need to normalize it to todays rate. I’ve put the 20yr AA corp bond rate as the denominator since the AA rate is slightly higher than the AAA and will give a slightly conservative number.

However, I use a very slight modification to this formula which I detail in an article I wrote titled “How to Value a Stock with the Ben Graham Formula”.

How To Use The Spreadsheet

I’ve tried to make it as user friendly and eye pleasing as possible. In order to get it working though, you MUST install the plugin for excel which is described below. The plugin allows excel to automatically retrieve all financial statements and prices that I use in the spreadsheet.

Some Explanations

A difficulty I had was to figure out how to come up with a reasonable future EPS guide. I know I’ve said I don’t like using EPS as a guide and I still stick to that. However, I wanted to see how the Graham formula worked and what type of valuation it revealed.

Here is how I calculated the future EPS. Note, I am a conservative guy. If you feel, the ranges are incorrect, let me know or try changing some things yourself and if it works better, let me know.

  1. For the 1st future year, I took the constant at which the EPS had linearly increased over 10 years
  2. I added the constant to the average increase of EPS throughout the past 10 years
  3. I then added an additional “growth sum” to the number I get from step 2
  4. For the 2nd future year, I took the constant
  5. Added it to the 1st future year
  6. Added the “growth sum”
  7. And so on

Other Points

Since this stock valuation calculator is free, if you enjoy this spreadsheet, consider buying the best stock valuation spreadsheet available on the internet.

IMPORTANT!

Please read the installation guide and FAQ.

To date, I’ve been spending hours helping people with simple excel issues on a free product rather than anything spreadsheet related.

So for all excel and install problems, place all questions in the comment sections below. That way I won’t have to answer the same question again and again.

How to Download

The download has now moved. To download the spreadsheet, you must go to the main page of Old School Value and sign up with your email to receive a list of free spreadsheets.

The list includes nine free spreadsheets, the add-in required to download data from the internet as well as all the installation help material.

Premium Spreadsheets

Feel free to check out the free version and then when ready, go to the stock valuation software page and review what you will get with the premium.

The premium version includes several valuation models as well as fundamental analysis data, historical data, charts and competitor comparison features. Just by entering one ticker, you can immediately get all that information on your favorite stock which will save you hours in your analysis.

Go now and see what you get.

Benjamin Graham Spreadsheet Screenshot

 


Tags: graham formula, intrinsic value, spreadsheet
Posted in Investment Tools | 45 Comments »

Apple (AAPL) Valuation – Part 2

January 28th, 2008 by Jae Jun

So a lot of people have been complaining that I was too conservative with my first valuation of AAPL. I assumed that AAPL would grow at 14.3% for the first 3 years, slowed down 10% in yrs 4-7 and further slowed down 10% in yrs 8-10 until the company slowed down to a steady 5% from yrs 10-20.

Why Do it This Way?

Because;
1) I am valuing a business, not a 4 letter ticker symbol
2) I’m not optimistic enough to believe that a company can grow at 20-30% for the next 10-20 years
3) I am valuing based on assets and previous performance
4) I want a solid, reliable investment. Not an emotional roller coaster.

More Realistic Growth

So you still think I need a realistic growth rate considering how Apple will take over the world… Well what is a realistic growth? I don’t believe there is such a thing since all growth predictions are just that… predictions. That is why I don’t care what growth rate the ‘pros’ give it.

And unless you are God, I won’t believe anyone trying to predict the future.

A More Aggressive Growth Situation

If I had used the growth rate based on AAPL’s FCF growth over the past 10 years, the growth rate would be 44%! That’s staggering! Apple was able to generate on average 44% free cash flow each year for the past 10 years. Can AAPL generate FCF at 44% for the next 10 years? Maybe, maybe not. I like to keep things safe and stick with the ‘maybe not’ option. After all, it’s my money I’m putting down, and I don’t want to lose it.

So just this once, I will use the 5 yr projected growth rate from Yahoo which is 22%.
So in years 1-5, growth rate will be 22%, slowed down 10% in yr 6-8, slowed down another 10% in yr 9-10, until it grows at 5% from yr 10-20.

Based on these assumptions, the present value of the future cash and its networth today comes out to be $172 billion. Which translates to an intrinsic value of $193.20.

In my first post I got an intrinsic value of $130 with a very conservative rate of 14.3%
Using a more ‘aggressive’ rate of 22% gives me an intrinsic value of $193.20

See the images below to see some ratios and calculations. (The first image got cut off but it should be from 1998-2007)

Price Follows Value

Now look at the following 2 graphs which represent actual stock price and calculated intrinsic value. The first one is the conservative scenario. The second one, aggressive.

Notice how price follows value?

In the conservative case, we see a situation where the market pushes the price up way above its intrinsic value. In the aggressive case, we see that if people had SOLD AAPL at $193, they would be sitting on some big fat profits before the market dived.

In either case, one has now reached its intrinsic value and the other is getting close to a buy price target. You be the judge.

It’s Just a Numbers Game

I could have made the intrinsic value come out to be $400 or $1000 just as easily. That’s what the speculators want to hear and they just want the affirmation of other people to agree with them. Don’t just fiddle the numbers to match the feeling you have about this stock.

Problems with the darlings on Wall Street is that people start buying just because someone else did. They find safety in numbers and in the deluded fact that if it goes down, at least they wont be the only fool.

Don’t be that fool. Buy companies based on objective analysis and reason.

Disclaimer
I hold no shares of AAPL

Tags: aapl, apple, intrinsic value, Stock Analysis
Posted in Stock Analysis | 3 Comments »

Apple (AAPL) Valuation

January 27th, 2008 by Jae Jun

As you probably know Apple is a leader in consumer electronics. If you’ve never heard of the iPod, it’s time to crawl out of the cave you’ve been living in.

Now the tech industry is a fast paced, constantly changing environment where companies have to be the first one to release an innovative product in order to get ahead. Just following the crowd won’t cut it in this sector. This is a reason why Warren Buffet and Charlie Munger do not invest in tech companies. They prefer stable, boring companies with steady growth rather than the wham-bam-thank you-maam nature of tech.

Apple as a Market Leader and Innovator

Apple are doing a lot of things right and it oohs and ahhs the crowd each time Steve Jobs releases a new gizmo at Macworld. The new Macbook Air is pure porn for the techies and nerds. However, Apple was only able to get back to being the darling on Wall Street with its iPod phenomenon. As it can be seen, nothing can currently penetrate the hold Apple has on the mp3 player market. Competitors like Creative, Samsung, Sandisk have all been trying but nothing seems to be working. Consumers just crave the small, sleek, clean design of the ipod. Who can’t resist?

The iPhone was launched in mid 2007 with huge success, selling over 1 million iPhones in its first 3 months. The iPhone catapulted Apple into the handset arena with its innovative and breathtaking features and usability. They also have many other products which I won’t go into here.

Making Sense of Historical Data

First off, Buffett tells us that we should be looking at at least 4-5 year histories. Makes sense, since figures from a single year does not say much. I tend to look at 10 year histories in order to get a sense of the company and how it has fared.

So What Do The Numbers Say?

Looking at the cash flow statement for the past 10 years we see that from 1998-2004 there was no real growth in Free Cash Flow (Buffett calls it Owner Earnings). The company burnt through $47 mil and $85 mil in 2001 & 2002 before turning a profit in 2003. This was probably due to the aggressive iPod campaign finally paying off. From 2003 on, FCF growth has been huge. Realistically, can this keep up?

Apple’s Future Growth

Since Apple has had a turnaround from the time Steve Jobs made a comeback, I will consider 5 years worth of historical data. Free Cash Flow grew at an average of 44.3% over the 5 years and CROIC at 14.3%. (For a full detailed explanation on CROIC go to http://www.fwallstreet.com/blog/23.htm). Now a company grows at the rate its cash grows since cash is what drives business and earnings. However, I prefer to think that the business will only grow as fast as its Cash Return On Invested Capital (CROIC). Here we see that for every $1 Apple invested from its FCF, it was able to generate an additional $0.143 in cash.

Apple’s Future Cash Value

At the end of fiscal year 2007, Apple had $4,735 mil in FCF. If the future cash of the business is to grow at a rate of 14.3% for the first 3 years, slowed down 10% for the next 4 years, and slowed down by a further 10% for the next 3 years, till it slowly grows at a rate of 5% for the next 10 years, the sum of the future cash for 20 years comes out to be $262,041 mil.

Apple’s Current Value

We’ve just calculated the sum of cash over 20 years. Apple will have $262,041 mil in cash. But we are not just buying the future $262,041 mil cash. We are buying the networth of the company as well. At the end of 2007, Apple’s shareholder equity was $14,532 mil.

But there is a problem, I don’t have $262,041 mil to buy the company’s networth of $14,532 mil + future cash of $262,041 mil today. And why would I want to pay $262,041 mil today just to receive $262,041 mil over 20 years resulting in a 0% return?

Discount it Back

By definition, the current value of the company is the sum of its future cash value discounted back to today. This means that you should discount $262,041 mil by a certain percentage in order to find how much $262,041 mil is worth today. Remember the time value of money concept? You want to receive something like yearly interest each year for the money you are investing today so that you can get a piece of the networth and $262,041 mil.

What Discount Rate Do I Use?

We use a discount rate of 9% since Apple is 1) a well known brand 2) I expect it to still be around in 20 years and 3) it has a moat.

The Intrinsic Value is…

Thanks to Microsoft Excel, discounting $262,041 mil by 9% and adding the 2007 networth results in a present value of $115,301 mil. This means I should pay only $115,301 mil today for the $262,041 mil + $14,532 mil.

Divide $115,301 mil by the current number of shares outstanding gives a per share intrinsic value of $129.70

But Wait, There’s More

Up until now, we have been projecting the future cash based on assumptions of CROIC growth rate. I have no ability in predicting the future and I know 100% that my calculations are not spot on. So what do I do? The core principle Benjamin Graham told us is to use a LARGE Margin of Safety (MOS). A 50% MOS shows that my calculations has the potential to be off by 50%. Therefore, I would have to purchase Apple at 50% of $129.70, i.e. $64.85. Closing price of AAPL on Jan 25th was $130.01. Pretty close to the calculated intrinsic value.

Click the image below to see the cash projection.


Here we see the historical price of AAPL over 5 years compared to the intrinsic value and target price. Note how price follows value. Pretty scary huh?

Tags: aapl, apple, intrinsic value, Stock Analysis, valuation
Posted in Stock Analysis | 11 Comments »

Newer Entries »