Using Big Data for Small Business : Excel Powerpivot Example

Recently I published a Linkedin article about Powerpivot, a tool that gives anyone the ability to harness big data to make better decisions and ultimately make more money for their business.

In this post I wanted to offer you a specific example of how you can use Powerpivot to answer a specific question. The file for this post can be downloaded at the bottom of the page.

Our Question

Will there be a market for our new product?

Our Demographic:

For this example we are going to be selling a product that tests well with hispanic men, around the age of 27, who reside in metropolitan areas of California.

Our Data:

After doing the simplest of searches online I found a great data set from data.ca.gov .

California Population Projection by County, Age, Gender and Ethnicity

 

Our Report

With the demographic and data source identified we can link Powerpivot directly to the CSV file hosted on the government server. By doing this, we are able to import 4.2 million rows of data to validate our assumptions, without crashing Excel. Without Powerpivot this would have never been possible.

data-connection-3

With the data loaded we can insert a pivot table and chart to the main tab of Excel. For the column values we will use the year values and our rows will be the Counties of California.

pivot-table-creation

After our table is created we can select the graph and use the year for the axis and population for the values.

pivot-chart-creation

Our graph and data looks good but our demographic is for those who live in metro areas. So we can use a quick value filter to give us just the top 10 populated counties in California.

10-value-filter

Now we have the locations to focus on but we still have to drill down to the age and ethnicity. To help with this we will use slicers tied to both the graph and table. This allows us to play around with the data if we need to and see the changes in real-time visuals.

slicers

With all of the information imported and formatted we can now start asking if our customers will be around in the future and if we should actually go forward with funding this new venture.

using-slicers

Our Answer

Based on millions of data points we were able to learn that our key market will shrink in the coming years and that it may behoove us to start with this single product and expand latterally into different ethnicity or locations.

This answer can help your new business invest in the right areas of the market and potentially save thousands in development and expansion costs that would have otherwise been wasted. And this was all done with Excel and free data from online.

Let me know if you have a specific question I can help answer with data.

Enter your email to download the example, you can opt out at any time!

[mc4wp_form id=”296″]

Leave a Reply

Your email address will not be published.