Menu

Import Price Rollup

  • Updated

You can import price rollups to add or update the Price Rollup Schedule from a spreadsheet.

Warning: The import process is not reversible. Changes update instantly.

See the sections below to learn about the data you can import, formatting the spreadsheet, and running a successful import.

Field Name Definition Sample
Price Group The Price Group Code. When mapped, price rollups for specific price groups are imported. 5%Load
Product Line The Product Line Code. When mapped, price rollups for specific product lines are imported. EQUIPMENT
Vendor The Vendor Number. When mapped, price rollups for specific vendors will be imported. 00324
Warehouse The Warehouse Code. When mapped, price rollups for specific warehouse will be imported. Leave blank for all. Company Master flag Maintain Cost By Warehouse must be set to Y to set by warehouse. 02
Effective Date* MM/DD/YYYY. When mapped, date the price rollup will take effect. Any current rollups will become affective the following date of the entered date after the nightly process runs. 01/01/21
Seq#* Value must be 1-8. When mapped, will set the sequence of the rollups for the grouping. 3
Column (C#/L#)* Indicates that the cost or list column the rollup will update. Cost fields should be entered as C1, C2, etc. Similarly, List fields should be entered as L1, L2, etc. 25
Base Column (C#/L#)* Indicates which cost or list column the rollup should use to calculate the Current Column new value. Cost fields should be entered as C1, C2, etc. Similarly, List fields should be entered as L1, L2, etc. L1
(+/-)* (+/-) Indicates that the formula is a markup or a markdown. +
(%/$)* (%/$) This field indicates if the cost/list is based off of percentages or a dollar amount and should contain either a % or $ symbol. %
Percent / Net Amount* The percent or dollar amount to be added or subtracted from the base column. 25

* This field has two options for a Current or Future date setup in the Price Rollup Schedule and needs to match those setting selections.

Review the data prior to importing; improper spreadsheet formatting can result in import errors.

Easily download a comma delimited file (CSV) of your current rollups.

  1. Click Export.

  2. Save the file.

  3. Open in Excel and make any necessary changes.

  4. Review helpful tips in the Spreadsheet Setup section below to ensure the file is ready to import.

Don’t have any rollups set yet in the Price Rollup Schedule? Click here to download a template.

Each rollup formula must include the following fields: Seq#, Column, Base Column, (+/-), (%/$), Percent/ Net Amount.

  • Separate Columns: Each piece of data being imported must have its own column on the spreadsheet.

    • Example: A "10%" increase must have the number "10" in one column, and the "%" sign in a separate column.

  • Exact Matches (case sensitive): To match on data, it must be entered on the spreadsheet exactly as you have it setup within Inform.

    • Example: A Product Line Code of "CHEM" will not be updated if the spreadsheet shows "chem".

    • Code Matches: Data must match on Code fields, not Descriptions.

  • If rollups have different effective dates, import the date as a unique column in your data.

    • If all rollups have the same effective date, use the effective date option in the Settings section.

    • Current rollup updates should be imported separately from Future rollup updates.

  • Not all data fields need to be entered on the spreadsheet. The Default Fields make it possible to fill in some information automatically.

    • Example: If the spreadsheet is intended to update a single Product Line, you can select it from the Default Fields instead of entering that Product Line on the spreadsheet.

  • When the spreadsheet is completed, save the file format as Comma Separated Value (.csv).

    Note: If you reopen a CSV file in Excel, it will convert the column formatting back to "General".

Tip: When editing the spreadsheet, be cautious about leading zeros. By default, Excel trims leading zeros when the column formatting is set to "General". Consider changing non-date fields to "Text" before making any changes or saving.

 

When importing, you can update Prices, Costs, or Both.

All rollups for the grouping should be imported together.

  1. Create a row for each rollup will all necessary data needed and the grouping in each row.

  2. In the spreadsheet, add a column for each of the following to add or update:

    • Seq#: Must indicate which order the rollups will update. Even if there is only one rollup to update for the grouping, you must include the sequence number.

    • Price/Cost (C#/L#/N): Whether the price/cost is derived from the Cost, List, or Net Amount. Cost & List fields should be entered as "C1" or "L1", depending on the cost/list field used. Net Amounts should be set to "N".

    • Price/Cost (%/$): Whether the price/cost is based off of percentages or a dollar amount. For a percent type" %"; for a dollar amount type "$". Net amounts can leave this field blank.

    • Price/Cost (+/-): Whether the change is a markup or a markdown. Use "+" or "-". Net amounts can leave this field blank.

    • Price/Cost Percent / Net Amount: The percent value or dollar amount of the change.

    • Example:

      Desired Result Price C/L/N Price %/$ Price +/- Price Percent/Net Amount
      $10 less than the List 1 Price L1 $ - 10.00
      25% higher than the C1 cost C1 % + 25.00

Note: Percentage amounts are based on markup. Consider using the Markup Conversion chart below.

Once you understand the available data and configure your spreadsheet, using the information above, you can begin the price rollup import.

  1. Click Sales in the top menu.

  2. Under Pricing, choose Price Rollup Import.

  3. Select the File

    • It must be in either Comma Separated Value (.csv) or Tab Delimited Text file (.txt). If the file is currently in the Microsoft Excel format: Open the file in Excel, select Save As, and choose Other Formats. Then select either CSV or TXT. Close the file.

    • In the File Name field, type the path to the CSV import file, or click the folder icon and navigate to the path.

  4. If the spreadsheet has headers (such as Product Line, Price Group, etc.), select Has Headers.

  5. Default Fields: Selecting any of the default fields removes the need to add this data to your spreadsheet.

    • Example: Selecting a Product Line sets all rollups to be applied to that Product Line, without having to map the Product Line column.

    • To update multiple product lines at the same time, do not use this setting.

  6. Map the data. Once the csv file is chosen, the columns become available for mapping in the Import Mappings list. Drop down the Mapped Column beside the Field Name to import, and choose the correct value from the spreadsheet.

  7. Click Analyze. Inform ERP analyzes the mappings to validate the data. If it encounters any errors, it displays an Exceptions report, which will help you fix the data before retrying your import.

    • To fix data, click the Data tab. Changes will be applied here only, not to the original spreadsheet.

  8. Once the data looks ready, click Import. When complete, a pop-up notification indicates how many price rollups were imported, created, and skipped (which includes locked and failed rows).

  9. To see a report detailing why data was skipped, view the exceptions report by clicking OK in the notification. Skipped data is often due to bad data, such as non-exact matches or poor column formatting.

All formula based percentages must be in markup format. Use the chart below to help identify the differences based on the desired Gross Profit amount.

 

Related articles