The Joys & Frustrations of DUMPING Data into Excel

Had a nice call from Moe from Sudbury late yesterday. He had started to push his knowledge (and that is a good thing! ) -- he was comparing the CORE prices of our Cdn line of Callipers by Cardone with Raybestos ...

Here is the NEAT fact he has learned ... of the 192 sku;s he crossed and compared --the AVERAGE price difference is 58% less in Raybestos .... if you add up all cores in CQ CAL it is $9160 and in Raybestos it is $3827. So ( 3827-9160)/9160= 58% ..... or if you divide by the NEW line it becomes ($3827-9160)/3827 = 139% less. Either way that is an amazing difference. It has not been verified if these core prices have been tweaked by CARQUEST ( or not ) - either way - this is a very surprising report. Remember that CORE PRICES should always have 100% price protection --as this is a value outside of our control. In fact I mentioned to Moe that Cardone is like Bernanke -- when they INFLATE the price -they are printing money - they set the price and then expect us to pay them that price.

Here is an example .....

CAL CQ # Core ......... Raybestos Core ...............% Difference

19B1596..........	63.00	FRC10395	8.00.............	-87%
19B1597	63.00	FRC10396	8.00	-87%
18B8046	67.50	FRC11007	8.00	-88%
18B8047	67.50	FRC11008	8.00	-88%

Now --to the technical side of the cross in Excel ...so we can all learn to o better data reviews.

1) EXCEL ( or openoffice.org or Google Doc or equiv. spreadsheet) is the SINGLE most important piece of software you will ever learn ..the fact that over 85% of all CARQUEST jobbers do not know how to work it - is something that YOU need to address ( do you have a friend - or co-worker - your spouse to help train you ... or grab a book and the don't forget youtube.com

2) run your report to PREVIEW and click the upper right hand envelope to export as DATA ONLY to excel (data only gets rid of many stupid formatting issues - you will see )

3) what version of excel do you have - older versions only handle 62,500 rows - newer versions handle over a million - and when you get going -- you can really get some HUGE reports - for example - dump your entire inventory file -- using Inventory Info .... and sort al parts with dat in the REPLACED BY column ... see how much stuff you have on hand that is obsolete and you could return ... as it is sold as something different ....

4)with what Moe did -- compare one lien with another ... now - that is where it gets tricky. Bob from Walker Rd in Windsor has a program that takes two excel sheets and when the part number match's - brings the data from one to the other --- for example- you have a price list for your callipers (with core ) and a price list for another line .. and a cross reference list ... with some learning you can quickly compare 100 or 1000 or 10,000 pieces of data .... versus the old fashion ay of doing it by eye manually. Personally I mastered the rather complex formula using =INDEX(MATCH) in excel -- if you go to google and type that in -- you will find some youtube video's --remember I mentioned them earlier ( too bad we don't have EXPLORIS TRAINING in 3 minute youtube type video's ) -- caution - this is for experienced users

5) for jobbers who want to learn - some quick excel tips to master

-much of the data comes out as TEXT even thought it looks like a number - when you sort you will see it sorts like text ....1 then 10 then 2 then 20 then 3 ...and so on ...there is an easy way to fix this - search using google and just ask the question "how do I convert test to numbers in excel (look for the multiply by trick )

-for advanced users - using =INDEX(MATCH) --- can;t make itwork ..everything looks right ... this will really blow you away -- the data you are trying to MATCH ... use =CLEAN(TRIM) functions....again google it ... it strips invisible 'junk' and all of a sudden your formula works

Excel is not something you can master quickly - but once you start -- it can be a lifelong friend helping you handle data ....

Now - back to cores being 58% less ( or was that 139% less )

How Many People for it Take to Change a LIGHT BULB ?

A few months ago there was a system wide service outage at CARQUEST for EXPLORIS ( NexPart by WHI ) ... after the dust settled and all was back to normal - we took a look and gave the events a once over ... and asked the question : WHY DIDN'T CARQUEST notify us via e-mail or automated voice mail ( or both ).

LOOK at the image --- here is another warehouse that just seems to have their SHIT TOGETHER (as the saying goes ) .... 6:56am today we get a notification ... calls are placed to all our stores to notify ONE counterman and a REQUEST that that person tell the rest of their co-workers .... and at 8:16am am notification that they are back on line .... and the same calls to ensure the stores know they are back up.

So - -lets look at the issue with CARQUEST

-they have be terribly slow at rolling out e-mail access to their store managers .... hmm ....

-even though senior staff were urged to be more pro-active ... there has to be the BOSS(s) are reluctant to be innovative .... was that Joe Z (who has since retired)? or ?? T3 ?? Someone at the highest level is sending chilling signals for management to 'stay the course' --and that what they have been doing is GOOD ENOUGH. Who is killing innovation ??????

So - when the next outage happens .... we have to resort to calling in to see what the hell is happening ...and WAIT ....

PLEASE -- plagiarism is the most sincere form of flattery ......as the saying goes --LOOK at good idea's and BORROW them ....

I am not saying that this suggested change ALONE will solve ALL problems ... but is is a baby step and every baby steps helps ....

Now --back to the issue of NO TRAINING for EXPLORIS .... gee ...even a 4 minute youtube video on how this or that works .... for us ... show us the Multi-store Creator-Editor for one ... or how about a re-cap on where we are for the Nex-Gen e-cat issue --especially for us Cdn's --who are always keep in the dark ... ( the definition of a mushroom ) ...like when do we get INTERCHANGE for Nex-Gen .... yep --Cdn's do NOT have that ....

OK bosses at CARQUEST ( GPI ) --- your move .... and the last time I said that -- I was told my presence at the San Diego golf outing in Jan-2011 ( that I have attended for 4 years and pay for ) was no longer allowed ( and told months after I paid and sent int he reply ... but weeks before the event is happening (class - eh?) .... gee ...what else can you do to me ?? Guess I need to look out for black helicopters coming my way ... ( smile ) . I have added that to my resume - right along with the fact that I was the FIRST jobber to be fired from the EXPLORIS Advisory Committee -see Mike W --beat you at something ...

How do we EFFECT CHANGE at CARQUEST ??

Tweaking Security

Since opening the ugly issue of yearly review on security settings, etc ... a few things have changed. One IMPORTANT issue I am reminded of ---NEVER ASSUME that Key staff see what I see ... taking with some -- pushing for more understanding on how to write up new parts and make changes that effect all stores ( like a part that is booming -- make sure all stores in our group stock ) .... and guess what ...the settings needed some tweaking ... here is a small change .... the level 2 security ( typical key staff on counter ) now see JUST the Creator/Editor and do NOT SEE the Mass Update option in INVENTORY>>MULTI-STORE ... gee ...even I dislike looking at the mass update tool -- need much more training on that puppy ( hey - GPI --where is the ONGOING training for this system ?? )

Been told that this change applied EARLY to our stores -- will change to all stores everywhere on next security update .... yours might not have changed yet -check in a week ( month? )

======== Additional Change we have requested -- we would like for level 2 staff to at least see ( if not change ) the PN Codes in VENDOR>>LINES>> We are starting to use different PN codes to move CRAP part numbers in the various X lines ( since this system can NOT delete old crap parts out of system -- at least not yet or in the foreseeable future