Dynamics 365: Migrating fixed price projects and WIP balances

To import a fixed-price project in Dynamics 365 for Finance, you need up to 14 different entities and more, and 18-20 steps, sometimes manual and time consuming.

Before importing, make sure that the employees have been declared as bookable resources in the company. If not, use the entities “Project scheduling resource” (ResResourceDataEntity) and “Project scheduling resource details” (ResResourceSetupDataEntity). See also AX7 Table Browser and Class Runner | Microsoft Learn for more information on the ResResourceIdentifier table.

Project contract and funding sources

Next, import the project master table in the following order:

Project contract (ProjContractEntity)
Project funding source (ProjFundingSourceEntity, the link between the customer and the project contract)
Project funding rule (ProjFundingRuleEntity, the link between the [multiple] customers and sources, and the project; typically, there is just one record with 100% allocation of the funds of just one customer to the whole project = project contract)
Projects (ProjectEntity, parent projects first; not a V2 version, that one does not work!)

Next, deal with the open (unbilled) on-account transactions (a.k.a. instalments in CRM Project operations, a.k.a. billing milestones). There is no entity to export these, and no entity to import. The easiest is to extract the billing milestones to Excel, then open the form Project management and accounting > Enquiries and reports > Transactions inquiries > On-account, tailor the Excel sheet to match exactly the order of columns in the grid, then paste all milestones and hit Save, see also How to copy data from Excel into D365. The financial dimensions and VAT groups are inherited from the project master.

By the way, the billed milestones i.e. the on-account transactions in the Fully invoiced status are imported with the Beginning balance journal, see below.

Work breakdown structures

Project Work Breakdown Structures (WBS) are often used to slice the project scope into phases and tasks. Dynamics is currently only able to import draft WBS’ through the entities

Project work breakdown structure – Draft (ProjProjectWBSDraftEntity, these are the projects tasks) and
Project work breakdown structure – Draft estimates” (ProjWBSActivityEstimatesEntity, number of hours per task).

To export a WBS, the current WBS must be turned into a draft with the Edit button first; this is a manual work with 3 mouse clicks per project: select a project, open its WBS, click Edit.

Conversely, upon the migration of the draft WBS’es these must be activated manually with 4 clicks: select a project, open the WBS, click Publish, confirm with OK.

Project cost budgets

Importing the Project budget is a somewhat similar job, with even more manual interventions. There is no entity for the project budget, but project forecasts may be used as a primer. First, prepare a set of 3 forecast files, all with the same forecast model ModelID:

Project hour forecasts (ProjForecastEmplEntity by category, the employee ID is negligible)
Project expense forecasts (ProjForecastCostEntity, by category)
Project item forecasts (ProjForecastSalesEntity by category; the item number may be omitted, populate the item allocation key ItemAllocateID instead).

Import the forecasts, then initiate the budgets project by project, this is a manual time-consuming operation with 7 clicks per project: select a project, click Project budget, choose Import, confirm with OK, then send the resulting budget into an automated Workflow (the project budget workflow must exist), close the form, repeat.

Historical project transactions

Finally, to migrate started, already running fixed-fee projects, you must import all historical project transactions. They are used to either build up the WIP balance of the project, or for the revenue recognition, or both. The historical project transactions are imported through the Beginning balances journals:

Project journal lines for on account beginning balances (ProjBeginningBalanceOnAccEntity) for the billed on-account milestones. The grand total of billed and unbilled (Chargeable) milestones gives the total contract value of the fixed-price project.
Project journal lines for cost/sale beginning balances (ProjBeginningBalanceCostSaleEntity) of 3 kinds: hour, expense, item [consumption].

The 3rd entity of this class, called “Project journal lines for fee beginning balances” (ProjBeginningBalanceFeeEntity) is rarely used, because the accrued revenue may be caught up differently.

The target project must be in the In progress stage already. Transactions posted through the beginning balances journal do not affect the general ledger but stay in the project sub-ledger, hence the voucher number in the line is not important. The impact on the P&L and/or balance (for project posted into WIP) only becomes visible after a proper GL balance migration.

