Modelling Milestone Invoicing

The scenario

In some businesses, revenue from a single transaction is recognised over a series of months. This type of arrangement is common across a range of industries including engineering, construction and consulting.

Where the billing follows a predictable split by month, we can use a combination of Driver elements and Element Formulas to model the scenario in Castaway. 

For example:

• Month 0: Order received
• Month 1: Invoice 40% of order value
• Month 2: Invoice 40%
• Month 3: Invoice 15%
• Month 9: Invoice the remaining 5%

The modelling logic

In the example above, an order of 100k in say July, would be invoiced:

• 40k in August
• 40k in September
• 15k in October
•   5k in April the following year

Turning this logic around, the total amount we would invoice in April is:

• 5% of sales orders from July the previous year (ie 9 months before the current month)
• 15% of sales orders in January (3 months before)
• 40% of sales orders in February (2 months before)
• 40% of sales orders in March (1 month before)

In Castaway element formulas, we can use the Period Offset function to model this scenario.

The solution

In the attached file, we followed these steps to model the scenario:

1. Create a Driver element for 'Sales Orders' and add 100,000 in July
2. Create a Sales element for 'Sales Revenue' and set the Revenue Method to 'Add Formula'
3. Click the ellipse box (ie the 3-dot box) at Formula Editor to bring up the element formula definition screen
4. The formula we need is: (Sales Orders[t-9] x 5%) + (Sales Orders[t-3] x 15%) + (Sales Orders[t-2] x 40%) + (Sales Orders[t-1] x 40%)
5. Add the formula in the following order:
   1. Single-click the '(' button
   2. Double-click the 'Sales Orders' driver
   3. Change the Period Offset value to '-9' and click 'Apply'
   4. Single-click 'x'
   5. Type 0.05 in the Numeric Box and click Add
   6. Single-click the ')' button
   7. Single-click '+'
   8. (
   9. Sales Orders
   10. Change the Period Offset value to '-3' and click 'Apply'
   11. x
   12. .15
   13. )
   14. +
   15. (
   16. Sales Orders
   17. Change the Period Offset value to '-2' and click 'Apply'
   18. x
   19. .40
   20. )
   21. +
   22. (
   23. Sales Orders
   24. Change the Period Offset value to '-1' and click 'Apply'
   25. x
   26. .40
   27. )
6. Click OK to return to the element editor
7. Click Reporting to review your reports

• Milestone Invoicing.pxif (10 KB)