Dumping the Whole Inventory Report - part 2

I had written this piece ( Click Here ) about how to dump the inventory file - after being prompted to get off my ass and do it by a fellow CQ Jobber ( and excel geek ) - Bob Miller.

Holly Cow -- for as good as I thought I was - this is taxing my abilities

GROUPING ... had never run across GROUPED sheets in Excel -- hint - you will need to know - google it

FAST WAY to CHANGE TEXT to NUMBERS - since I have an older copy than Bob--the easiest way ( until I Get Office 11/Mac that was just announced) is to copy a cell with a 1 ( digit of one ) and paste special "Multiply Only" --something else I had never done before - but after wasting an hour -I now know to paste just the 125,000 rows - or remember to flush out the excess data from rows 125,000 thru 1,040,000 .... if you have a really old version you may be limited to 125,000 or 62,000 rows --time to upgrade.

Once we have it right - and tweeked the way - well you can sort to the cows come home - and see all sorts of patterns and issues ... like how much do you have in Mvt code 4 that you have NEVER sold .... or how much have you sold in Mvt Code 7 - that maybe you better up the stock level ... etc etc

This exercise is not the newbie in Excel - start with the smaller stuff - but geeks will like this - and will tax their abilities ( well - it does for me anyways )

To DASH or not to DASH -- that is the QUESTION

Ever since we heard that DASHES matter ( most systems treat a dash as a transparent item - more for cosmetics to make thing look pretty -- but oh no --NOT EXPLORIS )

Ever wonder what happens if you added a part without a DASH -- the images show part that was entered by someone at one of the stores .... and the result AFTER we imported the full line of data ... well ..at least it didn't DUPLICATE the part # ( well-for this one test anyways )

Any other CARQUEST Jobbers have any experience with the damm dash ... ?

A tough thing to EXPLAIN -- but someone has to try

Somehow we are NOT co-ordinated -- here is the PROBLEM

Part # a is being replaced by Part # b .... that happens all the time -- but there is something WRONG on the way it FLOWS thru the system and ends up in EXPLORIS ( Nexpart by WHI ).

Example - PLB 12258B1 is changing to a much easier and INDUSTRY STANDARD part # H1 B1 ( b1 referring to the display pac ) ... but when you see the MOVEUP SCREEN ( click image to make larger ) --unless you have memorized these ....how in the hell would you have any idea ... and the 2nd part # also is updated from 12258CVS2 to H1 CVS2.

The point I am trying to make is --that someone at CARQUEST has to WALK thru the entire process and see what the jobber sees and has to deal with .....

What would help - a MUCH BETTER moveup screen for one thing ... with the option to see DESCRIPTIONS -- and remember we have to see BOTH desc #1 and #2 ....

IF only the delay in getting fixes and improvements wasn't at such a glacial pace ... cloud computing is supposed to offer FAST improvements - WHI has sure burst this idea ... we have seen slow but this is slower than slow.

I will forward a copy to my two fav's - Mike Deal and Dane Hunter - so I can get them to have a coffee and see this point I am trying to make...

HOW does a JOBBER do a moveup - when there are many supercessions in a line ?????? If we go out of step with what the warehouse does - all we get are B/O's ...

Note --warehouse sales are MOVED to the new part # --so sales you see are sales on the part AFTER the first move.

DUMPING the whole Inventory Report ... hmm ...

Talking with Bob Miller - he has been dumping ( exporting ) the entire inventory INFO report for his store for more than a year on a monthly basis ... sure put a shame on me ...

Points

-remember to save as Excel data only

-the file will cut into 62,000 records on sheet #1 - then continue to sheet#2 - and if you have a HUGE amount of stock - to sheet #3 ( we have only been live for 10 mths - so we have approx 120,000 records - you will find a HUGE amount of crap ..remember in J-Con we could only have 62,000 records -- heck - you have 40,000 parts o f sheer junk - too bad you can't delete

-handy way to change TEXT to NUMBERS - do a google search - you can add a 1 to empty cell - copy and then paste special to all cells you want to change - paste special - Multiply -- neat ... first time I have done it that way ( Bob tells me that latest Windoze version has an even better way - but I have Mac version )

-we added a couple of columns - Sales in Dollars ( sold 12 months x cost )

- total Inv $$ ( qty on hand X cost ) ...these columns will help you sort out your problems to the top or bottom on Excel

-sort out the inactive parts and move to new sheet - consolidate remaining data into one sheet -and start sorting

So I have some learning on this report. The first pass we sorted on ascending sales ( looking for ZERO sales $$ ) and descending Inv $$ .... so we can see what we have NOT SOLD in 12 ( or 24 )months ... and look for problems

Stay tuned - and thanks Bob for the 'gentle' reminder to do this .... . smile

Define an EXCEL GEEK ?

So you know you are a redneck ... when ..... is the start of the punch line for many jokes ...

Time to start a new one -- you know you are an EXCEL GEEK when .... and this is aimed at Bob Miller from Walker Rd Auto ... Bob has now passed me to the next level of EXCEL GEEKdom

