How to Complete a Physical Count with Sage 100 ERP (MAS 90 & 200) V4.40

Physical Inventory is a time-consuming yet essential task in a well-run business. In this post we cover best practices for performing a physical count, using the tools in Sage 100 ERP (formerly MAS 90 & 200), and we will highlight some of the new features of Inventory Management Version 4.4 that help speed the process along.

The Importance Of An Accurate Inventory Count
There are many good reasons to keep an accurate inventory. Perhaps the most important is your ability to provide good customer service. There is nothing worse for customer satisfaction than taking a rush order in the morning and then calling the customer in the afternoon to explain that the product is not available after all. Accurate inventory counts also enable you to write off non-existent items and reduce your tax liability. Purchasing will be more efficient, and regular counts can help you detect and minimize theft and fraud.

Preparing Your Data
Before beginning a physical count, there are a number of steps to take to prepare your data: posting transactions, clearing the variance register, running the negative tier report, and running the inventory balancing utility.
First, make sure all transactions affecting inventory have been posted, including those from the Inventory Management, Sales Order, Purchase Order, Return Merchandise Authorization, Bill of Materials, and Work Order modules. Preview and if necessary clear the Physical Count Variance Register to make sure there is no data left over from previous physical counts.

Next, run the Negative Tier report. This report identifies over-distributed tiers that need to be resolved. Over-distributed tiers exist when sales or issues are made for items prior to processing the receipts. Reconciling these tiers should be a regular part of your Period End Processing procedures, but it is a good idea to clear them before physical count as well. Print the Negative Tier Report and the Inventory Valuation Report and compare them to find positive tiers that can offset negative tiers. If none are available in a particular warehouse, use Inventory Transaction Entry to transfer or adjust quantities as needed. Then run the Negative Tier Adjustment Task to automatically apply the positive tiers to the negative tiers. Next, run the Inventory Negative Tier Report again to verify that no negative tiers remain.

Finally, run the Inventory Balancing Utility; it identifies discrepancies between the warehouse detail file and the cost detail file. Data damage in either file can cause the on-hand quantity information to be inaccurately reported. Always make a backup before running any utility program. To run the utility, from the File menu type the following: IM_BALANCEQTYONHAND_UI.

After the utility runs, a report will be produced showing any discrepancies. You can then choose whether to use the quantity on hand in the warehouse detail file or the cost detail file, and the system will automatically correct discrepancies. Your choice depends on a number of factors such as the valuation method of the item(s). The inventory balancing utility should not be used to correct the out-of-balance condition if:

  • Item’s valuation is Standard or Average Cost.
  • The Stock Status Report is correct and the items are Lot or Serial.

For more information refer to resolution ID 626 in the Knowledgebase, and Help menu > Help Topics > Correct out of balance inventory items. Please give us a call if you would like assistance with this procedure.

Physical Count Process
Now you are ready to run your physical count. There are four steps to the process.

  1. Print the Physical Count Worksheet.
  2. Count the items and record the quantities on the worksheet.
  3. Enter the variance in Physical Count entry.
  4. Print and update the Physical Count Variance Register.

Physical Count Worksheet – On the Physical Count Worksheet selection screen there is an option to print Report Settings. Printing report settings makes it easy to select the same settings in Physical Count Entry so that they match, to make data entry faster.

New options for the worksheet in Version 4.4 allow you to:

  • Sort the report by bin number.
  • Choose whether to print the on-hand quantity on the worksheet.
  • Select items for counting by product type and/or procurement method.
  • Select to have the system quantity default to the quantity on hand, so only variances require an entry on the worksheet.

When you print the Worksheet, the system freezes the items, keeping a record of the current quantities in Inventory Management, Sales Order, Purchase Order, RMA, Work Order, and Bill of Materials. In previous versions, only Inventory Management was frozen.

Physical Count Entry – New features in Physical Count Entry make this process much faster. The Warehouse field no longer defaults to default Warehouse, and there are new sort and selection criteria similar to the worksheet. The Sort Lines By option defaults to the selection from the Physical Count Worksheet. Plus, in Version 4.4 you have all the flexibility and power of the Business Framework grid entry, as well as a quick row selection field that allows the person entering data to quickly navigate to rows requiring edits.

Physical Count Variance Register – The Physical Count Variance Register now includes sort options and search criteria similar to the Physical Count Worksheet.

Choose a quiet time of day to update the Physical Count Variance Register and do not interrupt this process. During the update, the quantity on hand in the Inventory Item Warehouse file is updated, the average cost is recorded in Inventory, Lot/Serial and LIFO/FIFO items are updated in Item Cost file, and the Physical Count detail is recorded in the Item Transaction History file. In addition, a GL Transaction Register is created with the cost amounts associated with adjustments.

Cycle Counting
Performing a physical count on all your inventory at once can be burdensome and bring all other activity to a halt. We recommend you manage inventory by counting in item subsets. The physical count worksheet allows you to select items to count according to their Inventory Cycle code entered in Item Maintenance. Physical count can be done in smaller chunks that are easier to accomplish. This way, you can count high value or high volume items more frequently. Make certain that you count all inventory items within the year. Give us a call with your questions about the physical inventory count process.