Commercial is fairly similar to the way we order for retail , although there is more manual tasks that are needed in order to ensure our ordering is successful .
Visit Commercial Replenishment and download the file. By default it should be AOL commercial orders. Three weeks to cover SOH and 21 days non core
Import this file to a google sheet then create another tab.
Go to the pricing link , copy the replen tab into the new tab.
Ensure we use the lowest price for each SKU (if there are two prices for a certain SKU) listed on the "Replen Format" tab.
Remove apostrophes ' and set both pages to plain text. Once done, set the number based columns and vendor number columns to Number.
Back in the main tab paste the following fields in as headers for the vlookup > Quote number Buy Price ex gst Vendor
Then create vlookup formula as per this example
VLOOKUP($C:$C,Sheet1!$A:$E,3,FALSE)
a) Column 3 is quote, 4 buy price and 5 for vendor
b) Add another column to check pricing using IF(M2<P2,"Yes","No")
c) Yes means the retail pricing is lower and should not be ordered through the commercial price list, so clear O P Q column (not delete the entire one just clear it.)
Filter and clean using ordering guidelines
a) We do not want to order for anything without an SO so Filter all columns and remove all 0s from column k
b) In column S add =IF(I2+J2>=K2,"No","Yes") No means the is already enough SoH or POs to cover the Sales orders so delete all the No lines. (If it all come up as no then adjust Column I J and K with formatting as currency.
c) in column T add =K2-(I2+J2) This will show how much is required to order so use the column for your order qty
d) Then open the filter on column h and untick 0 and 1 so we can check if we are ordering less than the minimum, raise qty ordered to match.
e) Match up the quantities in column T with the min order qty (make sure they are a multiple of the MoQ).
f)Change vendor numbers to 000000 (click column, format to custom 000000) > so we can see vendor #
g) Take note of any SKUs that need qty >10. After checking that they aren't removed by any rules below, locate the SO they are needed for and ask the salesperson if they would like us to remove the SKU from the ordering. We do this because the salespeople often get specific quotes for larger orders.
h) Look for V03125 on the sheet and ensure the items have pricing. If they do not, check them on the price list and update the price, quote and vendor number as required from the price list. They may be on another tab, so check all sheets.
Prepare the uploads still using ordering guidelines
a)remove V02956, V01774, V08872 (PHONES AND HEADSETS ONLY), V03040, MISC-PRODUCT|LKV3913|LKUV1613|BCDV4113|LKV3913-LH|HC90PLX4
b) Create a blank doc and add the headings as per the examples commercial and retail . Commercial has the quoted field retail does not.
c) Search for HBF113BR0A on the sheet. If located, update the Vendor number to 000388 if not already done.
d) Filter column O (Quote Number) filter out blanks and #N/A for the commercial orders, after filling out the commercial sheet, now remove all besides #N/A and Blanks for the retail orders.
On the commercial sheet, shorten any quote numbers that exceed 20 characters as they will cause your upload to fail.
Download these sheets as CSVs with the format DD.MM.YY ACOM and DD.MM.YY ACOM Retail respectively.
Save into (X:) Departments > Buying > AOL Replenishment Orders
Upload these files in req worksheet. Using the commercial com import replenish lines for the upload with comm pricing
When importing comm sheet all lines will be unticked for auto release, re-tick them at this point to ensure the below steps are done properly.
Remove all consignment V06826|V06897|V06915|V06917|V06920|V06924|V06947|V06957|V06959|V06988|V07001|V07062|V07113|V07207|V07247|V07280|V07437|V07731|V08617|V08863|V08877|V09500|V09605|V09700|V08862|V09758|V09947|V10117|V10064|V10170|V10186|V10438|V10149
Remove all overseas vendors that may have appeared: V08493|V08495|V08526|V08496|V08497|V08498|V08499|V08500|V08501|V08502|V08503|V08522|V08505|V08506|V08523|V08508|V08525|V08510|V08512|V08776|V08511|V08513|V08514|V08521|V08516|V08517|V08518|V08520|V08524|V09540|V08887|V08986|V09271|V08989|000515|V06484|V08961|V08095|V08279|V08972|V08990|V08966|V08969|V08970|V08979|V09013|V09913|V09015|V09061|V09064|V08959|V08960|V08968|V08976|V08978|V08980|V08985|V08987|V08992|V08994|V08999|V09001|V09007|V09017|V09024|V09028|V09055|V09060|V09108|V09169|V09170|V09617|V09628|V10052|V08982|V08973|V09187|V08996|V08965|V08997|V08982|V08998|V08962|V08984|V09000|V09006|V08964|V08977|V08967|V08981|V08963|V09004|V08975|V09003|V08971|V08995|V08991|V09005|V08988|V0897
Remove all lines for Northern Territory using Location Code:
601WHSE|WA-DC-NT|NT-AO-CST|NT-WA-CST|601ACOM
EXCEPT FOR FRIDAYS, remove V05774|000548.
Now get the total value of the retail and the commercial sheet and add this to the Supply Chain Gang chat.
Check Speed Queen Vendors - 000569|V01298|V00573
F.L Costello (V01298)
NSW, WA, TAS, SA and ACT
mark@speedqueenlaundry.com.au;BDM@speedqueenlaundry.com.au
Speed Queen Equipment Sales (000569)
VIC only
sales@speedqueensales.com.au
Andrew Barton Laundry Systems (V00573)
QLD
sandra@andrewbarton.com.au
mark.barnes@andrewbarton.com.au
untick TCL ( V05773) and RHEEM ( 000050 ) to be manually sent via email
Untick Auto Release for all EDI - 000022|000040|000042|000050|000054|000061|000071|000188|000199|000201|000227|000248|000264|000290|000307|000319|000326|000352|000382|000446|000470|000472|000478|000497|000525|000540|000541|000542|000543|000555|000558|000688|V00056|V00540|V00734|V00904|V02343|V02384|V02574|V02658|V03161|V03201|V03401|V03585|V04985|V05969
Remove any SMEG ( 000561 ) Commercial POs (need specific quotes).
Remove VZUG ( V06866|V06484 )
Untick 000136 EHP spare parts - Has to be ordered through the EHP spare parts portal
After Placing the POs go through the EDI and change the location code of the PO in PO (not the item lines) to the AOL equivalent. Aka 201ACOM to 201MAIN
Double check nothing you placed today is left in the "Open" status.