Depreciation of Stock


#1

Hi, i am wondering how i would go about calculated my expected depreciation of stock based on sales rate.

For example, if i have 20 units in stock and i expect to sell on average x amount (which changes) per month, how would i go about creating a formula which declines my stock from the current month.

E.g

I am currently in March with 20 stock.
January i expect to sell 3
February i expect to sell 5
March i expect to sell 2

therefore when placed in context of months i should see my estimated stock depreciation appear as
jan = 17, Feb = 12, March = 10… and so on for the year.

I can easily do this using 12 measures but thats ridiculous, i was wondering how you would do this using an iterating function?

Cheers,


#2

Hi, check out one of the videos in the course concerning budgetting. A table (New Table) or (in Excel) could be used to apply the depreciation.
Paul


#3

Yes Paul is correct here, using many of the budgeting type techniques should enable this type of analysis. The main thing you’re looking to solve is data at different granularities.

I want to run an inventory management member only training in the next few months…this would be a good example to use, with some others.

Here’s are some links

See how you go with some of these examples


#4

Cheers Everyone Ill have a look


#5

Sam,
Would be really interesting to see a showcase for a comprehensive Inventory Management model. I studied the model and resources included in the training courses. Restocking costs were an interesting part in it. For a few projects I developed Excel analysis models in this context, also focusing on calculating the Economic Order Quantity. No time yet to reproduce this in Power BI.

Paul


#6

Definitely, really want to start diving into more niche topics and models with some of the member only trainings coming up. More to come soon