Revenue recognition with project budget shadow forecasts
Recently I came across this: Idea: Automaticaly update the total forecast when budget is revised and I thought it is time to share with everybody my sacred knowledge in Dynamics 365 for Finance: the use of project budgets in the fixed fee project planning and revenue recognition. I encourage all my customers to consider the project budget as the only UI to maintain and keep the history of forecasts.
It seems that the regular project review is the best practice in the construction industry and in the complex engineering. Once a month the project manager assesses the project progress, extracts the current actual cost, evaluates the cost “burn rate” and produces an Estimate at Completion (EAC), which is the forecasted cost of the project at its end. The new EAC is compared with the original (baseline) project forecast. The baseline (or the last adjusted) forecast is copied, updated and becomes the most recent forecast of the project:
The ratio of the actual costs (Inception to Date, ITD) to the Project forecast is the new estimated Percentage of Completion (POC) of the project:
The Estimated to Complete (ETC) is the forecasted cost still to occur:
For a “Fixed fee” project, the contract amount is fixed and the current estimated revenue in line with IFRS 15 can be calculated as
In Dynamics 365 for Finance, the process of the PoC estimation and revenue recognition is called an “Estimation” (About estimates | Microsoft Docs), the forecast is called … well, a Forecast, and the actual cost is recorded by the Project posted transactions. The percentage of completion revenue recognition method is called Completed percentage in the Project group:
The problem with the D365 forecasts is that
- There are three of them: Hour forecasts, Expense forecasts, Item forecasts. There is a view All forecasts, but it is not editable.
- There is no facility to aggregate forecasts, e.g. Item IDs → Item Project categories
- There is no adequate tracking of forecast revisions other than with the static forecast models (‘Jan’, ‘Feb’ and so on).
Basically, the project forecast UI in Dynamics 365 for Finance is unusable, but there is a trick:
In one form Project budget one can manage all 3 types of costs: Hours, Expenses (procurement category-based costs) and Items (costs for stocked products). To start using the project budgets, activate Use budget control in the project master (and project management parameters) and make sure that the Project budget number sequence is set. Independent budgeting… is advised. There must be an [automatic] budget approval and budget revision workflow configured, too.
The planned hours may be quickly Imported into the project budget from the Work breakdown structure with an aggregation by the project category (i.e. reduced by the WBS Task IDs).
One useful feature is missing: an ability to import Item requirements and compress them by the Category ID. Anyway, a project budget by category may be entered in 2 minutes, while maintaining the project forecasts for the same in 3 different forms may take 20 minutes.
There is an instant totals calculation. There is a (mandatory) approval workflow, which is a common process in large engineering companies. There is a strong history of Revisions and a workflow for their approvals.
Here comes the surprise: on an approval in the Workflow, the project budget [revision] creates 2 shadow forecasts (“original” and “remaining”, here: PRJ-O, PRJ-R) you can use in the PoC calculation or elsewhere.
The “original” forecast is what was called Current forecast or EAC above: on any project budget [revision] the Original forecast is updated with the budget values.
The “remaining” forecast is what was called ETC. I do not consider it useful, because the actual costs ITD to be subtracted from the “Original forecast” are taken at the moment of the budget revision approval. However, if the burning rate lays within the predicted limits, there is no practical need to make or submit a new project budget revision, and the Remaining forecast becomes outdated. There is an ability to update the Remaining forecast on-line by choosing the forecast model (here: PRJ-R) in the Project management and accounting parameters, but I do not recommend that: this online update on every project transaction seems to be buggy and brings errors and warnings across the whole system.
To create the PRJ-O and PRJ-R models, use the Project management and accounting > Setup > Forecasts > Forecast models. Choose the Budget type = Original budget or Remaining budget, respectively, otherwise you won’t be able to pick the models in the Project budget form.
However, if you do, so, the model becomes automatically Stopped. This will prevent it from being mis-used in the revenue recognition later. You should use the trick of exporting and updating them in Excel: Overwrite a read-only configuration in D365FO.
To display the shadow forecasts once the budget [revision] is approved in the workflow, remove the default filter Budget type = None in the forecast form(s):
The Cost price = Total budget = Original budget + Approved revisions. The shadow forecast models only work properly with amounts but not quantities: the quantity in the forecast is always 1. One can enter a budget line, and click Details then break the hours into multiple lines for different resources, but they are rolled up and applied as 1 hour for the total Budget line amount. There are some bugs with regards to hour indirect costs too.
Revenue recognition (estimation)
To use the ‘shadow forecasts’ in the Project Estimation, configure an appropriate Project management and accounting > Setup > Estimates > Cost template and Cost lines:
The Cost to complete method must be Total forecast – actual, because we will be hijacking the “Original forecast” PRJ-O. Tick Percentage of completion where appropriate. In the construction industry the Item line may be de-selected. As the great Rav Lal once explained to me, “Having all the materials delivered to the construction site doesn’t mean the house has been erected”.
If you don’t want your users asking for the right forecast model every time, pre-populate the revenue recognition periods (Project management and accounting > Setup > Timesheets > Timesheet period types, button Periods) with the Model = PRJ-O.
The proper estimation mode will be then From cost template – From cost template – PRJ-O, unless for the last estimation at the end of the project where Cost to complete method = Set cost to complete to zero.
The only area where the project budget falls short is the data migration. As of today, there are no entities for it. For the total project contract, you can use my voodoo practice Electronic reporting for data migration
For the forecasts, there are 3 entities:
- Project hour forecasts
- Project expense forecasts
- Project item forecasts
If the number of projects is low, import the 3 parts of the forecast for each project, scroll the projects one by one, click Project budget→ Import from Forecast → OK → Workflow → Submit. An experienced PC gamer with nimble fingers or a trained Tinder millennial may process up to 200 project budgets per hour. A larger volume may require a X++ job.