Using Microsoft Dynamics AX and SAGlobal ProjAX (now known as Projects+) for Services Revenue Forecasting
The services revenue forecasting process is an important financial management practice for all professional services firms.
These revenue forecasts help provide a critical input to hiring and manpower decisions and other investments that your firm may be considering. Unfortunately, building accurate forecasts is something that most firms have found to be a constant struggle.
Our experience in implementing the Microsoft Dynamics AX Service Industries ERP solution for a wide variety of professional services firms, has allowed us to gain valuable insight into how a number of different organizations have driven their forecasting process. This insight has allowed us to derive some best practices to be used as a starting point for discussing the services revenue forecasting process with our clients. This blog article will provide a high level overview of some of these concepts.
First, let’s define the typical sources of forecast data that need to be captured in Dynamics AX in order to generate your forecast report.
- Project Revenue Forecasts – The Project Management & Accounting module in Dynamics AX supports the creation of revenue forecasts for projects in a couple of ways depending on the type of project it is (T&M vs. Fixed Price).
- T&M – To forecast revenue for Time & Materials projects, SAGlobal leverages its ProjAX (now known as Projects+) role-based resourcing tool for building out the required resource roles for the project and determining how that work will be time-phased over the life of the project. By allocating the hours for each role into weekly buckets, we can model how the revenue should generally flow for T&M projects. And then using either unassigned roles or specific resources that have been assigned to a role, we can utilize the embedded bill rate tables to calculate the revenue in each week based on the time-phased distribution of hours described above. A screenshot of this feature is shown below.
ProjAX (now known as Projects+) Role-based Resource Forecast in Microsoft Dynamics AX
- Fixed Fee – While we’ve had some organizations say that the time-phased T&M resource forecast described above is also sufficient for Fixed Fee projects because “it’s close enough” to how their revenue will fall, we have other clients that prefer to use more of a true Fixed Fee project approach. To forecast revenue for Fixed Fee projects, there are a couple of options that can drive the timing of this revenue for the forecast. The more commonly used feature is the On Account feature, which allows you to set up a schedule for how the contract value will be billed on Fixed Fee projects. A screenshot of this feature is shown below as well.
On Account Schedule in Microsoft Dynamics AX
- Sales Pipeline Revenue – Some of our clients also want to include forecasted revenue from their project opportunities in their revenue forecasts to account for future projects that may fall into the forecast, but which are not official projects yet. This is typically done by looking at opportunities of a certain confidence level or better, and discounting the revenue based on the probability that the deal will close. This allows us to include not only firm project work in our revenue forecast, but potential future project work from our sales pipeline as well.
Now that we’re capturing the necessary data to drive the revenue forecast, we have several options for how to display and consume this forecast data. These options include building a Management Reporter financial report, writing a custom SSRS report, or building an analysis in Excel using the Atlas add-in. We at SAGlobal decided to use an Atlas Excel report for this exercise since Excel is a great tool for organizing and displaying time-phased data in column formats and for charting and graphing this data.
Using Atlas, we were able to extract the data from Dynamics AX that is described above into Excel, and then perform a few calculations to display the data the way that we wanted to see it and to manipulate the data for things like discounting the potential value of a sales opportunity based on the probability of close. We then used the native charting features in Excel to visually display our trended revenue forecast and variance within the spreadsheet. Here’s a look at what the output looked like in our example.
Sample Forecast Report in Atlas (Excel)
About the Author
Andy Yeomans is Director of Business Development for SAGlobal, where his role is to help project-driven organizations evaluate and select ERP software systems to better manage their finances, projects, clients, and resources. Andy was instrumental in developing the organization's Microsoft Dynamics AX industry solutions and practice area for the AE & Environmental industry. Andy has over 17 years of ERP experience and has helped dozens of AE & Environmental firms evaluate, select, and implement ERP software systems for their business.