Posts Tagged ‘valuation’

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

Free 5YR DCF Fair Value Investment Spreadsheet

Although finding great, stable companies with more than 10 years of historical data to examine is ideal, not all companies fall into this category. A vast majority of companies fall into the category of around 5 years of operational history. This free investment spreadsheet which is a modification of the free 10 year investment spreadsheet calculates the fair value of a company based on the past 5 years of available data from Morningstar and other sources. I’ll also take you through the steps to modify the spreadsheet yourself so that you can apply it to other databases other than Morningstar.

Changes to the Investing Spreadsheet

  • Multi yearperformance is calculated based on timeframes over the past 5 years.
  • Modified the forecasted EPS formula in the Benjamin Graham formula.

How To Fix The #DIV/0! Error

For companies such as DLB with 4 years of data instead of the required 5, you will get #DIV/0! errors because Excel is trying to calculate the median with invalid numbers.

DIV error

To overcome this, you simply have to restate the cell ranges by double clicking and then changing the range from =MEDIAN(B41:J41) to where there are actual values. In the case of DLB, this would be =MEDIAN(F41,I41;J41).

Median Edit

The screenshot shows the edit for the third row, but you would want to do it for the first row and then just grab the corner and drag it down to apply it to all cells.

Median edit 2

You can actually apply this method for the 10yr intrinsic value spreadsheet instead of downloading this one.

How To Customize The Spreadsheet

The stock investment spreadsheet utilizes an excel add-in called SMF (Stock Market Function) and in order to really understand how it works, you would have to follow the code and the comments within, but I’ll go over the main function.

(If you want further spreadsheets and templates with this add-in, visit the Yahoo page and navigate to the files section)

In the spreadsheets, the data in the statements tab is retrieved from Morningstar with the =RCHGetTableCell() function. Simply, this grabs the value from the cell of the table.

