SOURCE OF DATA: MAVEN ANALYTICS DATA PLAYGROUND
SITUATION
I had a potential client who was uncertain about launching a health-related business, specifically in determining if investors were keen on funding such ventures and to what extent.
TASK
My objective: Identify and assess the level of interest in health ventures.
ACTION
- Data Source
- Maven Analytics Data Playground
- Two files
- CSV with 1,074 rows and 10 columns
- CSV of data dictionary
- Two files
- Maven Analytics Data Playground
- Data Cleaning
- Reviewed the data dictionary sheet
- Created a table
- To ensure changes are synced
- To quickly apply the filter to all columns
- Filter
- Checked if the countries were categorized in the right continent
- Corrected the entry for South Africa from Asia to Africa
- Checked if the countries were categorized in the right continent
- Conditional formatting
- Looked for blanks and "unknown"
- Missing data
- 12 rows had missing data under the funding column
- Filled out the missing values with the AVERAGE of funding
- 12 rows had missing data under the funding column
- Text to column
- Separated the select investors into different columns to get their distinct count
- Used TRIM to remove space in front of the text after splitting the data
- Separated the select investors into different columns to get their distinct count
- Converted text to number
- Turned the text under "funding" such as "$8B" and "$527M" to a numerical value to use for calculations
- Removed the $ sign and the letters M and B through this formula:
- =IF(RIGHT(I9, 1)="B", VALUE(MID(I9, 2, LEN(19)-2))*1000000000, IF(RIGHT(I9, 1)="M", VALUE (MID(I9, 2, LEN(19)-2))*1000000, ""))
- Removed the $ sign and the letters M and B through this formula:
- Turned the text under "funding" such as "$8B" and "$527M" to a numerical value to use for calculations
- Outliers
- Otto Bock Health Care was the oldest company founded in 1919 and the only one who didn't receive any funding
- Others were founded from 1979-2021
- The time when the companies was founded was irrelevant to the analysis so I didn't show this on the infographic
- Otto Bock Health Care was the oldest company founded in 1919 and the only one who didn't receive any funding
- Data Analysis
- Filter
- Filtered unicorns that belong in the health industry
- Copied the result and pasted it on a different sheet
- Sort
- Ascending/descending
- To see the highest amount of funding
- Discover which health venture received the highest amount of money and which investors backed this business
- To see the highest amount of funding
- Ascending/descending
- Filtered unicorns that belong in the health industry
- Added a new column
- Placed all the investor's names in one column
- Removed the duplicates
- Counted the number of times they invested in health ventures through this formula
- =COUNTIF(K2:N75,O2)
- Placed all the investor's names in one column
- Pivot tables
- Created pivot tables to get the following:
- Sum of funding in each industry
- Percentage of funding in each industry
- Number of unicorn companies
- Number of unicorn companies in each industry
- Number of countries that have health unicorns
- Sum of funding for health companies in each country and each continent
- Percentage of health funding for each country
- Used the tabular format for select pivot tables
- For enhanced readability
- Collapsed subtotals
- Removed/added buttons
- For enhanced readability
- Created pivot tables to get the following:
- Filter
- Data Visualization
- The entire dashboard was created through Excel.
- Font
- Used the font Futura to make it easier to read
- Color
- Used shades of grey and cream for less cognitive load
- Layout
- Ensured there was enough white space to create a pleasant infographic viewing experience
- To create sections for a clean and organized visualization
- I added shapes, an outer shadow, and rounded corners
- I grouped all the shapes and content, then saved it as an image
- Deleted lines, axis labels, and legends to remove clutter
- Map chart
- Through the data ribbon, I turned the country column into a geography data type
- Font
- The entire dashboard was created through Excel.
RESULT
Investors allocated $591.8B across 15 industries. Of 1,074 companies, only 74 were in health, receiving 6% of funding. Fintech led with 18%. Health unicorns spanned 3 continents and 12 countries, with the U.S. at $23.5B, China at $5.5B, and France at $1B. The U.S. boasted 69% of health unicorns, 51 out of 74 companies.
Based on the analysis conducted, it is evident that the health industry presents a compelling opportunity for investment. The following recommendations can guide my potential client in their decision-making process:
- Focus on Key Markets: Given that the United States, China, and France are the top countries in health funding, consider targeting these markets for potential business expansion or investor outreach.
- Emphasize Uniqueness: Highlight what sets my client's health venture apart from others in the industry. Investors are more likely to show interest in innovative and unique offerings.
- Build a Strong Investor Network: Leverage the information on investors who have shown a keen interest in health ventures. Develop a targeted outreach strategy to engage with these investors effectively.
- Financial Viability: Continue to emphasize the financial viability of the health venture. Clearly communicate the potential return on investment and growth prospects to attract investors.
- Diversify Funding Sources: While the United States dominates in health funding, explore opportunities in other countries and continents to diversify sources of investment. This can help mitigate risks associated with market concentration.
- Collaboration and Partnerships: Explore collaboration opportunities with established health unicorns or related businesses in the industry. Partnerships can not only attract investors but also enhance credibility.
By implementing these recommendations, my potential client can better position their health venture to attract investors and secure the funding needed for a successful launch and growth.
Presenting the Excel dashboard: