Notification of Next Quarter’s Pricing
We update our pricing with ISG on a quarterly basis. They require 120 days notification of next quarter’s pricing. Send an Advantus COP price list along with any price increase letters by the respective deadlines as outlined below.
- Q1 – notify by September 1st
- Q2 – notify by December 1st
- Q3 – notify by March 1st
- Q4 – notify by June 1st
Remember to save any documents (PDF of price list, price increase letters, etc) in ISG’s pricing folder on Sharepoint.
Approximately 90 days before the next quarter, ISG will email a notification that their quarterly price file is available in the IS portal. You will typically have 3 weeks to complete it before their deadline.
Using login credentials in Bitwarden, log into the ISG portal.
Hover over Suppliers tab, then File Processing, and then click on Quarterly Cost-List processing files
To review the price file you will need the following documents opened:
v
From there,
On the new screen, click the download button to get the cost file. Save a WIP version of this to the respective price quarter/year folder on Sharepoint.
Open the WIP price file and make the below modifications:
- Add a column to the right of ISG Product Code and VLOOKUP the Advantus item # from this cross-reference spreadsheet.
- Add a column to the right of this one and VLOOKUP in Status from the Inventory Quantities: Results – NetSuite (Advantus Corp.) report.
- If an item is marked Closeout or Liquidate, add an additional column to the right and VLOOKUP in the Net Available Qty
- Add a column to the right of this set and VLOOKUP the increase percentage for each item (will come from Sales price increase spreadsheet)
- After Future List Price and Future Direct Cost column, add 1 column to cross reference the List and Direct prices (using IF formula).
First, look at the status codes. For any items that pull in a #N/A, this means the item is Obsolete.
- Go to the Disco column and enter “D” for these items.
- For each item, copy the Current List Price and Direct Cost and paste into the Future List Price and Direct Cost cells.
- Gray out each of these rows on the spreadsheet.
Next, look at the Closeout and Liquidate quantities on hand.
- If it looks like there’s a decent amount, leave the item as is.
- If there’s less than 25 pieces left, double-check with Supply Chain (SCM) for run rates.
- If they don’t think this item will still have inventory in 3 months, follow the same process as the Obsolete items in the previous step.
Then, open the Advantus quarterly price list that was sent to ISG in the first step.
In the Future List Price column, VLOOKUP in the MSRP.
In the extra column, use the IF formula to cross reference Future List Price with the MSRP. This will flag any price increases; make note of these and compare the increase % with what was VLOOKUP in the earlier step.
In the Future Direct Cost column, VLOOKUP in the National Net. Repeat the IF formula to cross reference Direct Cost with National Net. Compare the increase % with what was VLOOKUP in the earlier step; they should all match. If not, alert Sales.
Copy & paste special values for the future pricing cells.
Remove all the columns you added.
Save the file as PRICING_YEAR_Q#ADV in the respective price quarter/year folder on Sharepoint.
Go back to the portal and click on the pencil image. 
We have the opportunity to update our pricing with c on a quarterly basis. You will be triggered to update pricing by ISG, as they email out price files roughly 90-75 days in advance of the effective date.
The email will contain an instruction Word document, an Excel file with the Countries & Codes, and the quarterly price file in Excel format.
Review the Word document for due dates and other important pieces of information.
To review the price file you will need the following documents opened:
- Current Quarter’s Pricer: S:\Pricers\COP
- Price Increase Database: S:\Sales & Marketing\COP\Pricing
- New Item Database: S:\Sales & Marketing\COP (click into the correct year’s New Product folder to find)
** When reviewing the price files for Q1 (which will happen around October of the previous year), be sure to look at the Price Increase Database and New Item Database for the coming year. **
Open the Excel file and save as a “WORK COPY”.
Remove the AVT, LED, REA, VRT, GEM, CVR, GNM, INN, KLS, and LNE prefixes from Column C. This can be done using Find and Replace to make the spreadsheet compatible for a VLOOKUP.
Open the current pricer and in the WORK COPY of the price file:
- In column T perform a VLOOKUP for the status code.
- In column U perform a VLOOKUP for ISG’s price.
Notes:
- You will likely have some “#N/A” values, as there are some item numbers that still need to be corrected to match the pricer (Angler and Vertiflex usually, which are missing a dash in the item number). Make the corrections to have the VLOOKUPs work.
- If there are still “#N/A” values, check in Multi-Level, as OB items will not be on the pricer. If it is OB, type in OB in column T and ‘0’ in column U.
- For state Labor Law Posters (item 838xx) look for the Labor Law Posters on the pricer and find the row for State Labor Law Poster, item number Varies and copy in those values for cost and status.
- Brand new items will also not be on the pricer, but should be in Macola or the New Item Database. New items can start to show up as early as the Q3 price files.
Mark any OB items as “D” in the Add/Drop Indicator column (G) and list the drop date (usually 12/31/current or last year – depending on which quarter this price file is)
For the remaining active items, make sure the Invoice Cost column (J) matches ISG’s Price column that you pulled in via VLOOKUP in column U. In column V, pull an IF formula to check if there are any discrepancies.
- If the prices do not match, look on the Price Increase Database to see if a previous price increase was missed. If so, add the values to Current Invoice Cost column.
Then, check your Price Increase Database and make those changes as well to the Future Invoice Cost and Future List Price columns.
Next, add columns to VLOOKUP and confirm the information listed is accurate. If it isn’t, please change.
- Consumer UPC: should match what is on our pricer.
- Min Order Qty: should match our Cust Qty Multiple. If ISG has a higher Qty listed than we do, leave as is.
- Country of Origin: should match what is on our pricer.
Now that all changes have been made, open the original file from ISG and copy/paste from the following columns from your WORK COPY file to the ISG Price File:
- Consumer UPC (D)
- Brand Name (E)
- Add/Drop Indicator (G)
- Drop Date (H)
- Current Invoice Price (J)
- Future Consumer List (L)
- Future Invoice Price (M)
- Min. Order Qty (O)
- Country of Origin
Save completed copies in link shown, in the correct year and quarter folder. Do not change the file names from ISG: S:\Sales & Marketing\Customers\is.group-Independent Stationers\Pricing
For any price increases/decreases, you will need to complete Price Increase Letters.
Respond to the original email and attach a) price increase letters and b) National Pricer in PDF & Excel formats.
If there are no changes to be made, complete the 3rd page of the Word document from the original email. By completing the Cost & List Verification Form, we are confirming that absolutely no changes need to be made to the price file that ISG sent to us. Reply to the original email and attach this completed form.
