News

Thursday 3004 / 2020

Revenue Forecasting with Power BI

So, you are logging project and customer data within CRM and the time has come to forecast your company’s estimated revenue over a defined period. Typically, this will involve analysing all projects in the pipeline and beyond, with a view to creating a report showing the value of those projects split by stage, something like this:

Pipeline Projects View

That is a good start, but when do you really expect to receive revenue for these projects? As we know, in the world of construction, it is much more realistic to assume the revenue we receive for completed work will be split periodically, with income received as the project progresses.

To allow for more accurate forecasting, the above value can be broken down into what KMS call “Period Revenue” records.

Period Revenue

A Project can have multiple associated Period Revenue records below it, capturing the date and value of the revenue expected. Users can input monthly, quarterly or yearly periods, breaking the value of the project down to a realistic cash flow forecast.

In the below example, you will see the value of a tendered Project, “Casino/Leisure Complex”, is £22m. A Project-CRM user has created the first month’s forecast revenue record to indicate we anticipate £2m of this figure in the first month of the project, from the already entered Estimated Site Start of 01/08/2020.

Revenue Forecasting 2

So far so good, right? With this project lasting 12 months, the user is only to enter 11 more of these Period Revenue records against this project and we are away. Herein lies the age-old problem, something many of you will have already recognised…

How do you get the users to enter this data?

We certainly do not want users bogged down entering this data manually. What if the project is 5 years long? That is a lot of monthly records to create! This is where the KMS Project-CRM platform comes into its own.

At the top of the above screenshot, you will notice a button, “Initialise Period Revenue”. Using the already entered Project Value, Estimated Site Start and Estimated Site End, clicking this button will automatically create the required Period Revenue records for you, split across the duration of the project lifecycle:

Revenue Forecasting 3

Making use of editable sub-grids within Dynamics 365, users are able edit these values of each record from within the project record itself and adjust the initial breakdown to more accurately reflect likely revenue:

Revenue Forecasting 4

Power BI

Once the relevant data has been entered, reporting has never been easier with the combination of Project-CRM and Power BI. In the below report, the values of the Period Revenue records have been broken down by Sector:

Revenue Forecasting 5

Various slicers have been provided to the end user, meaning a more interactive user experience and deeper insight into the data being presented. For example, if I want to see the expected revenue of all Projects currently at the Tender Received stage, I can use the Stage slicer and the results will update accordingly:

Revenue Forecasting 6

Contact sales@kms-software to find out how Project-CRM help you enhance your handling and use of project information.