Hello guys
Just forcasted FRI and coming up with a bunch of bad part numbers, maybe US? Some ex. are
DE31 to DE658, DM31 to DM679, ES9 to ES703. Called the help desk, they have no clue, waiting for response from tier 2. Anybody else have them problems?
Tuesday, February 26, 2008
Monday, February 25, 2008
sorting inventory report
I have always preferred sorting my FRI line by number/prefix/suffix. When printing inventory count sheets, you can't choose how you want a line sorted. I discovered today the answer, go to vendor>line>line entry. Enter your line code and you can edit the sortbutton on the top right hand side.
Friday, February 22, 2008
Reports to Excel - when good data goes BAD
Working on temp solution to consolidate two line code reports - Jobber 1 and jobber 1 .... but the forst try we found how bad the data is when you get it formatted ( see post from a while back) --so we have data on non-formatted format.
This example shows a could of errors - if you compare the original to the FIXED version-- we have changed some titles and shifted the TOTAL line so it matches the sub/line data -- try to work with this in excel will drive you NUTS. These reports have to be cleaned up and de-bugged - then working in excel will be MUCH EASIER
Click to expand the samples
Thursday, February 21, 2008
customer price plan
Just got a great tip from the help desk( at least something I was not shown how to do). If you are wondering what discounts you are selling a customer at, enter the part number into the sell line and click on the jelly bean to the right ,called, price lookup. A table will pop up showing you the discounts you have him set at, wether velocty pricing is included etc. Very slick, as it save you from bopping back and forth between billing schedules.
Merge data - part #2
Things that go 'bump' in the night --things that go wrong
One thing that has happened to us -- is that we have the formula right -- it just won;t work ... after repeated attempts ...ahh ... what is wrong ... the data may have INVISIBLE characters - especially if you are pulling data from IBM AS400 or similar computers.
Here is how to clean your data .... in a NEW column use this formula -- =TRIM(CLEAN( your data cell here ))
Then PASTE SPECIAL ... using VALUES ONLY and paste the cleaned data back ONTO the original data ... and delete the new column you used to clean the data in.
Another problem --if you are working WITHIN the same work book --- sheet 1 and sheet 2 .... the formula will not automatically ADD the dollar sign to FIX the points of data you are starting and ending with in the comparison. You will need to add this manually. This is NOT a problem if you use two different work books
One thing that has happened to us -- is that we have the formula right -- it just won;t work ... after repeated attempts ...ahh ... what is wrong ... the data may have INVISIBLE characters - especially if you are pulling data from IBM AS400 or similar computers.
Here is how to clean your data .... in a NEW column use this formula -- =TRIM(CLEAN( your data cell here ))
Then PASTE SPECIAL ... using VALUES ONLY and paste the cleaned data back ONTO the original data ... and delete the new column you used to clean the data in.
Another problem --if you are working WITHIN the same work book --- sheet 1 and sheet 2 .... the formula will not automatically ADD the dollar sign to FIX the points of data you are starting and ending with in the comparison. You will need to add this manually. This is NOT a problem if you use two different work books
Wednesday, February 20, 2008
work around for MERGING two sets of data
Until we get a better multi-store reporting system --you early adopters might have to do a work around to MERGE several stores data onto one report. I will also mention this on the sister blog J-Con Tips -- click here to see it as they have a similar need
So you have two (lets keep it simple to start ) stores -- and you want to see the line code sales for each line -- by store -- on ONE LINE ...see the picture examples - you can click on the images to make them larger.
Excel has some functions built in like VLOOKUP or HLOOKUP ( yep--click to see more--or google the term )
But if you have the time -a more complex byt accurate method is this excel formula
=INDEX( range of 2nd data , MATCH( part # on first data, range of part numbers on second data, 0 ), x where x is the column number in the 2nd data you want to bring back )
We will post examples of how this works in the future
So you have two (lets keep it simple to start ) stores -- and you want to see the line code sales for each line -- by store -- on ONE LINE ...see the picture examples - you can click on the images to make them larger.
Excel has some functions built in like VLOOKUP or HLOOKUP ( yep--click to see more--or google the term )
But if you have the time -a more complex byt accurate method is this excel formula
=INDEX( range of 2nd data , MATCH( part # on first data, range of part numbers on second data, 0 ), x where x is the column number in the 2nd data you want to bring back )
We will post examples of how this works in the future
Tuesday, February 12, 2008
update - Excel & X Lines
Quick note
Ian ( aka DAD ) is working on downloading --- www.openoffice.org --- and using the FREE version of the Excel type program so you can export data to excel ( or equiv ). You have to set your computer to tell it that .xls files open with ..... and select the program that does it. Just curious to know how locked down the current Exploris terminal is for downloading and adding a new program
Jim ( aka Jim ) is working on getting X line data to add & UPDATE within his Exploris
stay tuned --things are getting better
Ian ( aka DAD ) is working on downloading --- www.openoffice.org --- and using the FREE version of the Excel type program so you can export data to excel ( or equiv ). You have to set your computer to tell it that .xls files open with ..... and select the program that does it. Just curious to know how locked down the current Exploris terminal is for downloading and adding a new program
Jim ( aka Jim ) is working on getting X line data to add & UPDATE within his Exploris
stay tuned --things are getting better
Friday, February 8, 2008
Exporting data to Excel
Want some fun - LEARN EXCEL --then you will be amazed how you could operate without such a powerful tool.
Exporting from EXPLORIS you have two options - WITH FORMATTING and DATA ONLY. here are examples of what the data looks like
The first is DATA ONLY - it is taken on a Windows version of Excel and you can see everything is fairly clean and straightforward. Once you get into Excel you can shrink some columns and expand others and all the 'other' things
The second image was taken from a Mac version of Excel to help show you HOW UGLY the formatted data is. I set the border for every cell -to showcase the merged cells ..... Why do I say this is UGLY? --well- if you are going to do any extra work in Excel - say you want to SORT -- you have to remove MERGED CELLS ..and believe me - there are a lot of merged cells here ... and once you do that the report is unreadable. So - the short story - if you want to use the power of Excel - get data only from Exploris
Next example we will post - is how you can take stores and line in the data ...stay tuned
Thursday, February 7, 2008
X Line Data part 3 - adding new parts
Late flash - I have been told that the same template is used for adding new part #'s as well.
Hmm - lets think now -- say you have the line FRAM - they send you a list of approx 4,000 parts - you clean up and send in - you will automatically get all the NEW parts that Fram has added into their system - good ... how will you identify the parts that FRAM has dropped and perhaps they no longer send in the data -- we may need a report of parts that did NOT GET updated in your X line.
On the other hand - there is still some question as to how you can DELETE parts from your data base ( and how you might do it in multi-store ?? )
General Points
- should you remove the dashes and slashes from parts in your X line? There is a bigger question --what did you do when you did the original IMPORT? Why does the system make such an issue for dashes and slashes??? Other systems treat them as transparent - but not Exploris. If any reader has more info - appreciate feedback. This will be a HUGE issue I think.
Hmm - lets think now -- say you have the line FRAM - they send you a list of approx 4,000 parts - you clean up and send in - you will automatically get all the NEW parts that Fram has added into their system - good ... how will you identify the parts that FRAM has dropped and perhaps they no longer send in the data -- we may need a report of parts that did NOT GET updated in your X line.
On the other hand - there is still some question as to how you can DELETE parts from your data base ( and how you might do it in multi-store ?? )
General Points
- should you remove the dashes and slashes from parts in your X line? There is a bigger question --what did you do when you did the original IMPORT? Why does the system make such an issue for dashes and slashes??? Other systems treat them as transparent - but not Exploris. If any reader has more info - appreciate feedback. This will be a HUGE issue I think.
X Line Data part 2 - submit the data
At this point ( 2/08 ) you send your data to Normand Delisle for Canada and to Mark Atkinson for USA jobbers data.
Click here for the sample template in excel ..... ----PriceUpdate - Unsupport.xls---
Questions we asked --
Q-do you have to list every part in the line
A-no -only the parts that are changing
Q-do you have to fill in every column
A-no -only the data that needs to be updated.
Q-do we have to send in any sorted order
A-no
Now - lets hear some feedback from users who try this and see how it works in the real world.... feedback please
Click here for the sample template in excel ..... ----PriceUpdate - Unsupport.xls---
Questions we asked --
Q-do you have to list every part in the line
A-no -only the parts that are changing
Q-do you have to fill in every column
A-no -only the data that needs to be updated.
Q-do we have to send in any sorted order
A-no
Now - lets hear some feedback from users who try this and see how it works in the real world.... feedback please
X Line Data part 2 - submit the data
At this point ( 2/08 ) you send your data to Normand Delisle for Canada and to Mark Atkinson for USA jobbers data.
Click here for the sample template in excel ..... ----PriceUpdate - Unsupport.xls---
Questions we asked --
Q-do you have to list every part in the line
A-no -only the parts that are changing
Q-do you have to fill in every column
A-no -only the data that needs to be updated.
Q-do we have to send in any sorted order
A-no
Now - lets hear some feedback from users who try this and see how it works in the real world.... feedback please
Click here for the sample template in excel ..... ----PriceUpdate - Unsupport.xls---
Questions we asked --
Q-do you have to list every part in the line
A-no -only the parts that are changing
Q-do you have to fill in every column
A-no -only the data that needs to be updated.
Q-do we have to send in any sorted order
A-no
Now - lets hear some feedback from users who try this and see how it works in the real world.... feedback please
X line data part 1 - basics
The info for X lines is NOT finished ( i has to assume ) so remember to check back and ask the help desk periodically.
There are TWO input forms - one for adding NEW PARTS - we will NOT deal with this in this article. We will deal with how to UPDATE data you already have in your system for X lines ( purchases from other than CARQUEST )
Important - if you don't know Excel ( or equiv. ) you are limited from using the full potential and you will do things slower. Excel is NOT an easy program to learn quickly - it takes time - start now.
To have an effective stable of X lines you are going to consider how you are going to document what you are doing. If you just want one or two lines - then you can stay less methodical. Consider having a word doc. file to write down what you do for each line. What is you discount from jobber. What formula will you use for pricing ( much like your own competitive position sheet for each X line). Will you use a VARIABLE MARKUP? What is that? The more something costs typically the less margin/markup you can make on the line. In Excel you can sort the data by cost and set markup formulas to decrease as the cost increases - for example is you have a broad line with many different misc items you can BAND the parts into cost groups. here is just a sample of what you could do:
Cost from-to Base Price
$0 to $5 cost * 250%
$5 to 20 cost * 200%
$20 to $100 cost * 175%
$100 and up cost * 166%
Since you have SLIGHT variations for different customer groups - you can add or subtract their discount. For this example lets assume you set walk in retail price as your base price and for each additional customer group you deduct 5% which is very easy to do in excel.
Do you want your retail pricing to end in a 9? Then you use the Excel function of CEILING and set to .10 and deduct one penny ( eg. =ceiling( A1*2.5, .1 ) -.01 ) where you are taking what is in cell A1 and 250% - and set the value to round to .10 then deduct one cent -- like I said --if you know Excel basics - this is SO MUCH easier ).
If you set your prices up first with better logic - you don't have to do as many dumb things for exceptions latter on.
Most companies you deal with will e-mail you the price updated for their lines. Most have had 5 to 10 years of doing this so they have their process down pretty good. When the writer started doing this in 1996 it was very uneven in company procedure and logic. Still remember battles with Draw Tite hitches who would send excel data that looked like their price list ( dumb ) with data for part numbers on TWO LINES. Norton Abrasives was similar - but time and pressure have gotten then to smarten up and provide clean data.
You may want to keep the original ( virgin ) data and keep your work in progress as a separate file name just in case you goof up and have to do it again. Again we urge you to get organized as this becomes very difficult as you add more X lines to your stable of data to maintain.
So- what have we got --
-suppliers to send you updated price files as they change
-you have a spot to save date in such a fashion you know what it is?
-you have a quick list for what you do on each X line for markup logic and any other additional work you need to do to the line
Now - lets go to part # 2 - and send clean data for updating
There are TWO input forms - one for adding NEW PARTS - we will NOT deal with this in this article. We will deal with how to UPDATE data you already have in your system for X lines ( purchases from other than CARQUEST )
Important - if you don't know Excel ( or equiv. ) you are limited from using the full potential and you will do things slower. Excel is NOT an easy program to learn quickly - it takes time - start now.
To have an effective stable of X lines you are going to consider how you are going to document what you are doing. If you just want one or two lines - then you can stay less methodical. Consider having a word doc. file to write down what you do for each line. What is you discount from jobber. What formula will you use for pricing ( much like your own competitive position sheet for each X line). Will you use a VARIABLE MARKUP? What is that? The more something costs typically the less margin/markup you can make on the line. In Excel you can sort the data by cost and set markup formulas to decrease as the cost increases - for example is you have a broad line with many different misc items you can BAND the parts into cost groups. here is just a sample of what you could do:
Cost from-to Base Price
$0 to $5 cost * 250%
$5 to 20 cost * 200%
$20 to $100 cost * 175%
$100 and up cost * 166%
Since you have SLIGHT variations for different customer groups - you can add or subtract their discount. For this example lets assume you set walk in retail price as your base price and for each additional customer group you deduct 5% which is very easy to do in excel.
Do you want your retail pricing to end in a 9? Then you use the Excel function of CEILING and set to .10 and deduct one penny ( eg. =ceiling( A1*2.5, .1 ) -.01 ) where you are taking what is in cell A1 and 250% - and set the value to round to .10 then deduct one cent -- like I said --if you know Excel basics - this is SO MUCH easier ).
If you set your prices up first with better logic - you don't have to do as many dumb things for exceptions latter on.
Most companies you deal with will e-mail you the price updated for their lines. Most have had 5 to 10 years of doing this so they have their process down pretty good. When the writer started doing this in 1996 it was very uneven in company procedure and logic. Still remember battles with Draw Tite hitches who would send excel data that looked like their price list ( dumb ) with data for part numbers on TWO LINES. Norton Abrasives was similar - but time and pressure have gotten then to smarten up and provide clean data.
You may want to keep the original ( virgin ) data and keep your work in progress as a separate file name just in case you goof up and have to do it again. Again we urge you to get organized as this becomes very difficult as you add more X lines to your stable of data to maintain.
So- what have we got --
-suppliers to send you updated price files as they change
-you have a spot to save date in such a fashion you know what it is?
-you have a quick list for what you do on each X line for markup logic and any other additional work you need to do to the line
Now - lets go to part # 2 - and send clean data for updating
Wednesday, February 6, 2008
how to make a list of Important people
Key people who can make things happen? Are you tired of asking the wrong guy on a question for Exploris only to be given some pat answer that seems to be a 'canned response'
Here are two new guys I have been told about
1) Dan Kellly ( BTM Ohio ) comes highly recommended by some else in Ohio ( come on - with a hint like that - you should know who I mean ) Dan has a good parts knowledge ( not just computers ) and has key knowledge on security setting for associates ( since they LOCK DOWN the system so severely for JV stores ) but we all know that associate jobbers won;t stand for that kind of draconian measures.
2) Mike Buss ( Sales Manager Salt Lake DC ) again impressed by someone from Ohio - key person on what associate jobbers need in Exploris system, Wow --that is also a person we need more of.
Greg
Here are two new guys I have been told about
1) Dan Kellly ( BTM Ohio ) comes highly recommended by some else in Ohio ( come on - with a hint like that - you should know who I mean ) Dan has a good parts knowledge ( not just computers ) and has key knowledge on security setting for associates ( since they LOCK DOWN the system so severely for JV stores ) but we all know that associate jobbers won;t stand for that kind of draconian measures.
2) Mike Buss ( Sales Manager Salt Lake DC ) again impressed by someone from Ohio - key person on what associate jobbers need in Exploris system, Wow --that is also a person we need more of.
Greg
e-cat problem - follow up
A quick note -- Mike W mentions the underlying problem to the example of superseded parts on e-cat is this ...
IF Exploris does NOT have the NEW part in the system -- it doesn't pick up the proper super-cession.
So in the case a few entries below the Exploris system has the EC TW-20 in the system --the older replaced by part of DS1121 would point to the newer ( and btw - cheaper ) part.
We look forward to this error to be corrected. But - one does have to wonder how such a simple error has lasted for so long - who is not trouble shooting the problems in the beginning? Where is Marc Beasley when you REALLY REALLY need him...
IF Exploris does NOT have the NEW part in the system -- it doesn't pick up the proper super-cession.
So in the case a few entries below the Exploris system has the EC TW-20 in the system --the older replaced by part of DS1121 would point to the newer ( and btw - cheaper ) part.
We look forward to this error to be corrected. But - one does have to wonder how such a simple error has lasted for so long - who is not trouble shooting the problems in the beginning? Where is Marc Beasley when you REALLY REALLY need him...
Monday, February 4, 2008
Popularity Ranking in Exploris
Do you guys only have 4 periods of sales in 1st prior year field? Mine goes from aug to dec? Help desk doesn't know why? Do you have the same problem?
New Member
Hello Guys
Have been doing some follow up on that e-cat errors in exploris. I have been on for a week and already found four numbers that do not sup properly. They sub if you go to source it but not at point of sale or in ecat. Called the JV store manager in town and asked him if he found that a problem? He said yes he runs in to that all the time (having to go to sourcing screen to get the sup) Hes been on for 2 yrs and they still have got it fixed. Called everybody that I could on this one because it is really important, Doug sent me an email tonight that the Blue Streak number was a result of a manufacter error (ds1121 not going to tw20) but still doesnt solve the problem, hope they figure it soon.
Have been doing some follow up on that e-cat errors in exploris. I have been on for a week and already found four numbers that do not sup properly. They sub if you go to source it but not at point of sale or in ecat. Called the JV store manager in town and asked him if he found that a problem? He said yes he runs in to that all the time (having to go to sourcing screen to get the sup) Hes been on for 2 yrs and they still have got it fixed. Called everybody that I could on this one because it is really important, Doug sent me an email tonight that the Blue Streak number was a result of a manufacter error (ds1121 not going to tw20) but still doesnt solve the problem, hope they figure it soon.
e-cat problem
Exploris uses Activant e-cat
J-Con uses Activant e-cat
so --why does 92 Jeep Wranger turn signal supercession show up for J-con and NOT for Exploris ...
Especially for the large price difference ... ouch ... some times our web-link customers give up on us when we look that bad. If you call up the EC DS1121 in POS ( not e-cat ) it shows as being replaced by TW20 ....anyone know what causes this problem??
Subscribe to:
Posts (Atom)