Managing Inventory Risk with Power BI / DAX

Petra was getting tired. She was running a parasol production company on high leverage. Practically, all the capital to finance her enterprise came from the Rip Off Brothers Bank.

During the first years, there was no problem: she presented her forecast for the summer sales to the bank, and they checked the business plans from previous years. If the numbers were not totally off as compared to last years, they agreed to the necessary credit for the fiscal year.

Two years ago, everything changed:

there was this nerd guy Bob who asked questions like: what is the probability that your forecast is 20% too high? What is the money risk when this happens?

Petra tried to argue from her experience. Bob was never satisfied with her answers. He wanted to see some mathematical framework to assess the situation. The relationship got that bad that he wanted to have a corrected forecast after two months of the four months sales period.

This ended in unpleasant discussions, whether Petras guesses about her own business were right. Furthermore, the work to compile the mid-season forecast was heavy lifting.

Now, the business plan for the next fiscal year was due and she was not looking forward to the meeting in the bank. She asked her friend Sarah to go out for a drink. Sarah was a business analyst and programmer. Naturally, they talked about Petras business situation:

The sales season went from May to August and amounted for 95 % of the sales. The business idea for the parasols was to offer a selected number of styles and sizes that changed every year. The reason being that the standard parasol business with production in China was already covering the market. Petra was trying to play in a niche with stylish products, that should meet next summer’s taste.

It goes without saying, this was all push production: the planning took from autumn to spring. Then the main components were procured, the umbrellas were printed. Finally, the umbrellas were assembled, packed, and sent to selected retail stores in the entire country. The main components were procured in the Far East with a typical sea route lead time.

A postponement strategy for the printing was no option as the total sales figures were too low. So, she had one shot for the production figures per year. If she was too careful, she missed sales. If she was overoptimistic, she could only sell the overproduction at a salvage price through a B channel at a loss.

So, Bobs concerns were not completely unsubstantiated. However, Petra had no clue how to satisfy Bob. “Sarah, I will need this fancy AI stuff to get along!”.

Sarah said “I think Bob knows, that a forecast is always wrong, and he wants to have a quantification of the probabilities, how far the forecast will be off. Furthermore, he wants to see a framework, how these figures are derived. As the creation of the forecast is time consuming, you would need something automated that helps you create the forecast quickly.”

Petra and Sarah agreed that Sarah would meet Bob to understand what he really wanted. Here is what Bob told her: “To be perfectly honest, I don’t care about Petras forecast. More often than not it was completely off. I want to see a measurement of the downside risk of the business. So, I would need the company profit that would realize with a probability of 80 %. And I would have to understand how the figure was derived.”

Before we look at Sarahs solution, we do a little side trip how to generate a time series in Power BI.

Time Series Generation in Power BI

There are endless ways to make up time series within Power BI. The described way is using some assumptions that seem reasonable in this scenario. Then some randomness is added, and we get sales figures with a certain trend and a certain degree of randomness.

In our scenario we have 6 products with a sales price, total costs allocated per product and a salvage price below costs when products are sold in the B channel after season.

ID ProductName SalesPrice Costs SalvagePrice
1 Pink Rabbit 300 200 100
2 Yellow Submarine 200 120 70
3 Green Zoo 180 130 60
4 Purple Salamander 90 30 25
5 Black Widow 110 70 40
6 Blue Velvet 230 180 100

Every product gets a monthly base line quantity and an assumption of YoY growth.

ProductID BaseLine YoYGrowth MonthlyGrowth
1 50000 0.05 1.004
2 20000 -0.02 0.998
3 16000 0.02 1.001
4 13000 -0.01 0.999
5 9000 0 1
6 11000 -0.03 0.997
Product MaxTimeIndex Forecast StandardError
Pink Rabbit 48 60813 7802
Yellow Submarine 48 19257 2805
Green Zoo 48 17117 2548
Purple Salamander 48 12327 1917
Black Widow 48 8997 1303
Blue Velvet 48 94774 1499

The year-on-year growth is translated in a monthly growth assumption by means of a calculated column:

Inventory Growth DAX

If we assume that we have an already defined standard date table, we take the first day of every month of the relevant season months.

