How to combine large datasets with calculations in Excel

Hi all, hope everyone is doing fine.

I wanted to ask the opinion of the community on a project I'm currently working on. I want to create a dashboard that will help me keep track of product stock in certain stores, let me create statistical figures on products, categories, and stores, and will alert me on possible inventory breaks (products running out of stock in certain stores).

The main issue I'm facing is that I have more than 40k combinations of products and stores, hence I'm not sure on how to work on a long term feasible solution since I haven't done something similar in the past. My main objectives are:

1. 1. Create a dashboard that will give me average numbers of total stock numbers as a general KPI, along with its historical data.
2. 2. Create lists of products that are likely to run out of stock, following a certain rule (as in, more than 5 products left at the end of the week).
3. 3. Create datasets of historical data for these numbers (totals, for each product/store combination).
4. 4. Perform calculations (geographical averages, identify outlyers, products or category averages, etc.) along with their historical evolution.
5. 5. Easily include weekly new data into the model.

I'm attaching an example of the input format (simplified), and I'll answer any questions on issues that may have not been clearly depicted. Should I consider using SQL databases? Should I be thinking of creating a VBA script? There are many points where I am not clear, so I hope you guys can help me, any/all advice is welcome.

Kind regards,
Pedro

Nothing that you have mentioned cannot be done with a combination of formulae and charts.

How 'big' is this database going to be? Is it flatfile (that means just one data table - as you have now), or will it need to be relational (that is, multiple interdependent tables)?

The size of the dataset is of primary concern - Excel cannot handle an infinite number of rows.

What I have done in the past when working with really large data files, is to have 1 file for the data - with maybe some basic formulas if needed. But then I have a 2nd file linked to the data file when the bulk of the calcs are done. This speeds up calculations and lets you interrogate the data file in many different ways.

Give this some thought and see if this is something that might help?

Another way is to process the data in an Excel workbook and then use Power BI (if you are in a corporate environment that has it) to create dashboards, but the learning curve is quite steep.

Here is a example of how a combination of Power Query and Power Pivot might be used to handle large amounts of data to display information about sales.
1. Use Power Query (Get & Transform) to convert the sales data into records using the following advanced editor code:

Please Login or Register  to view this content.

2. Close and Load to the data model which is capable of holding millions of records
3. In the data model add the following measures:
Number of Weeks:=DISTINCTCOUNT(Table1[Week])
Average Sales per Week:=SUM(Table1[Sales])/[Number of Weeks]
4. Produce two pivot tables which are connected to a slicer
Let us know if you have any questions.

What do you believe?

For the amount of data you are going to be dealing with, I'd go with the right tool for the job - a database. I've built a few in my time in Access.

It's probably possible to query an Access database's tables in Excel for the analysis and dashboarding.

Thank you again for your help. I was thinking along the same lines, so I'll try to go with that.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Please change the title of the first post so that members see the new question.

I will move this to The Water Cooler, as it has moved away from being just an Excel query.