Hi, I would really appreciate some help with the above
Currently, I have the following:
Total Stock used
The (project) Start Date
Average Stock Usage per month is then calculated by using Total Stock Used / Months using DATEDIF (today's date minus start date) in months.
This works fine but, to give an exaggerated example, what if the product is out of stock for 6 months in the year, the Average Stock Usage/month is now effectively halved. BTW, re-order levels are based on Actual Stock Usage per month.
To get a more accurate Average Stock Usage/month, I thought of adding a column labeled ‘Actual Months with Stock’. This column would have a formula/function as follows:
If ‘Total Stock on hand’ is > than zero, add 1 (day) to this column, which will then be divided by 30.42 to give ‘Actual Months with Stock’.
If Total Stock on hand is = to zero, do not add 1 (day) to this column.
It is important that this automatic increment of 1 (day) based on the above condition should occur for every day whether the workbook is left open overnight or not accessed for several days or opened and closed several times a day.
Another big advantage to using this conditional formula/function would be that if a new product were added to the stock list, say 12 months after the start date, my current DATEDIF calculation above would give a totally erroneous Average Stock Usage per month.
Currently, I have the following:
Total Stock used
The (project) Start Date
Average Stock Usage per month is then calculated by using Total Stock Used / Months using DATEDIF (today's date minus start date) in months.
This works fine but, to give an exaggerated example, what if the product is out of stock for 6 months in the year, the Average Stock Usage/month is now effectively halved. BTW, re-order levels are based on Actual Stock Usage per month.
To get a more accurate Average Stock Usage/month, I thought of adding a column labeled ‘Actual Months with Stock’. This column would have a formula/function as follows:
If ‘Total Stock on hand’ is > than zero, add 1 (day) to this column, which will then be divided by 30.42 to give ‘Actual Months with Stock’.
If Total Stock on hand is = to zero, do not add 1 (day) to this column.
It is important that this automatic increment of 1 (day) based on the above condition should occur for every day whether the workbook is left open overnight or not accessed for several days or opened and closed several times a day.
Another big advantage to using this conditional formula/function would be that if a new product were added to the stock list, say 12 months after the start date, my current DATEDIF calculation above would give a totally erroneous Average Stock Usage per month.
