Posts Tagged ‘spreadsheet’

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

Morningstar Vs Old School Fair Value Estimation

Until Nov 21, you have access to all the premium content on Morningstar. Quickly sign up (no credit card required) or log in now! Go go go.

Thanks to Contrarian Value Investing for the notice.

Just out of curiosity, I was checking out the Morningstar fair values for my companies and surprisingly, they were all very close, minus AEO. So just for fun, here is a comparison of the intrinsic values between the companies I have written about on this blog. Small caps are not covered by Morningstar, so I have left them out.

(Morningstar fair value estimates as of Nov 19, 2008)

Morningstar vs Old School Value

ETN (published Nov 17, 2008)

Morningstar: $81 | OSV: $77

DPS (published Oct 28, 2008)

Morningstar: $28 | OSV:$25-$32

KSWS (published April 16, 2008)

Morningstar: $19 | OSV: $19.86

AEO (published March 11,2008)

Morningstar $22 | OSV: $37.37

So in the spirit of having some fun with intrinsic value, it doesn’t seem like I need to bother with a premium Morningstar membership. I get pretty much the same quantitative assumptions and you can too starting from only $6.95.

[tags]morningstar, intrinsic value, valuation, spreadsheet, ksws, aeo, dps, etn,  [/tags]

Free Competitor Comparison Investment Spreadsheet

Need information on competitors? Tired of Google, Yahoo or other websites showing incorrect competitor data or just not enough information? Problem solved.

It’s a fact that many people come here for the spreadsheets and I’ve been receiving positive responses, and seeing how I grew up singing “Sharing is Caring”, here is another tool you may find useful.

The current competitor comparison provided in the intrinsic value spreadsheets display incorrect competitor information and I’ve been meaning to update it to something more meaningful.

Screenshot of the Investment Tool

What Does The Spreadsheet Do?

Displays the following ratios and statistics

  • Stock Price
  • Market Cap (millions)
  • P/E Ratio (TTM)
  • Price to Sales (TTM)
  • Price to Book (MRQ)
  • Price to Tangible Book (MRQ)
  • Price to Cash Flow (TTM)
  • Price to Free Cash Flow (TTM)
  • Dividend Yield
  • Dividend Yield – 5 Year Avg.
  • Dividend 5 Year Growth Rate
  • Payout Ratio (TTM)
  • Sales (MRQ) vs Qtr. 1 Yr. Ago
  • Sales (TTM) vs TTM 1 Yr. Ago
  • Sales – 5 Yr. Growth Rate
  • EPS (MRQ) vs Qtr. 1 Yr. Ago
  • EPS (TTM) vs TTM 1 Yr. Ago
  • EPS – 5 Yr. Growth Rate
  • Capital Spending – 5 Yr. Growth Rate
  • Quick Ratio (MRQ)
  • and MUCH more

How To Use the Investment Spreadsheet

The input cells are the yellow cells, and the competitor spreadsheet supports 6 companies which you can compare side by side.

I found a much simpler template somewhere and fixed all the cells and corrected all links and data pulls. You will also notice a new layout and design which I use in my personal investing spreadsheets.

(If you are the original creator, let me know and I will give you credit)

Installation of the SMF-addin is required and visit the full detailed spreadsheet installation instructions.

If you want to add this to the other spreadsheets, you just have to cut and paste the cells into a new worksheet. Then just update a few of the cell references and it should be good to go.

Warning

Since many ratios CANNOT and SHOULD NOT be used at face value, this spreadsheet can actually provide a false picture and also prevent you from performing additional proper research.

Download the Best Investment Spreadsheet to Compare Competitors

Competitor Comparison Spreadsheet

Also get the best premium version 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.

——————————————————————————
CompTIA N10-003 network plus certification exam demonstrates competency as a network engineer. You can take this exam along with Microsoft mcdba certification, mcp and mcdst (Microsoft Certified Desktop Support Technician). All security training exams can be prepared by microsoft training centers.
——————————————————————————

10 Year History Chart Investment Tool

I was browsing through the SMF Yahoo Groups and I came across a nice looking spreadsheet that graphs up to 10 years of information. When I look for or create spreadsheets my focus is on ease of use and pleasing aesthetics. If at first glance the spreadsheet is overwhelming, no one will want to use it, including myself. With that in mind, I bring you this new spreadsheet.

Screen Shot

What It Does

Enter any ticker (not all information for financial companies will be displayed) and the spreadsheet will grab the following data and more.

  • Cash Flow (Continuing Operations)
  • EBITDA
  • Enterprise Value
  • EPS (Diluted, Total Operations)
  • Market CapitalizationMagic Formula Investing — Earnings Yield
  • Magic Formula Investing — Return on Capital
  • Net Income (Continuing Operations)Net Profit
  • Net Profit Margin
  • Operating Revenue
  • Piotroski F-score
  • Return on Assets (ROA)
  • Return on Equity (ROE)
  • Stock Price
  • Current Ratio
  • Quick Ratio

