In this webcast, I show you how you can use MS-Flow for allocating depreciation costs from the fixed asset module to the projects that you operate in the project management and accounting module. Everything works out of the box without a system modification – something that was not possible in the past.
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.
Additional repair costs are recorded on the repair cost account no. 855000 for cost center 240 in a General Ledger journal.
Those costs are transferred to the cost accounting module for example through a periodic batch run.
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.
The previous posts showed three different approaches how cost allocations in the cost accounting module can be made based on recorded project hour transactions. The approaches used
- project hour quantities that were imported into the cost accounting module for making cost allocations based on the recorded project hour quantities,
- project hour transaction vouchers that were generated by making use of the project module ledger integration, and
- overhead rate policies that allocated the costs to other cost centers based on project hour quantities recorded in the project module.
A major disadvantage of the first approach is that the complete cost center costs of the car pool cost center are allocated to the other operative cost centers irrespective of whether those costs are too high or low compared to an external market price.
The second and third approach did not completely allocate the car pool cost center costs but did not provide a benchmark of the required cost rate needed to allocate the complete cost center costs.
In this post, we will investigate how such a benchmark can be obtained by making use of the overhead rate policy feature, which is described on a step-by-step basis in the following.
Step 1: Record hours in the project module
To get the cost allocation and overhead rate policy incorporated, hours are recorded in the project module first.
Please note that no additional projects and postings have been created but that the transactions shown and recorded in the previous post have simply been reused. The next screenprints consequently document the previous postings and resulting costs only.
Step 2: Setup statistical measure provider template
Also the second and third setup steps are identical to what has been shown in the previous posts. Therefore, reference is made to what has been described there and the following screenprints are included for reasons of completeness only.
Step 3: Setup statistical dimension measure
Step 4: Create cost accounting ledger
The set up of the cost accounting ledger slightly differs from the cost accounting ledgers that have been used before. That is because the project cost and payroll allocation accounts (853100 and 853200) have not been included in the cost element and dimension hierarchies used before. The next screenprints document the minor changes that have been made to the newly created cost element dimension (‘DEMF CE P16’) and the newly created cost element dimension hierarchy (‘DEMF CEH P16’).
Step 5: Create cost allocation policy
The cost allocation policy that is used in this post is identical to the one that has been used in post no. 14 except that the newly set up cost element dimension (‘DEMF CE P16’) is used.
Step 6: Create cost rollup policy
Also the cost rollup policy is identical to the one used in post no. 14 except for the newly set up cost element dimension hierarchy.
Step 7: Create overhead rate policy
The overhead rate policy used in this post differs from the one used previously in the overhead rate policy type (‘fiscal period’ vs. ‘user specified’). Because of the ‘fiscal period’ type selected, one cannot enter an overhead rate in the rule section, as D365 calculates the rates automatically.
Step 8: Process data in cost accounting ledger and configure workspace
After processing the data and policies and after setting up a cost controlling workspace, the following costs can be identified for the various cost centers used:
The next overview summarizes the cost allocations made.
From the previous screenprint it can be identified that two allocations were made; one in the project module for the hours recorded with a cost price of $180/hour and a second one in the cost accounting module, where all remaining costs of the indirect cost centers no. 110-130 were allocated.
Only the cost allocations made in the cost accounting module result in secondary cost transactions. The cost allocations made in the project module are considered to be primary costs from a cost accounting module perspective.
After allocating all costs from the supporting or indirect cost centers no. 110-130 to the direct operative cost centers no. 210-250, the initially raised question on the benchmark cost rate for the car pool cost center has not been answered. An investigation of the overhead rate policy form – shown in the next figure – allows answering this question.
The overhead rate policy form shows a cost rate of $1640.43 for 40 units (hours) of work for the car pool cost center. Dividing the amount by the units results in a rate of $41.01 per hour. Because the cost accounting module allocated the remaining costs only that were not previously allocated through the postings in the project module, the $41.01 represent a cost rate difference. That is, in order to allocate the full costs of the car pool cost center, a rate of ($180 + $41.01 =) 221.01 is required.
If a ‘fiscal period’ overhead rate policy is applied to the cost accounting ledger that has been used in the previous post no. 14, the same rate can be identified. For details, please see the next screenprint.
In the next post, we will investigate how cost allocations that are made in D365 can be tracked and analyzed. Till then.
In the previous post, we had a look at how transactions that were recorded in the project module can be used as statistical measure for making cost allocations. This post will introduce two alternative options that illustrate how those allocations can be made.
Option 1: Project module
A first possibility how cost allocations between cost centers – based on recorded project hours – can be made is through project costing vouchers. Those vouchers can be realized by making use of a project group that has the ledger integration parameter for hour related postings activated. This is the case for the projects shown in the next screenprint.
Posting the very same hour transactions for the car pool cost center manager that have been recorded in the previous post results in a voucher that is shown in the next screenprint.
The voucher above shows that a project cost account (no. 853100) is – in combination with the operative cost centers – debited. The credit transaction is made on another profit and loss (P&L) account (no. 853200). As the debit and credit ledger accounts are both P&L accounts, no effect on company’s profit arises. However, because of the financial dimensions used, an allocation from the car pool cost center costs to the other direct cost centers occurs. This allocation effect is illustrated in the next figure.
- A cost price of $180/hour has been setup in the project module for recording the project hours of the car pool manager.
- From a cost accounting module perspective, the cost allocations made in the project module occur at the primary cost level. That is because the cost allocations are made outside of the cost accounting module
- The cost center financial dimension of the car pool manager that is credited is set up at the employee level in the Human Resource module.
From the previous screenprint, one can identify that some costs remain at the car pool cost center no. 120. Those remaining costs are a sign that the cost price of $180/hour is not sufficient to allocate all costs from the car pool cost center to the other operative cost centers. Provided that the $180 is a market rate (price), the costs remaining on cost center no. 120 can be taken as a sign that the internal management of the car pool is more expensive compared to an external (outsourced) management of that cost center services.
Option 2: Cost accounting module
The second alternative for the cost allocation of the car pool cost center costs is the use of an overhead rate policy, which is exemplified in the next screenprint.
Making use of an overhead rate policy requires that a link to the statistical measure allocation base (‘4. PROJ HOURS’) is made (1). In addition, a secondary cost element that records the cost allocations (2) and the financial dimension to which the overhead rate is applied (3) need to be set up.
Most importantly, an overhead rate that is used for the creation of the cost allocations has to be determined (4). In the example shown above, a rate of $190 has been used to differentiate it from the cost rate that has been setup and used in the project module example shown before.
Similar to what has been shown in the previous post, a statistical measure provider template needs to be setup next for the transfer of the recorded project transactions (hours) to the cost accounting module.
The only difference in the setup of the statistical measure provider template used in this post compared to the one used in the previous post is that a range for the newly set up projects has been specified. This is necessary because the hours of the cost center manager have – for illustrative purposes – been recorded twice; a first time at the projects ending with the number 26ff and a second time at the projects ending with the number 27ff.
Processing the overhead rate policy through a cost accounting ledger results in the following costs for the different cost centers:
As shown in the previous posts, the next graphic summarizes the cost allocations made and the total costs that remain at the operative cost centers no. 210-250.
Please note that a rate of $190 is not sufficient for allocating all costs of the car pool cost center to the other ones. This can be identified by the $900 that remain at the car pool cost center after the allocations are made.
In the next post, we will investigate an approach that makes use of cost allocations in the project module and an overhead rate policy in the cost accounting module. Till then.
In this post, we will analyze how transactions – or more general records – that are entered in other D365 modules can be used as statistical measures for making cost allocations.
To illustrate how such cost allocations can be realized the same demo data that have been used in prior posts will be used again. However, this time, the costs of the car pool cost center no. 120 will be allocated to the other direct cost centers based on the hours that the cost center manager recorded in the project module. Details thereof as well as the allocation bases used can be found in the next screenprint.
Step 1: Record hours in the project module
For recording the hours of the car pool cost center manager, a project with several subprojects – one for each cost center – has been setup.
All projects used in this post are setup with a project group that does not generate a voucher for hour transactions. This has been realized by making use of the ‘never ledger’ project accounting integration setup. As a result, time recordings made in the project module record quantities only.
Step 2: Setup statistical measure provider template
Once all costs are recorded for the different projects, a statistical measure provider template is setup. This template is required to transfer the recorded project hour data into the cost accounting module.
When setting up this statistical measure provider template, one has to select the source table that holds the data, which will be used for the definition of the cost allocation basis. In the example used, the data are held in the ProjEmplTrans table (1). In addition to the table that holds the statistical data, one has to specify (2) the function (sum vs. count), (3) the sum field (hours) and (4) the date field (project date) in order to get the data correctly transferred into the cost accounting module.
Step 3: Setup statistical dimension measure
The next required setup for using the recorded project hours as statistical measure in the cost accounting module is the creation of a new statistical member (‘4. PROJ HOURS’), which is shown in the next screenprint.
Step 4: Setup cost accounting ledger
Thereafter, the cost accounting ledger can be setup in the same way as it has been shown in the prior posts.
Setting up the cost accounting ledger necessitates the configuration of the statistical measure data providers. This configuration of the project hour related measure is shown in the next screenprint and requires that a link between the statistical dimension measure (‘4. PROJ HOURS’) from step 3 and the previously setup statistical measure provider template (‘P14_LRE_ProjectHours’) from step 2 is made.
Step 5: Create cost accounting policy
Finally, the cost allocation policies can be created. Please note that the cost allocation for the car pool cost center is made on the basis of the project hours recorded.
Step 6: Process data and overhead calculation
The outcome of processing the cost allocation policy on the sample data can be observed from the next screenprints.
In the next post, we will take a look at the overhead rate policies and how they can be used for making cost allocations. Till then.
This post focuses on cost distribution policies and how they differ from cost allocation policies. According to the D365 documentation, cost distribution and cost allocation policies differ in a way that cost distributions always occur at the level of the primary cost element of the original costs.
Applied to the previously used example, cost distribution policies should distribute the costs from the indirect cost centers to the direct ones, as indicated by the arrows shown in the next figure.
To verify this, the following cost distribution policy has been setup:
Please note from the previous screenprint that cost distribution policies also refer to a cost object dimension (‚INDIRECT CC’), as cost allocation policies do. Yet, different from cost allocation policies, a cost element node needs to be specified. The remaining cost behavior and allocation base columns are once again identical to what has been shown for cost allocation policies.
After setting up the cost accounting ledger and processing the data and the cost distribution policy, the following costs can be observed from the cost controlling workspace for the different indirect cost centers:
As one can identify from the prior screenprints, some costs remain at the indirect cost centers no. 120 and 130. The underlying reason for this outcome are cost distributions that are made within the group of the indirect cost centers. Those distributions are caused by the employee allocation basis used. The next graphical overview aims to illustrate the underlying issue.
To avoid that costs remain at the indirect cost centers, the cost distribution policy is slightly modified in a way that a hierarchy allocation base (‚DEMF_P13_B’) is used.
The major advantage of using a hierarchy allocation base is that one can specify to which cost objects cost distributions (or cost allocations) shall be made. In the example shown above, to the direct cost centers (‘DIRECT CC’) only.
Reprocessing the cost distributions with the modified cost distribution policy finally results in the cost data shown in the next figures.
The next graphic has been prepared for a better overview of the cost distributions made.
Before ending this post, please note the following concluding remarks:
- The distributed costs won’t be shown as secondary costs even if a cost rollup policy is defined and assigned to the cost accounting ledger.
- The same outcome that has been shown above can be achieved by making use of a cost allocation policy that is not linked to a cost rollup policy.
The next post will introduce how to import statistical measure data from other D365 modules. Till then.
In this post, we will take a look at a more advanced allocation scenario where fixed and variable parts of selected cost elements are allocated from indirect to direct cost centers based on separate allocation bases. The next graphic illustrates the allocation approach applied in this post.
Step 1: Setup dimension hierarchy
To get this allocation approach incorporated into D365, the previously used dimension hierarchy that differentiates between direct and indirect cost centers is used once again.
Step 2: Setup cost behavior policy
The next setup required concerns the cost center behavior policy of the different cost elements. The following screenprint documents how this setup has been made.
Please note the different fixed percentage rates for the cost elements 853000, 854000 and 855000 from the screenshot shown above. To ensure that no other fixed costs are setup and caught by the allocation policy, an additional cost element dimension hierarchy node (‘DEMF CEH’) – with a fixed percentage rate of 0.00% – has been included in the cost behavior policy rule section.
Step 3: Setup hierarchy allocation bases
The third setup step relates to the hierarchy allocation bases that will be used for allocating the fixed and variable costs.
The first hierarchy allocation base (‚DEMF_P12_FIX’) that will be used for the allocation of the fixed costs from the indirect cost centers to the direct cost centers refers to the numbers of bikes produced.
The second hierarchy allocation base (‘DEMF_P12_VAR) also allocates costs to the direct cost centers. However, different from the fixed cost part, the employment related statistical measure (‘1. EMPL STAT DATA’) is used as allocation base for the variable costs.
Step 4: Setup cost allocation policies
The cost allocation policy shown next, combines the different ‘pieces’ that have been setup and explained before. Because the setup of cost allocation policies have already been explained in previous posts, no further explanations are made here and reference is made to the previous posts on the new cost accounting module.
Step 5: Setup cost accounting ledger & process data
After the cost accounting ledger is created and the costs and policies processed – not shown for reasons of brevity – the following costs can be observed from the cost controlling workspace for the different cost centers used.
For a better overview of the fixed and variable cost allocations made, the following graphic has been developed, which summarizes the different costs and allocations for the cost centers used.
In the next post, we will take a look at cost distribution policies and how they differ from cost allocation policies. Till then.
Similar to what has been shown in the previous posts on cost allocations, also this post will shown an allocation technique that makes use of a dimension hierarchy that includes additional summary nodes. The allocation technique shown below will once again be based on the financial data that have been used in prior posts.
However, this time a hierarchy allocation base is setup for making the cost allocations.
The hierarchy allocation base that is setup and illustrated in the previous screenprint defines that the previously used formula – which calculates the total direct costs – will be used as the allocation base for those cost centers that belong to the ‘DIRECT CC’ node of the cost object dimension hierarchy.
This cost allocation base is then used in the cost allocation policy shown next and defines that all cost centers that belong to the ‘INDIRECT CC’ node will be allocated to the operative (‘direct’) cost centers based on the total costs that have been recorded on the operative cost centers.
Different from what has been shown in part 7 of this series on the cost accounting module, the cost allocations ignore the cost relationships within the group of the indirect cost centers because the hierarchy allocation base specified refers to the ‘DIRECT CC’ node only.
As before, the next screenprints summarize the allocation results.
Please note that the total costs on the direct or operating cost centers no. 210-250 are identical to what has been shown in part 7 of this series. Yet, a detailed investigation of the allocations made reveals that the allocations within the group of the indirect cost centers are skipped.
In the next part, we will extend the hierarchy allocation base allocation technique and investigate how it can be applied to costing scenarios that make use of fixed and variable costs. Till then.
This post continues the previous one in a way that allocations are made once again by referring to summary nodes in a dimension hierarchy. However, this time multiple allocation bases – shown in the next overview graphic – are used for making the allocations.
To realize the multiple base hierarchical allocation approach, a new cost object dimension has been setup that includes three additional summary nodes (‚110 INDIRECT’, ‘120 INDIRECT’ and ‘130 INDIRECT’). In the example used, each of those summary nodes holds a single cost center only.
With the new cost object dimension hierarchy in place, one can then refer to the different indirect summary hierarchy nodes when setting up cost allocation policies. This is exemplified in the next screenprint that shows how the different indirect cost object nodes are linked to allocation bases for cost allocation purposes.
In the example illustrated in the previous screenprint, all cost centers (respectively their costs) that belong to the ‘110 INDIRECT’ hierarchy node are allocated to the operative or ‘direct’ cost centers based on the number of employees working there.
The cost center costs of the ‘120 INDIRECT’ group are allocated based on the number of company cars and the cost center costs of the ‘130 INDIRECT’ group are allocated based on the total direct costs of the other ‘direct’ or operative cost centers.
This example illustrates once again that previously used statistical measures and formulas can be re-used multiple times in the new D365 cost accounting module. This ability can be very beneficial for example if one wants to run simulations or tests based on previously used allocation bases.
As in all prior posts on cost allocations, the next screenprints document the obtained allocations and are summarized in an Excel document at the very end of this post.
In the next post, we will investigate how one can make use of hierarchy allocation bases. Till then.