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.