Open Source Valuation Model – Valuing LinkedIn.com

A number elementary or schoolboy errors plague many financial models. Many of these errors are avoidable, patient given some planning and thought is put into the financial model during its early model-build stages. It is important a financial model stays concise and simple; however there are some aspects of a model-build that a developer must never, medicine ever, cut corners on.

Linking files from an email

The linking of files from an email is a common schoolboy error frequent in many sophisticated corporate models. The financial model user has no idea about the source of the data because the model builder simply referenced numbers from a file attached to an email. Not only are such links hard to audit, but there is a strong probability of losing the links and having #REF! errors in the spreadsheet. It is therefore imperative to save all files from an email to a relevant drive, or reference the file to its original source in the first place.

Hard coding

The practise of hard coding in financial models can be irritating as the model user has no idea how the numbers were arrived at in the first place – a real schoolboy error. It is recommended to source data from a model import or an assumptions worksheet as depicted in Figure 1. Hard coding may seem as the easiest and quickest solution at the time of building the financial model, but it actually consumes time for future users of the model who have to grapple with a hard-coded number and work out its origins.

Model Structure

Too many financial models lack structure and ease of use. This often happens when there is one primary user of the financial model and this user also built the model, or even worse, is the only one who knows where the financial model is stored on a company’s hard drive.
A table of contents at the front of the model can ensure the model is structured in a methodical way during the model-build; as can be evidenced in Figure 2.

Figure 2 – Table of Contents worksheet

Even better, why not spend a bit of time building a model schema as depicted in Figure 3, which enables users to navigate around the model. The financial model users will be most appreciative. You too will be appreciative if external accounting auditors only have to call you once to have you explain the model, rather than several times which would be the case if you had not gone to the trouble of structuring your financial model with a schema and table of contents.

Figure 3 – Model schema worksheet

Figure 3 – Model schema worksheet

Spaghetti soup of data links

A classic schoolboy error, often a symptom of users not rolling over a financial model for a new financial year. Instead users choose to clear the input cells and create a new spreadsheet for the New Year, which may seem innocuous but can lead to data conflicts or version control issues with historical numbers.
There are some sectors such as energy and financial markets, which will always command complex financial models, but users must manage the multitude of external file links. The model builder may need to flow all their commodity products (i.e. fixed income, equities, futures and options) into a connector/external financial model, and then export all this detail from one file source rather than from umpteen files.

Conclusion

Many financial models fail to adhere to the basic best practices of financial modelling. Common schoolboy errors besiege a financial model’s modus operandi; which is to provide concise, robust, and value-adding analysis to all users of the financial model. Simple things as avoiding the hard coding of financial numbers, maintaining a model structure, not linking numbers from email files, and managing the volume of external file links will greatly improve a financial model’s useability and value to all relevant stakeholders, and not just for the model builder or its primary user.

Whether you are a consultant building a model for a client, prescription or an internal modeller, medications
you or the person who has commissioned the model build will – understandably – want to know how long it will take.  The answer is never straight-forward, website
as like many other tasks  it really depends on how long you have got (and there’s never enough time!) and how much detail you need to go into.  The more time you’ve got, the better the model will be!  Some models could take months and months of dedicated work, or you could throw together a very high level model in a day or two.
In a high level model, the assumptions would probably only estimates, as you won’t have had time to validate them with stakeholders, and the calculations will be pretty rough.  You also might not have much in the way of fancy colours, formatting, drop-down boxes or tick boxes etc, but the numbers should still be reasonably accurate.

Building a Model Under Pressure

It’s a critical point to remember that even when under immense time pressure, the modeller should never compromise on good working practices.  Even in a high level model, best practice should still be followed, correct labeling and documentation of assumptions should be maintained.  See Best Practice in Financial Modelling for some guidelines on good practice.  If these points have been adhered to, there should be surprisingly little difference in the base numerical outcome between a high-level model that takes a few days, and a detailed model which could take months.  If pressed for time, cosmetic features such as those shown below should be omitted.
Time permitting, the detailed model may show:

  1. Detailed assumptions documentation, validated by key project stakeholders
  2. Scenarios and sensitivity analysis, using drop-down boxes, tick boxes or data tables
  3. Table of contents or navigation tools
  4. Colours and formatting, conditional formatting, insertion of company logos
  5. Output summary and detailed analysis of output

