View original (French)

Stock Expiration Date

Solved
Yves19 -  
safach Posted messages 293 Status Member -
Hello,

I would like to calculate the expiration date of a stock based on different monthly averages.

Configuration: Windows / Mozilla 11.0

2 answers

  1.
    Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    It's a great idea ...

    --
    Retirement is good! Especially in the Caribbean ... :-)
    Raymond (INSA, AFPA, CF/R)
    0
    1. Yves19
       
      Happy retirement
      0
    2. Yves19
       
      Here is some additional information.

      Monthly, I have a sales number for each of the marketed products. By dividing by the number of days in the month, I get a daily average that varies each civil month.

      I want to calculate the depletion date of each of my products based on the stock I have on a given date.

      My problem arises when my stock allows me to cover several months because I have a different daily average each month.

      To be more specific, I will take an example with today's date:

      Daily sales average in November = 3.5
      Daily sales average in December = 6.2
      Stock on November 15, 2015 = 100

      How can I calculate, with an automated formula, the number of days my stock will last and on what date it will be depleted?

      I hope I have been clearer.

      Best regards.
      0
    3. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > Yves19
       
      Hello

      - With a single formula, it's difficult
      - With several calculation cells, it's possible but cumbersome if there are many products
      - With a custom function to create in VBA, it's possible
      Before establishing it, we need to know if there are any days to exclude from the calculations (weekly closing days, public holidays)

      Looking forward to your clarification on this last point

      Best regards
      Via
      0
    4. Yves19 > via55 Posted messages 14393 Registration date   Status Member Last intervention  
       
      Hello,

      I suspected that the best option would be to go through VBA. Unfortunately, I don't know anything about VBA at all.

      To answer the question, we only have 4 days of closure each year: December 25th, January 1st, Easter Sunday, and, considering I'm in the United States, one day in November for Thanksgiving.

      Best regards.

      Yves
      0
    5. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > Yves19
       
      Good evening

      So here is an example of what is possible with a custom function
      Explanations in the file
      http://www.cjoint.com/c/EKrwOZScJe0

      To be adapted to your needs

      Best regards
      Via
      0
  2. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Did you realize that you are presenting a problem without any data whatsoever?
    There is neither file nor formula; we don't even know how you plan to arrange your information. It feels like a dissertation topic!
    What kind of response were you hoping for?

    --
    Retirement is great! Especially in the Caribbean... :-)
    Raymond (INSA, AFPA, CF/R)
    0