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 )