Open Source Valuation Model – Valuing

To assist investors in the evaluation process of new investment opportunities, malady Pristine has developed an open source valuation model. This model can be used for pricing and financial statement analysis to identify strong investment opportunities which, recipe for example, can be used for IPO market value analysis.

Pristine wants to make this analysis tool public to help smaller companies make informed investment decisions. By changing the assumptions of the model users can analyse different financial scenarios and identify strong investment opportunities.

Pristine’s model uses LinkedIn IPO financial statement data to present an example.

Download the workbook here.

Absolute or Relative Valuation in Excel

Valuing a company’s stock using absolute valuation or relative valuation is required in most of the fields of finance. In this article we list the steps needed in the evaluation of a company. We have used LinkedIn Corporation’s $175 million issue to illustrate these steps:

Issue Information:

  • Issue Size: $ 175 million
  • Stocks to be issued: N number of Class A common Stock of $ 0.0001 par value
  • Expected Market Capitalization: To be estimated

LinkedIn is one of the world’s largest professional network on the Internet with more than 90 million members in over 200 countries and territories. Through their proprietary platform, members are able to create, manage and share their professional identity online, build and engage with their professional network, access shared knowledge and insights, and find business opportunities, enabling them to be more productive and successful.

LinkedIn’s comprehensive platform provides members with solutions, including applications and tools, to search, connect and communicate with business contacts, learn about attractive career opportunities, join industry groups, research organizations and share information.

Defining Key Metrics of LinkedIn:

The key metrics can be extracted from the S-1 filing of the company filed with SEC. The key metrics for LinkedIn are:

  • Number of Registered Members: Presently 90 million, but most of them don’t contribute in revenues
  • Unique Visitors: LinkedIn defines unique visitors as users who visited LinkedIn at least once during a month regardless of whether they are a member
  • Page Views: These are the number of pages on LinkedIn that users view during the period in which page views are measured.

Revenue Heads:

The most important task is getting information about the revenue sources which can also be extracted from the S-1 filing or from the news. Major revenue heads of LinkedIn include:

  • Revenue from LinkedIn Corporate Solutions Customers: Number of LinkedIn Corporate Solutions customers are the number of enterprises and professional organizations that have active contracts for the product.
  • Revenues from LinkedIn Premium Subscribers

To get the revenue we need to get to the basic parameters which are steering the revenue numbers. In LinkedIn’s case it was Number of users (Parameter A) and the Charges users pay (Parameter B) for the service. So we get those historical numbers and project them in future to get the revenues by multiplying Number of users with the charges per user i.e. A x B.

Major Cost Heads:

  • Cost of Revenue
  • Sales and Marketing
  • Product Development
  • General and Administrative
  • Depreciation and Amortization
  • Other Income (Expense), Net

The cost calculations are also worked in a similar manner as revenue calculations. We define a formula for the cost calculations (A x B) and get the final numbers by projecting the values of the parameters (A and B).

Building the Asset Base:

The next important step is building the asset base using the proceeds of the issue. This is completely management’s prerogative so we need to check the Management’s Discussion and Analysis to get the hint of prospective usage of the funds. If you don’t get any concrete idea you can project the assets by some generalized rule such as increasing assets using growth rate of revenues.

Analyzing the statements:

The performance of the company can be measured around using various ratios.

Return on Equity can also be broken into its components using Du Pont Analysis.


There are two types of valuation methods as discussed above.

  • Absolute Valuation: Once all the statements are in place, the free cash flow to the firm and equity is calculated which is discounted at WACC and Cost of equity respectively to get the Firm Value and Equity Value of the company.
  • Relative Valuation: In relative valuation the ratios of comparable companies are taken and the price is calculated using those ratios. e.g.: Average P/E of comparable companies is 5 and the EPS of LinkedIn is $10, then assuming that LinkedIn will also trade at same P/E will yield us the price of LinkedIn as $50.

Last Step:

The last step of the valuation process is summarizing the valuation in one sheet which can be done using the football field, in which different ratios will give different ranges of price and the price from absolute valuation will lie somewhere on those price ranges or outside those ranges.

The content of this article is the opinion of the author.

Recent posts by Anil Bains

Comments for “Open Source Valuation Model – Valuing”

  1. […] between 32 USD and 35 USD. When the scrip opened, it was at 33 USD. The LinkedIn model was then featured on the popular Financial Modeling blog – […]

  2. Nick says:

    Please advise what the password is for unprotecting the graphs

  3. Anil Bains says:

    Dear Nick,

    Password to unprotect the sheets containing graphs is: 921

    Anil Bains

  4. […] predicted that the share would be priced between 32 USD and 35 USD. The LinkedIn model was then featured on the popular Financial Modeling blog – […]

Comment on this Article