Forum Discussion
Average days in Inventory. FIFO
Hello,
I have a table of a all items in inventory each with their purchase dates, items sales, purchase cost and inventory stock at hand. I am trying to calculate the average days in inventory of these items using FIFO. In other words I need the formula to work something like this:
Average days in inventory=((no of items purchased in purchase N - sales item FIFO) * number of days in inventory)/inventory at hand
Thanks
7 Replies
- Ashish_Mathur
Super User
Hi,
Share a dataset and show the expected result.
v-juanli-msftCommunity Support
Hi lisamariegp7
Could you show some data example?
Please use some example to show "purchase N, sales item FIFO, inventory at hand" so i can make a test.
Best Regards
Maggie
- amitchandak
lisamariegp7 ,Can you share sample data and sample output.
I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-ye5L
Hi amitchandak , v-juanli-msft , Ashish_Mathur
Here's a link with a sample dataset of what I'm trying to do.
https://drive.google.com/open?id=1yFP0WlZkJE2USgeRYWuHevMGZ7q0sJn4maEY4c2OYC4
Column E has the Average days of Inventory formula I'm trying to create.
Thanks,
- v-juanli-msft
Hi lisamariegp7
As tested, the cell E3 should be 1 based on your formula.
Also every cells in Column E has different formulas.
I don't understand the calculation rule.
Is your formula like something here?
Hello v-juanli-msft
Every cell has a different formula because I want to determine the age of the inventory taking into account the order in which the product(s) entered. Is the same thing as if I buy a product in different dates and the selling price of the product is different. On sheet2 of the following link I've created an example doing what I want to do but instead of using dates I used price. In column F you can see the formulas I used. Just like the previous example there's in not a constant formula because it depends on the date the product was bought.
https://drive.google.com/open?id=1yFP0WlZkJE2USgeRYWuHevMGZ7q0sJn4maEY4c2OYC4
Thanks,
