Physical Inventory Count - Importing an Excel File

Objective

The Import an Excel File option in physical inventory is used to import material on a given date and adjust system inventory based on the inventory.

 

When the physical inventory count is transferred, the system generates a project-to-project transfer transaction, if necessary, and posts to the project and general ledger account identified in the group or activity specified in Configuration to adjust the inventory amount in accounting. The system also uses the configurations in the Inventory (project, activity, group) and Physical Inventory Adjustment (project, activity, group) sections in Configuration to generate the transaction.

 

Prerequisites

 

Steps

Import an Excel file for conducting a physical inventory

  1. In the physical Inventory screen, click the Import an Excel File icon.
  2. Select the Excel file to be imported and click on OK.
  3. Complete the Import tab to meet your requirements.
    1. Default Values section:

    Fields

    Description

    Date*

    Date on which inventory is taken.

    NOTE: The system displays the first date in the breakdown grid if the Excel file contains a date. If no date is specified in the Excel file, the system uses the date specified in this field to create the adjustments.

    Reference*

    Identifies the physical inventory count.

    NOTE: This field can be used as a filter when the Variances List is prepared.
    1. Remove all quantities for items not present in the import file section:
     

    If this box is checked, the system creates negative adjustment lines to reset the inventory to zero for the items that are not in the Excel file and that do have quantities in inventory in the specified location and site range. If the box is not checked, only the lines in the Excel file will be assigned.

    The Site and Location fields are mandatory if the box is checked.

    A transaction will be created by date and reference by sequential entry order in the Excel file.

     

    Fields

    Description

    Site

    Used to determine the site that will be processed to delete items.

    Location

    Used to determine the location that will be processed to delete items.

    NOTE : It is not required to specify the target location.

    To encompass all locations, just leave the source locations blank and type in ZZZZZZZ for the target location.
  4. Click on the Validate the Table icon so that the system can check the file and identify the errors listed in the breakdown grid in red.
 

You can correct the entries in the grid before performing the final import in physical inventory.
  1. Click on the Find the Next Error icon to access the next error identified in red.
  2. Click the Apply icon to transfer the Excel file to the physical inventory.
 

At this stage, the system creates the entries for the Physical Inventory. You can recall transactions to view the breakdown.

 

See also

 

Last modification: November 12, 2024