Defect Ratios - Part #2

In part 1 - you will notice that we use TEXT and not NUMBERS for the part number --that helps you keep LEADING ZERO's in part #'s - a tip that is handy

We found it easier to get TOTAL SALES from a 6-3 report and not add up every invoice here ...you use a similar method - but use your Ethernet line if you have one and go thru Acconnex --WAY FASTER ( 20 to 50 times faster )

The 6-3 report is a GREAT REPORT that we do every month to see where we are on select accounts (salesmen accounts and key other accounts )

I am sure I have written on how to take TWO DIFFERENT spreadsheets and take data from one into the other using something we thing is way better than VLOOKUP or HLOOKUP ( =INDEX( range , MATCH ( part #, range of part #'s, 0 ), x is the column you bring back ) --this is trickey at first - but very very handy

Use sub totals from your 6-9 report and get the values for the warranty returns by customer #.

NOW - great tip we found -- how do you get the SUB RTOTAL ONLY data .... use EDIT ... GO TO .... SPECIAL ..... VISIBLE CELLS ONLY ..... and copy the data. The PASTE SPECIAL for VALUES ONLY into a new spreadsheet. From here - import into your 6-3 report (or a clone of it ) and calculate defect ratio's

So - what steps might I have skipped - one is the reverse the negative sign ( older Excel programs like I use do not have it - so get the www.asap-utilities.com add on ( come on--make this for the MAC !! - so far only PC only )

There you have it --data that you can USE -- but with care & caution -- since habits take time to improve - and there might be valid reasons for specific warranty ratio's

Better data makes better decisions

how to calculate defect ratio's for customers

This is a bit complex - and assumes you have some good excel skills.

Master these skills -
1) get a laptop ( or regular PC ) and multiview PC software. Set up the 'capture screen data' option ( mentioned previously in this blog. You have to use a SERIAL line ( not the ethernet Acconnex fast line ) - so it will be slow for capture of the 6-9 data

Set up the laptop/PC as a PRINTER (call J-Con help desk) and have at the > prompt -=-do NOT sign into menu or the port will think it is BUSY. From another PC --send the 6-9 dump to the Laptop/PC ( which you are capturing the data).

If you get more than 500 pages you will need to open into a word doc and split into 500 page sections ( maybe you can go to 600 or 700 pages before you exceed the excel limit --since there is so much crap - extra lines and headers )

Import into Excel - click on the image to see the template setting to cut at what position ( found thru trial & error )

Now you have EVERY INVOIVE you have done in that time frame ---

Transaction code types
DR - Dept Return
FR - Fleet Return
IAP - Web-Link
IR - store return
IS - Store Use
RET - cust return
SR - Shop Return
SU - Store Use
SVC - ???
WHR - Warehouse Return
WR - Warranty Customer

You can sort and have fun ....... end of part # 1 --- see part # 2 on defect rations coming soon
WVR - Warehouse Warranty

Ont stores with enviro fees

Some J-Cons' didn't have an upgrade to the 8-15 control panel --and as such were not charging taxes on the new Ontario envior fees - this has now been cured for our stores -- how is yours?