# VBA function help -- smooth deal flow numbers across months

#### mkaysr@verizon.net

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

Replies
4
Views
206
Replies
1
Views
234
Replies
5
Views
142
Replies
1
Views
502
Replies
3
Views
1K

1,196,042
Messages
6,013,049
Members
441,746
Latest member
ArtemisAlex

### 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.

### Which adblocker are you using?

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

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