Edit

Provider templates for statistical dimension members and measure providers

Note

Community interest groups have now moved from Yammer to Microsoft Viva Engage. To join a Viva Engage community and take part in the latest discussions, fill out the Request access to Finance and Operations Viva Engage Community form and choose the community you want to join.

Use a statistical dimension and its members to register and control nonmonetary entries in Cost accounting. Use statistical dimension members for two purposes:

  • As an allocation base in policies such as cost distribution or cost allocation
  • For reporting of nonmonetary consumption

Statistical dimension

A statistical dimension has a unique name and a set of unique dimension members. Assign the statistical dimension to a Cost accounting ledger ID. This relationship ties all corresponding statistical dimension members to the Cost accounting ledger. Therefore, you create all statistical entries in the context of the Cost accounting ledger.

Note

You can assign a statistical dimension to more than one Cost accounting ledger.

Here's an example of a statistical dimension.

Name Data connector for dimension members
Shared Statistical elements Imported dimension members

Here's an example of a statistical dimension that you assigned to a Cost accounting ledger.

Name Accounting currency Exchange rate type Fiscal calendar Cost element dimension Statistical dimension
Managerial accounting USD Constant currency Fiscal period Shared Cost elements Shared Statistical elements

Statistical dimension members

A statistical dimension member represents an entity that you want to register nonmonetary measures for. Use these measures as an allocation base or just to report nonmonetary values.

Create statistical dimension members manually. Alternatively, import them from a file by using the Data management import/export tool.

A statistical dimension member automatically becomes a predefined allocation base. Use it as an allocation base in policies or as input in other types of allocation bases.

Here are some examples of typical statistical dimension members.

Statistical dimension name Statistical elements Description Unit
Shared Statistical elements FTE Full time employees Ea.
Shared Statistical elements Electricity Electricity consumption kWh
Shared Statistical elements Pack CC Packaging Cost center Hrs.

Statistical measure provider template

Statistical measures can come from many kinds of sources. Dynamics 365 Finance is a great source for extracting statistical measures. Use a statistical measure provider template to easily configure the statistical measures you want to extract.

The definition of a statistical measure provider template is generic and can be reused in multiple statistical dimension members.

Note

You can use all tables that contain financial dimensions as sources for statistical measures.

Example: Count of employees per cost center

The count of employees per cost center is a statistical measure that you can use for various purposes that provide managerial insight:

  • A statistical reporting measure by cost center

  • An allocation base for various types of expenses

  • Internal cost rates by cost center:

    • Cost by employee
    • Revenue by employee

The HcmEmployment table holds a list of all employees in the instance. This table is a global table. Therefore, the records aren't related to a specific data area ID.

Here's an example of employees in the HcmEmployment table.

Name Cost center Description Worker type
Employee 1 CC001 HR Employee
Employee 2 CC002 FI Employee
Employee 3 CC002 FI Employee
Employee 4 CC003 IT Employee
Employee 5 CC003 IT Employee
Employee 6 CC002 FI Contractor

When you create a Statistical measure provider template record, decide which function to use:

  • Count – A count of records per cost object is transferred.
  • Sum – A sum for records per cost object is transferred. (The Sum field and Date field are required.)

Using the Count function

For example, set up a statistical measure provider template as follows.

Name Function Source table Sum field Date field
FTEs Count HcmEmployment Not applicable Not applicable

You can also add one or more ranges to narrow the measures from the source table.

In this example, if you want a count of all full-time employees (FTEs), add a range in the Worker type field. In the Criteria field, select Employee to limit the output range as follows.

Ranges

Source table Field Criteria
HcmEmployment Worker type Employee

Before you can get statistical measures into Cost accounting, you must establish the relation between the statistical measure provider template and the statistical dimension member. Create this relation per Cost accounting ledger and version. The relation consists of a data connector and a data provider. You can have several data connectors and data providers per statistical dimension member.

Note

In this example, you create a relation only for the Actual version.

Go to Cost accounting ledger > Actual version > Manage > Statistical measures to establish the relation. For this scenario, select the Dynamics 365 Finance – Statistical measures data connector, because you want to extract data from Finance.

