Slow moving help (page 1)

Introduction

Part nos. from slow moving inventory can be compared with part nos. of your current project in order to look for possible substitutions of parts in order to use up inventory.  File names of BOMs from SolidWorks and SyteLine end with _sw.xls (or _sw.csv) and _sl.xlsx respectively.  By adding a third file to the mix ending with _sm.xlsx, which contains a BOM of slow moving parts, the comparison can be made by clicking the 2nd run button on the menu bar, i.e., the second green triangle icon.

Obtaining a slow moving (SM) BOM

Go to https://app.powerbi.com/ then click PC Indiana Dashboards > Inventory (on the left side menu) > Inventory Forecast.  This will lead to a page that looks like this:

inventory forecast pic

At the upper right is the "View" drop down menu.  From that pick "Actual size".  (If you don't resize to "Actual size", then the "More options" menu is difficult to access.)  Now go to the "More options" menu (the three dots, ...) and choose "Export data" and "Data with current layout".  Name the file you are exporting to anything you want except that the end of the file name must end with "_sm.xlsx", for example slowmoving_sm.xlsx.  Drag and drop this file into bomcheck.

Optional:  When you first export the SM BOM, it downloads to your Downloads folder.  Do not change the name of this file.  Bomcheck will look for for this file in the Downloads folder and use it for the SM check.  You do not need to use the _sm.xlsx extension.  You do not need to drag and drop into bomcheck.  If in a week or two you put anther file in the downloads folder, a (1), (2),  . . . , etc. , is appended to the file name.  When bomcheck looks at these file names, it will choose the one with the latest date and will delete the older files.

bomcheck menu options pic

You'll notice on the top menu bar a number of icons and buttons.  These are:

  1. Run bomcheck button:  This button runs the bomcheck program. (Does not access the _sm BOM.) It compares sm to sl BOMs.
  2. Run the slow moving (sm) function:  The button runs the comparator that compares sw (or sl) BOMs to parts in the slow moving (sm) BOM.
  3. The X, i.e. clear button:  Delete all entries from bomcheck's drag/drop list so that it can repopulated with other files.
  4. folder button:  Open the last folder used from which files were dragged.
  5. filter: This is a mask by which you can select what part nos. the comparator compares.  This filter is examined in more detail on page two.
  6. % similarity:   When the sm comparator is run, you'll see a column named "similar" in the results. The number there indicates the similarity of the text from the sw/sl descriptions to those of the pn descriptions from the sm BOM.  This similarity does not guarantee that the sm part can replace a part from the sw/sl BOM, but rather allows the user to examine those parts first since they are more likely to be a candidates for alternate parts.  In the menu's text entry box for % similarity you can enter a number to mask out lesser scoring parts.  For example, if you enter 50, then only scores of 50% and above will show in the results.
  7. on shelf:   In the comparator's results is a column named "on shelf".  This is how long that the part has been on the shelf (days).  If for example 60 is entered, then only parts that have been sitting on the shelf for more that 60 days will be shown.
  8. switches:   Check boxes by which you can filter results even more:
    1. Include "Demand" pns. The default is to filter out parts where there is a Demand for those parts.  Only a portion of the parts in inventory may have a demand.   To see if some of these demand parts are available, in SyteLine go to Time phased inventory status.
    2. Include "On Hand" pns. The default is to filter out "Qty On Hand" parts that are zero.
    3. Ignore drop list settings. Ignore the settings shown at File > Settings > drop list

Note: Whenever you hover your mouse over any of these buttons, you'll see a description of the button at the bottom of the main window.

Settings

settings pic

If you go to File > Settings, you will find the settings menu shown above.

  1. For pns check if 'Type'≠'Material':  In a SyteLine BOM there is a column named "Type".  For a part the value there should be Material.  If it is not, then this part does not get ordered.  On occasion on subassemblies that were created many years back a part may have this value set incorrectly.  This is a bug in SyteLine.  When the button is checked, bomcheck will look for this bug.  If bomcheck reports this bug then correct the subassembly by entering Material as the type.
  2. Round SW converted lengths to X decimal places:.  SW length values are rounded to this many decimal places before they are compared to lengths in a SL BOM.  Higher values mean that SL length values are expected to have values with equal or better accuracy than the SW value.  Otherwise bomcheck will mark the values as nonmatching.
  3. SolidWorks U/M:  Length values at Dekker should be set to inches.  This value is used only when using the bomcheck run function.
  4. SyteLine U/M:  Length values at Dekker should be set to feet.  This value is used only when using the bomcheck run function.
  5. drop list:  When the sm run function is activated, any items in this list are ignored.  Items should be separated by commas.   A star character, *, is a wildcard that represents 0 or more of any character.
  6. exceptions list:  Exceptions to the drop list.  That is, these part numbers will be included in results.
  7. Projects folders:   From the teams folders (VIN UWF US - PCI Sys Fldrs > Documents) create links to various folders onto your desktop via Add shortcut to My files.  Enter these folders in this field with each folder separated by commas.  With these folders entered (along with the location of the "Engineering Planner.xlsm") when results are exported to Excel, the default folder that appears to the user will be that of the Engineering subfolder of the system folder.  (If the Engineering subfolder is not already created, it will be created.)
  8. Location of file "Engineering Plannter.xlsm":  Add a link to the time sheets folder EP2 onto your computer.  That is, create a link to VIN UWF US - IPC Engineering Group > Documents > General > EP2.   In the field place the location of the file named Engineering Planner.xlsm.   This is necessary because the bomcheck program needs to know the CO number associated with a system part number.  The Engineering Planner file contains this number.

Sample output

sample of output pic

The first three columns, i.e. PN, DESCRIPTION, and COST come from the SolidWorks and/or SyteLine data.  The remainder of the columns are a result of the comparison of this data to the sm BOM.

Blank cells in the first three columns means that the first non-blank cell above a blank cell has the same value as the non-blank cell.

The primary column to consider above is the "on shelf" column.  This is the time elapsed since parts were last delivered and thus they have been on the shelf this number of days.

Implementing sm parts substitutions

Once suitable alternate parts from the sm are found, replace the parts in your assembly and drawing with these substituted parts.  If the substituted parts are significantly higher in cost, verify with a supervisor that the substitution is acceptable.

In the output from the Slow Moving parts comparison there is a column named alt qty used.   There, for the parts you are using as substitutes, enter the number of items you are substituting.

  1. Export the results to Excel.  Place this sheet in the system folder under the Engineering subfolder.  (Filename will automatically end with _alts.xlsx)
  2. If substitute parts were found, to the same folder also export a "short list";   i.e. the same report, but only showing rows that have a value in the alt qty used column.
  3. Copy the short list to the IDEM (Doc-track) folder.

In SyteLine's User Defined section, once the job is complete:

  1. DO NOT populate the "Engineering Reviewed Date" field.
  2. In the field "Character Field 3" put "Alt Item Check IDM".