To export project transactions of all kinds the entity “Project posted transactions“ (ProjPostTransViewEntity) may be used. Please keep in mind it is a SQL UNION VIEW, and it may skip some transactions under unclear circumstances. To export data from a legacy Dynamics system, consider using the usual Project management and accounting > Enquiries and reports > Transactions inquiries instead.

The minimal set of data for the beginning balances is listed below. Sales prices and amounts are irrelevant in fixed-price projects, and may therefore be omitted:

TRANSACTIONTYPE	Cost	Hour	Item	Remark
JOURNALNAME	ProjBeg	ProjBeg	ProjBeg
JOURNALID	149-000020	149-000020	149-000020	A new number makes a new journal head
LINENUMBER	1	2	3	increment the line number
CATEGORYID	Hotel	PrjMan	ItemReq
COSTPRICE	123,45	345,67	567,89
ITEMID			R0028055	Not really mandatory for phased out products
DESCRIPTION	Trv.exp 238	Kick-off 11th	Little pwny	Not mandatory
PROJECTDATE	06.11.2024	06.11.2024	06.11.2024
PROJECTID	ABC000011	ABC000011	ABC000011
RESOURCE		000_542100		Personnel number, not mandatory
QUANTITY	1	8	1	For items, only the cost price may be imported; when multiplied with the Quantity, a rounding error may occur. Set the quantity to "1" for items and use the Cost amount for the Price
COSTSTATUS	Operations	Operations	Operations	"Balance" for projects with WIP, "Operations" for projects with an immediate P&L recognition

Once the beginning balances have been imported and posted, the recognised revenue may be brought up to date. The revenue recognition must be performed in the “Update with beginning balance amounts“ mode: the posting of such a revenue calculation record does not update the general ledger. If manually amended percentages of completion are used, let the revenue recognition run in this mode, then set the Completion method to Manual, enter the voluntary Percentage complete = XX%, hit Calculate, then Post.

Sales and purchase orders

At last, use the entity “Project item requirements” (ProjSalesItemRequirementEntity) to import open (not delivered) Item requirements (also known as a “project BOM”). Be aware that you cannot control the Sales order number with this entity, D365 simply draws one Sales ID per project.

The entities

Purchase order headers V2 (PurchPurchaseOrderHeaderV2Entity) and
Purchase order lines V2 (PurchPurchaseOrderLineV2Entity)

are suitable for the project PO import. Keep in mind that open project POs for tangible goods (stocked products) are getting imported as standalone project purchase orders, “self-destructing” upon receipt of the goods; D365 prepares a neutralizing negative inventory transaction. However, if the purchase order was derived from an item requirement, this is going to leave an uncovered demand for items, since a standalone project PO remains “stock neutral”, so to say. When importing SO–backed purchase orders, remove the Project ID from the PO header, then apply a marking to the SO item requirements line (manually). This will allow for PO line consumption through the item requirements.

Good luck!

Project Management and Accounting blog series

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.

Introduction

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:

Current Project Forecast := EAC

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:

POC = Actual_cost_ITD / EAC = Actual_cost_ITD / Current_Forecast

The Estimated to Complete (ETC) is the forecasted cost still to occur:

ETC = Current_Forecast – Actual_cost_ITD

For a “Fixed fee” project, the contract amount is fixed and the current estimated revenue in line with IFRS 15 can be calculated as

Revenue to Date = Contract amount * POC%

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:

Project budgets

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.

Data migration

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.

Project Management and Accounting blog series

Intercompany project invoicing in practice: Process

With the Intercompany project invoicing in practice: Setup in place, let’s proceed with the business process step by step.

Timesheet entry

Go to Project management and accounting > Timesheets > My timesheets and create a new one. People often experience issues here for the first time. These may be traced to 2 possible causes: a wrong/missing user-employee relation and/or missing timesheet periods or timesheet weeks records (every employee has a set of his/her own: refer to Part 1, Show/Update timesheet periods).