Data source

Name Data connector Statistical dimension member
FTEs D365FO Dynamics 365 Finance – Statistical measures FTEs

Data provider configuration

Statistical template name
FTEs

After the source data for the statistical measure is processed, Cost accounting creates the following statistical entries.

Journal

Journal Journal type Fiscal calendar period Year Period Version Data connector source entries
00001 Statistical entry transfer journal Fiscal 2017 Period 1 CA ledger USMF FTEs

Statistical entry transfer journal entries

Accounting date Magnitude Statistical element Description Cost center
31-01-2017 1.00 FTEs Full time employees CC001
31-01-2017 2.00 FTEs Full time employees CC002
31-01-2017 2.00 FTEs Full time employees CC003

Statistical entries

Cost object Description Accounting date Statistical dimension member Description Magnitude
CC001 HR 31-01-2017 FTEs Full time employees 1.00
CC002 FI 31-01-2017 FTEs Full time employees 2.00
CC003 IT 31-01-2017 FTEs Full time employees 2.00

If you assign the FTEs predefined dimension member allocation basis as an allocation base in a cost distribution rule, the cost distributes by using the following allocation factor.

Cost object Description Magnitude Allocation factor
CC001 HR 1.00 (1/5) × Amount
CC002 FI 2.00 (2/5) × Amount
CC003 IT 2.00 (2/5) × Amount

Using the Sum function

A production cost center, CC010 (Packaging), is responsible for packaging the products before they're shipped to customers. Add the direct labor cost to the products through the bill of materials (BOM) and route. You must also allocate the indirect cost of running the cost center to the produced products. Often, the best statistical measure for such an allocation is the number of registered production hours per product within the given period.

The ProdRouteTrans table holds all production labor transactions per legal entity DataAreadID.

Here's an example of the ProdRouteTrans table.

Reference Number Operation Type Time Physical date Product group (Financial dimension) Legal entity
Production order P0001 Packaging Time 8.00 01-01-2017 Orange juice B2B USMF
Production order P0001 Packaging Time 8.00 02-01-2017 Orange juice B2B USMF
Production order P0002 Packaging Time 4.00 03-01-2017 Orange juice Consumer USMF
Production order P0003 Packaging Time 4.00 03-01-2017 Orange juice Consumer USMF
Production order P0004 Reconst. Time 10.00 03-01-2017 Orange juice Consumer USMF

When you create a Statistical measure provider template record, decide which function to use:

  • Count – A count of records per cost object is transferred.
  • Sum – A sum for records per cost object is transferred. (The Sum field and Date field are required.)

The statistical measure provider template can be set up as follows.

Name Function Source table Sum field Date field
Pack CC Sum ProdRouteTrans Hours Physical date

You can also add ranges to narrow the measures from the source table.

In this example, if you want only the sum of hours that relate to the CC010 Packaging cost center, add a range in the Operation field. In the Criteria field, select Packaging to limit the output range.

Ranges

Source table Field Criteria
ProdRouteTrans Operation Packaging

Before you can get statistical measures into Cost accounting, you must establish the relation between the statistical measure provider template and the statistical dimension member. Create this relation per Cost accounting ledger and version. The relation consists of a data connector and a data provider. You can have several data connectors and data providers per statistical dimension member.

Note

In this example, you create a relation only for the Actual version.

Go to Cost accounting ledger > Actual version > Manage > Statistical measures to establish the relation. For this scenario, select the Dynamics 365 Finance – Statistical measures data connector, because you want to extract data from Finance.

Data source

Name Data connector Statistical dimension member
Pack CC D365FO Dynamics 365 Finance – Statistical measures Pack CC

The system recognizes that ProdRouteTrans is a table where each record belongs to a separate legal entity. Therefore, you're asked to select the legal entity that transactions should be imported from.

Data provider configuration

Statistical template name Legal entity
Pack CC USMF

After the source data for the statistical measure is processed, the following statistical entries are created in Cost accounting.

Journal

Journal Journal type Fiscal calendar period Year Period Version Data connector source entries
00002 Statistical entry transfer journal Fiscal 2017 Period 1 CA ledger USMF Pack CC

