…continued from part (1)
C. Dynamics AX Project Setup
To get the EV information posted on ledger accounts, the following project structure has been setup:
First, a T&M project that records all transactions on P&L accounts. (Note that this project will subsequently be used only for invoicing the project customer. No other transactions e.g. for hours, expenses and alike will be recorded on this project).
Second, a fixed-price subproject that holds all hours, expense, etc. transaction but that will not be used for invoicing the customer. This project has been setup in a way that all transactions will (initially) be recorded on balance sheet accounts as illustrated below.
In addition to the setup of the T&M and fixed-price subproject, both projects are setup with the same project financial dimension that is required for analyzing the data not only in the project module but also in GL and/or other Dynamics AX related analysis tools.
D. Dynamics AX Project Accounting Setup
All ledger accounts used in the following are setup in line with the project ledger posting types to make it easier following up the transactions recorded. As an example, for recording project costs, a ledger account no. 540500 with the same name “project cost” has been setup.
While setting up the project ledger accounts, some considerations had to be made in respect to the type of the accounts to be setup in the different sections, such as costs, payroll allocation and alike.
D.1. Consideration 1: Payroll Allocations
When recording hour transactions on a project, Dynamics AX post those costs against (“credits”) the payroll allocation account. As mentioned in the previous section, all hour transactions will be recorded on the fixed price project that is setup with balance sheet posting accounts.
As a result, recording hours on a project will have a temporary but immediate effect on a company’s profitability if a P&L account is used for recording payroll allocations. That is because a WIP (Balance sheet account) is used for the debit transaction while a P&L account – the payroll allocation account – is used for the credit transaction. From an overall company-wide perspective an immediate profit effect resulting from project hour transactions is actually not an issue as the payroll allocation transaction temporarily shifts those costs to a project WIP account that gets reversed when posting the project estimate. Yet, as I do only focus on project related transaction in this post, I decided using a Balance Sheet account for recording payroll allocations to avoid any temporary effect on a company’s profitability.
Please note that this setup is specific to the setup and example that I used and might be different for your company / Dynamics AX environment.
D.2. Consideration 2: WIP & Accrued Sales Value
The second consideration made here for the project accounting setup concerns the WIP & accrued sales value ledger account setup that will be used when running and posting project estimates for the fixed price project.
Against the background of the fixed price project group setup, Dynamics AX will shift the costs for hours, expense and item transactions that are stored on the WIP cost value account to the project cost account when the fixed price estimate process is run and posted. At the same time a second transaction for the WIP sales value and accrued revenue will be generated (highlighted in orange and yellow in the next screenshot).
An important question is how the WIP sales value and accrued revenue accounts are setup. To answer this question, the following options were taken into consideration:
Option 1: The first option taken into consideration is setting up the WIP sales value account as BS account and the accrued revenue account as P&L account. Advantage: Such a setup does have the advantage that project costs and revenues will match within the different project periods allowing a detailed analysis of the project’s progress over its lifetime. Disadvantage: A disadvantage with this approach is that many countries do not allow recognizing such a “fictional” revenue in a company’s P&L statement.
Option 2: The second option taken into consideration is setting up both accounts as P&L accounts in order to avoid recording “unrealized” revenue in a company’s P&L statement. Advantage: Depending on the country your company operates in, you will be able to comply with local GAAP regulations that prohibit the recording of “fictional” (unrealized) revenues. Disadvantage: During the lifetime of the project only costs accumulate and influence a company’s profitability, which will give a wrong picture of the overall profitability of the project and the company.
Option 3: The last option taken into consideration for setting up the WIP sales value account and the accrued revenue account is setting them up as “statistical” accounts, i.e. as accounts with the account type balance sheet – for the WIP account – and profit and loss – for the accrued revenue account – as in the first option but with the difference that those accounts are not included in the company’s external financial statements. Advantage: The use of those “statistical” accounts allows combining the advantages of the first and second option and provides companies with the flexibility of incorporating those accounts in internal analysis for tracking and matching project costs and revenues.
Due to the flexibility and the advantages of the last option, the author decided setting up the accrued revenue and WIP sales value accounts as statistical accounts as illustrated in the next screenshot.
E. Dynamics AX Other Project Related Setups Required
In addition to the setups shown in the previous two sections, the following setup is made.
E.1. Setup WBS For The Fixed-Price Earned Value Project
The first additional setup required for the EV analysis is setting up a work breakdown structure (WBS) for the project that specifies the different stages, hours required and alike. Example:
E.2. Copy WBS Estimates To Forecast & Budget
In order to compare the different earned value measures later on in more detail, the WBS estimates need to be copied to a forecast model that subsequently gets transferred to the project budgeting module via the transfer forecast to ledger functionality. Example:
E.3. Setup Planned Value As On-Account Milestone
The third additional setup requires setting up the planned value as on-account milestone for the fixed price project.
On first sight, this setup might look strange for the one or the other who has worked with the project module before. Yet, please remember that the fixed price project will not be used for invoicing the customer but rather for tracking costs and calculating the EV only. Setting up the planned value for the project in the on-account window as milestone is necessary as the fixed price project estimates refer to the figure setup here in order to calculate the EV.
… to be continued in part (3).