Thursday, October 29, 2009

VBA to Create Sample Budget Data

Yesterday I was working with one of our partners to create a proof of concept using Dodeca and one of the issues that came up was creating some sample budget data to go with the actual numbers obtained from the customer. The partner wanted the budget numbers to be within the range of actual +10%/-15%. Not a problem, so I dug deep into my Excel experience and came up with the following VBA algorithm in about 10 minutes.. I *think* it did what I wanted so I thought I would share it:

Option Explicit

Private Const MIN_VAR_PERCENT As Double = -0.15
Private Const MAX_VAR_PERCENT As Double = 0.1

Sub AdjustTargets()
Dim oRange As Range
Dim oCell As Range
Dim vNumber As Variant
Dim iPlaces As Integer

''' use the selected cells
Set oRange = Selection

''' loop the selected cells
For Each oCell In oRange
''' if the cell contains a number
If IsNumeric(oCell.Formula) Then
''' grab the number
vNumber = oCell.Formula

''' get the number of decimal places
If InStr(CStr(vNumber), ".") > 0 Then
iPlaces = Len(CStr(vNumber)) - InStr(CStr(vNumber), ".")
iPlaces = 0
End If

''' compute a random value between the bounds and
oCell.Formula = Round((vNumber * (1 + MIN_VAR_PERCENT)) + (Rnd() * (MAX_VAR_PERCENT - MIN_VAR_PERCENT)) * vNumber, iPlaces)
End If
End Sub
The HTML rendering on the blog is sometimes not very good, so here is a screenshot of the VBA window (click on the picture to see a full sized version).

To use the VBA, select a range in an Excel workbook and run the macro.


Anonymous said...

Is there a @random function in Essbase? Similar to your case, I would like to set some Budget numbers by randomizing Actuals by some random factor of 80%-110%.

Tim Tow said...

I am not a calc script guy, but I don't think there is a random function in the calc script language. You could do a CDF to create one. In fact, if I did that I would probably do the whole calculation in the Java CDF.