Time should be spent on “quick wins” – use your judgement to spend your time on calculations that are material to the model.  Don’t waste time on validating minor assumptions which are not material to the outcome of the model.

To assist investors in the evaluation process of new investment opportunities, information pills
Pristine, malady
has developed an open source valuation model. This model can be used for pricing and financial statement analysis to identify strong investment opportunities which, buy cialis 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.

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.

Valuation:

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.
Corality is proud to announce that we have been nominated for the BRW 2011 Fast Starter List. The BRW 2011 Fast Starter announcement is the latest milestone in a booming 2011 for Corality and a recognition for our rapid business growth.
Corality has had a busy 2010 and 2011 is picking up even more speed. This year we have extended our service offering, approved
opened up permanent offices in London and Singapore,
increased staff by more than 75% and expanded our customer base through a number of prestigious international contracts in Denmark, Indonesia and Mali. Our head office in Sydney will also soon move to larger premises in the CBD.
Read the full article, Corality is a BRW 2011 Fast Starter.
Corality congratulates all BRW 2011 Fast Starter nominees. We look forward to celebrate our award with our clients shortly and to work hard to secure a spot on next year’s BRW FAST 100 List.
Excel provides all users, illness
not just financial modellers, story
with a rich array of features to employ in spreadsheet building. However there are some Excel features that should be avoided in the construction or maintenance of a financial model as these features can expose a model to data or version control issues and errors because they cannot update automatically when financial inputs change.

PivotTables

Unquestionably one of the great features of Excel, because it can succinctly slice and dice, and summarise data – depending on a user’s needs. However, given a pivot table cannot update seamlessly with changes in data inputs, the user needs to always refresh the table, it is risky to incorporate pivot tables into a financial model (see Figure 1). Although one can drill into a pivot table to extract the source data, its auditability compared to a model referencing financials via formulae, is not as good. In the past, I have even seen financial models that source data from a pivot table, which comes from another pivot table – this is not recommended!

Array formulae

The complex nature of certain formulae requires their completion via an array formulae, a static calculation that requires manually updating if financial inputs change. An array is executed by pressing Ctrl+Shift+Enter to turbocharge the formulas in a spreadsheet to great effect. This may seem a simple burden for the model builder, but as you are likely to be dealing with numerous cells, as well as other stakeholders accessing and using the financial model, this is a no-go zone for financial modelling – it is unmanageable.

Solver or What-If Analysis

These tools (Solver, Data Table, Scenario Manager and Goal Seek) are paramount for any financial model providing added value via strategic forecasting and analysis, courtesy of scenario or sensitivity analysis. In the case of Solver, users must remember to “re-solve” if their financial inputs change because Solver calculates and hard codes its answer to a scenario. Unlike Pivots and array formulae, especially in the case of Solver, its pros outweigh its cons, but model builders must clearly state where and when Solver is employed in the sensitivity area of the financial model.

Macros

Except for exceptional reasons, such as the construction of a macro to sculpt a company’s debt tranche for balance sheet purposes, vanilla financial models should avoid the use of crunching financial numbers through the use of macros. The beauty of Solver and What-If Analysis tools are, for the purposes of sensitivity analysis, that they replace the need for financial models to create macros.

Conclusion

There are certain static and manual applications that should not be used in a financial model. Array formulae and PivotTables are two powerful Excel tools but require manual updating if financial inputs change and could therefore jeopardise or make a financial model unmanageable. Solver or What-If Analysis tools are exceptional, but must be clearly expressed in their use in a model, especially for the use of sensitivity analysis and should avoid users from using macros as well.
Corality is proud to announce that we have been nominated for the BRW 2011 Fast Starter List. The BRW 2011 Fast Starter announcement is the latest milestone in a booming 2011 for Corality and a recognition for our rapid business growth.
Corality has had a busy 2010 and 2011 is picking up even more speed. This year we have extended our service offering, medical
opened up permanent offices in London and Singapore, advice
increased staff by more than 75% and expanded our customer base through a number of prestigious international contracts in Denmark, salve Indonesia and Mali. Our head office in Sydney will also soon move to larger premises in the CBD.
Read the full article, Corality is a BRW 2011 Fast Starter.
Corality congratulates all BRW 2011 Fast Starter nominees. We look forward to celebrate our award with our clients shortly and to work hard to secure a spot on next year’s BRW FAST 100 List.

