Question on Maturing inventory

D0nmac
D0nmac
in Modeling

Hi Everyone,

 

i’m hoping you might be able to help me, I’m looking to build a model which takes an inventory with a start date and then shows when it comes of age e.g. beef that needs hung for 28 days, or wine that needs stored for a couple of years.   

 

I’d know the current demand but I’m not sure how Or if I can get the supply figure for something that needs to get aged for 24 months.

 

Any ideas or suggestions would be appreciated.

 

thanks in advance D0nmac 🙂

Tagged:

Best Answer

  • ArunManickam
    ArunManickam
    Answer ✓

    Hello @D0nmac ,

     

    I would suggest you to maintain the inventory data by batch.

    Create lists called Batch and Product. Batch rolls into Product.

    Create a module @ Product level, create a line item called Maturity days.

    Create a module @ Batch level -- It will have the line item Start date, Mature date, On hand, Matured On hand.

    Each batch will have a Start date and On Hand.

    Mature date can be calculated as Start date + Maturity days.

    Matured On hand = if Mature date <= <Current date> then On Hand else 0

     

    You can use Matured On hand, roll up to Product level and match the demand.

     

    Thanks

    Arun

     

    (Assuming all the inventory is available in one location, otherwise you need to add Location dimension in the above in the respective places)

Answers

  • D0nmac
    D0nmac

    @ArunManickam  Thanks a lot for the suggestion, I was thinking something along the same lines I just didn't know if I would have needed a special calculation to work out when the item came of age.   would the same calculation work if the time frame was months instead of Days e.g. 12, 24,36... 

     

    Thanks D0nmac

  • ArunManickam
    ArunManickam

    Yes, Months also can be used. Need to adjust the formula accordingly.

     

    Thanks

    Arun

  • D0nmac
    D0nmac
    That's great thanks for your help. I'll give it a try today 🙂

Categories