John’s Journal is a newsletter to provide SMARTSystem™ Users with the latest software highlights and solutions to improve your business operations.
Weighted Average Cost (WAC) is a new feature in the SMARTSystem.
WAC sometimes called the moving average is a method used to track the value of inventory under a perpetual inventory system. This method requires that a new weighted average cost is calculated after each purchase. The weighted average cost per unit is the cost of the goods available for sale divided by the number of units available for sale.
This feature can be easily turned on in the System Control Table under Options. See the checkbox to “Use WAC (Weighted Average Cost)” and if you want to include your Open Orders that have not been invoiced there is an additional option “Include Open Orders in WAC Calculation”.
For additional information just let me know and I can email you an Overview.
Our goal is to provide you with tools to make your job easier and improve your success with quality service delivered quickly. Your feedback is always welcome.
If you have any questions or need support, please contact us at Support@Floralcomputer.com or call 727-483-5306 for assistance.
John Henderson
SMARTSystem Management Training Series
Training Session #4D – SMARTSystem 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.
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
- From: Adjustment Record will show current “Average Landed Cost” (WAC) from inventory
- To: Adjustment Record will show New WAC
- To: Product Inventory Record WAC will be updated
- Consolidate
- From: Adjustment Record should show current “Average Landed Cost” (WAC) from inventory
- 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
- 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):
- When a new entry (lot) is added WAC will be updated based on the quantity and cost of the new entry (lot)
- Adjustment record will be written at the new WAC
- 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:
- SMARTSystem Configuration:
On the Options Tab in Systems Control below, there are three checkbox’s provided to manage WAC:
- Use WAC (Weighted Average Cost)
- This is required and selected automatically when the user selects the WAC 2 option
- Use WAC Calculation method 2 and
- This option sets the use of WAC Calculation 1 when unchecked and the WAC 2 calculation when it is checked.
- The “Use WAC” box must be checked to apply either of these calculation methods
- Include Open Orders in WAC Calculation
- 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:
- Special Utility to Calculate WAC
A one time 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.
- 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:
- 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:
- 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.
Questions and Answers
If you have any questions or need support, please contact us at Support@Floralcomputer.com or call 727-483-5306 for assistance.