on-hand inventory

SMARTSystem Management Training Series

Training Session #7A – SMARTSystem Inventory Reconciliation

Purpose:   How to use Inventory Reconciliation.

Keywords:  The following is a list of key terms used in the SMARTSystem that you should be familiar with and understand.

Inventory Reconciliation – A process of updating the computer with physical inventory counts.

Data Grid – Is a type of display screen used with Inventory Reconciliation.

On-Hand Inventory – Inventory in the computer that represents what is in the cooler/warehouse. On-Hand includes the On-Hold quantity.

On-Hold – An inventory quantity set aside in the computer and removed from the Available Inventory for sale by the sales staff.

Available Inventory – On-Hand Inventory less On-Hold Inventory is the Available Inventory for sale.

Sales Orders – A Sales Order contains product scheduled for delivery to the customer.  All products on a sales order have been removed from the On-hand inventory in the computer and ready for delivery/shipping.

Pre-book Orders – A Pre-book represents product scheduled for future delivery and these products have not been removed from the On-Hand inventory in the computer.

Invoiced Orders – Sales Orders invoiced for delivery to the customer.

Pre-packs – Products in the cooler that have been packed and staged for delivery/Shipment. This needs to be counted if using the “Subtract Open Orders from Quantity” option in Inventory Reconciliation.

Cooler Count – Quantity of product in the cooler that is not invoiced and would not include Pre-packs.

Open Orders – Products on Sales Orders (not invoiced) that have been removed from the On-Hand Inventory Balance.

Quantity – Cooler count quantity used on the Inventory Reconciliation Screen which is uploaded from an Excel spreadsheet or scanning device or entered by hand.

Net Avail – Net Avail (Available) is the Cooler Count, less Open Orders. This is will be the new On-hand Inventory quantity after reconciliation.

Auto-Hold – This is a special Growers process usually run by the Sales Manager to reserve products for fulfillment of Pre-book orders. This is reserved by putting the products On-hold through the date selected in the program. Wholesalers use Pre-Book Allocations to obtain this result.

SMARTScan – Is a specially written series of programs that run on the Motorola MC-3190 hand-held barcode scanner below.  These programs directly integrate with the SMARTSystem and can operate in a wireless environment.

Overview:

The purpose of this document is to detail how to create multiple Excel worksheets that can be used for physical inventory cycle count without causing conflicts during the reconciliation process.

Note: This process is designed to work with a handheld scanner and this procedure defines how to use it manually without the scanner.

Select Inventory Reconciliation by using the following Menus:

                   INVENTORY > INVENTORY RECONCILIATION

Creating Worksheets for Inventory Counts:

To create a worksheet of inventory records, open the Inventory Reconciliation program and click the Manual Reconciliation check box:

After the Manual Reconciliation box is checked ALL of your inventory products are automatically selected and displayed or you can identify specific products from the options provided (Category, Item, etc.):

Confirm selection for specific products then click the Find button to populate the data grid.

 

After you have clicked the Find button, click the Create Excel Worksheet button to create the spreadsheet that will be used for entering your physical inventory counts.

When you click the “Create Excel Worksheet” button the following screen will appear:

NOTE: The “Export to Excel” button will only appear if your computer has Microsoft Excel installed.

Click the “Export to Excel” button to save the Excel Worksheet to your hard drive. 

The Excel file is created as an Excel file in XML format.  Older versions of Microsoft Excel may not be able to read this format.

You are now ready to enter your inventory into your new spreadsheet.

Capture you inventory count for this cycle and when complete go to the next step.

Safely Recording Inventory Data:

This section will describe how to safely import the Inventory Records using Inventory Reconciliation and the Excel spreadsheet created using the procedures defined in “Creating Worksheet Records” above.

Before executing this procedure, please confirm that there is no inventory receiving activity being run on any other workstation.

To safely reconcile the inventory data follow these steps:

Make sure that the Excel Spreadsheet containing the data you intend to bring into Inventory Reconciliation is copied onto the computer that is going to run the Inventory Reconciliation before you start this procedure.

Open the Inventory Reconciliation program and click the Manual Reconciliation check box:

After the Manual Reconciliation box is checked select the inventory being processed from the options below, as done in the create worksheet section above:

Confirm selection for specific products then click the Find button to populate the data grid.

Click the “Get Data From Excel” button to select the Excel or Excel XML file that contains the inventory data to be reconciled.