Relevant Season Dates DAX

We do a cross join of the relevant dates with the products to get all relevant combinations.

Cross Join Product Season Date DAX

We have now a record for every product in every season month and can again create a calculated column for the sales figure.

Synthetic Sales Figures DAX

Here, arbitrary randomness of plus/minus 25% is applied to the month-on-month growth. We get some random looking sales figures per product. As mentioned, there are countless ways to create this kind of data.

Synthetic Sales Figures Power BI Visual

Sarahs Solution

When Sarah looked at the data a certain trend, but this was heavily overlayed by fluctuations from month to month. It could easily be the case that for one year there were so strong fluctuations that the long-term trend was meaningless and the yearly sales per product had nothing to do with the trend.

Therefore, she decided not to do classical time series forecasting but rather use linear regression by product to get the most likely number of sales units by product. Furthermore, she wanted a simple model that Bob would understand, rather than a black box that produced a result, which could be hard to explain why.

Using linear regression is based on a lot of assumptions that might not completely hold. As mentioned, Sarah wanted an easy model that can be communicated, and the forecast is always wrong. Even the best model is based on assumptions and in all real-life scenarios these assumptions change over time. Forecasting is a special science in its own right and not the subject of this text.

The idea was to generate a baseline forecast per product and then do scenario considerations for the likelihood of the profits.

Rolling Yearly Sales Table

The base table had the beginning of the month as date and a date index and rolling four months sales units. This was a better choice as this was an aggregated figure as compared to the monthly sales figure and it was a yearly sales figure.

The forecast was done by linear regression by product with the assumption that the next time index as the x coordinate of our forecast value.

Forecast with linear Regression Table

Sarah used the DAX LINESTX command to create the forecast and the error.

Forecast with linear Regression DAX Forecast Error DAX

We just state the fact that with holding assumptions for linear regression, the forecast value is the mean of a normal distribution, and the standard error is the standard deviation of this distribution.

Sarah was happy so far: the forecast values stemmed from a clear methodology and could be easily explained to Bob.

However, Bob wanted the probability of the profit. After some thought Sarah went for the next big assumption in her model: the forecasts of the different products are independent of each other. This was presumably wrong, but again a simple model was the preferred option.

Her idea was to randomly draw a value from the normal distribution per product and interpret this as the demand that would realize. With one draw for each product the total profit could be calculated. If this was done a lot of times, the result would be a probability distribution for the profit. And then it would be easy to find e.g., the profit that would realize with a probability of 80%.

Monte Carlo DAX 1

In the example, the drawing from the normal distribution will be done 10000 times per product. Indices are created from 1 to 10000 per product. For every line the relevant product and forecast data are linked to the product name.

Monte Carlo Table

The random value that is calculated per line is the crucial point of the method. With the inverse of the normal distribution function a random value for the actual demand is created.

Random Gauss Value DAX

And this leads to random profits taking into account the salvage price for unsold products in the B channel.

Random Profit DAX

If we sum over all products per index, we get a total random profit.

Profit Distribution DAX

The distribution of profits can be visualized.

Profit Distribution Visual

Sarah wanted the question of the profit with 80 % probability graphically. She calculated the probability per profit bin and added column for the 80 % threshold.

Profit Bin Table Probability of Profit DAX

Now, the intersection of with the threshold showed the 80% probable profit.

Probability of Profit Visual

There would be a profit of 200.000 with 80% probability.

When Bob saw the presentation of the results, he was satisfied. “I did not think that I ever saw a Monte Carlo simulation in this work.”. He saw immediately that the risk of huge losses in the business was not big. He agreed to the necessary credit line for the business.

Key Takeaways

Use simple models to start:
If a simple model, like the linear regression here, does not give you results that that correspond to your business understanding, you have to dig into your business understanding or your model assumptions. A more sophisticated model can be more accurate, but will not change the fundamental drivers of your business.
Add randomness to your analysis:
How confident are you of a figure? This questions always arises. E.g. two gauss distrbutions with the same expectation value but drastically different standard deviations tell you a lot about the quality of your expectation value. The Vertipaq engine is very fast and it is absolutely feasible to create a lot of random numbers.
Cookie Consent with Real Cookie Banner