Wiki

Case Status
Log In

Wiki

 
Creating Price Lists
  • RSS Feed

Last modified on 12/14/2018 1:20 PM by User.

Tags:

Creating Price Lists

 

(Advanced User Features)

Getting Started: The Concepts

OASIS has a very powerful pricing database with many features to help not only the CSRs and quoters, but individuals managing price lists within the system. Many of the price list management features are discussed below:

  1. Importing different file formats
  2. Mapping manufacturer data into OASIS price list fields
  3. Naming Levels and Working with OPath and QPath
  4. Using price exceptions
  5. Using part categories

Importing Different File Formats

When requesting a price list from a manufacturer, ask for one of the following technologies:

  • “Excel format” or “.xls” (referring to Microsoft’s Excel)
  • “Comma separated format” or “CSV format” or “.csv” (readable by most spreadsheet programs)
  • “Tab separated text file” (the old CaHill format is an example of this format)
  • Fixed width files.

Using a spreadsheet program, it is possible to import data from a wide array of sources into OASIS. We will refer to Microsoft’s Excel program in this document. (Please contact Ingen Software if you do not have access to such a program.)

“Excel format” or “.xls” (referring to Microsoft’s Excel)

Most spreadsheet programs can “import” a spreadsheet created by another program. Regardless, the following steps will need to be performed before proceeding:

  1. Remove all images
  2. Remove all headings
  3. Remove all formatting (e.g. use the “general” cell format in Excel)
  4. Align common data in the same columns (e.g. ensure “part numbers” are all in the same column and “pricing” is in another column).

“Comma separated format” or “CSV format” or “.csv”

Most spreadsheet programs read “CSV files” (having a .csv file extension) directly without any problem. See the “Excel format” notes above once the CSV file is loaded.

“Tab separated text file” (old data systems typically use tab or fix field formats)

Most spreadsheet programs are able to import tab separated files. Here is the process for Excel:

  1. Obtain the file and save to “My Documents”
  2. Open Excel then select “File” the “Open”. The following window will appear:
  3. Select “Delimited” and press “Next”:
  4. Select “Tab”. In some cases, the text qualifier may be needed. Just select other options until the text is formatted properly in columns:
  5. Press “finish” and the spreadsheet should appear:

Refer to the “Excel Format” section above to complete the import.

Mapping manufacturer data into OASIS price list fields

OASIS is able to track a number of different data elements for each fixture. All that is required is the part number (pricing is also optional):

FIELD Description
Part (REQUIRED) The part/catalog number
Description Description to use on quotes and orders
Book Book (list, distributor net) price. Will appear in the "Book" column on quotes.
Price 1 Price where agency receives "Comm 1" commission.
Comm 1 Commission agency receives for price 1.
Price # "Price 2" through "Price 15" work just like Price 1.
Comm # "Comm 2" through "Comm 15" work just like Comm 1.
Cost When reselling this item, the agency will purchase at this price.
CartonHeight Used by the inventory application in OASIS - carton height
CartonLength Used by the inventory application in OASIS - carton length
CartonWidth Used by the inventory application in OASIS - carton width
EffectiveEnd Information only - end date for part pricing
EffectiveStart Information only - start date for part pricing
FreightClass Used by the inventory application in OASIS - freight class
Local Inv. Local inventory count onhand and not reserved
MFGQuote MFG quotation number (for pricing)
MFG.Inv. Manufacturer inventory count onhand (where available).
Part Quantity Quantity "label" - E=Each, FT=Foot, CS=Case, etc
Product Code Manufacturer product code
Program Manufacturer promotional program (e.g. shipping program)
SKU SKU - for labels.
Standard Carton Item count per shipping carton
Stock Stocking terms (e.g. Stocked, Custom Build, etc)
Sub. Notes Substitution notes. Use this for "extra information" about the product.  When part is used the information will be shown in a popup. (Character limit of 255)
UPC UPC - for labels
UQ Unit quantity - pricing is per unit quantity: e.g. $10 per thousand where 500 units would equal $5 extended. The field has the following options: "E" for each, "C" for pricing per 100, "M" for pricing per thousand. These can be customized by going to Manufacturers in Oasis, then File > Configuration, then to the Unit Quantity tab.
Unit Weight Weight per item

Naming Levels and Working with OPath and QPath

The “Level Names” tab of the Price List editor has a number of very powerful features used to fully automate the pricing of products on quotes and orders:

Level 1 – Level 15

