Empower Smarter Decisions: Building a Financial Planning & Analysis System For Success
- sudhiranand1
- May 14, 2024
- 4 min read
Why was this assignment undertaken?
Construction costs were not reported in a meaningful and detailed manner for the stakeholders to understand the costs to date and forecast to completion. There was a requirement to report costs on a more granular level.
External stakeholders views of estimate to completion were required to assess the level of risk to meeting the final estimated cost.
Min requirements were:
Compare costs for the current period with business plan and latest forecast,
The results were to be presented effectively with charts, graphs,
Commentary on variances, significant change in historic trends and future trends, and
This process required approval by Chief Financial Officer (CFO) before implementation,
This post describes our solution to building a Financial Planning & Analysis System
Case Study
This post is a case study of implementing a financial planning, analysis and reporting system for a private equity owned and regulated construction company. An investigation was made into data available from applications used to manage the construction project. Once the sources of data were identified, they were to be harnessed to produce a financial analysis and reporting system to report progress of construction each month.
Systems in Use and Data Available
Two main systems were in use:
Cost control system to record construction cost, and
Financial System used to record historical transactions including payments made to Main Contractors.
These two systems were independent, and data was not reconciled between them.
After detailed analysis, suitable data was identified in the Cost Control System. This provided enough detail for planning, analysis and reporting.
However, the difficulties that arose were:
The format of the data was not suitable for analysis,
The Area, Site, Activity and Analysis codes were rolled into one field. Under this arrangement it was not possible to summarise data by any of these components,
The codes themselves were numerical or short abbreviations which would not be interpreted by anyone by looking at this code,
The data type contained information for budget, forecast and others. For required analysis only the Forecast was required.
The periods to the right were in the Year-Period format. For the purposes of this report, a period started in April of each year and ended in March the following year. After December, the year did not change. For example, December 2017 was shown as 2017-09 and March 2018 as 2017-12.
The data as it stands is not very useful.
A sample of the data is shown below:
Data Research – Cost Control System
The Hierarchy Code had to be split into four individual fields to summarise data.
The four codes themselves do not convey any useful information to end user. There were master files for each of the codes giving much more meaningful descriptions. A solution had to link the codes to the master files to provide better information.
An example of this cross referencing is as follows:
The data on the right conveys better meaning than the one on the left. In effect, a solution had to be found to break up the Hierarchy Code into four columns and generate another four columns with the descriptions after the cross referencing.
The next issue to overcome is the periods are in individual columns. The data has to be transformed to show the year and period in one column on multiple rows. The data has to be shown as follows after allowing for all transformations:
Each row in the original table has will result in multiple rows in the new format. The actual data set in question has just under two thousand rows which resulted in over a million rows of data. On examination, there were a lot of rows with zero values which could be eliminated.
This whole process would be cumbersome if carried out manually. An automated solution to run through this process each month was designed, approved and implemented. The process is summarised below.
Conclusion - Successfully Building A Financial Planning & Analysis System
This methodology proved to be highly effective. The resulting solution met or exceeded key stakeholders expectations.
The data was graphically shown and rolled into a dynamic and engaging presentation, further illustrating our expertise to present and communicate essential information to key stakeholders.
This solution was accepted by the CFO.
Is your business drowning in a sea of inefficient and frustrating processes?
At AnalytixPlanning, we throw you a life raft of streamlined solutions. Our team is a crack force of technical wizards, programming ninjas, and design gurus. Together, we don't just identify process roadblocks, we demolish them and build sleek automation highways in their place.
First, we embark on a deep-sea exploration of your current workflows, unearthing hidden inefficiencies. Leveraging automation like a turbocharger, we develop solutions that free up your team's time and ensure seamless data management. But that's not all! Our user-friendly design ensures smooth sailing for your team, minimizing training needs.
We're not just problem solvers; we're workflow optimizers. Imagine a future where manual tasks are a distant memory, replaced by automated efficiency that delivers accurate results and lets your team focus on higher-value activities. If you're ready to ditch the anchor of inefficiency and streamline your path to success, contact us today. Let's transform frustration into smooth sailing, together!