Too much data - sales tracking advice

[Vinval]

I am trying to analyze sales by store for approx. 300 stores. the data is a running 12 months. I do not know how to create a dashboard with any meaning because of the number of stores. All of the stores are for one customer.
An additional aspect that I would love to address is the fact that each store has anywhere from 3-9 SKU's.
I am at a complete loss on how to track sales.

 

[Luke M]

Welcome to the forum Vinval.

Here's how I would tackle it. You can follow my process going left-to-right through the spreadsheets.

First, we need to re-arrange your data a little to make it Pivot-able. Used a trick known as a "reverse Pivot" to do that. Once that's done, data analysis becomes much easier! For starters, I created a basic PivotChart, letting you Pivot down to a selection of store(s) and/or dates. You can now better see if sales are trending up/down.
Beyond that, it's up to your imagination as to what you want to show. CHandoo has many examples of Dashboard, which you could use to get some ideas of how you want to present your data. Take a look, see what you like, and if you get stuck, we can help you build them.

 

[Vinval]

Luke,
Thank you for the quick response. When I look at the custom pivot tab you created none of the sales by store came over with the stores so I am a little confused.
However, my big issue is that I am trying to analyze sales by store not all stores consolidated. I need to be able to focus our limited resources on the stores that need the most help. When I try to chart data I am overwhelmed because there are 300 stores.

 

[Luke M]

"Custom Pivot" sheet, since it's only step 2, is an in-between step. The raw data sheet and Custom Pivot are only there to show you how I manipulated the data. From the Custom Pivot, I created the "New Data" sheet, which runs the Dashboard. That is there you can select which store(s) to look at. As you say, looking at all 300 would be overwhelming. With the Pivot, you could choose specific ones, or the top 5, or top 10, or best 10 by year, or best 10 by month of September, etc.

Top 10 stores:


Top 10 stores for each of past 3 years


As I said, it's up to you to figure out what story you want to tell, and then we can build the dashboard/chart to tell it.

 

[Vinval]

Ok, that is good. How would you go about creating a trend so I can see if sales by store are moving up or down?

 

[Luke M]

With the original file I posted, right-click on chart, add Trendline. From dropdown in B1, pick a store (or group of stores). That will give you a basic trend. Again, we can filter by dates as well, if desired.

 

[Vinval]

Luke,
I am not sure if I am supposed to manipulate the data. I thought the sheet was set up based on your comments. As you can see on the uploaded sheet when I choose one store like 901 there is no sales data. there is a number for one month but it does not come from any sales data from that store.

 

[JuliusV]

Hi Luke,

can you explain how you were able to create the bar graph and trend line to be linked to the pivot table? If you could point me to resources or list the basic steps, I would really appreciate it.

Thank you,

juliusV

 

[JuliusV]

Vinal,

If you look at the Custom Pivot tab you will see that the data for Home Depot 901 only has one value for Jun 2014. I am assuming that the data will have to be manipulated.

 

[Luke M]

Hi Vinal,
Sorry for the confusion I caused. I was trying to show how to take the raw data in your current layout, convert it to a different layout, and then show a PivotTable based on the new data. Presumably, new data that you generate could be added to the new layout, and you wouldn't have to keep changing the layout. But, I know that we can't always control the layout that data comes to us.

Once it's in the new layout, my intent was to show that it is easier to analyze, and you have many options. As Julius pointed out, Store 0901 only had a single data point, so the chart is correct. However, I do see that your raw data has "Store 901" and "Store 0901". If these are supposed to be the same thing, some data correction will be needed (again, at this point, you can make changes/updates to the "New Data" worksheet.)

Julius,
The chart is a regular PivotChart, no fancy linking needed. If you select a cell in your PivotTable, then you can hit this button

and make your chart like normal.

 

[Vinval]

"Custom Pivot" sheet, since it's only step 2, is an in-between step. The raw data sheet and Custom Pivot are only there to show you how I manipulated the data. From the Custom Pivot, I created the "New Data" sheet, which runs the Dashboard. That is there you can select which store(s) to look at. As you say, looking at all 300 would be overwhelming. With the Pivot, you could choose specific ones, or the top 5, or top 10, or best 10 by year, or best 10 by month of September, etc.

Top 10 stores:
View attachment 10178

Top 10 stores for each of past 3 years
View attachment 10179

As I said, it's up to you to figure out what story you want to tell, and then we can build the dashboard/chart to tell it.

Luke,
I have gone over this and am still not seeing what you are explaining. If I go to the new data tab in Pivot Dashboard2 I still do not see the original sales data. If I go to cell A1 in New Data tab and select only store 901 it shows $0 for all months except for $903 in June 14. If you look at the raw data for 901 there weas sales in 12 months totaling $12,679.35. am I missing something that I should be doing?

 

[Luke M]

There were two ideas to the file. The first idea is re-arranging your original data from 'Raw Data' into a layout that could be better data mined/analyzed. The process for this idea was to create the PivotTable in 'Custom Pivot', which we can then double-click on the grand total line and create info that we see on 'New Data' sheet. Again, this is just moving data around, not the dashboard. Once you've re-arranged your data, no need to repeat this process.

I'm not sure where you are looking that you don't see the info...if we filter 'New Data' for store 901 (again, not 1901, not 0901), we get this. Note that I've created a Total row for this image, showing that SUM = 12,679 (just like in original)


If we go to the New Dashboard, we can use the PT to graph/analyze our data. And again, if we pick store 901, we see the correct total


If you still have questions/problems, I would suggest trying to paste a screenshot of what you are seeing, as I am lost as to what is (if anything) is wrong.

PS. It appears that you errantly were looking at store "0901" in the 'New Data' sheet, and comparing that to '901'. If these are supposed to be the same store, I would recommend standardizing your data so that all stores use a 4 digit number.