Inventory management solution is a set of tools that enable you to track your inventory levels as well as the movements in your stock room or warehouse. Power BI is the perfect analytical tool for this. You may watch the full video of this tutorial at the bottom of this blog.
In this tutorial, I go through some inventory management demo data and show you how to effectively manage your stock levels based on sales through Power BI. I’ve created an entire inventory management solution that allows any company to look at all the key indicators or metrics that go along with inventory management.
So we’re looking at things like products – how many are sold versus the amount that we have in stock – the total inventory value, the restocking levels, the vendors whom we purchase from, and so much more.
Sample Inventory Management Dashboard
I’m big on dashboarding and data visualization. It’s important to make visualizations pop with colors. The way they’re structured should create compelling insights. Simplify things in your dashboard, but make the data dynamic.
This is the inventory management dashboard that I made to show how we can compare and analyze our inventory dynamically in Power BI.
We’re also incorporating our sales information to check how much we’re selling versus the stocks we currently have, as shown in this table. The metric that I chose for this example is 12 months, but you could change this around, depending on your sales.
On the upper part of the dashboard, we have the warehouses, the costs, and stock units.
The rest of the visualizations here are quite helpful in providing us with significant inventory insights.
Dynamically Drill Into The Data
I made this dashboard in a way that we can dynamically drill into specific items. We can find out which items we’ve sold a lot of, but we don’t have that much inventory for. For example, we can see that we’ve sold a lot of bamboo, but we don’t have much of it in stock.
When we click on this item, we’ll see where this inventory sits. On the dashboard, we can see that it’s in this warehouse and this is the value of that inventory. We also have some information about the vendors of this item.
We can go deeper into any of the information here. For example, we’re looking at this warehouse. If we click on it, we can see the stock we hold there.
It’ll show us the list of items or products in this warehouse, the value, the vendor who have the stocks of the items, etc.
Automated Restocking Cost
Another great thing that we can do when managing our inventory within Power BI is to automate the analysis on what our restocking cost is going to be. This will ensure us that we have enough cash available to purchase things that we need to restock or repurchase.
This automated calculation enables us to know how much cash we need for the next repurchase of the stocks based on the reorder levels.
The Data Model Structure
If we look at the data model, what’s interesting is that we have two fact tables here. We have our Sales data, which is historical, and our Inventory Stock Control data, which is static like a timestamp, so every day it’s going to be different.
We need to compare both these tables with all the other different lookup tables. This is an interesting concept and something that you have to get your head around. In many cases, you’re going to have multiple fact tables when doing Power BI analyses.
Developing a model that incorporates these elements in an intuitive and seamless way is really important.
***** Related Links*****
Inventory Management Insights With Power BI: Comparing Stock To Sales
Inventory Management – Power BI Showcase
Calculate Days Of Zero Stock – Inventory Management Insights w/Power BI
It’s my intention for this tutorial to show you what’s possible with Power BI as an inventory management solution. You can find many unique insights from this analysis if you just start in a simple and intuitive way, and then build on those insights and collate them in a great visualization. Then, you can add a lot of value to the analysis that you’re doing for your organization.
This is just one example, but you can take a look at the video and the related links below for more information.