• Home
  • About
  • Contact
  • German

Dynamics 365FO/AX Finance & Controlling

Dynamics 365FO/AX Finance & Controlling

Tag Archives: Project module

Earned Value Management (5)

25 Friday Mar 2016

Posted by Ludwig Reinhard in Project

≈ Comments Off on Earned Value Management (5)

Tags

Earned Value Analysis, Project controlling, Project module

… continued from part (4)

The following screenshots summarize – for reasons of completeness – the project transactions and postings generated for the example used.
EN_84_0195EN_84_0196

As shown already before in slightly different form, a simple actual-budget comparison is not sufficient to control long-term projects. In the example used the project was running behind schedule and above budget right from the beginning until the end of the project (see the schedule and cost variance lines). Yet, the actual-budget comparison did not provide any indication that a problem might exist until August 2016. EN_84_0200 EN_84_0201

Earned Value Management (4)

25 Friday Mar 2016

Posted by Ludwig Reinhard in Project

≈ Comments Off on Earned Value Management (4)

Tags

Earned Value Analysis, Project controlling, Project module

… continued from part (3)

G. Dynamics AX Project Analysis
Within this subsection, the standard Dynamics AX earned value management tools will be analyzed and compared to the additional analysis option arising from the project setup and posting exemplified before.

G.1. Dynamics AX Standard Earned Value Analysis Tools
The standard Dynamics AX earned value analysis tools consist of an EV chart and an actual vs. forecast chart and are illustrated in the next screen-print. As both charts rely on the standard Dynamics AX SSAS cubes, a processing of those cubes is required before project analysis can be made.
EN_84_0166
What you can identify from the EV chart is a single EV data point that is identical to the planned value as the project has been finished. Interestingly, the EV data point is reported at the beginning of the project as all transactions have been recorded in future periods. As the EV chart is only able to calculate and illustrate one single EV data point, not detailed EV analysis over the lifetime of the project can be executed. What is more, actual costs values cannot be identified from the chart. Whether this is a bug in the authors Dynamics AX test environment or whether this is a general lack of the EV chart tool could not be identified.
The lower actual vs. forecast chart compares the actual and forecasted, that is planned, project values. As mentioned in the beginning of this post, such a comparison does not make much sense as the underlying reason for the actual vs. forecasted difference cannot be identified.

Based on this analysis, it can be summarized that the standard EV analysis tools available in Dynamics AX are not of much use for a detailed project analysis.

 

G.2. Alternative Dynamics AX Standard Earned Value Analysis Tools
Now let’s have a look at the additional project analysis opportunities that the project setup and project postings shown above offer.

The first thing that you can identify when opening the fixed price project estimate window is the EV that has been calculated and posted for the different project stages and that can be identified in the accrued revenue column of the estimate form.
EN_84_0170
In addition, the cost variance can be identified in the general tab of the estimate form.
EN_84_0175
Through a personalization of the estimate form, the EV and cost variance can be identified by default in one single screen for the different project stages without implementing any system adjustment.
EN_84_0180
Unfortunately, the schedule variance cannot be identified by default in the estimate form. Yet, as the schedule variance is defined as the simple difference between the planned and earned value, a simple system modification allows also including this value in the estimate form as well as other project reports.

If you shun making a system modification for executing the EV analysis you can also use the Management Reporter (MR) for doing this analysis. The only prerequisite for doing the analysis in MR is that forecast values have been copied to the budget module.
Once this has been realized the EV analysis can be executed by setting up a simple MR report that combines actual and budget values. Example:
EN_84_0190

 

H. Summary
Overall it can be summarized that Dynamics AX fully supports the Earned Value Management technique through a corresponding project setup.

What is more, the project accounting approach illustrated in this blog post allows creating project and financial statements based on the POC (percentage of completion) as well as the CC (completed contract) method in parallel and thus allows you following different accounting principles such as local GAAP, IFRS and US-GAAP in parallel.

… to be finalized in part (5)

Earned Value Management (3)

20 Sunday Mar 2016

Posted by Ludwig Reinhard in Project

≈ Comments Off on Earned Value Management (3)

Tags

Earned Value Analysis, Project controlling, Project module

… continued from part (2)

F. Dynamics AX Project Processing

F.1. Record Fee Transactions For The T&M Project
After finishing all the necessary setups, project work started. The first thing that I did was recording three fee transactions for the T&M project that are used for invoicing the customer.
EN_84_0095

Please note that project fee transactions do not generate any ledger transaction at the time they are recorded through a project fee journal as no costs are related to project fee transactions.
EN_84_0100

 