The file selection dialog will allow you to navigate to the file containing your inventory records.  If you are importing from an Excel XML file you will need to change the file type to “Excel XML all version” in the file type selection drop down in the file selection window.

Select the file you want to reconcile and click Open:

After you click Open, the following screen will be displayed indicating how many records were found in the file you selected.

If there is a product code in the import file that is inactive or invalid in the SMARTSystem inventory table the following message will be displayed:

All products that are inactive or invalid in the SMARTSystem will be added to the exception report that is displayed after the file is completely loaded.

After the file is completely processed an exception report will be displayed along with a dialog box indicating how many records were processed. The number of records processed includes records on the exception report and records that were successfully matched to the active inventory.

 

The exception report shows all of the products found in the file that could not be found in the SMARTSystem database. These products will need to be corrected (modified) in order for them to be reconciled.

After the import process is complete you can easily check the quantities that were imported by sorting the inventory data grid by the Quantity column in descending order.  To do this, click the Quantity Column Header twice to bring all of the numbers to the top of the list.

Once you are satisfied with the inventory quantities, and you are 100% sure that no one is running Inventory Receiving or Reconciliation. Click Save to reconcile the values in the list.  Blank Quantities are ignored.

Once the Excel Spreadsheet has been imported into the Inventory Reconciliation program you must click SAVE to update the inventory quantities. If you do not click save, no adjustments will be made to your inventory.

When SAVE is selected a copy of the data before processing will be saved on an excel spreadsheet.  The spreadsheet will be saved automatically in C:\WFSDATA\Excel with a file name

Inventory Reconciliation – MM-DD-YYYY_HH-MM.xlsx

Note: Excel is installed on the PC you are using

Subtract Open Orders from Quantity:

This feature was added to provide a way to compensate for product that is in the cooler and being counted, but already sold on sales orders.  The reason this was done is that when a Sales Order is created in the SMARTSystem the product is immediately removed from inventory; however, some of our users do not pick the order the same day it is entered.  This creates a condition where some of the inventory in the cooler is not shown in the SMARTSystem.

To handle this condition the “Open Orders” column was added to show the quantity of each product that is currently on Sales Orders that have not yet been invoiced.  The “Subtract Open Order From Quantity” feature was added to subtract the quantity of product on sales orders that have not been invoiced from the physical count entered into the Inventory Reconciliation program.

This feature is selected by checking the “Subtract Open Orders From Quantity” box:

When this box is checked a new column is displayed to show the New Available quantity that inventory will be set to after subtracting out the Open Orders. This will become the new “On Hand” quantity when the Save button is clicked and the inventory is reconciled.

Explanation of this example:

There are currently 10 on hand and 16 on open orders.  This means that there 10 units available for sale before the reconciliation and 16 items that have been sold but are not yet invoiced.  When the “Subtract Open Orders From Quantity” box is checked the value of 16 will be subtracted from the quantity entered into the Quantity box to yield the correct New Quantity Available to sale (26 – 16 = 10).

Add Quantities to Available Balance:

This feature was added to allow the Inventory Reconciliation program to be used for bulk inventory receiving.

Effective June 13, 2013 you are allowed to enter a negative quantity, for example “-25”

To use this feature check the “Add Quantities to Available Balance” box:

When this is checked all quantities entered will create new receiving lots for the product.  After checking the box simply enter the quantity of each product that needs to be added to inventory and click SAVE.  This will increase the available quantity by the amount entered into the quantity box.

“Subtract Open Orders from Quantity” feature is disable when “Add Quantities to Available Balance” is checked.

Manage Staged SMARTScan data:

This feature was added to provide a way for SMARTScan users to preview and modify the data collected by the SMARTScan software before reconciling it into the live inventory.

This feature is now a mandatory step between retrieving the data from the SMARTScan devices and reconciliation. 

The SMARTScan procedure has been changed as follows:

1. Cradle the SMARTScan device.
2. Click the “Get Data from SMARTScan” button
3. Respond to this prompt

   

   1. Clicking “No” will cancel the operation
   2. Clicking “Yes” will cause the SMARTSystem to communicate with the SMARTScan device and retrieve its Inventory Data.
   3. This step can be repeated for each SMARTScan device and all of the data from the SMARTScan devices will be stored in the Staged SMARTScan Data table to be managed when you are ready
