Free Spreadsheet to Analyze IPO’s

November 26, 2010 | Comments (5)

Free IPO Spreadsheet

Free IPO Spreadsheet

Going Public – Free IPO Spreadsheet | Flickr: Sebastiaan ter Burg

How to Use the Free IPO Spreadsheet

Ever wanted to know how to value an IPO? Or a company with very short history?

I didn’t create this spreadsheet but if you don’t mind doing some work to find numbers from the SEC filings and then copying and pasting, this is just for you.

This isn’t a spreadsheet I created but I wish I had.

The creator is Indian, so the default company is Coal India. Not that hard to change to a US company.

It is brought to you by Pristine, an Indian business that offers financial modeling classes.

Click to Download the Free IPO spreadsheet.

(Compatible on Windows and Mac)

About Jae Jun


Jae Jun is the founder of Old School Value. He is on a mission to provide practical and actionable value investing tools, tutorials and educational material to help empower the individual investor. Keep in touch with Jae via any of the methods linked below.

Get Tips and Strategies to Achieve Higher Returns
Bonus: Get 9 FREE Investing Spreadsheet Calculators
  • T.I.

    Hi! Jae,

    Thanks for sharing.

    I think we can obtain the similar free spreadsheets from Damodaran’s web site. In chapter 23 and 24 of his book “Investment Valuation”, Damodaran provides us several spreadsheets that can conduct the valuation of IPO, young companies and private companies.

    Thanks.

    Regards
    T.I.

  • http://www.selectedfinancials.com Ankit Gupta

    IPO’s are tricky – remember that investment bankers are following their own incentives. Their incentive is a percentage of the transaction that occurs when the company raises money and goes public. This means that they are not paid based on the performance of the securities.

    They will line up both parties – the company issuing securities and the buyers of them (institutional investors, etc.). They will also publish research and 99% chance, a buy rating on the stock. You should approach with caution and just for added margin of safety assume that this rating was bought.

    This spreadsheet has a lot of variables that you can change, but remember that it’s all based on future performance. As Ben Graham talks about, it’s much harder to forecast accurately into the future than to look back in the past. Sure, you can start playing around with average days of inventory as an income statement to balance sheet measure 5 years out from today, but the fact is that a lot is going to change in that time period. I have a hard enough time projecting revenue for a small cap, let alone intricate little ratios 5 years out for a mid cap company that is just now disclosing financials.

    As you approach IPO’s, think about it from the perspective of every party involved. There is a way every one can do well, but the venture capitalists and management team have probably been pushing along for 4-5 years and are looking for big pay day of many millions of dollars. Their incentive to work was much more than just their salary. Once they cash out, will they have the same fire burning that keeps them going?

    Again, IPO’s can be good for every party involved, but there are plenty of examples where they weren’t. The current investors in the company are very smart and so they won’t do an IPO unless the price they receive is fair.

    I typically stay away from IPO’s, although in the recession, a few fell below net cash (after all liabilities) and so I should have grabbed those, so that might be a potential source of ideas if you want the potential upside but to pay rock bottom pricing.

  • jrallen81

    hi Jae – if we want to learn the fundamentals of excel modeling, what advice would you have? Their course looks to be about US $400. I was thinking more of books and website resources…

  • http://www.oldschoolvalue.com Jae Jun

    @ T.I.
    Thanks. Prof Damodaran has heaps of spreadsheets, but I find it extremely difficult to figure out and use.

    @ Ankit,
    Good points. IPO’s certainly carry a lot of risk and are never worth it most of the time, but for companies that are emerging from bankruptcies, completing spinoffs and other situations where the historical data either disappears or is hard to find, this spreadsheet is of great use. Certainly cant rely on it as you are correct that many assumptions must be made. But there is definitely lots of learning potential with something like this.

    @ jrallen81,
    I wouldn’t spend so much money for excel classes unless you are really new to excel.
    Everything I do on excel was self taught so it isn’t hard. Just lots of practice and thought. Excel is such a powerful tool that you can do countless things with it.
    Start off by getting the thick excel tutorial books are a bookstore. Work your way through it until you are confident in doing a lot of basics all the way up to expert stuff like mixing functions and formulas.
    Then slowly move onto to VBA.

    Come up with a problem that you want to solve or a project you want to create, then just search online for bits and pieces of code and add it all up.
    It’s how I got everything done. lol
    My secret is out.

  • http://www.selectedfinancials.com Ankit Gupta

    One more comment, this comes from Klarman’s book, Margin of Safety.

    “A perfect business in terms of the simplicity of valuation would be an annuity; an annuity generates an annual stream of cash that either remains constant or grows at a steady rate every year. Real businesses, even the best ones, are unfortunately not annuities. Few businesses occupy impenetrable market niches and generate consistently high returns, and most are subject to intense competition. Small changes in either revenues or expenses cause far greater percentage changes in profits. The number of things that can go wrong greatly exceeds the number that can go right. ”

    As we forecast far into the future, if your net income is only 2-3% of revenue, then you need to understand the implication. If we hold SG&A steady and COGS at 50%, then a 4-6% decrease in revenue can mean that net income is wiped out 100%. Net income is in a sense, leveraged against SG&A and revenue. Again, a decrease in 4-6% of revenue could mean a complete 100% loss of net income.

    This is my issue with forecasting far into the future with too many variables – it doesn’t take much to modify them and make numbers appear however you would like them.

x
Receive Email Tips and Strategies on Achieving Higher Stock Returns

Bonus: FREE Set of 9 Investing Spreadsheets