F.2. Invoice Customer Through T&M Project
After recording the fee transactions – for reasons of simplicity and illustration – I directly invoiced the customer. The outcome of those invoice transactions can be identified in the following screenshots.
EN_84_0105 EN_84_0110
Please note that project revenue is recognized through the invoices created from the T&M project. This way of recognizing revenue is required in order to make the EV calculation work.

 

F.3. Record Hours & Expenses
My next steps were recording the hour and expense related transactions for the fixed price project for each of the different project stages. Example: EN_84_0115
EN_84_0120

 

F.4. Mark Project Stage As Completed
After the work for each project stage was finished, the stage was marked as complete, which resulted in a recalculation of the progress percentage (POC) for the whole project.

Example: Before marking the first project stage as completed, a POC of 9,56% can be identified.
EN_84_0125

After marking the first project stage as completed, a POC of 9,7% is calculated due to the change in the remaining effort column.
EN_84_0130

 

F.5. Run Project Estimate
After posting hours and expenses and marking the different project stages as complete, fixed price project estimates were run and posted.
EN_84_0135

Example for running the project estimate for the first project stage:
EN_84_0140

Note: The cost template that is used here has the completion method “work in progress percentage” selected as illustrated in the next screen-print.
EN_84_0145

Result: The outcome of the estimate calculation can be identified in the next screenshot.
EN_84_0150
What you can identify from the above screenshot is the POC of 9,7% that was identified earlier in the WBS form. In addition you can identify a “contract value” of $1218000 that is identical to the planned value of the project that has been setup as the on-account milestone for the fixed price project.

Based on the POC and the “contract value”, an accrued revenue (“earned value”) amount of $118088,64 is calculated based on the following formula:
EN_84_0155 EN_84_0160

After posting the first estimate, the following ledger transactions result:
EN_84_0165
From an accounting perspective, those transactions can be interpreted as follows:

  • First, the hour and expense transactions that are initially posted against the WIP cost value account do not have an influence on the company’s overall profitability as they are recorded on Balance Sheet (BS) accounts only,
  • Second, the transfer of the amounts posted on the WIP cost value ledger account to the project cost account when posting the estimate reduces the company’s profit as costs are shifted from balance sheet accounts to income statement accounts,
  • Third, the WIP sales value and accrued revenue posting that records the EV for the first project stage can be used for project controlling purposes to match project costs and revenues and to calculate the cost and schedule variance. As statistical accounts are used for recording this posting, no immediate effect on a company’s profitability – as reported in external financial reports – can be identified.
  • Fourth, the first customer invoice posting in Feb-16 records the project revenue that is used for external reporting. This transaction together with the WIP sales value and accrued revenue transactions allow combining and analyzing projects in line with the POC (percentage of completion) and CC (completed contract) method.

F.6. Repeat Steps F3 – F5
For each project stage, repeat the steps F3-F5 until project work is finished.

… to be continued in part (4)

Earned Value Management (2)

16 Wednesday Mar 2016

Posted by Ludwig Reinhard in Project

≈ Comments Off on Earned Value Management (2)

Tags

Earned Value Analysis, Project controlling, Project module

…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).
EN_84_0040 EN_84_0045

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.
EN_84_0050 EN_84_0055

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.
EN_84_0060

 

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.
EN_84_0065
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).
EN_84_0066

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.
EN_84_0070

 

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:
EN_84_0075

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:
EN_84_0080 EN_84_0081

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.
EN_84_0090

… to be continued in part (3).

Earned Value Management (1)

13 Sunday Mar 2016

Posted by Ludwig Reinhard in Project

≈ Comments Off on Earned Value Management (1)

Tags

Earned Value Analysis, Project controlling, Project module

Within this and the following posts I want to focus on Earned Value Management (EVM) and how this project management & controlling instrument can be implemented in Standard Dynamics AX. The next subsection will give you a brief introduction into the main pillars of the EVM technique but cannot provide you with a comprehensive overview. If you are interested in more details about EVM, you can start digging into details by having a look at the following website.

 

A. Earned Value Management – Background
If your company is working on long-term projects, a major issue from a finance & controlling perspective is how to check whether those projects are on time and within budget. A simple “actual vs. budget” comparison is not able to provide you with this kind of information. That is because budget overruns can simply be caused by the fact that more work than originally planned has been executed causing the cost overrun. The next screen-print exemplifies this issue.
EN_84_0005
Source: Wikipedia

What is needed is thus an instrument that is able to incorporate a measure for the work accomplished, i.e. a measure of the “value earned”. Once you know this value you can compare it with your original planned value to calculate the so-called schedule variance that gives you an indication of whether your project is still on, ahead or behind schedule. (Please see the next screen-print for an example).
EN_84_0010
Source: Wikipedia

In addition, a comparison of the earned value with your actual costs results in the so-called cost variance that gives you an indication of whether your project is above or below budget. (Please see the next screen-print for an example).
EN_84_0015
Source: Wikipedia

Combining all three measures (actual, planned and earned value) into one single chart (as illustrated below) does thus equip you with an instrument that allows you controlling how projects actually run.
EN_84_0020
Source: Wikipedia

 

Within the next session I will provide you with details of the sample data that I used for the subsequent earned value management analysis in Dynamics AX.

 

B. Sample Data
The example used in the following is based on the project of constructing a house that starts in January 2016 and ends in May 2017. As per agreement with the customer, three (fixed price) payments have to be made:

  • The first one in February 2016 for $350k
  • The second one in July 2016 for $1250k and
  • The last one in June 2017 for $400k

Those payments and the dates when the customer needs to make the payments are included in the customer invoice plan shown on the right-hand side of the next screenshot.
EN_84_0025

What you can also identify from the screenshot above are the planned and actual costs for the different construction phases. For reasons of simplicity, I only considered hour related expenses in the first line of each stage and ordinary vendor related expenses in the second line of each stage. As an example, in the first construction phase (“ditch for the foundation”) people need to work for 800 hours. With a cost price of $50/hour total costs of $40000 arise. In addition to those hourly expenses, a digger needs to be rented for the same 800 hours. As the digger costs $250/hour total costs of $200000 arise that add up to a total of $240000 for the first construction phase.

Based on the project plan, total costs of $1218000 should accumulate for the whole project. However, due to some price changes and additional (unplanned) work actual costs of $1329500 arose.

The next screenshot shows you the earned value and variance calculations that I did in MS Excel for the sample data illustrated above.
EN_84_0030

The important figure for the whole earned value (EV) calculation is the percentage of work completed (POC) that is multiplied with the planned value figure to arrive at the EV which is used for calculating the cost and schedule variances. In line with the AX calculation principles, the POC is calculated as follows:
EN_84_0031

Based on this formula a POC of 9,7% for the first stage results.
EN_84_0032

For the second construction phase a POC of 33,24% results.
EN_84_0033

With a POC of (rounded) 9,7% an earned value(EV) of $118088,64 can be calculated for the first construction phase (ignoring rounding differences).
EN_84_0034

The next graph summarizes the earned value analysis for the sample data and compares the calculated earned value measure with the actual and planned values.
EN_84_0035

Note: Please note that there are many alternative options available for calculating the POC. Using the actual hours worked and comparing them to the expected future hours of work that need to be done is only one possibility that is nicely supported by Standard Dynamics AX. If you are running projects where the majority of your costs are caused by expense or item transactions, this approach for calculating the POC might possibly not be the right one for you. Yet, the major concern in this post is not how the POC is calculated but rather how the project accounting engine can be setup in order to get the EV information posted on ledger accounts for subsequent analysis.

 

… to be continued in part (2)

Challenges for trading companies using the project module

01 Tuesday Mar 2016

Posted by Ludwig Reinhard in Project

≈ 2 Comments

Tags

Inventory valuation, Project module, Purchase Order, Sales order

Using the project management and accounting module in companies that are not primarily operating in the business project area but rather in trade or manufacturing can be beneficial due to the numerous additional analysis options available in the project module. Those advantages do however come at a price especially in regards to a more complex inventory valuation. Within the following, I will illustrate you those difficulties and provide you some hints on how to overcome them.

The starting point for the next illustrations are time and material (T&M) projects that have been setup with profit and loss (P&L) and balance sheet (BS) posting project groups in order to show you the difference in the usage of the one and the other project group.
EN_83_0005
Note: For reasons of simplicity the project with the BS posting is referred to as “WIP” project while the project with the P&L posting is referred to as “ordinary” or “non-WIP” project.

From a financial accounting perspective, the following ledger accounts have been setup.
EN_83_0010
Note: All ledger accounts setup for the “WIP” posting types are Balance Sheet accounts and all ledger accounts setup for the “Cost” posting types are Profit & Loss ledger accounts.

With this setup in place, I created the following two T&M projects that are used for recording an ordinary process cycle of a trading company that starts with the creation of the purchase order and ends with invoicing the customer. This process cycle will be illustrated step-by-step in the subsequent sections.
EN_83_0015

 

Step 1: Create Purchase Order
At the time the purchase order is created, Dynamics AX does not create any inventory / ledger transaction. Yet, depending on the setup of the project module, a so-called item requirement is created that allows you identifying and estimating the forthcoming costs.

 

Step 2: Post product receipt
The next step in my process cycle is recording the product receipt for the items ordered (100 pcs * 10 EUR/pcs).

For the WIP project, Dynamics AX generates the following two vouchers:

  • The voucher highlighted in red color is the one created at the purchase order level and represents the usual inventory product receipt.
  • The second voucher highlighted in blue color represents the project voucher created at the project level. Please note that this voucher directly reduces the company’s inventory and transfers those costs to the WIP ledger account (154600).
    EN_83_0020

