Stock Turnover

[ElliotP]

Evening,

I'm trying to calculate the average rate of stock turnover. I'm not attempting to use any sort of inventory system or anything like that but rather the sales data and sound assumptions. The assumptions being for example that the venue buys beer by the case of which has 24 beers.

So I'm trying to work out how long it takes on average across a time period, let's say how many days, it takes to consume 24 of a specific beer.

My feelings were initially a cumulative figure and then an IF function which perhaps worked out the difference in dates between when the first beer was sold and the last beer was sold but I wasn't sure of how to put it together.

I've attached my pbix if that helps anyone; My Pbix

[v-yulgu-msft]

Hi @ElliotP,

According to your description, you want to get the difference between the date when sold the first beer and the date when sold the last beer (the 24th beer), right? Then what does " how long it takes on average across a time period" mean? The average date range about selling all kinds of beers?  

From the pbix file you rprovided, I am confused about so much tables and data records. Would you please create some sample data, only to list those tables and columns , and thier relationsips that will be used in this scenario so that I can reproduce your requirement?

Thanks,
Yuliana Gu

[ElliotP]

I am attempting to create the level of stock turnover without an inventory management system so we have to take a slightly different approach.

For example; Assuming there are 24 beers in a case and the venue purchases the beer by the case. I would like to work out the time difference between when the first beer was sold and the 24th beer was sold. I would like to be able to work out this value continuously as so over a period of a time you could average the resultant time values to work out how long it took to sell a case of beer.

My thoughts were to use a rolling cumulative measure and every 24 beers for example (1-24, 25-49,etc) it makes a note of the difference in time.

In my linked pbix it has a lot of things. The relevant Columns in the data table, Itemdetails$dogfood are [date], [item], [qty]. The date table is the 'extendedcalendarenglish'.

[Frank]

What you are asking for is an inventory tracking system, but using an assumption of FIFO to identify lots and track their duration in the system.  To make the calculation, I would divide the cumulative qty by the package size (i.e. 24 beers, 8 hot dogs, etc.) and filter where the remainder is 0.  The divide by column is your lot code that you can join (with product) the sales and receipts tables together with.

I've attempted something similar in the past for component production in a factory, but in my opinion it took too many resources and I'm not sure the results meant much to the audience (myself included) and while it was nice to look at I don't think it was very actionable data.  The reason I went that route is component jobs were opened and closed within the same week so ending balance data was not useful...in your case it doesn't sound like you have that problem in which case I would stick to textbook method for Stock Turnover. You can always convert it to days on hand instead of inventory turns.

[ElliotP]

An idea I've been playing with is using a cumulative measure and an if function with the DATEDIFF function.

A cumulative measure to work out how many have been sold.

An IF function as so it identifies when more than 8 cakes for exampl have been sold and the DATEDIFF function to work out the difference in time.

The part I'm stuck on is working out how to calculate this value on a continuous basis as so I can for example have differing durations or "number of days" such as 2.5 days for every 8 sold over a period of time.

Thoughts?