Here's a useful Excel macro function for simple working capital models where you want to model changes in cash cycle inputs, such as supplier and customer payment terms. It's low on error checking for inputs :-)
I'm the author and I put this into the public domain. It's Visual Basic for Applications (VBA).
Option Explicit
Public Function agingMonths(startCell As Range, ageMonths As Double) As Double
' this function is designed for working capital spreadsheets
' consider receivables. Collections are based on past sales.
' The function takes a cell reference, and a payment term, in months.
' this function first takes a cell reference pointing to the sales of the current month
' The prior month is assumed to be one cell to the left, and sales of two months ago is assumed to be
' two cells to the left
' If the average payment term is 6 weeks, pass 1.5 (one and a half months) as the second parameter
' it will return the receipts
' error checking should make sure that the startCell is exactly one cell: todo
' startCell should be the current month.
' Prior months are immediately to the left
Dim integerPart As Integer
Dim fractionPart As Double
Dim firstMonthColOffset
integerPart = Application.WorksheetFunction.Floor(ageMonths, 1) ' if the ageMonths is 1, then the first month is one month ago.
' if the ageMonths is 1.5, then the first month's share is 0.5/1.5 = 1/3 and the second month's share is 2/3.
' the second month comes from two months ago.
fractionPart = ageMonths - integerPart
firstMonthColOffset = -integerPart + 1
Dim firstMonthsShare
Dim secondMonthsShare
secondMonthsShare = fractionPart
firstMonthsShare = 1 - secondMonthsShare
'cell index (1,1) is the current cell
'cell index (1,0) is one cell to the left
agingMonths = startCell.Cells(1, firstMonthColOffset).Value * firstMonthsShare + _
startCell.Cells(1, firstMonthColOffset - 1).Value * secondMonthsShare
End Function
| < Prev | Next > |
|---|