4. After the SMARTScan data is collected from the device it is then stored in a Staging area to be reviewed and edited before being reconciled.

   

   1. This data can be edited, saved and recalled at a later time.
   2. All fields on this screen can be modified by the user.
   3. Rows can be deleted by selecting the row and pressing the delete key.
   4. ONLY the rows that are marked as “Selected” will be brought into Inventory Reconciliation.
   5. After making changes the user can choose from the following buttons:
      1. “Save Changes” to save the work they have done
      2. “Exit” to exit the program without saving changes
      3. “Export to Excel” to create an Excel Spreadsheet from the Staged SMARTScan Data in its current form.
      4. “Move Selected Items to Inventory Reconciliation” to import all “Selected” records into the Inventory Reconciliation program to be reconciled when the user clicks the “Save” button in the Inventory Reconciliation program.
         1. Note – this process purges the Staged data at when it is clicked clearing the system to be ready for the next Inventory Reconciliation Cycle
         2. The user is presented with a final opportunity to modify the data in the Inventory Reconciliation program before clicking “Save” to reconcile the inventory from the SMARTScan devices.

Questions and Answers

If you have any questions or need support, please contact us at Support@Floralcomputer.com or call 727-483-5306for assistance.

SMARTSystem Management Training Series

Training Session #4B – SMARTSystem Inventory Quantity HOLD Options

Purpose:   To provide options to place inventory quantities on HOLD.

Keywords:  The following is a list of key terms used in the SMARTSystem that you should be familiar with and understand.

• On-Hand Inventory – Inventory in the computer that represents what is in the warehouse. On-Hand includes the On-Hold quantity.
• On-Hold – is an inventory quantity set aside in the computer and removed from the Net Available Inventory for sale by the sales staff.
• Net Available Inventory – On-Hand Inventory less On-Hold Inventory is the Net Available Inventory for sale.

Training Areas for this Session:

Placing Product On-Hold

There are several ways that product quantities can be placed On-Hold.  These include:

1. Under the Management Menu, the sub-menu Inventory Management program allows the placement of HOLD quantities on specific inventory lots received.
2. Pre-books allocated to Purchase Orders when received are automatically placed On-Hold to prevent them from being sold in your open market inventory.
3. The Inventory View provides access to place a quantity On-Hold when you right click the product and select View Inventory Detail Items from the Inventory.

4. The product on an entire Purchase Order can be put On-Hold by clicking the “Hold Order” button on the Purchase Order Entry screen.
5. For Growers: the sub-menu under the End-of-Day Menu, Auto Hold for Pre-Book Orders can be run by the Sales Manager to automatically place inventory product entered into the SMARTSystem from harvest to anOn-Hold status based on the delivery date to customers.

Practice Drills

The purpose of Practice Drills is to get you more familiar with the SMARTSystem and comfortable using it.

1. Login into SMARTSystem.
2. Select the Inventory View and locate 1 product to place product on HOLD.
3. Go to the MANAGEMENT Menu, select Inventory Management and search for the inventory item, in this example the product is ACACIA. Enter 50 into the *Qnty On Hold field as shown below:
4. Click SAVE at the bottom of the screen to save your entry:
5. When you click SAVE the following message will appear when all HOLDS are SAVED.
6. Go back to the Inventory View and verify that 50 items are on HOLD.
7. Another way to set and clear HOLDS is to right clicking the product line and select View Inventory Detail Items:
8. Set the On Hand Hold Quantity (highlighted below) to zero
9. Click SAVE to update the On Hand Hold to zero and see message confirming the change was saved:

Questions and Answers

If you have any questions or need support, please contact us at Support@Floralcomputer.com or call 727-483-5306for assistance.

 

SMARTSystem Management Training Series

Training Session #4A – SMARTSystem Inventory Maintenance

Purpose:  To gain a working knowledge of the Inventory Table and maintenance of inventory items.

Keywords:  The following is a list of key terms used in the SMARTSystem that you should be familiar with and understand.

• On-Hand Inventory – Inventory in the computer that represents what is in the warehouse/cooler. On-Handincludes the On-Hold quantity.
• On-Hold – is an inventory quantity set aside in the computer and removed from the Net Available Inventory for sale by the sales staff.
• Net Available Inventory – On-Hand Inventory less On-Hold Inventory is the Net Available Inventory for sale.
• Product Type – Is similar to Product Category but used to establish margins for each Customer Sales Level and when printing the Pick Ticket. Pick Ticket products are organized in groups by product type.
• Unit of Measure – Is the standard of measurement for physical quantities