Yesterday he calls (Bob doesn't type as best I can figure - he rarely posts on the CARQUEST JOBBER Message Board ) -- but boy --does he have good idea's . .. anyways he asks if I am still dumping all the inventory into Excel. In J-Con you had to do this to get any good report but in EXPLORIS ( NexPart by WHI ) the reports are so crappy I have avoided this ... waiting for the universal report writer that may never happen ( the 2-20 screen in J-Con ). Aways he tells me he IS dumping the data and pulling quite a bit f good info

- the third column that is UNMARKED by some sku;s have an asterisk * --these parts are flagged as being REPLACED by something else - this allows you to sort and then look further on issues ... now if we had additional fields in the INVENTORY INFO report - we would look at te DESCRIPTION#2 (which is only available on a count list ) ,.... but this is the system we have now.

-Bob is also sorting on the sales for 12 and sales for 24 months to see dead stock ...

OK -he has shamed me into starting to run this report.

BUT --if that was not enough - he calls today and asks questions on the warranty returns - see REPORTS-FINANCIAL-Line Sales&Purchases ( that would be a 5-1-5 if someone had the vision to number the menus ) ... he is dumping this data and sorting for the bad lines -- now I have a lot of work ahead.

Good points Bob

An EASY hour to save

Want to see how each store can save an HOUR a month ? Well - some stores it might be on 15 minutes but for other stores ( or multi-stores ) it can be well over an hour -- so lets round it off and call it an hour .... That is EVERY MONTH --12 months a year and for how many years ... times how many stores are on EXPLORIS ( Nexpart by WHI ) --and even little things like this will add up.

WHAT is it ?? -if you make the PRINT BUTTON work on POST ENTRIES -then all the extra charges like extra fees on customers WEB-LINK accounts for extra labor programs ..etc would print out --we could state what the charge is and ELIMINATE a lot of customer calls on questions on their statements.

LITTLE things to FIX in EXPLORIS just do not get much easier than this --do their ??????

We wait ....... and wait .....

Thanks to Rhonda for reminding me.

PS -- OF COURSE we would also like to have more DETAIL on the actual statement - where the EXPLANATION is also printed ... think like a customer and we can make better software

Interchange -just a suggestion

Wondering out loud here --on INTERCHANGE data ... I am a firm believer that no enough attention is placed on interchange data ( and the lack thereof - or the errors & omissions )

Look at the image ( click to enlarge )

1) the SM5147 is NOT Monroe --it is KYB ...how do these errors occur -and are they wrong for ALL data or did I get lucky and pick the ONLY ONE - I hardly doubt that

2) when we mistakenly key in a GOOD PART NUMBER ( in this case SHK 142413 ) --- gee --why not show the part number in the data ???????? and make life easier for the counter staff

Note sent to Raleigh --any support appreciated.

For this example we used the Activant e-cat as I believe the interchange is still missing from the NexGen e-cat ... hope someone taps me on the shoulder to remind me when the LONG OVER DUE interchange finally makes it to the NexGen e-cat .... in Exploris ( NexPart )

NOTE --it s ONLY Canada has is missing the Interchange in NexGen -- the USA have it -- OK - yet another example of ..well .. you know

Worldpac in Activant

see our store blog -- click here -- Worldpac e-cat in Activant --too good to be true -- wel -- maybe -- we need to dig the details on the part #'s used

But --there can be hope - for Activant users ... and you will be reminded that MCL file control is a handy thing.

DREAMING of better integration in WL2 to EXPLORIS

In Canada the IT dept is finally rolling out WebLink2 ( WL2 ) - so we are looking at what is new and different -- came across this on the JV store site -- the ability to request invoices and statements ... neat - but wait --this is NOT on our Independent Jobber customer's page ... hmm...

So the question is - HOW LONG will it take to get something similar - so our BEST CUSTOMERS can request on-line extra invoice copies or a statement ...all on line all from one computer to another .....

One Again - OFF Again -- is now back - On Again

Something that I never ever knew -- has been on --then turned off ( due to other issues ) - and is now ON AGAIN. What is it? The script in EXPLORIS ( Nexpart by WHI ) that sees a movement code of ZERO - and will set back any re-order point to ZERO as well.

I wrote this piece about an obsolete report ( click Here ) and had Mike Mizelle take the time to write me explaining I am wrong --and guess what --I am wrong. I also appreciate Mike's efforts to help make the info right.

Obsolete Lists were very awkward in J-Con - but in Exploris they have gotten better. However --there is still a ROAD BLOCK - while we WAIT for Toronto to update the movement codes ... we found that Toronto would DELAY updating the codes -and that stops us from having things move as smoothly as Mike says they should - the image with Dorman 210-631 was verified that it was still a mvt code 8 and not a zero today when I called Angella ... hmm ... the report dated Sept 1 2010 and today is Oct 5h

BTW -- I will post the entire comment from Mike in the original post ( Mike was kind enough to OK this - bravo for him )

How to make Countermen Happier ?

Walking by Ken at D&T just now - he grabs me and says 'if only they could ....'

His BIG BEEF on the invoice-E-cat combo is the ability to 'step' out of sourcing momentarily and got straight to e-cat. Situation -- customer asked for outer tie rod end for 1969 Whatever -- we go and look it up - and go to source it ... then customer says -- hay - I should have an inner tie rod end as well ...

EXISTING - we have to Return 'to Invoice' ( or Cancel - both buttons on the sourcing screen )

PROPOSED - add a button that allows the user to return 'to E-Cat' and bypass the delays seen in the existing. This could also hold the info for cash customers.

Call # 1355773 called in 10-4-10 .... lets see how long this request takes .... at least Ken - you know I asked on behalf of you and all countermen everywhere.

Productivity in EXPLORIS

The attached chart shows TOTAL HOURS worked by our office by year - the large drop happened when we switched to CARQUEST (part way thru one year ) ... but notice the INCREASE in hours worked this year as a result of the new EXPLORIS computer. We are spending MORE TIME - we challenge any claim that this is a more efficient system.

The hours represent standard office duties ( but not for inventory control ) and my hours are not part of this data. Examples of MORE WORK required to do the same job -- posting payments. We have reported these time wasters - but ...