Inventory Valuation Report Balancing
ProfitMaker w/Fulfillment
Objective: Provide an overview of Inventory Valuation Report balancing for ProfitMaker w/
Fulfillment. This information is based on the standard printing of the report.
Terminology
Unvouched Receipts
Inventory items that have been received and not yet vouched.
Pending Receipts
Inventory items that have been vouched and not yet received.
Customer Unbilled Qty
Customer owned inventory that has not been billed. Requires use of Prebilling to accurately balance the reportto the G/L.
Customer Billed Qty
Customer owned items that have been billed. Requires use of Prebilling to accurately balance the report to the G/L.
Data File Analysis
Compmast, Itemmast, Apocost, Purchord, Aoprecs and Ffxifo are the primary files being used to generate the report. Much of the processing is performed within temporary files based on the data files listed.
Report Section 1
Refer to highlighted section in the following report.
Primary files used in this section are Itemmast, Compmast and Ffxifo. The Item, Sub, Item Cust, Own By, Description, On Hand, Type and Standard Cost are based on the Item Master (Itemmast and Compmast). The actual cost is based on the Lifo/Fifo record (Ffxifo) and is determined by calculating and accumulating for each record. Please note that this value is used for the ending report value prior to the G/L summary page.
Common problems
The on hand shows –0- but actual shows a value. This is normally caused by two or more Lifo/Fifo records where the on hand shows a positive and a negative that net to –0-, but the costs associated with each record do not match exactly.
Value extends out incorrectly due to improper Per.
Actual Cost is incorrect.
On hand is incorrect.
To correct
To correct the “On Hand” showing 0 with Actual Cost showing a balance: Use the Inventory Adjustments count option. For incorrect Actual Cost use Record adjustments/Breakages/Rejects and select Cost Record. For incorrect On Hand quantity: Use Record Adjustments/Breakages/Rejects. For Client Stored use Client Stored Inventory Processing.
Report Section 2
Refer to highlighted section in the following report.
Primary files used are Apocost, Purchord and Aoprecs. These contain the details for the Unvouched and/or Pending Receipts. Each P.O. Cost History record is analyzed to determine if it is considered an Unvouched or Pending Receipt.
Unvouched Receipt is generally correct if the P.O. Cost History record received qty is greater than the vouched qty and the P.O. Cost History record is not marked vouched complete.
Pending Receipt is generally correct if the P.O. Cost History record vouched quantity is greater than the received quantity and the Purchase Order/P.O. Cost History record is not marked received complete.
FF DS Cost is very similar to a Pending Receipt. There is an additional check to see if the P.O. Cost History ->Billing Status field is empty before determining if it is to be considered.
Common Problems
Unvouched and Pending Receipts showing that are incorrect. Quantities and values that are incorrect. Many others too numerous to list here.
To Correct
Generally the problems relate to P.O. Cost History records that were not maintained properly during Receiving, Vouching or Billing. In most cases the operator can use PO Cost History Maintenance to correct the situation by modifying the record or deleting the record. There is also an option within Company Information-Inventory that allows entry of a Date to Begin Unvouched Receipts Calculation. Only P.O. Cost History records created with a date greater than the date entered in Company Information will be recognized.
Note: PO Cost History Maintenance does have a Complete action button that can be used to mark the record completely Vouched, Received and/or Billed. Options are only enabled for the relevant possibilities. Ie. If the record does not relate to a FF or PB order then marking the record Billed Complete is not allowed.
Report Section 3
Refer to highlighted section in the following report.
Primary files used are Ffxifo and Apocost. There must be items setup as Customer Owned for report section 3 to show up on the Inventory Valuation Report. The On Hand is based on cost (Lifo/Fifo) records. The Billed and Unbilled is only in regards to Customer Owned Inventory. To determine if the cost record is Billed or Unbilled the P.O. Cost History Record for that specific Lifo/Fifo record is checked for the billing status. If the Billing Status has a “B” the Lifo/Fifo On Hand value is considered Billed, otherwise it is Unbilled. Ext cost is based on the cost record’s extended values.
Common Problem
The incorrect On Hand may display. This is generally due to the cost record not having a corresponding P.O. Cost History record. A valid corresponding P.O. Cost History record is based on the Item, Sub, Item Cust, P.O. and Lot# Values from the Lifo/Fifo record.
To Correct
If the problem is due to the P.O. Cost History record not existing, the operator will need to create that record. When the values don’t match it will normally be due to a rounding error and should be small enough of a difference to ignore. Large differences should be reported and researched with ASICS assistance.
Common Problem
The On Hand and Customer Billed quantities do not match.
To Correct
View the Item Master’s cost records and note the P.O. number and quantity for each record. In P.O. Cost History Maintance, review each record. Each record is used to determine the quantity based on Received – Shipped + Adjusted. Correct the quantities so each P.O. Cost History record matches the cost record.
Note: PO Cost History Maintenance does have a Complete action button that can be used to mark the record billed.
Report Section 4
Totals are based on accumulated values from Report Section 1. The sub-categories found in Report Section 2 – 3 are not relevant to the totals. Totals are purely based on the extended Lifo/Fifo record costs.
Report Section 5
Totals are based on various data elements from Report Section 1 – 3. Basically three types of accounts are balanced.
Inventory (136) is for House Owned Items or Customer Owned Items that have been received but not yet billed.
Customer Owned Inventory (137) is for customer-owned items that have been billed to the customer.
G/L Total is from the General Ledger (Aglbal) for the current period.
For House Owned Inventory G/L Accounts:
House owned items will accumulate into the report total by the account number listed next to the record printed in Section 1. This account is controlled by either Company Information – Inventory for the Inventory G/L Account or by the Program Master setup (requires that the Program # is entered on the general tab in Item Master Maintenance) or by entering an account number in the Item Master on the Order Information Tab. Both the Unvouched and/or FF DS Cost/Pending Receipts that appear on a House Owned inventory item will be accumulated into their respective columns. The Imbalance is a calculation that takes the Report Total – Unvouched Receipts + FF DS Cost/Pending Receipts – G/L total .
An inventory purchase order is entered to restock house owned items. When the item is received it creates an unvouched receipt against the Inventory G/L account (136). The value of the item is included in the report total . When the vendor invoice is vouched it will be vouched to account 136. At that time the G/L total will increase and the Unvouched Receipt will decrease, but the Report Total will not change. When the item is sold it will reduce the inventory G/L and the report total.
For Customer Owned Inventory G/L Accounts.
Customer owned values are a little more complicated because part of the values will appear against House Owned Inventory and part of the values will appear against Customer Owned Inventory.
Note: For the purpose of this example, all Inventory items are vouched to the G/L for House Owned Inventory (136) regardless of ownership.
A Prebill Order is entered to purchase the Customer Owned Items and bill the corporation. The Prebill order creates an Inventory Purchase Order for Receiving and Vouching. When the merchandise is received it becomes an Unvouched Receipt against the House Owned account (136). When the Inventory Valuation Report is printed the items will show as a Customer Unbilled Quantity which will cause it to appear in the Report Total for the House Owned Inventory G/L account. When the vendor invoice is vouched it will be Vouched to account 136. At that time the G/L total will increase and the Unvouched Receipt will decrease, but the Report Total will not change. Hence we are still in balance.
The next logical step is to Bill/Ship the Prebill order. During Bill/Ship a transfer of inventory cost for the prebilled item is performed through a G/L entry from the House Owned inventory account to the Customer Owned inventory account. The Valuation report will print the value as Customer Billed quantity and will be part of the G/L summary Report total for Customer Owned Inventory.
Later as the Customer Owned merchandise is shipped both the Customer Owned inventory account and