Average Unit Cost Valuation in Sage X3

How inventory & average cost valuation are managed, considering input pricing fluctuation or additional landed cost imposed following inventory receipt.

Sage X3 maintains several methods to value inventory including Standard, FIFO, LIFO, Lot Average Unit and Average Unit Costs.

Industries, such as Food & Beverage, with supply chains prone to fluctuating material and component costs or manufactured units indistinguishable from each other may benefit from an Average Unit Cost stock valuation. The operational measure, cost of goods sold, is updated continuously using Average Unit Cost and allows for accurate calculation of sales margin in these circumstances.

Valuation methods are assignable by site and product in the Sage X3 application.

Sage X3 Master Data

Valuation methods for raw material & finished goods inventories are managed within Sage X3 by the product category, product, and product-site records.

An organization may value a stocked part with the same method, like Average Unit Cost, across its sites or choose to maintain Standard Cost on Site A, while Average Unit Cost is observed on Site X.

In this post, we will walk users through master data setup & transactional configuration to calculate an inventoried part’s Average Unit Cost, while referencing common system inquiries to visualize the evolution of cost across time.

Purchase Order

With Average Unit Cost established for part, RAW046, Organic Powdered Eggs, we issue a system Purchase Order from vendor NA055 for 100LB @ $1.50 per LB, to receive at manufacturing site NA021 (the quantity and price are highlighted).

Purchase Receipt

Warehouse personnel conduct a Purchase Receipt from NA055 PO, bringing 100 LBS of RAW046 into the system at $1.50 per LB for an order cost of $150.

Prior to this receipt, the Average Unit Cost (AUC) for this raw material was $1.17/LB, stemming from a base quantity of 700 LBS and base value of $820 ($820/700 LBS = $1.1714/LB). This information is visible to users on the Product-Site Record Cost Tab. Average cost is always calculated during the receiving process.

This receipt adds 100 units in quantity and $150 total to AUC base value, again, visible on the Product-Site Record Cost Tab.

Purchase Invoice

Transactionally, a Purchase Invoice is generated for the receipt at the PO purchase price – there is no impact on Average Unit Cost.

Product-Site Record Cost Tab

Checking the cost tab, a user will see the impact of the recent receipt, bumping the AUC from $1.1714 to $1.2125.

The new base quantity is 800 LBS (700 + 100 from receipt) & new base value is $970 ($820 + $150 from receipt).

Another Purchase Order

Site NA021 places another Purchase Order with vendor NA055 for 100 LBS of RAW046, though price is now $1.75/LB.

Warehouse personnel conduct a Purchase Receipt from NA055 PO, bringing 100 LBS of RAW046 into the system at $1.75 per LB for an order cost of $175.

The same AUC calculation, taking the recent receipt order cost and adding to on-hand raw material units & base value, yields a new cost of $1.2722/LB.

A Supplier Wrinkle

Several days later vendor NA055 submits an invoice for $2.00/LB, not the $1.75/LB cost as received (pays to get that PO Acknowledgement)!

Processing the Supplier Invoice with a different value than the cost received will, once posted, prompt a re-valuation of material AUC.

Checking the Product-Site Cost Tab, a user will see the AUC of $1.30/LB, owing to the $0.25 difference in received versus invoiced price. Considering 100 LBS received, that is an additional cost of $25 for the order.

Average Cost Evolution Inquiry

As stock transactions are validated or posted the average cost is calculated and tracked in the system.

To review the history of changes to an item’s Average Unit Cost a user may run the Average Cost Evolution Inquiry, found in menu path Stock > Inquiries > Movements (Average Cost Evol/Movement).
Below is the average cost history of RAW046, considering two (2) receipts processed and Supplier Invoice booked at higher cost than received.

Costs Incurred After Receipt

Sage X3 will readily manage subsequent costs from the same or a new vendor, related to a purchase part’s receipt.

In this case, a 3rd party freight provider billed site NA021 for $1000 for the most recent receipt of RAW046. A process known as “complementary invoicing” will pick up this additional cost and apply it to on-hand inventory on a pro-rata basis.

Following successful posting of this complementary invoice, a user may check the Product-Site Record Cost Tab to view the impact to AUC.

Average Cost Evolution Inquiry

Running this inquiry, again, we can see the impact of the additional $1000 charge.

Examining Row 1 of the inquiry we see the 100 LBS added to inventory, comparing Stock Before and Stock After columns. Then the value of $1000 (900 LBS on-hand with an AUC base value of $1170, now reflected as $2170).

AUC base value $2170/900 LB = $2.41/LB.

Stock Value Change Utility

Sage offers a Value Change Utility to re-cost from a global amount or % basis for products valued as Average Unit Cost.

By site & product a user may update the global amount to reflect a new value and run the Stock Accounting Interface to generate journal entry, G/L, for the Inventory account (differences will book to PPV account in G/L).

The same re-valuation may be initiated, by tier, for items costed with a FIFO valuation (but that is for another day).

For more information about Average Unit Cost valuation in Sage X3, please contact us.