Formula for flow-through of product

[Kuyuk]

Client asked me to calculate flow-through for product (example being bottled beverages) to track for evidence of theft..

Problem is I can't for the life of me either think of a logical formula for it, or make sense of the data I am getting.


What I've settled on for a basic formula=

(Starting inventory + Number purchased) - (Ending Inventory + Number sold) = net change in inventory

But I'm drawing a blank on the next step, and I don't know why. Usually I'm good at this stuff :(


Some weeks the net change was +, some was -, but trying to think of how to tie it together to determine theft?

Also the shitty thing is, purchases are M, W, F.
Inventory is done Friday.
Sales for week go from Monday - Saturday.




Any thoughts?

[Keller]

Are there any other variables? Damaged product, I guess.

It seems like the only thing you can do differently is to calculate more frequently. But given that ending inventory is calculated once per week, it's pretty much impossible.

[Kuyuk]

Other variables? Not to my knowledge unless you can think of something

No real damaged product in this case.


For example: For 1 month:

Beginning inventory: 4729
Ending Inventory: 3523

Inventory difference = -1206 actual on hand


Purchases: 19020
Sales: 17,776

Difference: +1244 to on hand (inventory)

So, given this information, I'm missing 2450 units? Or am I smoking something?

[Rolis]

Could be you have some asshole theif stealing and then returning the products at a later time.

Took me a couple times reading the question. If i understand it correctly your formula for theft seems good enough if 0 means products sold are accounted for and no inventory is unaccounted for.

The other guys suggestion of damaged goods seems probably and should be accounted for in the equation.

[Rinualdo]

With returns, you'd have a disparity between sales and inventory.

[Kuyuk]

There's been no returns, or compensation for damaged goods.

[Rolis]

Other variables? Not to my knowledge unless you can think of something

No real damaged product in this case.


For example: For 1 month:

Beginning inventory: 4729
Ending Inventory: 3523

Inventory difference = -1206 actual on hand


Purchases: 19020

Sales: 17,776

Difference: +1244 to on hand (inventory)

So, given this information, I'm missing 2450 units? Or am I smoking something?


This sucks writing response on my phone. I think i see your problem -- you are trying to combine apples and oranges if i understand your example correctly you are trying to add units and dollar amount. I just assumed the second set of numbers was a dollar amount. If that is not a dollar amount ignore this.

[Rolis]

From that data provided in the example you gave. I interpret the information like this.
at the end of an inventory period you have a delta of 1206. The 1206 represents sold and missing units. The 3523 is the units that you have on hand as inventory in the story. I am not sure what to make of The second set of information. What exactly does the purchase data and sale data mean? Is that the cost of how many units bought for that inventory period or month? Sales the money made from unit sales. You might also need a unit cost for purchase and unit cost at sales for that data to be useful. Then you can extract the units stolen knowing that information. If you cab post that i can tell you the number with formulas to get there.

[Keller]

Other variables? Not to my knowledge unless you can think of something

No real damaged product in this case.


For example: For 1 month:

Beginning inventory: 4729
Ending Inventory: 3523

Inventory difference = -1206 actual on hand


Purchases: 19020
Sales: 17,776

Difference: +1244 to on hand (inventory)

So, given this information, I'm missing 2450 units? Or am I smoking something?

I would simplify a little.

Beginning inventory (4729) + units purchased (19,020) - units sold (17,776) = ending inventory.

To the extent that actual inventory on Friday is different than ending inventory in the calculation above, you've got missing units.

One issue might be how they account for units sold. Are units shipped/sold on Friday prior to inventory included in the "units sold" for the week? If not, that might be your discrepancy.

[Rolis]

Wow.. Okay. My bad totally ignore my posts. I misunderstood. But after rereading and calculating i also get 2450... Not much help for you here.

[AnticorRifling]

If you're just looking for shrinkage are you doing cycle counts on a more frequent basis of a fast mover than just monthly?

You need to know starting point, amount in, amount out, and number of sold units. If you can't balance based on those 4 numbers you've got shrinkage.


Starting inventory lvl + any receipts of inventory after count - number of sold units = inventory on hand.

If cycle count != inventory on hand you've got shrinkage.


If you're keeping this data by hand and your Kardex isn't up to date you're going to fuck yourself. Shrinkage doesn't always have to be theft, errors in receipts, errors in sales, misplaced or discarded product can all add to shrinkage.

Taking the numbers from your later post I'd look at it like this:


4,729 + 19,020 - 17,776 = 5,973

If you're doing a physical count and you're getting 3,523 for your actual count then yes you're missing a lot of product. (2450)


When you're doing your selling/counting/receipts/etc is it all in one unit of measure UOM or do you have valid alternate UOM? I've seen counts go bat shit stupid when someone counts in SKU qty but everything else is calc'd for box or case qty.


We could have some more discussion on this if you'd like, just let me know if I'm missing something or if any of the above seems off.

[Keller]

Anticor just made what I said much more complicated.

Just more proof that lawyers > NFL centers.

[g++]

Im really just waiting for Stanley to explain it to me so I can understand.

[Kuyuk]

If you're just looking for shrinkage are you doing cycle counts on a more frequent basis of a fast mover than just monthly?

You need to know starting point, amount in, amount out, and number of sold units. If you can't balance based on those 4 numbers you've got shrinkage.


Starting inventory lvl + any receipts of inventory after count - number of sold units = inventory on hand.

If cycle count != inventory on hand you've got shrinkage.


If you're keeping this data by hand and your Kardex isn't up to date you're going to fuck yourself. Shrinkage doesn't always have to be theft, errors in receipts, errors in sales, misplaced or discarded product can all add to shrinkage.

Taking the numbers from your later post I'd look at it like this:


4,729 + 19,020 - 17,776 = 5,973

