Unicorn Companies Analysis

Introduction:

As part of my work with Maven Analytics, I recently worked on a financial dataset related to unicorn companies from around the world. The dataset contained key information about these companies such as their valuation, funding, industry, country, continent, year founded, and investors. In this article, I will share my analysis and insights from the dataset, and answer some key questions.

Question 1: Which Unicorn Companies Have Had the Biggest Return on Investment?

To answer this question, I first converted the valuation and funding columns into a comma-separated format using the following formulas:

Valuation:=IF(RIGHT(A1)="B",LEFT(A1,LEN(A1)-1)10^9,IF(RIGHT(A1)="M",LEFT(A1,LEN(A1)-1)10^6,A1)**

Funding:=IF(RIGHT(A1)="B",LEFT(A1,LEN(A1)-1)10^9,IF(RIGHT(A1)="M",LEFT(A1,LEN(A1)-1)10^6,A1)**

After converting these values, I calculated the Return On Investment (ROI) by using this formula:

=(Valuation-Funding)/Funding

The answer to the first question is:

=3999% Yuga Labs Company with Maximum ROI

Question 2: How Long Does It Usually Take for a Company to Become a Unicorn? Has It Always Been This Way?

To calculate the expected time for a company to reach unicorn status, I used two dates: the date when the companies were founded and the date when they reached a valuation of $1 billion. I extracted the year value from the Date_Joined column by using the Excel function Year().

=Year(cell name)

The answer to that question is:

On average, it takes 7 years for a company to reach unicorn status. The maximum time for a company to achieve unicorn status is 98 years, but it varies from company to company. The minimum time is 4 years for a company to reach a valuation of $1 billion.

Question 3: Which Countries Have the Most Unicorns? Are There Any Cities That Appear to Be Industry Hubs?

To answer this question,

I analyzed the dataset and found that the country with the maximum number of unicorns is the USA, and the city with the maximum number of unicorns is San Francisco.

Question 4: Which Investors Have Funded the Most Unicorns?

To answer this question, I performed the operation of delimitation by using the Text to Column function from the Data tab by removing the comma from the investor's column. Then, I used the formula =SUM(COUNTIF(K:N,K2)) to find the sum of investments by each investor. After that, I used Max() to calculate the maximum value from the column, which came out to be 30. To get the name of the investor with the most investments, I used the following formula: =INDEX(K:K, MATCH(P2,O:O,0)).

The answer to that question is:

Sequoia Capital China with a total of 30 investments.

Conclusion:

In this article, I have shared my analysis and insights from the financial dataset related to unicorn companies. I have answered some key questions and provided formulas for calculations. My findings show that the USA has the most unicorns, and San Francisco is a city known for its industrial hubs. I hope this analysis provides valuable insights to those interested in unicorn companies and their financial status.

To achieve all of this I use Excel and the dashboard below is also made in Excel: