Within the previous posts, many different ways how cost allocations can be made have been illustrated. As the cost allocation transactions recorded are quite complex, summary Excel documents that showed the allocation bases and amounts have manually been prepared.

Preparing those summary Excel documents manually is time consuming and feasible only for the simplified examples used.

If the manual preparation of those summary Excel documents is not feasible in practice, the question arises, how one can follow up and track cost allocations in live environments?

To answer this question, the following four cost accounting ledger Excel export options have been reviewed:

The costs allocations made for this post are identical to what has been shown in post no. 8 on the new cost accounting module. That is, the supplies cost center costs are allocated to the other cost centers based on the number of employees. The car pool cost center costs are allocated based on the number of company cars and the product management cost center costs are allocated to the other cost centers based on production quantities. For details, please see post no. 8.

 

Excel export option 1:
Making use of the first Excel export option (‘cost entries with dimension hierarchies’) results in the following outcome:
As one can identify from the highlighted columns in the previous screenshot, the first Excel export option is not very helpful because it exports the primary costs only. The secondary costs, that is, the cost allocation data are, however, missing.

 

Excel export option 2:
The second Excel export option (‘statistical entries and cost entries with dimension hierarchies’) resulted in the same outcome that is shown in the previous screenprint and did also not include the cost allocations posted on the secondary cost elements.

 

Excel export option 3:
For the third Excel export option (‘statistical entries with dimension hierarchies’), the Excel export returned no data.

 

Excel export option 4:
The last Excel export option (‘cost accounting ledgers’) finally resulted in the following outcome, which does not help with the intended detailed analysis of the cost allocations made.

 

As none of the previous Excel exports resulted in the intended outcome, the following data export option was investigated:

Making use of this data export functionality finally resulted in the intended outcome. That is, the Excel export also included the cost allocation data, which are highlighted in green color in the next screenprint.

After exporting the primary and secondary cost accounting data to Excel, a pivot table was created. This pivot table is almost – except for the allocation base information – identical to the summary Excel documents that have previously been prepared manually.

 

The last data export functionality shown results in a dynamics Excel export. That is, subsequent cost accounting ledger transactions do not require a new Excel export but a simple data refresh only. The next screenshots prove this standard behavior by making use of a step-wise illustration.

 

Step 1:
Additional repair costs are recorded on the repair cost account no. 855000 for cost center 240 in a General Ledger journal.

 

Step 2:
Those costs are transferred to the cost accounting module for example through a periodic batch run.

 

Step 3:
After the data are transferred to the cost accounting module, the Excel document is refreshed and shows the additional costs recorded.

 

The data export functionality used for the illustration of the cost allocations made helped illustrating the recorded cost allocation amounts. It did, however, not provide any information on how those allocations were made.

In order to find out how cost allocations were made, one can analyze the cost allocation journals that have been created in D365. Let’s take the $32,983.43 that have been allocated from cost center 110 as an example.

By making use of the cost entries button, one can identify the other cost centers that got those costs allocated.

While this information can be retrieved from the Excel pivot chart shown above, the allocation base information that is shown next, is available only in the D365 cost accounting module.

As the current data export functionalities do not support the export of the allocation base data, an idea has been created on the D365 ideas portal and it would be great if you could vote for it. Here is the link: https://ideas.dynamics.com/ideas/dynamics-operations/ID0002454

Many thanks and see you again in the next post on cost accounting security.