How To Use

All the formulas and data have been updated in the Data tab so it should work for everyone from the get go.

Installation of the SMF-addin is required and the installation instructions can be found here.

There is only one input field and that is the yellow box in the top left corner. To change the chart type, select or click on the title of each graph and a drop down box will appear which will allow you to select which charts you want to display.

Recommended Additional Usage

You could also integrate this spreadsheet into the 10yr or 5yr spreadsheets to reduce file management. That way, you can load everything in one spreadsheet.

Full credit goes to the SMF add-in group for providing the template.

Download

10 Year History Charts Spreadsheet

You can now download the premium version with the 10 year charts incorporated. Click here.

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.

Beware The Bad Habit of the Spreadsheet

As the title suggests, beware of how you use the spreadsheets. Like a pen, a finance spreadsheet can be used to create wonderful ideas and opportunities or it could just as easily ruin you and leave your mouth gaping open with a crap taste in your mouth. I’ve noticed a lot of people downloading the files and although I have a disclaimer tab in there, I would like to expand on some things.

Learn From Experience

When I first downloaded the FWallStreet spreadsheet, I thought it was the coolest thing. I read the post on JNJ on FWallStreet and I thought to myself

I’ve got it made. Just find companies with a nice linear FCF history which are cheap according to this spreadsheet and I will make money – Jae Jun

(p.s. please don’t quote me =P )

So I kept searching for companies that I knew and their competitors, companies that hit 52 week lows, companies that were recommended, abused, and the works. Each day, I would go through so many companies just to find something that showed “YES” on the spreadsheet.

Note that I did not mention researching any of those companies. Of those screens, the ones that the spreadsheet claimed to have a large margin of safety, I admit that I did “invest” my money.

The result, the price went up for a few weeks but slowly started to lose 20-30% of its value. I felt uneasy, unconfident, worried and found myself questioning myself. All signs that I didn’t know a thing about the company, except that I thought it was cheap.

Luckily I knew I had made a mistake and only realised a 10% loss. At least I learnt my lesson and I remember what I learnt, unlike the sentiment I have for Wall Street.

Wall Street people learn nothing and forget everything – Benjamin Graham

It is Just the Beginning

The spreadsheets are just the beginning. If your research starts and ends with the price, then I must say that the spreadsheets will probably cause you more harm then good.

Combine the spreadsheet information with a little deeper research and not only will your circle of competence increase, your judgement and analysis skills will surely improve as well.

Just because a stock is considered extremely underpriced, without doing any research we cannot tell the difference between market panic freefall and company fundamental breakdown.

Detachment with Investments

Ever that get feeling in your stomach when you have a “tip” or a “hunch” that a stock is the next Microsoft or Google?
That, my friend, is just a signal for you to find a toilet to sit on.

Along with that gut feeling, another dangerous investor pitfall is not selling when you should. One of the causes is due to an investors attachment to a particular investment. When the price starts to climb, we start to believe the company is invincible and limitless. Exactly what happened with Google and AAPL. When reality and common sense stating that a billion dollar giant can’t maintain its scary 30%+ growth, the price of both companies plunged. Ultimately, we miss the opportunity to sell when the price goes beyond its intrinsic value because of our greed.

Be fearful when others are greedy, and greedy when others are fearful. – Warren Buffett

Preparation Will Always Yield Fruition

What goes up must come down – Isaac Newton

There is no way around common sense. No company can produce consistent growth year after year forever. That is just impossible. Management gets shuffled around, founders pass away, the world changes and companies can not always adapt and capitalism creates ever increasing opportunities and competition. With so many variables to consider, a company, no matter how good it is, can not be constantly perfect.

By being patient, and preparing and researching companies, time spent trying to understand the company may cause you to miss an opportunity. But a time WILL come when the company you were researching will hit your radar again. This time, you already know most of the things you need to know. Now you can act quickly on the knowledge you’ve gained over that time.

I say this because this is something I (still consider myself a beginner investor) learnt the hard way like most people. I still find it hard. There is just a psychological tendency for us to act hastily when it comes to making a decision on an opportunity. Ever been in a situation where you saw something on sale for “a limited time” or “today only” and ended up buying it without going through your options? Yup, thats the issue I’m talking about.

Price is Just an Number

Price is just a number. It is important to uncover what the price is relative to. Read that sentence slowly again. It is important to uncover what the price is relative to. The spreadsheet can only do so much as to point you in a certain direction.
Don’t fall victim to the laziness a spreadsheet can bring.

Free Discounted Cash Flow DCF Valuation Spreadsheet

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