Import Price Rollup

* 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.

File Template

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.

Spreadsheet Setup

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.