This case study looks at a Construction Contractor Moving to Planning Analytics – the issues they were facing, and why Planning Analytics was the answer.
The client was a construction contractor using Excel to try and provide a comprehensive planning solution. Years earlier the client had successfully used the tool for these planning activities; but as the client grew so did the complexity of forecasting resulting in spreadsheets becoming increasingly unwieldy raising a number of issues, including:
- Lack of central control
- Risk of data manipulation
- Absence of traceability
This was compounded by the fact that the best people to input data for individual construction projects, are those working on site, and therefore the software solution needed to be accessible regionally. A further key challenge for this client using spreadsheets, was that Excel struggled to cope with the concept of their clients retaining some of the construction fees on a project. In addition our client was finding that they were spending significant amounts of time inputting all the data for different projects, and no time analysing it – learning lessons from the data, and ultimately being able to use it as a robust planning tool.
We spent time with the client ensuring that we fully understood their data – who was responsible for the data; as well as what their aspirations for the data.
Our solution was to implement Planning Analytics. The benefits that this brought to the client included:
- Providing real time information – the remote budget holders and quantity surveyors on construction sites could see immediately the impact changes to the project would make.
- Modular approach – a key metric within the construction industry is cost variance. This is standard within the Planning Analytics solution, and feeds into the forecasting and budgeting solution. In addition other areas of the business can feed into the planning system; providing a common platform.
- Centralised – the planning analytics tool is a central tool, so any updates are done once, centrally, rather than having to be made across a myriad of spreadsheets regionally.
- Efficiency gains – the planning analytics tool significantly reduces the time to input and consolidate data enabling teams to spend more time understanding and responding to the impact of data.
- Supported by IT – the system resides on company servers and is therefore supported by IT. The system owners can therefore work closely with technical support.
Our design of the Planning Analytics model enabled:
- Data to be sourced from multiple databases including remote construction projects. This provides actualised financial statements up to the current month.
- The various modules holding financial data to be reconciled for the opening position at the highest level; ie. If one module is at regional level, then other modules can be rolled up through project and company into region.
- The company structure for Balance Sheet, Profit and Loss and Cash Flow to be held in an Account List at its most detailed. This detail was fed into other parts of the Planning Analytics system using module specific attributes.
- Automated and accurate planning of Application Sales – each project has a valuation made up of Turnover (received payments), over/under certifications, payment withheld by the customer and retention. As the construction industry releases retention in two equal portions, the planning model uses time dimensions to release the final fifty percent twelve months after the trigger for the first release. In addition pipeline work will forecast sales based only on Turnover, but as the jobs move from targeted work through to more probable, then retention can be planned within the project valuation.
- The Output VAT is calculated for Application Sales by applying the Output VAT Rate to the elements derived in Application Sales, allowing the planning model to forecast the VAT liability accurately.
- Contract Costs to date (Sunk Cost) is brought in as part of the Actualised values and each project will forecast cost and overheads alongside Sales. Construction costs are categorised on a type that determines when they are paid such as within the current month, next month and any period the business agrees. A business reliant on sub-contractors may decide to pay on 30-day terms where other suppliers such as plant and machinery may wait 60 or 90 days. These terms can make a far more accurate schedule for creditors.
- Creditors are split based on cost types and payment terms which will provide analysis for Input VAT and taken through to a VAT Cash Flow module with Output VAT. The release of VAT is controlled using attributes in the model’s time dimensions.
- Projects fall into Current or Pipeline categories for further diverse treatment:
- The Construction Industry holds a wealth of information at project level which provides opportunity for alternative reporting. If for example each project holds a Work Type of Core Business or Small Works, then this allows the business to report on these attributes. The opportunities are endless.
- Using a start and end date against Pipeline work the forecast model shows forecast Sales and Margin spread. This distribution can be manually overridden for forecasting purposes. Pipeline work increases in probability moving from Targeted to Probable which apply to planning valuations. For example, a Targeted Job could be valued at £10m and the Construction industry may apply a standard (but adjustable) probability of 10% graduating to 75% for Probable work. This can adjust Turnover and Margin to make accurate forecasts.
- When a new month brings in an Opening Balance cash may need adjusting to the original plan. Planning Analytics can hold the total so that the Opening Balance and phased cash reduce any difference to zero.
The client has now used Planning Analytics for 18 months and have seen great benefits, including increased certainty in their financial planning. If you would like to learn more about how Planning Analytics can help you contact us here.