The Data Tells a Story
(That's practically the definition of Business Intelligence)
Knowing how to build a Power BI report is one thing.
Designing it to analyze actionable information is another.
Deciphering the story it tells is yet another.
Companies by Industry
The following analysis is of 7.5 Million organizations from LinkedIn, with date of formation, industry and size. This is provided free in CSV format by People Data Labs. I used Power BI to produce these reports.
In a practical setting, new data would feed reports with minimal manual intervention, enabling near real-time monitoring. Monitoring near real-time transforms how managers oversee the business, resulting in tangible bottom line improvements.
First, Please Try a Few Things to Interact with this Data:
Interact with the Map
Click your favorite state to see the data automatically update for company information specific to that state.
Click it again to clear your selection and return to the full map.
You can even hold ctrl and click multiple states, perhaps representing a region you manage…
View Organization Details
Use the ‘U.S.’ button to view reports filtered for U.S. organizations.
Find the Construction Industry in the middle table and click the ‘1.2%’ in the 10000+ column. The other tables and the statistics at the top refresh to be specific to the largest Construction companies, and the bottom table now shows details of the 47 Construction Companies with more than 10,000 employees.
You can click it again to clear your selection.
Narrow Your Focus
Staying on the U.S. reports, click on Colorado in the top right table, and click the ‘+’ symbol to expand the cities.
All the other tables are now updated to reflect statistics for Colorado. Click Denver to refine your data even further.
The Story...
What can we learn from the data? Here is what I see.
Industry Summary and Global Trends
Company formation decreased globally and for the U.S. in 2018, 2019 and 2020, a trend that began before COVID started spreading.
Worldwide, 479,268 companies were formed in 2017, followed by 449,888 in 2018, 404,698 in 2019 and 397,890 in 2020.
Real estate is one industry that grew in company formations between 2017 and 2020, during a time when housing sales and prices rose substantially. Professional Training & Coaching, Architecture, Media Production, and Staffing & Recruiting are among sectors that also grew during these years.
Formations by Industry
Starting in 1996, Information Technology and Service takes over as the most commonly formed business globally. While Information Technology was a leading industry in the U.S. for many years, it is now Marketing and Advertising that leads the U.S. company formations.
Size by Industry
Information Technology, Higher Education and Pharmaceuticals tend to be the largest companies.
Tool Tip: Click on specific years in the trend graph on the Global reports to see how industries stacked up in that year.
Formations By Industry
Overall, the industry with the most companies created is Information Technology and Services. This changes over time, with Construction being the most commonly formed business in 1980, followed by Real Estate and Retail. Information Technology and Services ranked 12th in 1980.
In 2004, Information Technology gave up its lead in the United States. Construction and later Marketing and Advertising take over as the most often formed companies in the U.S.
Size by Industry
Most Accounting businesses have 1-10 employees, and more than 93% have 50 employees or fewer.
Photography businesses are the smallest, with more than 92% employing 10 or fewer people.
Information Technology companies are the most likely to be large, comprising globally 5.3% of all companies employing more than 10,001 people.
A different mix of industries
U.S. Organizations
Marketing Focused
While Information Technology dominates the global company landscape, the leading Industry in the U.S. is Marketing and Advertising, followed by Contruction. Information Technology is the 3rd most common Industry in the U.S.
Size Doesn't Matter
The mix of small to large companies is relatively consistent between the U.S. and the rest of the world with the smallest category comprising 59.7% and 57.4%, respectively, and both having 0.2% of companies with over 10,000 employees.
California is King
With 321,989 companies formed between 1985 and 2020, California almost doubles the second place Texas at 175,241. California companies are led by Marketing and Advertising, Information Technology, Construction and Computer Software industries (Silicone Valley).
Healthcare Heavy
2.2% of all companies in the U.S. are in the Hospital and Healthcare Industry, compared to 1.2% worldwide.
a Local focus
Colorado Organizations
Denver Dominates
Denver has as many companies as Boulder, Colorado Springs, Fort Collins, and the next 3 largest municipalities combined. And that’s counting Englewood, Centennial and Greenwood Village as their own localities.
In Denver, the most common Industry is Marketing and Advertising, but overall in Colorado Construction represents the majority of businesses.
Fortune 500 Representation
Colorado is home to 76 companies with more than 10,000 employees, including Red Robin, Boston Market, Dish Networks, and CorePower Yoga.
That number pales in comparison to California at 714 companies with more than 10,000 employees, and Texas at 307.
Focused Marketing
The Organization Details table at the bottom of the U.S. reports show individual company information including the LinkedIn profile and website.
When these reports are made available on a company intranet, the option is available to export these details into Excel for focused follow-up.
Data Cleanup
The data includes several entries with invalid formation dates. For example, a company formed in 2019 is valid, but a company formed in year 1010 is not. The data includes 646 companies formed before the year 1200, which are likely invalid dates. Also there are 14,397,037 companies without a formation date.
We filtered out entries with a formation date before 1985 and those missing a formation date for the purpose of this analysis with the understanding that more recently formed organizations are the focus of this analysis.
There are 504,334 organizations that have no country indicator and 1.3 Million records that have no industry information.
These have been omitted from these reports.
It is critical to understand what data is excluded. Decisions should be made while seeing the full picture, meaning the data needs to be complete. Information not included in the starting dataset, or that has been filtered out, needs to be understood and quantified to ensure decisions makers make smart decisions.
Financial data, for example, should be reconciled to the financial statements and general ledger.
Use the button below to view another example Power BI project
Car Sales
About the Author
This was created by Shane Bohlender, CPA and MBA. I have seen Business Intelligence reports like these transform how businesses are managed with tangible bottom line improvement.
Some examples that I’ve seen particularly effective:
- Sales by customer, region, product and sales person, with comparisons to budget and KPIs
- Operating expenses by department with comparisons to budget and KPIs
- Freight cost comparisons to amounts passed along to customers
- Accrual details to support research and ensure completeness and accuracy
- Cash flow tracking with forecasts
- Dashboard for executive monitoring of financial results in near real time
- Company-wide KPI management
- Combining data from multiple systems to provide the full picture