How to create an allocation base for 12 months sales data in cost accounting

In cost accounting, an allocation base that uses 12 months of sales data is a dynamic allocation base built on transactional measures (for example, “Items Sold (Amount)” or “Items Sold (Qty)” over a rolling or fixed 12‑month period).

Implementation depends on the product:

1. In Dynamics 365 Business Central cost accounting

Use a dynamic allocation base that points to sales-related entries and restrict it to a 12‑month date range:

1. Define the allocation source and targets
   • In cost accounting, create or edit a cost allocation definition.
   • Set the allocation source to the cost type(s) that must be allocated (for example, overhead cost types).
   • Define the allocation targets as the cost centers or cost objects that should receive the allocation.
2. Choose a dynamic allocation base tied to sales
   • For each allocation target, set Allocation Base to one of the predefined dynamic bases that reflect sales, for example:
     • Items Sold (Qty) – allocates based on quantity sold.
     • Items Sold (Amount) – allocates based on sales value.
   • These bases use underlying item or G/L data as the driver.
3. Set filters to restrict to the last 12 months
   • Open the allocation base filters (for dynamic bases).
   • In Base, select the sales-related base (for example, Items Sold (Amount)).
   • In No. Filter, specify the relevant items if needed (or leave blank for all items).
   • In Cost Center Filter / Cost Object Filter, restrict to the cost centers/objects that should participate.
   • In Date Filter Code, choose a dynamic date range that corresponds to 12 months of data (for example, a code that represents “last 12 months” or a fiscal year, depending on how date filter codes are configured). The system will then calculate the allocation ratios from sales in that 12‑month window.
   • If using Group Filter (for example, Inventory Posting Group), optionally restrict to a subset of items.
4. Run the allocation
   • Use the cost allocation batch job to execute the allocation for the desired posting period.
   • The system calculates the share for each target as:
     • Target’s sales over the 12‑month period ÷ Total sales of all targets over the same period.
   • It then multiplies this ratio by the cost amount to allocate.

This approach gives a 12‑month sales-based allocation without manually maintaining static percentages.

2. In Dynamics 365 Finance cost accounting

In Finance cost accounting, allocation bases are built from cost elements or statistical dimensions. To use 12 months of sales data:

1. Bring sales measures into cost accounting
   • Configure a data provider so that sales-related measures (for example, sales quantity or amount per cost object) are imported as statistical measures or statistical dimension members.
   • Statistical dimensions are designed for non-monetary or driver data (such as number of items sold) and can be used as allocation bases.
2. Create a statistical dimension for sales
   • Define a statistical dimension that represents the sales driver (for example, “12M Sales Qty” or “12M Sales Amount”).
   • Import or post statistical entries that aggregate 12 months of sales per cost object or cost center into this dimension. The aggregation (rolling 12 months or fixed fiscal year) is done before or during import.
3. Use the statistical dimension as an allocation base
   • In cost accounting, create a cost allocation rule or cost distribution rule.
   • For the Allocation base, select the predefined dimension member allocation base that corresponds to the sales statistical dimension (for example, the member representing “12M Sales Qty”).
   • The system will allocate costs in proportion to the magnitudes recorded in that statistical dimension.
4. Optionally refine with hierarchy allocation bases
   • If allocation should only consider a subset of the organization (for example, a region or business unit), create a hierarchy allocation base that applies the sales-based allocation only to a specific node in the cost object hierarchy.
5. Using formula allocation bases (Finance)

If 12‑month sales must be combined with other drivers, a formula allocation base can be defined:

1. Ensure the required statistical measures (for example, 12‑month sales and another driver) are available as allocation bases.
2. Create a formula allocation base using operators (+, −, *, /, parentheses) to derive a composite driver from those measures.
3. Use this formula allocation base in the cost allocation rule.

This allows advanced 12‑month sales-based allocation logic while still using standard cost accounting mechanisms.

References:

• Defining and allocating costs
• To set filters for dynamic allocation bases
• Cost accounting terminology
• Allocation bases