fbpx
Search
Close this search box.

FREE Excel Financial Model for Analyzing an IPO – Download Today

Financial Modeling - 9 comments

Lets keep this quick and short, As some of you may know, There is a mega IPO (initial public offering) going on in India these days. It is for a company called Coal India Ltd.

So my partners at Pristine Education thought, it would be cool to build a financial model analyzing the IPO.

Click here to download FREE Financial Model for Analyzing Coal India IPO.

Learn how to construct financial models like this:

I have partnered with Pristine to launch Financial Modeling School. It is an online training program to teach you how to build financial models using Microsoft Excel.

We have opened registrations for this program on Monday, October 18 and so far 74 students have already joined. We will be closing registrations on 27th October.

Click here to learn more about the program and join.

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.

Tutorials on Excel Financial Modeling:

Here is a six part tutorial explaining how to create financial models for project valuation in Excel.

  1. Introduction to Financial Modeling
  2. Building a layout for Project Evaluation Model – Best practices
  3. Building Inputs and Assumptions Sheet
  4. Building Projections for Project Evaluation
  5. Modeling the Cash Flow Statement and Projections
  6. Putting it all together – Final Project Evaluation Model

Do you find the IPO valuation model useful?

Share your experience in using the model thru comments.
Financial Modeling using Excel - Online Classes by Chandoo.org & Pristine

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

9 Responses to “FREE Excel Financial Model for Analyzing an IPO – Download Today”

  1. Mike says:

    Hi Chandoo,

    a nice model, thank you very much for all your ideas,
    well done,

    friendly greetings, Mike

  2. Just to make readers aware - This model is built using the standard steps that have been outlined in our financial modeling overview. Essentially the steps are very logical and intuitive!
    1) Create model for historical statements (P&L and B/S) - We just picked from the DRHP of CIL
    2) See the historical growth drivers (growth in revenue, most of the costs as %age of revenue, days of inventory, receivables, etc.)
    3) Project them in future (Here you need an experience in the industry and a view on how well the co./sector would be able to do)
    4) Project future P&L and B/S
    5) Project the cash flow statement
    6) Calculate key ratios for co. analysis (profitability, liquidity, etc.)
    7) Calculate value of firm through FCFF and comps

    Each of these steps can be learnt by following a disciplined approach...

    Feel free to discuss or raise your questions on the model/ modeling technique.

  3. Bruce says:

    HI Chandoo
    For the terminal value calculation should you not make the fixed capital investment and depreciation equal on the assumption that you will replace assets going forward
    Secondly not sure how the array table works - new at array tables - where do I find info on the TABLE function that you utilize as I cannot find it in Excel 2003 standard functions.
    By the way excel school is excellent - good job
    Regards, Bruce

  4. Hui... says:

    @Bruce,
    The Data Table is not an Excel Function in the normal coarse of things, it is a menu Item and is on the Data, What If Analysis Tab.
    It has been available since at least 1995

    For more on the Data Table function have a read of
    http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

  5. Bruce says:

    @Hui
    Great thanks - did not know about that feature
    Cheers Bruce

  6. Beks says:

    Hi guys, apologies as this is really an basic question....

    how did you block the view in the first 3 sheets?

    Thanks.

  7. Pawan says:

    Beks,

    To protect a sheet simply go to Review Tab and Protect Sheet. Uncheck all the boxes and give a password. If you further want to protect the structure of the Workbook, go to Review tab and Protect Workbook using a password.

    -Pawan

  8. Ankur says:

    Hi Chandoo,

    I have few queries from the IPO valuation of Coal India.
    1. Where did you account for the proceeds from the IPO in the projections?

    2. In the worksheet, in the 'Valuation' sheet, where did you get the No. of Shares Outstanding (6316)? Are these no. of shares outstanding pre-IPO or post-IPO?

    3. At last you calculated, Rs. 263 as price per share. Does it mean that this should be the price at which the IPO should be launched? Generally in IPOs we see a price band, how do we calculate the price band?
    Request you to kindly explain the above points using the Coal India sheet.

    Thanks.

  9. Dear Ankur,

    We have a detailed course on valuation, where we cover these aspects in detail. We have a full coverage of Coal India valuation (At the time of their IPO) covered in one of the weeks. You can consider the course on:
    https://chandoo.org/wp/financial-modeling/

Leave a Reply


« »