Pick the borrowing Legal entity, and the list of the eligible projects becomes available. All the constraints in the borrowing entity are respected: the project stage, a possible worker/project validation, billing Line properties etc. Unfortunately, this goes as far as to the extraction of the final sales price to the end customer and the ultimate customer-facing VAT group from the borrowing LE side. These parameters are ‘frozen’ once the timesheet is posted in the lending entity, but the game is not yet over: one can fix them after.

Perhaps the most critical flaw in the design of intercompany timesheets is the derivation of the financial dimensions: the project financial dimensions do not propagate the lending entity at all, despite the common chart of accounts and accounting structures. Instead, the employee dimensions are taken, and nothing else. This is going to hit us hard at the moment of the IC free text invoice booking, because the revenue account structures very often require a cost/profit centre, a line of business dimension and so forth.
The workaround may be to set the P&L dimensions in every employee card (which is awkward and may not always fit) or to extend the /Tables/TSTimesheetLine/Methods/initFromProjTable method.

The issue is aggravated by the fact, that an entity-backed Project ID dimension cannot span multiple legal entities, while the project itself obviously can! A common ‘solution’ is a custom or a manual Project ID financial dimension (business case: booking an asset depreciation against a project, booking service revenues against a CAPEX project and so on).

Either way, as soon as the first hours have been entered, they already appear in the Manage / Pending transactions view with the final sales price to the customer:

Submit the timesheet to the Workflow in due time, get it approved (learn the Reassign button in the Workflow history). The timesheets may either be configured for the automatic posting or they may be posted manually in the Project management and accounting > Timesheets > Unposted timesheets list. Beware that no Project transactions (ProjEmplTrans) get produced, the transactions are still “pending”.

Intercompany customer invoice

Run the Project management and accounting > Periodic > Project invoices > Create intercompany customer invoices function to collect desired types of transactions and bill them at once to the borrowing IC partner.
The result appears on the Project management and accounting > Project invoices > Intercompany customer invoice list:

In essence, it is a usual free text invoice with a few project extras. Every timesheet day results in one IC customer invoice line (!) and the printout may be quite long. To preview the invoice, use the common Sales ledger (en-us: Accounts receivable) > Invoices > All free text invoices form. Once the invoice is created, the pending project transactions in the borrowing LE disappear for a short moment of time.

Should anything be wrong, the invoice may be simply deleted and re-created at will. You cannot correct the source dimensions in the timesheet anymore, but the dimensions or the accounting distribution in the free text invoice may be amended… line by line.
Post the invoice. This is the moment when the system makes a copy of it in the borrowing legal entity as a Supplier pending invoice; the transactions reappear on the pending project transactions list.

Pending supplier invoice

In the borrowing legal entity, locate the Purchase ledger (en-us: Accounts payable) > Invoices > Pending supplier invoices. The IC invoice must be there with the free text invoice number from the lending entity. The lending LE financial dimensions have been overwritten with the dimensions of the project in the borrowing LE.

This is the fist chance to fix timesheet errors by amending the Billable/Non-billable Line property, the final Sales price, etc. The quantities (hours) are better tended by adjusting the resulting project transactions, once the Supplier invoice is posted.
Namely, there is one key constraint to realise: there is no facility to cancel the original IC customer invoice in the lending entity, therefore no legal options to fix it in terms of the cost amount (i.e. the transfer price), quantities etc. in the borrowing entity either. Game over. The lending entity may only issue an all-manual free text invoice for the disputed difference.

Post the invoice. The invoice lines finally appear as Posted project transactions in the project, where the sales price comes from the borrowing LE and the cost price = transfer price originates in the lending LE.

Note one weird thing about the transactions: the Origin is the Supplier invoice of the expense project transaction type, yet the categories are the hour categories. The project date is the original date on the timesheet (there used to be a bug around that, but it was fixed).

This concludes the specific intercompany part of the process. In the case of a Fixed fee project, it all ends with the cost, WIP and the revenue recognition at the end of the month. In the case of a Time and material project, the hours and/or expenses may be billed regularly:
Project proposal → [Invoice proposal workflow] → Project invoice, either detailed or condensed.