Function: Days of Sale Monthly fix?

[Pvaulter13]

First post so if I do something wrong I apologize in advance.

I have a function (below) to figure out days of sale on a weekly basis. This function works well as it uses 7 as a static base to figure out how many days out it will go considering starting inventory. However I need figure out monthly inputs and to alter the function so that it would incorporate another range of inputs that would tell it how many days are in each month. Using a static 31 days throws off the days considerably.

This is like a break even analysis but using a demand stream and starting inventory instead of investment $ and monthly income from investment.

So
Jan = 35 days
Feb = 28 days
March = 28 days
April = 35 days
etc..

How do I select another range and input as days in the month.

Say inventory covers demand out 2.5 months so..
Jan Demand= 1000 = 35 days
Feb Demand = 1000 = 28 days
March Demand=1000 = 28 days

The answer would 35+28+(.5*28)=77 Days of Sale

I would imagine the new Montly Formula it would start like this

Monthly DOS
Public Function DOSCalc(DD As Range, Beginning_QOH As Double, DaysInMonth as range)


Original working function for weekly DOS:

Public Function DOSCalc(DD As Range, Beginning_QOH As Double)
Dim EFC As Boolean
Dim i As Variant
Dim totDD As Variant


i = 1
DOSCalc = 0
totDD = 0
EFC = False




For i = 1 To DD.Cells.Count
If Not (Beginning_QOH - (totDD + DD.Cells(1, i).Value)) < 0 Then
totDD = totDD + DD.Cells(1, i).Value
If i = DD.Cells.Count Then
EFC = True
End If
Else
lastFcValue = DD.Cells(1, i).Value
DOSCalc = (i - 1) * 7
i = DD.Cells.Count
End If
Next i


If EFC = True Then
DOSCalc = "#N/A"
End If


If Beginning_QOH > 0 And EFC = False Then
DOSCalc = DOSCalc + ((Beginning_QOH - totDD) / lastFcValue) * 7

End If


End Function




I could really use the help, thank you!

 

[ChrisM]

try this:

Public Function DOSCalc(DD As Range, Beginning_QOH As Double, DaysInMonth As Range) As Variant

Dim i As Long
Dim totDD As Double
Dim DayCount As Double

For i = 1 To DD.Cells.Count
    If Not (Beginning_QOH - (totDD + DD.Cells(1, i).Value)) < 0 Then
        totDD = totDD + DD.Cells(1, i).Value
        DayCount = DayCount + DaysInMonth.Cells(1, i).Value
    Else
        DayCount = DayCount + (DaysInMonth.Cells(1, i).Value * ((Beginning_QOH - totDD) / DD.Cells(1, i).Value))
        DOSCalc = DayCount
        Exit Function
    End If
Next i

DOSCalc = "#N/A"

End Function

 

[Pvaulter13]

Yes you're right 59.5- typo

You said the answer should be 59.9, did you mean 59.5?

 

[Pvaulter13]

YOU are AWESOME!!!

That did it- Very happy-flawless, you saved me a lot of time-