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]

You just need to add one more parameter to your function:

Public Function DOSCalc(DD As Range, Beginning_QOH As Double, DaysInPeriod as Long)   '<----------------------------
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) * DaysInPeriod            '<------------------ change from 7 to this
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) * DaysInPeriod       '<------------ change from 7 to this

End If


End Function

 

[Pvaulter13]

ChrisM,

Thank You, however I'm getting # value unfortunately

Months - Jan, Feb, March
DaysInPeriod - 35, 28, 28
DD Range - 2000, 2000, 2000
Beginning_QQH - 5000

=doscalc( DD range, Beg QQH, Days In Period)

The answer should be 35+28+ (28*.5)=77

What could I be doing wrong?
Thanks again-

 

[ChrisM]

I meant for the function to be called for one month at a time, it won't work for all three months at once.

If that's what you want, what's the logic around the "inventory 2.5 months out" bit? Is it always 2.5 months? Shouldn't you use days to be more precise, like 75 days?

 

[Pvaulter13]

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


i = 1
DOSCalcMonth = 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
DOSCalcMonth = (i - 1)
i = DD.Cells.Count
End If
Next i


If EFC = True Then
DOSCalcMonth = "Max"
End If


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

End If


End Function

The above Function I altered a little to have it show a result of going X months out instead of days in weeks, ( I'm trying to get to days of inventory out of variable days in months)

Another example:
Demand = 1000, 2000, 1000
Inventory = 3100
Days in Month = 28, 28, 35



As the formula is written now above it yields 2.10 months of inventory out. So to get days of inventory I would add 28 + 28 + partial month of (.1 * 35)
in this example it would yield a result of 59.5 days of inventory. I'm not sure how to set the month's days inside this formula. For weekly formula I originally posted this was more simple as ever week contained 7 days.

I meant for the function to be called for one month at a time, it won't work for all three months at once.

If that's what you want, what's the logic around the "inventory 2.5 months out" bit? (No) Is it always 2.5 months?(no) Shouldn't you use days to be more precise, like 75 days?(definitely)

 

[ChrisM]

Back to your original post, you said:

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

that will work, then you just need to add up the # days in the DaysInMonth range right?

Dim z as Long
z = Application.WorksheetFunction.Sum(DaysInMonth)

 

[Pvaulter13]

I've tried plugging your line in but not sure what I'm doing wrong, I'm probably not as advanced as you think I am. Cant tell you how many hours I put into this function already.

 

[ChrisM]

I'm confused too, I'm not sure if you have a VBA problem or a business logic/formula problem at this point.

Let's go back to your original post + function. It was working fine with 7 days. I'm assuming all you need to do is change that 7 to whatever, 35, 77, whatever it needs to be.

And to calculate that number, isn't it going to be the sum of whatever new range you plug in to the function (ie DaysInMonth as Range)?

 

[Pvaulter13]

[TABLE="width: 200, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Demand[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Starting Inv[/TD]
[TD]3100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Days in Month[/TD]
[TD]28[/TD]
[TD]28[/TD]
[TD]35[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=doscalcmonth(B2:E2,B3,B4:E4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Getting #Value![/TD]
[TD]answer should become(59.9 days)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With your code below- I'm a newbie to this site and I can't stress how helpful it is, I'm on here constantly and getting really good ideas. But, I'm nowhere close to you and other genius excel gurus on here. Sorry if I'm not being the most clear, but this calculation is common to figure out days of sale to future forecasts based on current inventory. It can be calculate by extending out the months and subtracting from the initial inventory until you hit zero, then adding the full months days, then multiplying the last months partial ratio to the last month's days. Since months have different amt of days I cannot just * 30 and be done with it, especially when you run on fiscal months.

Public Function DOSCalcMonth(DD As Range, Beginning_QOH As Double, DaysInMonth As Range)Dim EFC As Boolean
Dim i As Variant
Dim totDD As Variant
[U][B]Dim z As Long[/B][/U]


i = 1
DOSCalcMonth = 0
totDD = 0
EFC = False
[B][U]z = Application.WorksheetFunction.Sum(DaysInMonth)[/U][/B]


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
    DOSCalcMonth = (i - 1) * [U][B]DaysInMonth[/B][/U]
    i = DD.Cells.Count
  End If
Next i


If EFC = True Then
    DOSCalcMonth = "Max"
End If


If Beginning_QOH > 0 And EFC = False Then
    DOSCalcMonth = DOSCalcMonth + ((Beginning_QOH - totDD) / lastFcValue) * [B][U]DaysInMonth[/U][/B]
    
End If


End Function

I owe you lunch Chris

 

[ChrisM]

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