If you're doing a physical count and you're getting 3,523 for your actual count then yes you're missing a lot of product. (2450)


When you're doing your selling/counting/receipts/etc is it all in one unit of measure UOM or do you have valid alternate UOM? I've seen counts go bat shit stupid when someone counts in SKU qty but everything else is calc'd for box or case qty.


We could have some more discussion on this if you'd like, just let me know if I'm missing something or if any of the above seems off.


There's a lot of random three letter combinations in there...

I do all counts by hand, myself, every week, so while I can say they're fairly accurate, I can also say I would fuck them up consistently in the same fashion week after week should it be a clerical error. We don't have a fancy bar scan system for inventory or check outs...


This is the first "shrinkage" assessment we've done at this account.. and we're only assessing the beverage cooler at this time.

But it looks like my numbers were correct, no matter which formula I used.. lots of MIA product.

[Keller]

There's a lot of random three letter combinations in there...

I do all counts by hand, myself, every week, so while I can say they're fairly accurate, I can also say I would fuck them up consistently in the same fashion week after week should it be a clerical error. We don't have a fancy bar scan system for inventory or check outs...


This is the first "shrinkage" assessment we've done at this account.. and we're only assessing the beverage cooler at this time.

But it looks like my numbers were correct, no matter which formula I used.. lots of MIA product.

Did you see my last point about accounting for units sold on the day of inventory?

[Kuyuk]

Yep, in general, we do inventory after/at closing, as we close at 2p on Fridays.. so it's fairly accurate on that point.

[Alfster]

If you're off by this much, it's not your math that's the problem.

Someone's giving out free beer to themselves/friends.

Install some camera's in non obvious spots, and you'll find the problem in no time.

[Rinualdo]

I'm waiting until Latrin gets a hold of this thread.

[Kuyuk]

No my pepsi bottles cannot be sold in matching socks.

WTF is wrong with you?! Do you KNOW The cost of matching socks?!?!

[Jace Solo]

What is your POSI system?
What days do you get shipments of said product? How many times a month?
Who does the inventory counts? When? Who logs the invoice data? Same person as the inv counts?

I set the whole thing up for entire restaurants that have used POSI-touch, Aloha, MMX, etc.
You can do it all on a spreadsheet though through. How trust worthy are the managers? Are they given a way to account for waste (ie. QSA/Voids due to employee waste, patron waste, etc.)

Also, do the managers have bonus incentives based on the amount of product wasted verse sold? This has a HUGE affect on inventory practices. Much like bonus structures based off Labor %s.

I know some of these questions don't pertain to your math but it'd make your client a whole lot more efficient.

[AnticorRifling]

What days do you get shipments of said product? M, W, F


How many times a month? Roughly 12 times a month.


Who does the inventory counts? He does


When? Fridays after COB


Who logs the invoice data? Same person as the inv counts? Invoice data I'm guessing is the purchase order receipts/bill of lading/etc invoice is really for A/P to handle and needs to match receipt :)


I set the whole thing up for entire restaurants that have used POSI-touch, Aloha, MMX, etc.
You can do it all on a spreadsheet though through. How trust worthy are the managers? Are they given a way to account for waste (ie. QSA/Voids due to employee waste, patron waste, etc.)

Also, do the managers have bonus incentives based on the amount of product wasted verse sold? This has a HUGE affect on inventory practices. Much like bonus structures based off Labor %s.

I know some of these questions don't pertain to your math but it'd make your client a whole lot more efficient.
First site they are looking at and I think it's him spearheading the research so I doubt their is a inventory variance bonus scheme in place.

Also F U Keller I didn't make it more complicated I made it more accurate. Inventory Management and Distribution Control is what I do (when I'm not snapping a football).

Kuyuk the math is confirming what you're thinking and what Alfster said; you're leaking product like a sieve.

[Back]

Kuyuk,

This seems extremely simple.

(Starting inventory+delivery)-sales=ending inventory-actual inventory. If the number is off by a large margin then you know people are stealing.

Install a 24/7 cam. They are cheap these days.

[Rinualdo]

Install a 24/7 cam. They are cheap these days.

Make sure to leave the side of the camera off for better airflow.

[Jace Solo]

Ok, so it was 4am and I missed some of that jazz.

Why exactly are you doing shipments that many times a week?

I think you would have a much easier time finding the leak as well as keeping your inventory on point if you reduced the amount of shipments. Is storage the problem? It's "soda" products right? So we know it's not cause their perishable. God, I'm still so tired. I need to quit that over night serving job.



M, W, F

Roughly 12 times a month.

He does

Fridays after COB

Invoice data I'm guessing is the purchase order receipts/bill of lading/etc invoice is really for A/P to handle and needs to match receipt :)


First site they are looking at and I think it's him spearheading the research so I doubt their is a inventory variance bonus scheme in place.

Also F U Keller I didn't make it more complicated I made it more accurate. Inventory Management and Distribution Control is what I do (when I'm not snapping a football).

Kuyuk the math is confirming what you're thinking and what Alfster said; you're leaking product like a sieve.

[AnticorRifling]

Ok, so it was 4am and I missed some of that jazz.

Why exactly are you doing shipments that many times a week?

I think you would have a much easier time finding the leak as well as keeping your inventory on point if you reduced the amount of shipments. Is storage the problem? It's "soda" products right? So we know it's not cause their perishable. God, I'm still so tired. I need to quit that over night serving job.

Just guessing I'm going to say it's a combo storage and just in time path of replenishment schedule that's resulting in receipts 3x a week.

[Alfster]

I have no idea what part of the country your in, but do you physically count your inventory before accepting shipments?

In the Midwest - Pepsi was notorious for being waaaay off on their inventory counts. Had problems with some of the smaller liquor vendors too.