One of my favorite features introduced to Tableau in the past couple of years has the ability to use relationships in data models. If you aren’t familiar with data modeling using relationships, you can check out a couple of other Tessellation blogs (here and here) to find out more. Using this relatively newer technique to Tableau, I am going to go over how to create an extremely common use case for many in industry; forecasting inventory.
In this example, I am going to bring in data from 3 different sources to put together a mock use case for projecting a fictional material’s inventory levels. The three different sources will bring in the necessary data points for projecting inventory: current inventory, projected use of material, and estimated arrival of new material.
Why Are We Doing This?
The basic answer to the question, why are we doing this, is because everyone is doing it. Although not a good reason to do a lot of things, the reason most people are putting together inventory projections is because most business’ supply chains depend on projecting how much inventory they are going to have 30 days out, 60 days out, 90 days out, etc.Â
Understanding how much inventory you expect to have down the road will drive how much material you are ordering, how often you are ordering it, and sometimes how much you will be paying for it. This demo will show you just how I put together this mock use case for projecting inventory and some of the Tableau techniques that I used to create it.Â
Data Model
As stated in the intro, to complete this projection of inventory we need to know 3 basic facts.
- How much inventory do we have right now?
- How fast are we churning through said inventory?
- When are we expected to get more inventory and how much?
Most of the time, the data that you need to answer these three questions will not come from the same source. Most likely, your data will also have some gaps in it with regards to the expected arrival of your inventory. This is where I really have come to appreciate Tableau’s new data modeling capabilities. In an instance like this, I will bring in a dates table as my primary table. This means that the other 3 data sources will be brought in and related to the dates table.
Current Inventory – this data is related to the dates table for the day that you are starting from. Most likely, your data will be a snapshot of the previous day’s ending inventory, so this means today’s starting number will be the ending number from the previous day (assuming data is refreshed daily). This means your current inventory data should be related via the dates table dates to the inventory amount date plus 1.
Inventory Churn – this data will most likely either come from a statistical model that your company has produced from historical churn, or it will come from manual projections about future use. Either way, this data will be related to the dates table for the time periods described within this file. If the churn is at a granularity higher than the dates table, then you will need to smooth the churn to the day level.
Future Inventory Arrival – whether your future material will be arriving via train, plane, or automobile, you will need to make some assumptions about when your inventory will arrive based upon the data that you have available to you. In our mock data set we have data that tells us different statuses about our fictional containers full of material. Based upon these statuses, we assign a certain amount of days that it will take for our inventory to get to our inventory location. This expected arrival date is the date that we use to relate to our main dates table.
In the model above we’re relating Dates to Churn where Date=Date but in the other two tables we do some calculations to get the proper date to relate the tables.Â
//Inventory Calculation =
DATE(DATEADD('day',1,[Date (Inventory)]))
//Shipments Calculation =
DATE(
IF [Status]="arrived" THEN [Date Arrived]
ELSEIF [Status]="in transit" THEN DATEADD('day',60,[Date Departed])
ELSEIF [Status]="waiting at port" THEN DATEADD('day',5,DATE(7/16/2021))
ELSEIF [Status]="waiting to depart" THEN DATEADD('day',60,DATE(7/16/2021))
END
)
As a note about the shipments calculation, we are using 7/16/2021 as the date of viewing, but in a real environment you might have a TODAY() or {Max[Date]} calculation that would populate that date.Â
Current Inventory
In order to start calculating our projections, we first need to start with how much inventory is available at that time. In this use case our inventory system will give us a snapshot of the previous day’s ending balance. In the calculation below I use the boolean calculation that identifies the date that we are looking at to bring in just the amount of inventory for that day.Â
//Starting Inventory =
If ATTR([Max Day = Date]) THEN SUM([Amt]) END
This value will be used as our starting point in the projection.
Daily Change
To get the amount of churn or expected inventory arrival, because of the relationships we built in the data model, it is as easy as including them in the Measure Values that you drag onto the Marks card. See the screenshot below:
As a side note, the file that we brought in to the data model gave us weekly churn predictions, so in order to populate the churn by day we fix the week of the date from the Dates table and then divide by 7 (days in a week).Â
//Churn =
{ FIXED WEEK([Date]):SUM([Churn Prediction])}/7
Next, we need to figure out the predicted daily change that will happen with our inventory based upon how much is churned through and how much arrives. To do this, I created a Daily Change field to display this net change.Â
//Daily Change =
[Quantity Predicted]-SUM([Churns])
Daily Starting Amount
Now that we have calculated our starting point and our daily net change, we need to be able to show the starting amount for each day after that. This starting amount will be the previous day’s starting point plus the net daily change based on our predicted churn and arrivals.Â
As you can see in the calculation below, if the day shown is our first day then the current inventory amount is returned. If it’s not the first day, then we have a table calculation that looks up the previous day’s inventory amount and adds in the net change to it. This is now that day’s starting amount.Â
//Daily Change =
If ATTR([Max Day = Date]) THEN SUM([Amt])
ELSE
LOOKUP(
RUNNING_SUM(SUM([Amt]))+
RUNNING_SUM([Daily Change]),-1)
END
You can see how the calculation above functions in the screenshot below:
Ending Amount
Now that we’ve calculated the starting amount of every day, we can create a calculation that will at a total end of day amount to our table. This will be the sum of our [Daily Starting Amt] field and the [Daily Change].Â
//Total EOD =
[DailyStarting Amt]+[Daily Change]
Final Touches
Even though we’ve completed the table showing our predictions, there are a couple things that users might want in an inventory projection. Generally, companies have some level of inventory that they deem necessary to have in stock at any given time. In the dashboard below, I’ve added in a Parameter that allows users to adjust that threshold of inventory required. In the Marks card, I use the Bar Chart visual to color that day’s Total EOD value when it goes below the required threshold.
You can also see that I’ve added in some supplementary values into the dashboard to give more details about the projected inventory categories. The hope is that these values will give users some more insight into the different areas of this supply chain.Â
In Conclusion
Projecting inventory is an extremely common use case, so hopefully this blog post has given you some ideas as to how to accomplish it in Tableau. Just keep in mind that there are a ton of different variables that will go into projecting inventory for your specific use case from different data sources, different data structures, and different metrics that you’re wanting to track. This example is just meant to help you think through some baseline scenarios.Â
Do you have more questions about Tableau? Talk to our expert consultants today and have all your questions answered!
