Training Session #4D – Inventory Weighted Average Costs

Purpose: The Weighted Average Cost (WAC) is sometimes called the moving average is a method used to track the value of inventory under a perpetual inventory system.

Keywords: The following is a list of key terms used in the SMARTSystem that you should be familiar with and understand.

LOTS are records received into inventory from purchase orders or inventory receiving.
WAC 1– Will re-compute a new WAC value based on all open lots.
WAC 2 – Utilizes the Average Landed Cost field from the Inventory Table to compute a new value based on receiving.
WAC – Value is stored in the inventory table in the Average Landed Cost field:

Overview

There are two ways to calculate WAC:

WAC 1: is a Moving Weighted Average Cost (MWAC). This method recalculates a new WAC whenever the On-hand Quantity changes and recalculated when:

a PO is received,

Inventory Receiving,

Inventory is transferred,

Inventory Adjustments,

Inventory Reconciliation, and

Product returned on a Credit Memo.

WAC 1 is affected when inventory is reduced on a Lot/PO, physical inventory line item(s) or entries. WAC 1 is Lot/PO based.

This method calculates WAC by evaluating the open lots in the system by inventory item or product code.

For any product when inventory goes to zero, the Average Landed Cost is also set to zero and the WAC calculations start over.

WAC 2: is weighted average cost calculated only when the On-hand quantity is increased, but not when it is decreased. This is based on the valuation of each inventory item.

This method calculates WAC by computing the current On-Hand Quantity x the current Average Landed Cost, then adding in the new item quantity times landed cost divided by the total On-Hand Quantity.

When WAC is turned off, this non-WAC method of calculating costs is based on the Landed Cost of products. Depending upon the circumstances either the Current Landed Cost or the Landed Cost of a specific lot (PO) could be used and exported to accounting.

There is an option for each item in the Inventory Table not to computer WAC. Just select the checkbox illustrated below.

Transaction Summary:

Purchase Order transactions that will update WAC in the Inventory Record

PO Received

PO Receive and Backorder

Receive a PO Backorder

Internal Inventory Transfer

Transfer

o From: Adjustment Record will show current “Average Landed Cost” (WAC) from inventory

o To: Adjustment Record will show New WAC

o To: Product Inventory Record WAC will be updated

Consolidate

o From: Adjustment Record should show current “Average Landed Cost” (WAC) from inventory

o To: Adjustment Record will show New WAC. The new cost that will be factored in to compute WAC which is calculated by dividing the total cost of the “from” items at their WAC by the number of items specified by the user on the consolidation screen

o To: Product Inventory Record WAC will be updated

Inventory Count Adjustments:

Can change costs using WAC 1 based on specific Lots/PO’s

Cannot change costs for WAC 2

Inventory Receiving / Production Data:

WAC for products marked as No WAC products will be set to costs defined in the Inventory Receiving Entry program or Average Landed Cost in the Inventory Table.

WAC applies to existing products (No WAC box is not checked in inventory):

o When a new entry (lot) is added WAC will be updated based on the quantity and cost of the new entry (lot)

o Adjustment record will be written at the new WAC

o Update and Delete options are disabled in the Inventory Receiving Module for WAC 2.

Inventory Reconciliation

WAC 1 will re-compute the new WAC based on the lots modified.

WAC 2 utilizes the Average Landed Costs from the Inventory Table.

Set-up Procedures:

1. SMARTSystem Configuration:

On the Options Tab in Systems Control below, there are three checkbox’s provided to manage WAC:

Use WAC (Weighted Average Cost)
o This is required and selected automatically when the user selects the WAC 2 option
Use WAC Calculation method 2 and
o This option sets the use of WAC Calculation 1 when unchecked and the WAC 2 calculation when it is checked.
o The “Use WAC” box must be checked to apply either of these calculation methods
Include Open Orders in WAC Calculation

2. Items marked as “Do not calculate WAC”

The WAC Value is stored and visible in the Inventory table. For your own Products entered using Inventory Receiving, the WAC value should not be calculated which allows you to set a fixed value. Therefore, some of your products may have the “Do not calculate WAC cost for this item” set on. See example below:

3. Special Utility to Calculate WAC

A onetime utility will be run to calculate WAC on all inventory items including those with the “Do not calculate WAC cost for this item” set.

Or, should you prefer, a utility can be run to move all of your Current Landed Cost to Average Landed Cost which may provide you a gradual shift in costing.

4. Import WAC for your own Products

Products that are set to “Do not calculate WAC cost for this item” can be updated manually using an excel spreadsheet to set the WAC values.

Products Received using Purchase Orders

For each product on a purchase order WAC is calculated using the following formula when the PO is RECEIVED.

The WAC1 formula includes:

PO line items received, not back orders

Existing On-hand inventory, including products on HOLD

If “Include Open Orders in WAC Calculation” is set in System Control, then Sales Orders not yet invoiced will be included

WAC1 Calculation:

The WAC1 calculation when a new purchase order is received and the option set to include open sales orders, the following math is applied:
New PO Detail Quantity x New PO Detail Record Landed Cost
+
Sales Order(SO) Detail Quantity x WAC cost of SO Detail at time of sale
+
Existing PO Detail Quantities x Existing PO Detail Costs
/ (Divided By)
Total Quantity (total New PO Detail Quantity + SO Detail Quantity + Existing PO Detail Quantities)

The WAC2 formula includes:

PO line items received, not back orders

Existing On-hand inventory, including products on HOLD

If “Include Open Orders in WAC Calculation” is set in System Control, then Sales Orders not yet invoiced will be included

WAC2 Calculation:

The WAC2 calculation for new purchase orders received and the option set to include open sales orders, the following math is applied:

If Adding items to inventory:

o WAC2 = ((Average Landed Cost x Total On Hand (includes quantities from open orders if option is selected)) + (New Item Cost x New Item On Hand)) / (Total On Hand + New Item On Hand)

If Quantity is reduced by operation:

o WAC2 = Average Landed Cost (WAC will not be affected by inventory reduction)

General WAC Information:

WAC value is stored in the inventory table in the Average Landed Cost field:

When product is sold the WAC value from the Average Landed Cost in the Inventory Table is stored with in the Sales Order Detail record, WAC value is shown above.

Inventory COST adjustment records are created when:

Costs adjustments made on a Received PO will recalculate WAC and generate an inventory adjustment record
Changes to the Average Landed Cost in the Inventory table

Items marked as Do not calculate WAC

WAC is an assigned value for these Products and can initially be up-loaded in an excel spreadsheet to begin processing with WAC.

Inventory Receiving will show Using Average Cost and display WAC from the Average Landed Cost in the Inventory table in the Cost Per Item field below:

This WAC value can be overridden by Inventory Receiving and will automatically be saved as the new WAC value in the Average Landed Cost field for this product in the Inventory Table. In addition, the Average Purchase Item Cost field is updated and the Average Freight Cost Per Item is set to zeros.

Adjustments

Any Cost Adjustment transactions against the On-Hand quantity will create an Adjustment Type 2 record.

Type 2 Adjustment records are also created when the Average Landed Cost in the Inventory Table is changed.

To protect the integrity of WAC, changes to the Average Landed Cost field requires an “Inventory Administrator” set in your Staff Record or a manager override password can be entered to set the WAC Average Landed Cost value. See Staff checkbox below:

As an Inventory Administrator you can change cost values in the Inventory Table:

Export to Accounting

All General Ledger information exported to Accounting, will use the WAC values.

Reports

Report associated with the Inventory Cost value will use WAC.