April 4, 2011 at 7:03 pm
bitbucket-25253 (4/4/2011)
CirquedeSQLeil (4/4/2011)
WayneS (4/4/2011)
Well, it looks like I (finally) got a QotD question that didn't have everyone up in arms over it... whew!Well, if you want, we can certainly nitpick it :hehe:
Congratulations Wayne, fully understand how you feel. But if you want I most certainly can join Jason in moaning and groaning about asking a question on a product that has not yet been fully released for manufacturing.... or I can make a deal with you
in return please do not pick on my next question coming up I think on April 12th or 13th
Deal! (But you have to not moan or groan about my next one coming out Thursday also!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 4, 2011 at 9:32 pm
Craig Farrell (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
I like Craig's advice, but Michael's is probably where I'd start. Usually shrinkage affects inventory, so I'd compare ordering with sales and look for discrepancies. One of the things you want to do is compare sales v orders v profitability. Have an accountant or manager see if the numbers are in line with each other. Then start looking for pattern deviations.You can't just use history because someone might have been taking a five finger discount for years, and your historical data would not be correct.
Steve's correct. I was trying to give you the research start point rather then specific items to check on. He's right on hindsight. Start with details and working out the broader picture is how I learned those basics as well and will probably serve you better.
So with that in mind, here's some ideas:
Store level:
- Amount of item sold since between times ordered. Ignore theoretical 'existing inventory'. That's rarely right anyway.
- Amount of profit vs. amount of expected profit. This you'll probably want in regional groupings. You're not looking at any store week to week, you want to see what this week to last week's percentages was across all the stores, and see if anyone missed the boat. There may be exceptions here to your exceptions. (Did I mention these can get complex?)
- Exception sale differences. When a manager or stocker position is NOT on duty, does inventory ordered (especially useful in just in time locations, like a Walgreens) match closer to sales? Recheck history when a blip comes up on a specific person. Note: This is not a be all/end all. IE: We had wondered who was eating the damned toiletpaper... It was the janitor. The manager had told him to grab it off the shelves if the employee bathroom was out. *facepalm*. I really don't want to know what everyone in the store did that week.
Per sale level:
- Create a sale history.
- - Find an average number per item per sale. Give it a 20% delta allowance and find your outliers. You may need to categorize, or even subcategorize, the allowance here.
- - Find the reasonable maximum CASH PURCHASE sale amount. Look for oversized numbers. It's easy to void one or two items of a large sale after the fact. This won't indicate a problem, it's merely an indication to glance and doublecheck.
Per Employee Level:
- Get an average sale/day history. Give it an allowable difference, and then check on it. The guy running the photo shop/pharmacy is going to have a major difference in volume from the person who usually runs the front register.
Why all these items? Well, honestly, when I did this we were more concerned about cash to pocket issues then stock issues. Most of your stock indicators are going to come from noticing higher then expected inventory orders while certain people are in (or not in) a store. The problem is noone ever has a per day inventory count, it's just too hard to keep except on the high end goods, and everyone loses a box of something behind the rack now and then.
I wish you luck in this... It's not fun, and there's no way to not feel dirty when you're finished doing it. :rolleyes:
You will also need to consider Procedural shrinkage, where all your checks from one point to the next balance. At a previous company we had a doddering old late-80's system that was prone to outages. As we needed to ship product during an outage a manual process was put in place to track deliveries and these were later added to the system.
Problem arose when a warehouse worker colluded with a delivery van driver and a bunch of complicit customers to falsify the amounts of product shipped. There was a quirk with the manual process that allowed the extra product to go unnoticed, for years. It all came undone when, with a new system just 2-3 weeks away from go-live, the guys got greedy and ran the manual process when the old system wasn't down and an inquisitive warehouse manager saw the process in play and thought "I wasn't aware the system was down. I'd better check what is happening..."
So you can cross your i's and dot your t's and still have a problem. (Cliche misquote intended.)
Steve.
April 5, 2011 at 2:07 am
Roy Ernest (4/4/2011)
I think then I have to remove quite a bit of target stickers. I think the target sticker is on me... 😀First they tried to hit me from the back, then they tried to hit me from the side door. Since they could not hit me as easily like that, this time they tried to hit me head on.. 😀
Have you tried one of these new-fangled rectangular cars instead of the round one you've been using? Also, try driving in areas which don't have an electrified mesh overhead 🙂
Good to hear you are well Roy.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 5, 2011 at 2:09 am
WayneS (4/4/2011)
... in return please do not pick on my next question coming up I think on April 12th or 13th
Deal! (But you have to not moan or groan about my next one coming out Thursday also!)
Meh! The thread is now breeding sycophants 😛
Far away is close at hand in the images of elsewhere.
Anon.
April 5, 2011 at 5:03 am
Stefan Krzywicki (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
Do you know what shrinkage is, from a data standpoint? Or is that where you are looking for guidance?Once you define it, then you might play with the data mining algorithms a little, maybe even the Excel add-in to try and detect what you expect.
If you're looking for ways to define this, then I might ask managers how they detect it, other than catching someone in the act.
That is one of the areas I'm looking for guidance. What kinds of patterns should I be looking for? I don't think they currently do detect it other than catching someone in the act. Someone ringing up the same order 10 times in an hour when that isn't usually rung up 10 times in an hour? Someone ringing up an unusually large sale? Someone ringing up 1000 ketchup packets as a side? Someone ringing up an unusually small sale?
I may be late to this (I haven't read the rest of the thread yet), but as previously stated shrinkage doesn't happen at the cash register. You'll need the cash register for a baseline, but you compare that to your yearly (or quarterly) inventory for the initial base shrinkage. Then you have to consider samples (which tend to walk out the door), breakage (items written off as defective), and displays.
I assume, though, you're talking about restaurants / fast food given your ketchup comment?
Working off my McDonald's experience... Consider shrinkage as manager-comped meals (which they do when customers complain or there's a bad incident at the restaurant). Shrinkage might be hiding in the daily waste (is there a trend at certain shifts where there's more waste than usual? Inventory is done on a monthly or weekly basis at fast food places. Check inventory counts against sales. Another thing to think about is toilet paper and other non-consumables that might be "disappearing" from inventory faster than sales & employee shifts can account for.
April 5, 2011 at 6:24 am
Stefan Krzywicki (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
Do you know what shrinkage is, from a data standpoint? Or is that where you are looking for guidance?Once you define it, then you might play with the data mining algorithms a little, maybe even the Excel add-in to try and detect what you expect.
If you're looking for ways to define this, then I might ask managers how they detect it, other than catching someone in the act.
That is one of the areas I'm looking for guidance. What kinds of patterns should I be looking for? I don't think they currently do detect it other than catching someone in the act. Someone ringing up the same order 10 times in an hour when that isn't usually rung up 10 times in an hour? Someone ringing up an unusually large sale? Someone ringing up 1000 ketchup packets as a side? Someone ringing up an unusually small sale?
The general rule of thumb on data mining is don't go in with expectations of what you're looking for. Start out by determining what's a "normal" sale, then start looking at deviations from that. You'll find that some are okay, like maybe sales/hour go up during lunch/dinner hours, and you can then filter out those, and just follow what you find.
There are probably a small number of "everybody buys that combination" sets that would be expected, and they'll change over time, but you'll need to analyze for those first. And don't assume they're what "everybody knows".
The trick is, look at the data and determine what's "normal" first, then take a closer look at anything that makes you go "what?"
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 5, 2011 at 6:28 am
Jack Corbett (4/4/2011)
Roy Ernest (3/31/2011)
Out of Curiosity, isnt 90K US or 80K Euro the normal salary for a Sr. DBA?Definitely depends on region AND size of the company. I'm looking now and I'm seeing everything from 65k-90k. I haven't seen anything in 6 figures.
http://www.salary.com (not linking so you can feel safe about being Rickrolled) has data on average, max/min, by city, etc., in the US at least. I haven't checked whether they have international data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 5, 2011 at 6:29 am
GSquared (4/5/2011)
Jack Corbett (4/4/2011)
Roy Ernest (3/31/2011)
Out of Curiosity, isnt 90K US or 80K Euro the normal salary for a Sr. DBA?Definitely depends on region AND size of the company. I'm looking now and I'm seeing everything from 65k-90k. I haven't seen anything in 6 figures.
http://www.salary.com (not linking so you can feel safe about being Rickrolled) has data on average, max/min, by city, etc., in the US at least. I haven't checked whether they have international data.
Rickrolled? I haven't heard that term before.
April 5, 2011 at 6:32 am
Brandie Tarvin (4/5/2011)
GSquared (4/5/2011)
Jack Corbett (4/4/2011)
Roy Ernest (3/31/2011)
Out of Curiosity, isnt 90K US or 80K Euro the normal salary for a Sr. DBA?Definitely depends on region AND size of the company. I'm looking now and I'm seeing everything from 65k-90k. I haven't seen anything in 6 figures.
http://www.salary.com (not linking so you can feel safe about being Rickrolled) has data on average, max/min, by city, etc., in the US at least. I haven't checked whether they have international data.
Rickrolled? I haven't heard that term before.
It's what I did on the 1st. Put up a link that looks like it goes to something else, and instead it goes to a video of Rick whateverhisnameis singing that "Never Gonna Give You Up" song. It's a common enough prank that it's in wikipedia. Sort of a more harmless and safer for work version of the older goatse joke.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 5, 2011 at 6:42 am
Brandie Tarvin (4/5/2011)
GSquared (4/5/2011)
Jack Corbett (4/4/2011)
Roy Ernest (3/31/2011)
Out of Curiosity, isnt 90K US or 80K Euro the normal salary for a Sr. DBA?Definitely depends on region AND size of the company. I'm looking now and I'm seeing everything from 65k-90k. I haven't seen anything in 6 figures.
http://www.salary.com (not linking so you can feel safe about being Rickrolled) has data on average, max/min, by city, etc., in the US at least. I haven't checked whether they have international data.
Rickrolled? I haven't heard that term before.
Whoa! What? You're a youngster and you don't live in a cave, how is it that an old <insert inappropriate term here> like myself knows what rickrolling is? BTW, better to be rickrolled than lambrolled. That other one sticks in your head.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2011 at 7:17 am
Grant Fritchey (4/5/2011)
Whoa! What? You're a youngster and you don't live in a cave, how is it that an old <insert inappropriate term here> like myself knows what rickrolling is?
Somehow, Grant, I think I'm older than you are. @=) But thanks for the compliment.
April 5, 2011 at 7:21 am
Fal (4/4/2011)
Craig Farrell (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
I like Craig's advice, but Michael's is probably where I'd start. Usually shrinkage affects inventory, so I'd compare ordering with sales and look for discrepancies. One of the things you want to do is compare sales v orders v profitability. Have an accountant or manager see if the numbers are in line with each other. Then start looking for pattern deviations.You can't just use history because someone might have been taking a five finger discount for years, and your historical data would not be correct.
Steve's correct. I was trying to give you the research start point rather then specific items to check on. He's right on hindsight. Start with details and working out the broader picture is how I learned those basics as well and will probably serve you better.
So with that in mind, here's some ideas:
Store level:
- Amount of item sold since between times ordered. Ignore theoretical 'existing inventory'. That's rarely right anyway.
- Amount of profit vs. amount of expected profit. This you'll probably want in regional groupings. You're not looking at any store week to week, you want to see what this week to last week's percentages was across all the stores, and see if anyone missed the boat. There may be exceptions here to your exceptions. (Did I mention these can get complex?)
- Exception sale differences. When a manager or stocker position is NOT on duty, does inventory ordered (especially useful in just in time locations, like a Walgreens) match closer to sales? Recheck history when a blip comes up on a specific person. Note: This is not a be all/end all. IE: We had wondered who was eating the damned toiletpaper... It was the janitor. The manager had told him to grab it off the shelves if the employee bathroom was out. *facepalm*. I really don't want to know what everyone in the store did that week.
Per sale level:
- Create a sale history.
- - Find an average number per item per sale. Give it a 20% delta allowance and find your outliers. You may need to categorize, or even subcategorize, the allowance here.
- - Find the reasonable maximum CASH PURCHASE sale amount. Look for oversized numbers. It's easy to void one or two items of a large sale after the fact. This won't indicate a problem, it's merely an indication to glance and doublecheck.
Per Employee Level:
- Get an average sale/day history. Give it an allowable difference, and then check on it. The guy running the photo shop/pharmacy is going to have a major difference in volume from the person who usually runs the front register.
Why all these items? Well, honestly, when I did this we were more concerned about cash to pocket issues then stock issues. Most of your stock indicators are going to come from noticing higher then expected inventory orders while certain people are in (or not in) a store. The problem is noone ever has a per day inventory count, it's just too hard to keep except on the high end goods, and everyone loses a box of something behind the rack now and then.
I wish you luck in this... It's not fun, and there's no way to not feel dirty when you're finished doing it. :rolleyes:
You will also need to consider Procedural shrinkage, where all your checks from one point to the next balance. At a previous company we had a doddering old late-80's system that was prone to outages. As we needed to ship product during an outage a manual process was put in place to track deliveries and these were later added to the system.
Problem arose when a warehouse worker colluded with a delivery van driver and a bunch of complicit customers to falsify the amounts of product shipped. There was a quirk with the manual process that allowed the extra product to go unnoticed, for years. It all came undone when, with a new system just 2-3 weeks away from go-live, the guys got greedy and ran the manual process when the old system wasn't down and an inquisitive warehouse manager saw the process in play and thought "I wasn't aware the system was down. I'd better check what is happening..."
So you can cross your i's and dot your t's and still have a problem. (Cliche misquote intended.)
Steve.
Currently I'm only being asked to check POS discrepencies, but that's something I'll bring up. Thanks.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 5, 2011 at 7:24 am
GSquared (4/5/2011)
Stefan Krzywicki (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
Do you know what shrinkage is, from a data standpoint? Or is that where you are looking for guidance?Once you define it, then you might play with the data mining algorithms a little, maybe even the Excel add-in to try and detect what you expect.
If you're looking for ways to define this, then I might ask managers how they detect it, other than catching someone in the act.
That is one of the areas I'm looking for guidance. What kinds of patterns should I be looking for? I don't think they currently do detect it other than catching someone in the act. Someone ringing up the same order 10 times in an hour when that isn't usually rung up 10 times in an hour? Someone ringing up an unusually large sale? Someone ringing up 1000 ketchup packets as a side? Someone ringing up an unusually small sale?
The general rule of thumb on data mining is don't go in with expectations of what you're looking for. Start out by determining what's a "normal" sale, then start looking at deviations from that. You'll find that some are okay, like maybe sales/hour go up during lunch/dinner hours, and you can then filter out those, and just follow what you find.
There are probably a small number of "everybody buys that combination" sets that would be expected, and they'll change over time, but you'll need to analyze for those first. And don't assume they're what "everybody knows".
The trick is, look at the data and determine what's "normal" first, then take a closer look at anything that makes you go "what?"
Yeah, this is one place where it is good to be a consultant and to be learning the business, I have few preconceived notions. My plan was to look for things that were out of place or unusual based on patterns in the data, whatever they might be and try to figure out what those anomolies represented after they were found.
Hooray science! : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 5, 2011 at 7:59 am
Brandie Tarvin (4/5/2011)
Grant Fritchey (4/5/2011)
Whoa! What? You're a youngster and you don't live in a cave, how is it that an old <insert inappropriate term here> like myself knows what rickrolling is?Somehow, Grant, I think I'm older than you are. @=) But thanks for the compliment.
Is this conversation gone end in the direction I think it is going to ??
( We already had an #oldfarts club at twitter :w00t: )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 5, 2011 at 8:27 am
Brandie Tarvin (4/5/2011)
Grant Fritchey (4/5/2011)
Whoa! What? You're a youngster and you don't live in a cave, how is it that an old <insert inappropriate term here> like myself knows what rickrolling is?Somehow, Grant, I think I'm older than you are. @=) But thanks for the compliment.
Late 30s/early 40s? Right? That makes you young in my book, since I'm in that same range and I still get accused of being a kid by lots of people.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 25,366 through 25,380 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply