VBA function help -- smooth deal flow numbers across months

[mkaysr@verizon.net]

I have a VBA function that smoothes numbers from 1 quarter to the next. For example, lets say that in quarter 1 the company assumes it wins no deals but in quarter 2 it wins 6 deals. from a planning perspective i want the deals to show up something like 1 in april, 2 in may and 3 in june for a total of 6. i wrote the function as follows:

'*---------------------------------------------------------------------------------------------------
'*-- Function Name: gf_smoothQtr
'*-- Brief Description: Trends monthly numbers based on previous and current quarter inputs
'*-- Last Modififed:
'*-- Written By:
'*-- Inputs:
'*-- Ouput:
'*---------------------------------------------------------------------------------------------------
Public Function gfun_smoothQtr(Last_Quarter_Month3 As Double, _
Last_Quarter As Double, _
Current_Quarter As Double, _
Current_Month As Integer) _
As Double

'*-- Declare local variables
Dim slope As Double
Dim month1 As Double
Dim month2 As Double
Dim month3 As Double
Dim returnVal As Double

'*-- Initialize local variables
slope = ((Current_Quarter / 3) - Last_Quarter_Month3) / 2
month1 = slope * 1 + Last_Quarter_Month3
month2 = slope * 2 + Last_Quarter_Month3
month3 = slope * 3 + Last_Quarter_Month3
returnVal = 0

'*-- Process main CASE statement for value of Current_Month
Select Case Current_Month
Case 1
'*-- First month of quarter
If Current_Quarter = 0 Then
returnVal = 0
ElseIf (month1 > Current_Quarter) Then
returnVal = Current_Quarter
ElseIf (Last_Quarter_Month3 <> 0) Then
returnVal = month1
Else
returnVal = (Current_Quarter / 6) * Current_Month
End If

Case 2
'*-- Second month of quarter
If Current_Quarter <= 0 Then
returnVal = 0
ElseIf (month1 > Current_Quarter) Then
returnVal = 0
ElseIf (month2 <= 0) Then
returnVal = 0
ElseIf (month1 + month2 > Current_Quarter) Then
returnVal = Current_Quarter - month1
ElseIf (Last_Quarter_Month3 <> 0) Then
returnVal = month2
Else
returnVal = (Current_Quarter / 6) * Current_Month
End If

Case 3
'*-- Third month of quarter
If Current_Quarter <= 0 Then
returnVal = 0
ElseIf (month1 > Current_Quarter) Then
returnVal = 0
ElseIf (month1 + month2 > Current_Quarter) Then
returnVal = 0
ElseIf (month3 <= 0) Then
returnVal = 0
ElseIf (month1 + month2 + month3 > Current_Quarter) Then
returnVal = Current_Quarter - (month1 + month2)
ElseIf (Last_Quarter_Month3 <> 0) Then
returnVal = month3
Else
returnVal = (Current_Quarter / 6) * Current_Month
End If

End Select

'*-- Return value to function
gfun_smoothQtr = returnVal

End Function

the problem i am having is that i am totally mind blocked on how to do the same thing across a year. so for instance, the users only want to enter, last years deal totals, this years deal totals and next years deal totals, and they want the function to smooth the deals across all months of the year. any help would be greatly appreciated. thanks.

-matt

 

[acw]

Matt

Is this the type of philosophy that you want to adopt.

A2: 2 (this is the Dec actual result)
B2: 20 (the next year's total sales)
A3-L3: 1-12 (the months)
A4: =$A$2+($B$2-$A$2*12)*(A3)/78
copy from A4 to B4:L4.

Effectively this use the December actuals as being the minimum amount for each month. It works out the difference between this minimum for each of the 12 months and the total annual amount, and allocates this difference linearly each of the months. In the example give, it will result in a decline as the annual amount is less than the total of the minimum amount for the 12 months. However, change the annual amount to 40 and it will be an increase each month.


Tony