Currently the formula in the spreadsheet is: =RCHGetTableCell(“http://quicktake.morningstar.com/Stock/ Income10.asp?Symbol=”&Ticker, 2, “Fiscal Year-End:”, “>Revenue”)

To grab data from another site such as Yahoo Finance Australia for Telstra, I could go to the balance sheet page and then edit the function to display the cash by entering the following

=RCHGetTableCell(“http://au.finance.yahoo.com/q/abs?s=”&Ticker, 1, “PERIOD ENDING”, “>Cash”) where the ticker entered into the spreadsheet would be TLS.AX.

This command does the following:

  • points to the page http://au.finance.yahoo.com/q/abs?s=TLS.AX
  • selects the table that contains the exact phrase “PERIOD ENDING”
  • selects the very first cell right after the cell containing the exact phrase “CASH” which is characterised by the > sign.

To retrieve the next value from the table, you would simply change the 1 to 2 to get the second cell.

Do this method for all rows and cells. I just found out it was rharmelink from the SMF Group that created the 10 year statement template for Morningstar. (Just giving credit to the proper person)

To retrieve other information such as stock prices, historical prices, charts etc, you would use the other functions mentioned in the documentation.

I could go over the other useful functions but I think it’s pretty easy to figure out and will leave it up to you unless a number of readers request it specifically.

How to Install

A step by step guide is provided in the full spreadsheet installation guide.

IMPORTANT

Please read the guide and FAQ section. 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.

Download The Free 5Yr Investment Spreadsheet

5 Year Fair Value Stock Investment Spreadsheet

Get a cheap yet the best DCF Valuation spreadsheet which includes more calculations, graphs and tools.

——————————————————————————–

If you found this article helpful and would like to receive more information on free investing tools and spreadsheets, as well as business valuations, stock analysis and updates on merger arbitrage, subscribe to the Old School Value RSS or via email.

Wellpoint (WLP) Valuation from Value Investors Club

For all value investors or those seeking to purchase $1 for 50c, I highly recommend you visit Value Investors Club. This is a site where people submit their best ideas once a year in order to gain or maintain their free membership. For those that just want to get some ideas, you can sign up for free to view valuations that were written 45 or 90 days prior.

Missed Boat?

As I was looking through, I came across a nice valuation on WellPoint, a company that came into my radar when it dropped to the low $40’s in March due to a lot of fear and uncertainty. Add to that Hilary Clinton’s healthcare policy and it surely was a great opportunity. Unfortunately, my research and due diligence wasn’t up to speed so here I am waiting for the boat to moor again.

This valuation is from Value Investors Club and written by miser861. Full credit goes to the author. Written Feb 20 2008.

WellPoint Valuation by Miser861

WellPoint trades for 7x my 2010 estimate of free cash flow, and 10x 2008 estimated free cash flow. This seems cheap for a business that should grow EPS 15% per year, has mild cyclicality, earns 50% returns on tangible invested capital, and is run by a shareholder friendly management that deploys capital aggressively into share repurchases.

My 10,000 Foot View of Managed Care

Managed Care Organizations (MCOs) add value by purchasing healthcare for its customers in bulk, and secondarily pricing and spreading risk. MCOs negotiate prices with providers, and mark up those services by around 20%. The MCO collects insurance premiums upfront and earns investment income on the float, but also has some overhead.

As the industry operates today medical costs per member may increase 7% for example, and WLP might raise premiums per member 7.5%. The business is very scalable on G&A. At WLP G&A has declined as a percent of revenue by 100 bps per year on average over the last several years.

Each MCO has a unique mix of members in Commercial, Medicare and Medicaid plans. The consensus is that Commercial in general has the least member growth potential as incrementally healthcare inflation is pricing many employers out of the market. On the plus side employees who aren’t covered by a group plan often purchase individual plans that carry higher margins than group plans since MCOs can be more choosy about who they cover. Medicare is viewed by most as the high growth segment for two reasons. First, a higher percentage of seniors are going to privately-run Medicare Advantage plans as opposed to government-run traditional Medicare plans. Second, the senior population is growing. However there’s considerable controversy surrounding Medicare Advantage funding as it is about 13% higher per member than traditional Medicare, and MLRs are likely lower. The Medicaid business is viewed by many as a potential minefield given straining state budgets, but is generally viewed as having decent member growth potential.

Politics

The market seems concerned about the outcome of any potential healthcare reform. While I think in some scenarios reform could be a threat, based on conversations with industry experts I think it could more easily be an opportunity, particularly for WLP.

Universal coverage in itself would be positive – 47 million potential new customers – but margins are more important than members. Of the iterations of universal coverage models, one that concerns me is guaranteed issuance combined with optional coverage. Guaranteed issuance would ensure that no one is denied coverage. Auto insurance operates on a universal coverage model, but coverage is mandated. Actuarially speaking this is important because safe drivers, who wouldn’t buy insurance voluntarily, subsidize risky ones. Were auto insurance optional, premiums would be significantly higher for everyone. The managed care industry in New York operates under a guaranteed issuance/optional coverage model. MCOs can operate profitably in New York, but anecdotally average premiums are $400-500 per month versus $200 per month in California where there is no guaranteed issuance. Guaranteed issuance could disincentivize good membership. As you probably know, groups already operate on a sort of guaranteed issuance model, it would only be a risk for the individual business. Individuals are 7% of WLP’s membership, so exiting that business would be relatively painless.

Another byproduct of universal coverage might be government funding cuts to Medicare and/or Medicaid as these programs might be expanded to extend coverage to a larger population. So many industry insiders fear that Medicare Advantage margins are at risk, but again any margin compression would likely accompany an influx of volume.

The risks to the group business seem minimal to me. The issue is about covering the uninsured, not nationalizing the already insured.

My overriding thesis on reform is that MCOs have a number of levers they can pull to stay profitable, and MCOs are good at pulling levers. Benefits can be cut, premiums can be raised, in every imaginable combination. Case in point, last year California had an ambitious reform agenda that included putting a floor on medical loss ratios. In the commercial segment the target floor was to be 85%. After some back and forth, concessions were made. Premium taxes were allowed into the MLR calculation (not the case for GAAP), which lowered effective GAAP MLR to maybe 82.5%, and some GAAP G&A was permitted into the calculation which brought effective GAAP MLR to around 80%, pretty close to where most insurers write commercial business anyway. Note that California ultimately abandoned the reform effort because of the state’s precarious budget situation. Ultimately, in America we generally don’t impair an industry without some input from industry participants who understand the implications of the action.

Choosing Among the MCOs

The table below is my best attempt to level the comparison quantitatively. The estimates are a combination of sell-side consensus peppered with my judgment.

2008 P/FCF 2010 P/FCF 2010 FCF/Shr Growth
UNH 11.2 8.6 15%
WLP 9.8 7.5 15%
AET 11.1 8.7 12%
CVH 11.4 9.1 12%
HNT 10.8 8.4 13%
HS 10.8 8.5 12%
HUM 12.5 9.4 16%
AGP 14.0 10.7 14%
CNC 9.5 8.2 8%
MOH 14.8 11.6 12%
SIE 15.4 12.4 12%
WCG 10.3 9.1 6%

WLP appears statistically cheapest. I think WLP is also superior qualitatively.

For starters WLP is a Blue Cross Blue Shield carrier. BCBS is the most valuable brand in managed care. 33% of Americans have a BCBS plan. BCBS has five times the brand awareness of their biggest competitor. BCBS has more bargaining power versus providers than any MCO because of its large membership. BCBS’ provider network is also the largest. WLP adds the most value for members by providing a superior product for similar prices. For this reason, WLP continually takes market share in the commercial space and is able to grow members profitably in this stagnant market.

In my analysis, rightly or wrongly, I’ve prioritized minimizing my exposure to Medicare as Medicare funding cuts are a single point of failure. My rationale is thusly: a plan’s exposure to Medicare today has little to no bearing on future exposure. I want to minimize my immediate Medicare blowup risk but I don’t want to give up any upside from Medicare/Medicaid expansion in the future if that’s how universal coverage materializes. If Medicare and/or Medicaid are expanded to extend coverage to a greater population, funding cuts will likely be necessary but would only affect existing members. A plan with no Medicare members today could benefit from member growth in the future without having to take a step back first. Larger plans have a cost advantage in bidding. Anecdotally I’m told that as long as an MCO has Medicare infrastructure and know-how there is no real barrier to acquiring as many members as the plan can bid for successfully. So I want to position myself with a large carrier with Medicare infrastructure but minimal exposure today.

The below table is a rough estimate of EPS contribution from Medicare Advantage and Medicaid for the various MCOs. I have no doubt it contains errors, but clearly WLP is at the very low end of the spectrum. WLP management has intentionally avoided aggressively moving into Medicare because of the questionable value proposition of Medicare Advantage that makes funding uncertain.

Medicare Medicaid
UNH 20% 7%
WLP 4% 6%
AET 6% 2%
CVH 35% 10%
HNT 12% 10%
HS 104% 0%
HUM 69% 2%
Valuing WLP

Management has provided guidance for 2008. I don’t feel smart enough to expound on the guidance except to say the sharecount guidance doesn’t make sense given their buyback guidance. I don’t make any assumptions about benefits from healthcare reform.

2007 2008E 2009E 2010E
Operating Rev 60.10 62.60 66.50 10.90
Investment Income 1.00 0.85 0.90 0.97
MLR 82.40% 81.60%
G&A Ratio 14.50% 14.40%
EBIT 5.70 6.00 6.50 7.10
EBIT Margin 9.30% 9.50% 9.70% 9.90%
Interest Expense 0.45 0.59 0.69 0.79
Net Income 3.30 3.40 3.70 4.00
Non-cash expense 0.76 0.76 0.76 0.76
Cap Ex 0.20 0.20 0.20 0.20
FCF 3.90 4.00 4.30 4.60
Diluted Shares EOP 0.56 0.50 0.45 0.40
FCF/WTD Avg Shr. 6.60 7.57 9.05 10.75

If we apply a 14-16x multiple on $10.75, WLP could trade for $150-175 in 2-3 years. Barring a legislative disaster I don’t see how EPS will decline, so at less than 10x ’08 FCF it’s hard to lose money from here in my view. But a legislative disaster is possible, and it’d be a gross overestimation of my abilities to assume that I can model a worst case scenario. Some faith that such a scenario is both unlikely and of a manageable magnitude is required.

As an afterthought on valuation, some have raised the possibility of monetizing the captive PBMs. WLP’s PBM is the fourth largest, it processes about 400 million prescriptions per year. Medco (MHS) should do about 570 million prescriptions in 2008, and will do a little over $4 of EBITDA per prescription. If WLP does $3 per prescription, that’d be $1.2 billion of EBITDA. MHS trades for 12x 2008 EBITDA. 10x would mean the WLP PBM is worth $12 billion, leaving us with a stub selling for maybe a multiple point less than today. UNH has announced that it plans to provide more detail on its PBM in 2008. WLP will provide more, though somewhat less helpful, disclosure in 2008 as well.

American Eagle Outstanding (AEO) Valuation

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.

Apple (AAPL) Valuation

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?