The Edit Product Pricing utility found at Hub / Utilities offers the ability to edit several product prices at the same time. The feature can be used to create Price Levels marked up by a certain percentage or a specific margin from cost or another Price Level. It may also be used to set up and save the formulas used to calculate the Price Levels, to quickly update prices based on predefined formulas, or to manually edit costs and prices.
Edit Products
This is a tool that allows changing the price of many Products at once. The prices may be changed by using a Percent Markup, by Profit Margin, by Dollars, or manually.
Any individual cost or price may be edited manually if the Use Formulas To Edit Pricing option is not selected. When this option is selected, choosing the Repl Cost, List, or any of the Level pricing column headings displays a new window allowing formulas to be set up that can be used to easily update Price Levels in the future. Formulas may be set up based on any Price Level or cost. Each Product can have a separate formula, or the first Product's formula may be applied to all Products in the grid.
Available Formula Types include Markup %, Profit Margin, Dollars, or No Formula. No Formula does no calculations, and a price may be manually entered.
Markup %
(List Price - Cost) / Cost = Markup %
Example: ($20.00 - $15.00) / $15.00 = 33.333% Markup
-Or-
Cost * (1 + Markup %) = List Price
Example: $15.00 * (1 + .3333) = $20.00
Profit Margin
(List Price - Cost) / List Price = Profit Margin
Example: ($20.00 - $15.00) / $20.00 = 25% Profit Margin
-Or-
Cost / (1 - Margin%) = List Price
Example: $15.00 / (1 - .25) = $20.00
Dollars
List Price - Cost = Dollar Amount Entered
Example: $18.00 - $15.00 = $3.00
-Or-
Cost + Dollar Amount Entered = List Price
Example: $15.00 + $3.00 = $18.00
Manually Edit Product Pricing
- Select the Products to edit by choosing Select. Those Products will display in the grid. There are two methods of editing prices at this screen: manually and by formula. It is recommended to test this process on one or two items before affecting an entire Inventory Department.
Note: Average, Last, and Estimated Average Cost may only be edited manually at this screen.- To Manually Edit – Uncheck Use Formulas to Edit Pricing and place the cursor in any cell with a single left click. Edit any prices by moving up or down the column using the arrow keys on the keyboard
- Edit by Formula – With the Use Formulas to Edit Pricing option checked, select in either the column heading or any cell in the column to display the Setup Formulas to Calculate window. Formulas may be used to calculate prices based on markup, margin, or dollars of some other price or cost level. These formulas may then be saved.
- Once formulas are established, Calculate Prices Based on Product Formulas may be used to quickly recalculate any prices with formulas for the selected Products.
Calculate Prices from Ingredient Prices
Choose which Price Levels to calculate/update. This includes the List Price, any Replacement Cost, and any Level 1-24 Price.
For example, when updating the List Price for a manufactured Product, it will use the raw ingredients' List Prices to use to calculate the new List Price for the manufactured Product.
If a manufactured Product has two raw ingredients where the first raw ingredient has a List Price of 4.0460 and the second raw ingredient has a List Price of 10, it takes 0.90 gallons of the first Product and 0.10 gallons of the second Product to make one gallon of the manufactured Product. When selecting Calculate Prices from Ingredient Prices, it uses the 4.0460 List Price from the first Product then the 10 List Price from the second Product and calculates the manufactured Product’s List Price as 4.6414.
To enable this button, check Show only manufactured products.
Import Prices
Information from Excel files may be imported into the Edit Product Pricing grid to make updating prices quick and easy. List and Level pricing as well as costs may be updated using this feature.
Navigate to Hub / Utilities / Edit Product Pricing. Right-click on the grid header, and select Import.
Select an existing Format Name or set up a new format. Selecting New allows a format to be set up that meets the needs of the import.
Enter a Format Name. If the file contains a header row, select the First Row Is Header option. Set the File Type to Excel or Delimited. If Delimited is selected, choose what delimiter is used in the file to separate the information. Select the Number Of Columns being imported, and link those column numbers to the appropriate grid headings by choosing the column number in the Excel spreadsheet (column A = 1, column B = 2, etc.) or clipboard that corresponds to the columns in the grid of the Import Setup window.
The Alternate ID column on the Import Setup window is used to determine what Product to update in Agvance.
Note: Verify all price columns in the spreadsheet are formatted as numbers and do not contain dollar signs.
The information in this column should be based on one of the elements set up on the Setup Product Barcode Lookup Order window found by selecting the Product Barcode Scannable Items button on the Product tab at Hub / Setup / Company Preferences.
For example, if the price list was provided by a vendor, the UPC code may be included in the file to identify the product. In other cases, the Product ID or Alternate Product ID may need to be the identifier.
Caution should be used if there are multiple Products in the file to be imported that have identical information. For example, if using the Product ID as the Alternate ID and the same Product with the same Product ID is included in multiple Inventory Departments, it will load the same Product twice as it is only looking at the Product ID. It will not try to match the Product based on Department ID and Product ID even though the Department ID may be in the file.
After the format is saved, it may be selected on the Import window. Select to import from File or Clipboard. If importing a file, use the Browse button to select the file to be imported.
Replicate Prices
This is a tool used by multi-site companies to update selected modified pricing to all corresponding Location Products. For example, copy newly established selling prices to all other Locations. This tool may be scheduled to run automatically by using the Agvance Reports Agent.
For this function to work, corresponding departments must be linked with a common Category as well as identical Product IDs in the corresponding departments.
How to Replicate Prices
- Establish the pricing to replicate across to the other Locations.
- At the Replicate Prices screen, select the Location to use for the pricing information to be replicated.
- All departments from that selected Location (which has a Category setting) appear in the grid. Select the box in the Copy column to indicate those departments to involve in the pricing replication.
- In the Copy To Which Locations frame, select the Locations to receive the pricing information by putting a checkmark in the Copy To box.
- In the Include which Prices frame, select the Price Levels to copy.
- On the right side of the window, indicate whether to replicate prices only or prices AND formulas.
- Select Replicate Now.
Promos & Discounts
Sale prices with an effective date range may be entered in this area for use on Quick Tickets. The price entered in this area temporarily replaces the List price. The prices may be manually entered or may be imported from a file. To import from a file, the file format must first be specified. Multiple file formats may be saved. When importing, the last used format defaults.
Import file formats may be edited or deleted by selecting Edit or Delete.
How to Manually Enter Promotional Prices
- On the Import tab, select the Products to edit by double-clicking the Product Name column heading. The selected Products display in the grid.
- The Start Date and End Date columns default to the current system date. Edit the starting and ending date range for the promotional pricing for each Product.
- In the Promo Value column, manually enter the promotional price or the percentage off discount for the specified date range.
- In the Promo Type column, select Price or Percent Off.
How to Import Promotional Prices
- On the Import tab, the file format must be established before the import can be done. To create a new file format, choose New and the Promos & Discounts Import Setup screen displays.
- To create the import format, enter the Format Name and select the Delimiter for the file. If the file contains any header rows, enter the number in the Number of Heading Lines to Exclude area. In the grid, specify the column location of the Department ID, Product ID, Start Date, End Date, and Price. The column numbering is a zero-based index, so the first column is numbered as zero and then the subsequent columns in the file increase from there.
- Once the file format is established, on the Promos & Discounts tab, select the import format from the Format Name drop-down.
- In the Import File area, use Browse to navigate to the location of the file to import.
- Select Import.
Process
Edit multiple or single Product pricing in a grid format.
- Navigate to Hub / Utilities / Edit Product Pricing.
- Choose Select to choose Products.
- Select the appropriate Products to edit and choose Done. Edit Products manually by unchecking the Use Formulas to Edit Pricing option. Edit the appropriate prices, choose Save, and close the window. Leave the option selected to establish formulas to edit pricing and for use in future recalculation of prices.
Note: To automatically change the Price Level headings to match the labels entered on the Inventory Department, double-click on a Department ID in the Dept ID column of the Edit Products tab. - Select the appropriate Price Level column to display the Setup Formulas to Calculate window.
- Choose the Source for the formula, the Formula Type for the change, and enter the Amount of the change.
Note: To automatically apply the formula to a single product, choose Calc for that Product and then Save. Proceed to Step 7. To copy the formula in Line 1 to the remaining Products in the table, proceed to Step 6. - Choose Apply Formula on First Row to all Rows to copy the formula to all Products listed in the table. Select Calculate Prices Based on Formula to calculate the new price value for the Products. Select Save.
- Choose Save on the Edit Product Pricing window and then close the window.
Note: To apply saved formulas to update multiple Price Levels for the selected Products, choose Calculate Prices Based on Product Formulas. On the Select Price Levels to Calculate window, mark the Price Levels to recalculate. Choose OK to update the Price Levels based on the saved formulas. Select Save at the Edit Product Pricing window.
Note: This tool does not trigger Product Synchronization automatically even if set to Live Product Synchronization. Use the Replicate Prices option or go to Hub / Utilities / Admin Utilities /Hub / Synchronize Products to push out any prices/costs according Product Synchronization Rules.
