Inventory Analysis

This chapter is all about analyzing inventory levels and changes in inventory. This is a specific kind of analysis, as we are interested in the status of something, in this case inventory quantity or value, at a specific point in time.

An inventory report could provide insights into how inventory levels vary over time, and which products risk running out of inventory. And, on the other side of the spectrum, you may have simply too much inventory for a product relative to its turnover speed – although the question "how much inventory is too much?" is not a simple one to answer. As an example, in this chapter, we will calculate the number of products that are likely to be still in stock twelve months from now, given a sales forecast. It may seem obvious that products that are on the shelves for a year could have been produced or purchased later, saving the company money.

Although this chapter uses product inventory for our beloved QuantoBikes...

Data modeling for status-oriented data

First, a discussion is needed on different ways to model data for which status is the determining factor. In the real world, how data is modeled is typically a trade-off between what is best for the Power BI model and what is achievable from a data preparation point of view. Here, we focus on what would be best for the model; if you apply this for your organization, you may find that data preparation efforts weigh in more heavily.

In deciding what the facts in the model will be, you have two basic options:

1. Store the status for each unit of time, usually each day.
2. Store the changes in the status.

The first option may seem the most straightforward. We just store one data point per day, per product:

Figure 2.7.1: Storing the status of inventory

There are, however, a number of issues with this option that you need to take care of if you choose to model this way:

• Most source systems store inventory...

Basic inventory calculations

Let us start by answering the most basic question: what is the actual inventory? Because of the way we have modeled our data, this one is easy to do:

Actual Inventory =
CALCULATE(
    SUM(fInventory[Quantity]),
    fInventory[Type] = 0
)

All we need to do is retrieve the rows with [Type] = 0, as it is in those where the actual inventory is stored.

There is one issue with this calculation, however: it assumes that we have selected the whole of the Calendar table, or at least made a selection that includes the date with the Type 0 rows. After all, when we select last year, the rows containing the current inventory will not be selected. It would be better to create a measure that returns a result for any selection on Calendar.

If we assume that we have some selection in Calendar, the first question to ask is: which exact date do we pick to return the status for? After all, the inventory status is strictly date-specific.

The common practice...

Inventory forecasting

You have now seen some examples of inventory calculations. In some way, the measures describe a static view of inventory: the quantities at a specific point in time, and the differences compared to a fixed inventory target level. In reality, inventory is constantly in flux: products are shipped from our warehouses, and new products come in from manufacturing. It is therefore more interesting to take turnover speed into account when we analyze inventory. And as the real business value comes from balancing our inventory levels as exactly as possible with our sales, we want to have some way of looking into the future of our inventory. In other words, what we want is an inventory forecast.

Two types of forecast

There are, of course, many ways to get a prediction of the future state of inventory. We will cover two options here: first, using a sales forecast that is provided by salespeople, and second, extrapolating changes in the inventory that have occurred...

Summary

In this chapter, we have dealt with analyzing inventory data, although the kind of analysis in this chapter can be applied to all sorts of status-oriented data.

We have discussed different ways to model this kind of data, how to calculate inventory status at some point in time, and how to compare actuals with targets.

You have also seen different ways to look into the future. As inventory is typically needed to fulfill sales orders, being able to estimate future stock levels is a valuable capability. We have covered creating a future inventory outlook through sales forecasts and through extrapolation based on average shipment volumes. We have also shown you how to do linear regression in DAX, providing a slightly more sophisticated method to predict future shipments and extrapolate current inventory levels to the future.

More advanced ways to extrapolate or predict future inventory than the ones covered in this chapter are possible as well; you could create extrapolations...