Function: Days of Sale Monthly fix?

Pvaulter13

New Member
Joined
Nov 25, 2013
Messages
10
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!
 
try this:

Code:
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top