Adjusting Landed Costs in X3 v8

Written by Ed Halloran | Jan 19, 2017 9:58:51 AM

Overview

Sage X3 allows for landed cost coefficients to be entered on the product record. These coefficients will default to the purchase order and be captured in the cost of inventory. However, X3 does not make the necessary adjustments to the offset accounts of the entry. This blog details the changes necessary to the auto-journal so landed cost adjustments post to the offset accounts appropriately.

Disclaimer

The changes included in this document were tested on version 8.0.4 of Sage X3. These changes are for average cost products with lot control, location management and do not have unit of measure conversions. Testing included transactions with purchase price variances and receiving multiple invoices for one receipt with the same invoice cost.

Not supported:

A receipt invoiced multiple times at different costs will require a manual adjustment to the offset accounts.
Credit memos for returns with a unit cost on the credit memo that is different from the invoiced unit cost.

The RNI account will have to be matched manually if these changes are implemented.

Creating a Custom Auto-Journal vs Modifying the Existing STKRE & STKPN

Patches update standard auto-journals only. Any changes made by the patch are not incorporated into custom auto-journals. For example, STKRE may be copied and called ZSTRE. Customizations can be made to this new auto-journal and the auto-journal can be linked to an entry transaction for proper postings. Sage recommends this approach.

Experience has shown that it is not always obvious the changes made by the patch to the standard auto-journal and the custom auto-journal may no longer work after the patch is applied. It then becomes necessary to create copy of updated auto-journal and add the custom changes to it. The new auto-journal may be called XSTRE. This now replaces ZSTRE. ZSTRE cannot be deleted because there is history associated with this auto-journal record.

It may be easier to document the custom changes and apply them to the standard auto-journal and re-apply them if necessary, after a patch is applied. This blog follows this approach.

Out of the box configuration

X3 allows the entry of landed cost coefficients on both the product and the purchase order. The receipt posting journal entry includes the landed cost but incorrectly incorporates this amount in the posting to the Received Not Invoiced account. The invoice posting related to the receipt does not include the amounts for landed costs. Thus, the landed cost amounts remain in the RNI account and the account must be manually adjusted.

This is an example of the out of the box entry. Note the remaining balance $530.00 in the RNI account needs to be manually adjusted.

Figure 1: Sage X3 Landed Costs out of the box entry example

Calculations for Adjustment Amounts in Modified Auto-Journal

Figure 2: Sage X3 Receiving & Invoicing example

The additional auto-journal lines created are noted in the last column, lines 990 through 993.
X3 rounds the received cost, column F, to four decimals. This requires the adjustment calculations to use the rounded amounts.
Line 990 – The receipt credits the Accrued Freight account with the amount of the landed cost.
Line 991 - The receipt debits the RNI account with the amount of the landed cost.
Line 992 – After the invoice is posted, an additional posting is generated by the stock accounting interface to adjust the RNI account for the landed cost portion of the purchase price variance.
Line 993 - After the invoice is posted, an additional posting is generated by the stock accounting interface to adjust the Accrued Freight account for the landed cost portion of the purchase price variance.

Returns & Credit Memos

Average cost products are returned at the cost at with which they were purchased. So even though the average cost of the product below is $1.6673 at the time of the return, the system uses the purchase cost of $1.5096.

Figure 3: Sage X3 Returns and Credit Memo example

The additional auto-journal lines created are noted in the last column, lines 990 through 991.
X3 rounds the returned cost, column F, to four decimals. This requires the adjustment calculations to use the rounded amounts.
Line 990 – The return credits the Inventory account with the amount of the landed cost.
Line 991 - The return debits the Accrued Freight account with the amount of the landed cost.

Figure 4: Sage X3 Changes to Auto-Journals example

The receipt line table and the invoice line table are referenceable even though the auto-journal does not list them as linked tables. This is the case in other auto-journals for other tables too.

Get the SQL Statements for Data Analysis

View full post