Training Areas for this Session:

Inventory View

The Inventory View provides user access to Inventory Items and the purchase order detail items.  Select the Inventory Main Menu > Inventory View and the following screen shows a list of Inventory Items.

Initially, the screen will display all products in alphabetical sequence by Product Description and as shown below the information can be filtered by Category, Sub-Category and Subset:

When you highlight an inventory line item you can:

• Double click to open the detail view for that inventory item or
• Right click, to show options to Open Detail View or View Inventory Detail Items.

This is a view of inventory detail from purchase orders or inventory receiving:

Below, the checkbox in the upper right is used to display only inventory with On-Hand balances.  To show all inventory items, un-check the box.

           

            

You may have a need to export your displayed inventory items to excel.  If so, you can include additional information when you check the second box to “Show Category, Sub-Category and Image Links.”  Image Links are links to your pictures associated with the inventory item. This button, at the bottom of the screen, will export inventory items to excel:

          

Selection options to display inventory items on the Inventory View are:

• Primary Vendor
• Category
• Sub-Category Filter
• Subset Filter (used for color, variety, etc.)
• Product Code/SKU (partial code entered will list all matching)
• Description Search
• MFG Code Search

The bottom of the Inventory View screen will:

• PRINT a list of items you have selected
• Create a NEW inventory item
• View Inventory Detail Items & Enter Adjustments
• Export to Excel
• Export IFD Data
• Clear Searches
• Refresh Screen

Inventory Item Maintenance

Inventory Items are the focal point of the SMARTSystem.

By selecting the Inventory Menu you can access your Inventory using the Inventory View or Inventory Entry.  The Inventory Menu lists many sub-menu selections to manager your inventory items.

There are certain pre-requisites that are required for inventory maintenance to function smoothly.  These pre-requisites on the Inventory Menu and include:

• Product Type
• Product Category Filter
• Product Sub-Category Filter
• Product Subset Filter
• Product Size
• Unit of Measure View
• Unit of Measure to create Associated Products
• Item LocationSelect the following from the Purchase Orders Main Menu:
• Vendor

• Buyer

On the Inventory Main Menu, select Product Filter Maintenance to access most of the items listed above:

The Inventory Table Maintenance program has 6 tabs.  These tabs are:

• General Info
• Inventory Info
• Vendor Info
• Accounting Info
• Customs Info
• Product Picture

General Info Tab:

The General Info Tab provides for the basic, minimum setup information needed for a new Inventory Item as illustrated below.

Required fields to create an inventory item are:

• Category
• Sub-Category
• Product Code
• Description
• Product Type
• Purchase Unit of Measure
• Purchase Item Quantity
• Selling Unit of Measure
• Selling Item Quantity

Each Inventory Item has specific filters assigned to it for quick access to inventory information.  These filters are Category, Sub-Category, Subset and Size as shown. 

Each of these filters are pre-requisites and if you are in the middle of creating an inventory item that does not have a filter defined, select Product Filter Maintenance from the Main Inventory Menu and create it. After it is created, select the Refresh List button (upper right) to make it available in the drop down menu.

The “Generate Product Code” button will be highlighted for a new entry.  If you click it, the Product Code for this inventory item will be created from the codes used from each of the filters you selected. Normally, this button is not used and you enter a Product Code with a maximum length of 14 characters.

The Product Description can be up to 50 characters, but its best to keep it under 35 characters for easy readability on screens and reports. 

You can set a color background for each description by clicking the “Select Background Color” button and selecting a color.

The Product Type has two main purposes:

1. To establish the percent of Mark-up or Margin for each inventory item, and
2. To organize products by Product Type printed on the Pick Ticket.

The SMARTSystem will calculate your preferred Mark-up or Margin based on Product Type specified for each inventory item and apply that margin to Customer Sales Price Levels. Each customer is assigned a Sales Price Level.   

Typically, your Sales Price Level 1 would be your most widely used margin down to Price Level 5 as your lowest margin.  Only Price Level 1 is required. See  example below:

Products on the Pick Ticket are organized by Product Type.  In the example below, Fresh Flowers and Supplies are Product Types.

Next, the Units of Measure are defined in the Units of Measure Table as illustrated below:

In the Inventory record as illustrated below, the Purchase Unit of Measure is the default box size defined in the Unit of Measure Table and will be used to automatically create purchase order entries when the option is selected.  Associated with this entry is the default Purchase Item Quantity showing the number of items, stems, pieces, etc. that would normally be packed in the Purchase Unit of Measure entry.

Selling Unit of Measure and Selling Item Quantity defines the way you sell product:  stems, bunches, each, etc. and the quantity.  For example, Roses sold by bunch containing 25 stems. 

Units per Tag are used to calculate the correct number of tags to print.  A Tag or Sales Label is used to tag the product set aside for customers.  The following is a Tag without bar code:

  

The following check boxes are used to set characteristics of each inventory item:          

Current Landed Cost is updated when a purchase order is received or costs are entered when using Inventory Receiving program for non-purchase order items.  

Average Landed Cost is updated depending upon the cost options specified in System Control.  Select Menu Control Info menu > System Control > Options:

Sales Price Levels 1 – 5 are updated automatically when the PO is received unless you have Manually Updated Sales Prices set in System Control:

When Manually Update Sales Prices are unchecked, the purchase order receiving process will update Sales Price Levels 1 – 5 based on your Price Level Factors in the Product Type for each inventory item.  Otherwise, when the checkbox is set, you control the Sales Price Levels manually.

The Buyer Code is used to identify who is responsible for buying this product.

Retail Price (for Labels) is used for retail price labels.

Print Receiving Labels checked on will force the printing of box receiving for each purchase order received.  There is an option in the purchase order to set this option when you want to print Receiving Labels for boxes.

Inventory Info Tab:

There are no required fields on this screen:

Item Locations are defined in the Item Location Table.  Using the drop down menu you can select the location of this Item.  Product Shelf Location and quantity can be updated automatically using the 3190 wireless scanner for physical inventory counts.

Minimum and Maximum Stock can be updated using a spreadsheet or on this screen.  These fields are used in several Buyer Reports.

The On Hand, On Hand Hold, and On Order are automatically updated and you cannot change this information.

Cur. Purchase Item Cost, Cur. Freight Cost Per Item, Avg. Purchase Item Cost, and Avg. Freight Cost Per Item are automatically updated when a purchase order is received.

The Costing Method field is not currently being used.

UPC and GTIN Codes is entered and stored in these fields. There are several ways to capture this information, includingInventory Reconciliation.

Item Classification will default to “Stock Item” if not entered.

Old Inventory Code, Substitution, Special Note Information, Special Order Information and Default Shipping Description provide for free form entry and are not restricted.

Vendor Info Tab:

This tab is used primarily for hard goods.  The Preferred Vendors are identified on this screen. There are no required fields on this screen:

Accounting Info Tab:

These are required fields on this screen to create an inventory item:

• G/L Sales Account
• G/L Inventory Account
• G/L COGS Account

G/L Adjustment Account

Select the Control Info Main Menu to select and set G/L Accounts.

This accounting information can be set manually for each inventory item or globally.  Global assignment can be found under the Management Menu, sub-menu Assign Inventory GL Accounts.

Customs Info Tab:

The Customs Info screen is used when you are clearing your own flowers.

There are no required fields on this screen:

Product Picture Tab:

Pictures can be captured and additional product information entered.

Before pictures can be selected, SMARTData Services must be installed.

Additional Product Information is a large area for text and normally used with the e-commerce SMARTSite.

There are no required fields on this screen:

Refer to Management Training Series #12 for more detailed information about Pictures.  Some of our preferences for pictures are:

• The preferred format is .jpg or .bmp.
• Must have URL assignments in:
  • Control Info > System Control> Doc & Dir Config
  • Add the URL’s for data and the root then SAVE:
    • •  Create a New Inventory Item using CopyAn easy way to create a new inventory item with a minimum amount of data entry is to select a similar inventory item, bring it up for display and click the “Copy to New Item” button.

         

        This will duplicate all of the inventory information except you will be required to enter a Product Code and Description for the new item you are creating.

    Practice Drills

    The purpose of Practice Drills is to get you more familiar with the SMARTSystem and comfortable using it.

    1. Login into SMARTSystem
    2. Go to the INVENTORY Menu, select INVENTORY VIEW to search for a specific inventory item and when found double click it.
    3. Click through each tab and get familiar with the content on each screen.
    4. Add a new Inventory Item.

    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.