Statistical entry transfer journal entries

Accounting date Magnitude Statistical element Description Product group
31-01-2017 16.00 Pack CC Packaging Cost center Orange juice B2B
31-01-2017 8.00 Pack CC Packaging Cost center Orange juice Consumer

Statistical entries

Cost object Accounting date Statistical dimension member Description Magnitude
Orange juice B2B 31-01-2017 Pack CC Packaging Cost center 16.00
Orange juice Consumer 31-01-2017 Pack CC Packaging Cost center 8.00

If you assign the Pack CC predefined dimension member allocation basis as an allocation base in a cost distribution rule, the system distributes the cost by using the following allocation factor.

Cost object Magnitude Allocation factor
Orange juice B2B 16.00 (16 ÷ 24) × Amount
Orange juice Consumer 8.00 (8 ÷ 24) × Amount

Imported statistical measures

You can import statistical measures into Cost accounting by using the Data management import/export tool.

The data entity that is used for the import is named Imported statistical measures.

Note

This data entity is designed to allow a maximum of five unique dimension values per entry.

You can record the consumption of electricity in Microsoft Excel by using the predefined format of the data entity. Here's an example.

Accounting date Dimension member name1 Dimension member name2 Dimension member name5 Magnitude Source identifier
31-01-2017 2,450.00 Electricity
31-01-2017 4,100.00 Electricity
31-01-2017 CC003 15,000.00 Electricity

When you've imported your data via Data management, the data is stored in a Cost accounting staging table. Therefore, the imported data can be used in multiple Cost accounting ledgers. A reload of data isn't required.

To import the data, go to Imported data > Data entity > Imported statistical measures.

Source identifier Accounting date Magnitude Dimension member name1 Dimension member name2 Dimension member name5
Electricity 31-01-2017 2,450.00 CC001
Electricity 31-01-2017 4,100.00 CC002
Electricity 31-01-2017 15,000.00 CC003

Before you can get statistical measures into Cost accounting, you must establish the relation between the source identifier and the statistical dimension member. This relation is created per Cost accounting ledger and version. The relation consists of a data connector and a data provider. You can have several data connectors and data providers per statistical dimension member.

Note

In this example, you create a relation only for the Actual version.

Go to Cost accounting ledger > Actual version > Manage > Statistical measures to establish the relation. For this scenario, select the Imported statistical measures data connector, because you imported data from a third-party system into Cost accounting via Excel.

Data source

Name Data connector Statistical dimension member
Electricity Imported statistical measures Electricity

Data provider configuration

Import source identifier Function Dimension1 Dimension2 Dimension5
Electricity Sum Cost centers

Note

When you define the data provider configuration, you must specify which cost object dimensions to match against the imported transactions. After the system processes the source data for the statistical measure, it creates the following statistical entries in Cost accounting.

Journal

Journal Journal type Fiscal calendar period Year Perid Version Data connector source entries
00002 Statistical entry transfer journal Fiscal 2017 Period 1 CA ledger USMF Electricity

Statistical entry transfer journal entries

Accounting date Magnitude Cost element Description Cost center
31-01-2017 2,450.00 Electricity Electricity consumption CC001
31-01-2017 4,100.00 Electricity Electricity consumption CC002
31-01-2017 15,000.00 Electricity Electricity consumption CC003

Statistical entries

Cost object Description Accounting date Statistical dimension member Description Magnitude
CC001 HR 31-01-2017 Electricity Electricity consumption 2,450.00
CC002 FI 31-01-2017 Electricity Electricity consumption 4,100.00
CC003 IT 31-01-2017 Electricity Electricity consumption 15,000.00

If you assign the Electricity predefined dimension member allocation basis as an allocation base in a cost distribution rule, the system distributes the cost by using the following allocation factor.

Cost object Description Magnitude Allocation factor
CC001 HR 2,450.00 (2,450 ÷ 21,550) × Amount
CC002 FI 4,100.00 (4,100 ÷ 21,550) × Amount
CC003 IT 15,000.00 (15,000 ÷ 21,550) × Amount

Additional resources

Allocation bases

  • Last updated on