For the project with the P&L posting, the following vouchers are generated:

  • The voucher highlighted in red color is the one created at the purchase order level and represents the usual inventory product receipt.
  • The voucher highlighted in yellow color represents the project voucher created at the project level. As you can identify, this voucher directly reduces inventory and posts all costs on the project cost account (540500).EN_83_0025

The next screenshot shows you the inventory value report that was run after the product receipts were posted for the WIP and ordinary T&M project:
EN_83_0030
Note: The first item (“L13100”) was used for the WIP project while the second item (“L13200”) was used for the ordinary T&M project.
What you can identify from the physical inventory section of the inventory value report (highlighted in red color) is the purchase order packing slip transaction that increases inventory.
At the same time, AX created a financial inventory transaction that reduced inventory by the very same amount resulting in an overall inventory quantity and amount of 0.
In other words, even though the items have not been sold / invoiced to the customer, you cannot identify them in your inventory anymore as they are already consumed for your project.

 

Step 3: Post purchase order invoice
The next step in my process cycle is posting the purchase order invoice. This posting generates the following voucher for the WIP and the ordinary T&M project:
EN_83_0035
After the purchase order is invoiced, the inventory value report shows the following outcome: EN_83_0040
As you can identify from the screen-print above there is no inventory left despite the fact that the items have not been shipped / invoiced to the customer.

If the items have not been shipped and invoiced to your customer the question arises how you could identify and report on your inventory?

One possibility to do that is by referring to the project WIP report that is shown in the next screenshot. EN_83_0045
What you can identify from this report is that it works only for the WIP project but not for the ordinary project with P&L posting.

An alternative is the project actual transaction report that allows you flipping between the different project types by changing the project statement focus.
EN_83_0050
Yet, in both cases you only get financial amounts but no quantities. Let’s continue by having a look at what happens if you invoice your customer a part of the items purchased.

 

Step 4: Invoice customer for a bundle of 30 pcs
After the purchase order invoice has been recorded, the customer is invoiced for a part (30 pcs * 100 EUR) of the goods via an ordinary project invoice.

This generates the following voucher for the WIP project:
EN_83_0055

The invoice for the ordinary project results in the following voucher:
EN_83_0060

Given those invoices 70 pcs of the item with a total inventory value of 700 EUR remains. The previously used project WIP report shows this amount for the WIP project but not for the ordinary T&M project.
EN_83_0065
Unfortunately, the second alternative report used before – the project actual transaction report – is not able to show you the inventory value, neither for the WIP, nor for the ordinary project and thus leaves you with the requirement of setting up a specific project related inventory value report.
EN_83_0070

 

Summary:
Overall it can be summarized that the use of the Dynamics AX project module can have several advantages for companies not primarily operating in the business project area. Yet, you have to be aware that recording ordinary purchase and sales orders through this module can cause issues when it comes to inventory valuation especially in situations where products are not purchased and sold within the same accounting period. From this perspective, the use of projects with WIP (Balance Sheet postings) seem to be better suited. Notwithstanding this preference of the author, it seems very likely that your auditors will ask for a specialized inventory value report that you need to create yourself to fulfil reporting / auditing requirements. This (cost) disadvantage needs to be weighed against the potential advantages that the project module offers for trading companies.

Project Accounting Book – Part 2

Project accounting book

Categories

  • Accounts Payable
  • Accounts Receivable
  • Bank Management
  • Book reviews
  • Budgeting
  • Cost accounting
  • Fixed Assets
  • General Ledger
  • Inventory
  • Management Reporter
  • Miscellaneous
  • Project
  • Uncategorized

Tags

Accruals Advanced bank reconciliation Allocations Approval Bank reconciliation Controlling Cost accounting module Cost center accounting Cost of Sales Method customer Dynamics AX Dynamics AX 2012 Earned Value Analysis Electronic reporting Email Fixed asset statement General Ledger journal indirect costs intercompany Inventory Inventory reconciliation Inventory valuation Inventory value report invoice invoice recording Management Accounting Management Reporter MS Flow MT940 Nature of Expense Method parallel Posting setup PowerPlatform Prepayments Profit & Loss Statement Project Project controlling Project module Purchase Order search settlement SharePoint standard costs Tax upload valuation Vendor invoice recording Vendor payments WBS workflow

Important Websites

  • Dynamics AX/365FO Links

Legal

  • Disclaimer

Subcribe

  • RSS - Posts
  • RSS - Comments

Enter your email address to follow this blog and receive notifications of new posts by email.

Archives

  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015

Blog at WordPress.com.

Cancel

 
Loading Comments...
Comment
    ×