This section of the screen names the levels (the name of the level is displayed for end users when accessing the price list), provides a manufacturer quote number and codes the column as:

  • Price – just a price, no special function
  • Overageable – Only one allowed per price list. Data from this column is automatically copied to the "OBase column on the manufacturer and rep versions of OASIS.
  • Base Calc - in the manufacturer and rep versions, the column sets the "agent lowest price". The overageable column is the high price. The commission and price of the two columns form a sliding scale, allowing a rep or manufacturer to key a price and get the commission (e.g. in a quote) or key a commission and get the price.
  • Territory – (Do not use) - currently has no functionality

Standard manufacturer pricing is normally “coded” as a price field and is copied directly into OASIS as described previously. Special hot sheet price columns are normally “coded” as a “territory” price within OASIS.

It is very important to label the “overageable” price column (for many manufacturers, this is the highest commission column). This value is directly copied into the quote “OBase” column when priced by the system. Any sell price over OBase is assumed to have overage at the standard split for that manufacturer.

Some agencies take an extra step and create a “sparse” (not all fixtures priced) column comprising prices from different commission level. The result is a price column that maximizes profits for the agency and manufacturer.

QPath and OPath

Once the pricing is loaded into OASIS, two different rules are used to when pricing an item:

  • QPath – Order to search price columns for a non-zero price for a quoted item
  • OPath – Order to search price columns for a non-zero price for an ordered item.

Consider this: a 5%, 7%, 10%, Hot Sheet, and Agency defined pricing as shown above. The agency does not want to use the Hot Sheet unless forced to do so. Additionally, the agency has created a price column containing a few prices from the 5% and Hot Sheet columns for their Stocking Distributors (ok, the “good guys”).

The QPath is set to use the 7% column (as shown above). When the part number is keyed into OASIS, the item is priced using the 7% commission rate. This allows the quoter to quickly produce a solid quote, without giving up commission. However, if the agency is pushed into a competitive environment, the quoter is able to access the full price list and manually select the 5% or Hot Sheet price.

The OPath is set to Stocking Distributor column and then the 7% column. In addition to the OPath, OASIS will require that the customer be assigned an account for the manufacturer (the actual account number for the customer may be blank, but the “accounts” tab for the manufacturer must be setup for the customer):

When OASIS auto-prices the item in the order entry screen, it will start with the pricing level assigned to the customer. If a non-zero price is found for the item at the assigned level, the OASIS will use it. Otherwise, OASIS will access the next level in the OPath, looking for a non-zero price.

QPath and OPath make OASIS very flexible and able to handle different pricing models for many manufacturers at one time.

Path functionality

Keep in mind the following.

Customer pricing allows you to set the level you would like to quote to a customer.

If your quote/order path is set like the following and you select to quote level 3 to the customer then it will not under any circumstances go backwards in that path to check the price for level 1.

If level 3 is blank it will not check if level one has a price in the part. It will move directly onto level 2, 4, and 5.

Same can be said if the customer is directly quoted level 4 it will under no circumstances go back and check levels 1, 3, and 2 for prices.

Using price exceptions

Price exceptions within OASIS cover special arrangements between a distributor (or other customer) and a manufacturer. Typically, these arrangements are for a few items with special pricing at a given commission amount.

On the “Price Exception” tab of the Parts List Editor, identify the part number, customer code, price and commission for the item. Also add the MFG Quotation number to be used with this item when ordering.

This information is only used when placing an order within OASIS (and not during quote conversion). When available, OASIS will ignore other pricing rules.

Using part categories (Product Information)

The product information editor allows the agency to defined multiple product categories for reporting, creating price books and applying blanket price adjustments. Additionally, Agent product codes may be used to group varying manufacturer product codes together for reporting (e.g. the agency may want to group all recessed lighting together, regardless of the different “flavors” of recessed lighting available from a manufacturer specializing in recessed lighting).

Part Categories

  • Product Code – The manufacturer’s product code.
  • Agent Prod. Code – The agency’s product code (may be the same as the MFG’s product code).
  • Mult 1- Mult 15 – use these values to do a blanket price adjustment on a price list. The multiplier uses the “book price” as a starting value, applying the multiplier to calculate the resulting price for the level.
  • Comm 1 – Comm 15 – use these values when performing a price adjustment using the above multipliers. The commission for the level is assigned to the value of the commission.
  • Thumbnail for: - After clicking on a product code, an image for the product code will appear. Add any image supplied by the manufacturer for the product by dragging and dropping onto the product information editor. The thumbnail is used when creating price books.

Special Product Code Multipliers

A few agencies have large stocking distributors selling a wide range of products. When the manufacturer provides these large distributors with a special price for a group of products, the OASIS “Special Product Code Multiplier” may be an answer.

Start by adding the customer and product code having the special pricing. Then add the multiplier (this is off the book price in OASIS) and commission earned by the agency. The MFG Quotation number related to the arrangement is also added here.