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:
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.
We do a cross join of the relevant dates with the products to get all relevant combinations.
We have now a record for every product in every season month and can again create a calculated column for the sales figure.
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.
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.
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.
Sarah used the DAX LINESTX command to create the forecast and the error.
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%.
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.
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.
And this leads to random profits taking into account the salvage price for unsold products in the B channel.
If we sum over all products per index, we get a total random profit.
The distribution of profits can be visualized.
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.
Now, the intersection of with the threshold showed the 80% probable profit.
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. |