Key setup options for perpetual inventory valuation

The system provides perpetual inventory valuation. This means that the system is interactively keeping the inventory balances and costs current (with the exception of standard and replacement costs, that are maintained manually by the user) providing up-to-date stock inventory valuation each time the Product Trial Balance of Stock Inventory Report is run.

This table lists the options you must review to ensure you maintain an accurate inventory valuation of product costs.

Function Option
SA Administrator Options-Products-Costs Which Cost to use for Sales Mgr/GL
Which Cost to use for GL
Include Addon
SA Administrator Options-Documents-Purchase Orders Do You Capitalize Addons
Order Discount
at Receiving or Costing
Distribute Capitalized Order Discounts by
SA Administrator Options-Products Distribute to GL Balance Sheet by
Distribute to GL Income Statement by
Post Cost of Goods Adjustments in Costing (stock only)
Post Cost of Goods Adjustments to
SA Operator Setup-GL Inventory GL accounts set up under the R&D operator
Product Extended GL Distribution Setup Balance sheet GL accounts
SA Administrator Options-Documents-Sales Orders During Entry, Default the Division # from the
SA Company Setup Divisional

Security to make changes is required for Product Warehouse Product Setup and Product Maintain Cost Entry cost adjustment. When costs are changed on either of these windows, there must be a GL Transaction Entry posting to the appropriate Inventory Control account(s) that correspond to this change. The SA Balance Maintenance Report may be run as an audit report to display changes made in Product Warehouse Product Setup or Product Maintain Cost Entry. Note, the SA Balance Maintenance Report will show the new amount of cost changes on a particular date if someone has made such a change, but this amount does not take into account the inventory quantity at the point in time that the change was made.

For example, suppose you have an On Hand quantity of 5 in Product Warehouse Product Setup and an Unavailable quantity of 2, for a total of 7 in inventory. If the product cost is $7.00 and is manually changed in Product Warehouse Product Setup to be $6.50, the SA Balance Maintenance Report would report a new change of $ -.50 on the product. In reality, the change that should be recorded to the General Ledger Inventory Control account is $ -.50 times 7 (quantity) to equal $ -3.50.

The operators that are allowed to make Inventory Control cost changes to either the Product Maintain Cost Entry or Product Warehouse Product Setup must be responsible for having the appropriate GL inventory postings made to ensure that the subsidiary inventory and GL inventory valuations remain in balance.

It is possible to go back in history as far as you would like in generating an As Of report. However, there are two limitations you should be aware of:

  1. As Of reports always process individual transaction records. Consequently, As Of reports are never processed at the same speed as current reports. With the Product Trial Balance of Stock Inventory Report, a key is used to roll backwards from current values for both Inventory Control and General Ledger values. Because of this, the time needed to process the report increases in direct proportion to the history required to process the report. An As Of report generated for just a few days back will probably perform as well as a current balance report. However, the further back in history you go, the more noticeable the difference becomes. Therefore, be careful when you schedule reports back farther than a few days.
  2. Archiving Inventory Control data has an effect on this report if the As Of date is earlier than the last archive date. If Inventory Control information has been archived, it is no longer available to calculate the correct balance for that time frame. If you generate Product Trial Balance of Stock Inventory Report with incomplete information, the result is an inaccurate As Of trial balance. This issue should not be viewed as an excuse not to archive, it should be used as a reason to produce a trial balance for the period PRIOR to archiving history.

Generate this report daily or weekly and compared to the General Ledger totals.

The General Ledger inventory accounts can be specified in Product Extended GL Distribution Setup or SA Operator Setup-GL. The Product Extended GL Distribution Setup record is reviewed for the General Ledger accounts and if none exists, the SA Operator Setup record is reviewed. If none exists there, the accounts are blank.

Build-on-demand kits do not appear on the report but are broken down into their kit components. The General Ledger and Inventory Control balances are reported by the kit components.

Labor products are not included on the Product Trial Balance of Stock Inventory Report.

Serial, Lot, and FIFO

Inventory quantity and values are calculated slightly differently if using FIFO or serial/lot rollup for General Ledger purposes.

For current value, each record (FIFO/lot/serial) is added to a running total for both quantity and cost. For FIFO and lot products, the quantity in that layer times the cost of that layer feeds this running total. FIFO quantities will only be used if the Use FIFO option in SA Administrator Options-Products-Costs is selected. For serialized products, simple addition is used. This provides both a quantity and value field for the report. The cost that appears on the report is calculated by dividing the total value by the total quantity, resulting in an average of all items in stock.

In an As Of report, the same method is used to produce a starting point. At that time, each transaction is added or subtracted based on type and date. Like other As Of products, the final Product Unavailable Inventory Entry determines the cost to use to determine the value of inventory at that point in time.

Special Price/Cost

All items with a special price/cost indicated in Product Setup are shown with a cost and quantity per the stocking unit. This allows a consistent costing point and provides a true balance to the General Ledger. The cost of special price/cost items are calculated correctly to bring them to a per stocking unit cost. For this reason, the cost on the report may not match the cost in Product Warehouse Product Setup record.

For example, suppose the following receipts were made for product 80-24:
PO # Qty Price Ext New Avg x Qty Value G/L
20 15 5.00 75.00 5.000 15 75.00 75.00
67 12 5.25 63.00 5.111 27 137.999 138.00
82 6 5.10 30.60 5.109 33 168.597 168.60
93 8 5.15 41.20 5.117 41 209.797 209.80

This diagram illustrates how the average cost tends to be similar to the General Ledger Inventory account, even if the incoming costs change on each receipt of inventory.

The last cost is the last cost at which this product was purchased or received. This cost is updated when items are received through Purchase Receipt of Inventory Entry or Transfer Receipt of Inventory Entry. If you use last cost as the costing method to update General Ledger, balancing becomes virtually impossible. For example, suppose you have two receipts for product 79:
Journal # Qty Price GL Extended Value
20 15 22.00 330.00
67 12 28.75 345.00

When you generate the Product Trial Balance of Stock Inventory Report, the report produces the following results based on last cost:

Product Inv Qty Unit Cost Last Inv Value
79 27 each 28.75 776.25

This example portrays a total General Ledger amount of $675.00. The Product Trial Balance of Stock Inventory Report shows an amount of $776.25, clearly indicating the difficulty of balancing the Inventory Control records to General Ledger when using last cost.

Replacement and standard cost are different than the other costs mentioned. The cost defined in the Standard and Replacement Cost fields in Product Warehouse Product Setup is used to update General Ledger and Product Warehouse Product Setup, regardless of what the actual cost was.

If balancing is performed using the standard or replacement costs, the General Ledger and Inventory Control will balance because the same cost is used to update both records.