Continuing our in-depth consideration of how IBM® Planning Analytics can ease specific pain points for businesses, we turn our attention towards how one of our construction sector clients dealt with the forecasting of long-term ‘client retention’ debtors and the associated cash collection.
Most construction contracts entitle the client to withhold a small percentage of the value of work done each month and defer payment until the project is complete. At that point, half of the sum retained is invoiced, with the remaining balance invoiced 12 months later.
Businesses attempting to use multiple spreadsheets to calculate and release retention can frequently end up in difficult situations when complex Excel formulae fail to produce the correct information when needed.
Using IBM® Planning Analytics, client retention is calculated by drawing on source values and moving through a trigger, spread and release process. This tiered approach lends itself perfectly to the application as business rules work on a basis of precedence, allowing each stage of the retention model to be calculated before impacting on the next.
Source Calculations
RELEASE FLAG TRIGGER = END DAY FORECAST – CERTIFIED TO DATE
End day forecast (EDF) is how this TM1 user describes the estimated final value of its contracts at any point in time.
Certified to date is the cumulative value of work completed at any point in time.
When the certified to date value is the same as the EDF value, the contract is complete and the first 50% of client retention will become payable.
As soon as EDF less certified to date hits a trigger threshold, the retention model is kicked into action. This is simply down to the release flag trigger roll up falling between certain parameters.
Trigger Calculations
Certain conditions must be met for the trigger calculations to be carried out:
- Trigger threshold has been met, release flag changes to 1
- Withheld by client movement <> 0
- No record of a release in previous months
(WITHHELD BY CLIENT = CERTIFIED VALUE x CLIENT RETENTION [%])
Spread Calculations
The IBM® Planning Analytics model uses a monthly time dimension, meaning 50% triggered in a specific month leads to the remainder being flagged for release exactly 12 months later.
Release Calculations
The beauty of using IBM® Planning Analytics is that it allows for the accumulation of easy-to-understand business rules in a specific order, replacing complex Excel formulae. Where spreadsheets are failing to correctly calculate and release retention, this system can drastically improve operations for a business, accounting for all retention in a single, centralised application which triggers at the correct time and alerts all relevant parties.
Spitfire Analytics have drawn on years of construction industry knowledge and expertise to create an out-of-the-box solution to fulfil the complexities of retention modelling and ease the pain for customers. Contact us today and let us help rid you of all your retention worries.
Simon Bradshaw
I have worked in finance and business systems development since 2001 and am an associate member of the Chartered Institute of Management Accountants. In 2016 I became a founding member of Spitfire Analytics, a consultancy specialising in IBM Planning Analytics. We are committed to building long-term relationships across all industries. I focus on my CPD through CIMA and IBM badges, ensuring I am always abreast of best practice and developments within the industry.
Linkedin Email