Corality is proud to announce that we have been nominated for the BRW 2011 Fast Starter List. The BRW 2011 Fast Starter announcement is the latest milestone in a booming 2011 for Corality and a recognition for our rapid business growth.

Corality has had a busy 2010 and 2011 is picking up even more speed. This year we have extended our service offering, rx
opened up permanent offices in London and Singapore, increased staff by more than 75% and expanded our customer base through a number of prestigious international contracts in Denmark, Indonesia and Mali. Our head office in Sydney will also soon move to larger premises in the CBD.

Read the full article, Corality is a BRW 2011 Fast Starter.

Corality congratulates all BRW 2011 Fast Starter nominees. We look forward to celebrate our award with our clients shortly and to work hard to secure a spot on next year’s BRW FAST 100 List.

Successful people generally share the same habits and motivations. The same can be said for successful modellers – their daily habit is a best practice modelling methodology. At Navigator, view
that methodology is called SMART.

Navigator has launched its financial modelling methodology – SMART. We want to raise the bar in financial modelling and help financial modellers build better models! SMART financial modelling meets the needs of decision makers and financiers across all major project finance industries and is a set of best practice guidelines, information pills
internationally applauded for effective and professional financial modelling. The SMART approach to modelling decreases model risk and increases the confidence of all users – it helps people work smarter, not harder.
SMART will

  • Increase user and management confidence
  • Reduce model risk
  • Increase modelling productivity
  • Allow model audits to be performed on time and cost effectively

Join the ranks of successful modelers and start working smarter.
Read the full article, SMART financial modelling.
Successful people generally share the same habits and motivations. The same can be said for successful modellers – their daily habit is a best practice modelling methodology. At Navigator, Sildenafil
that methodology is called SMART.

Navigator has launched its financial modelling methodology – SMART. We want to raise the bar in financial modelling and help financial modellers build better models! SMART financial modelling meets the needs of decision makers and financiers across all major project finance industries and is a set of best practice guidelines, ed
internationally applauded for effective and professional financial modelling. The SMART approach to modelling decreases model risk and increases the confidence of all users – it helps people work smarter, not harder.
SMART will

  • Increase user and management confidence
  • Reduce model risk
  • Increase modelling productivity
  • Allow model audits to be performed on time and cost effectively

Join the ranks of successful modelers and start working smarter.
Read the full article, SMART financial modelling.
Successful people generally share the same habits and motivations. The same can be said for successful modellers – their daily habit is a best practice modelling methodology. At Navigator, order that methodology is called SMART.

Navigator has launched its financial modelling methodology – SMART. We want to raise the bar in financial modelling and help financial modellers build better models! SMART financial modelling meets the needs of decision makers and financiers across all major project finance industries and is a set of best practice guidelines, viagra
internationally applauded for effective and professional financial modelling. The SMART approach to modelling decreases model risk and increases the confidence of all users – it helps people work smarter, not harder.
SMART will

  • Increase user and management confidence
  • Reduce model risk
  • Increase modelling productivity
  • Allow model audits to be performed on time and cost effectively

Join the ranks of successful modelers and start working smarter.
Read the full article, SMART financial modelling.
Successful people generally share the same habits and motivations. The same can be said for successful modellers – their daily habit is a best practice modelling methodology. At Navigator, search that methodology is called SMART.

Navigator has launched its financial modelling methodology – SMART. We want to raise the bar in financial modelling and help financial modellers build better models! SMART financial modelling meets the needs of decision makers and financiers across all major project finance industries and is a set of best practice guidelines, internationally applauded for effective and professional financial modelling. The SMART approach to modelling decreases model risk and increases the confidence of all users – it helps people work smarter, not harder.
SMART will

  • Increase user and management confidence
  • Reduce model risk
  • Increase modelling productivity
  • Allow model audits to be performed on time and cost effectively

Join the ranks of successful modelers and start working smarter.
Read the full article, SMART financial modelling.
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, pills
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.

Valuation:

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 LinkedIn.com”

  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

    Regards
    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