[personal profile] pyxaron
... with too much information.

It's fun, but at the same not. I've been messing a bit with the idea of actually having more information concerning sales, inventory, purchases and the such at work. As of now, we can only get the "current" information, no way to know how things were a week, a month or a year ago. It's now or nothing for everything.

An easy fix would be to simply record things live. But then if the machine goes down, there's a blank, and that still would be only worth something a few years from now once enough of a database has been built.

So the fun idea i came up with was simply to use every other bit of data i could find to reverse things and figure out how things were back then. Should be simple enough, i just need to add sales, remove purchase and the such.

Turns out, this led me to discovering all our current data is wrong. There's some major bugs in the system i hadn't even noticed yet. One of them being that sales, money-wise are calculated right, but inventory is not. In other words, if an item sells by the box (BX) and individually (EA), normally, a value is assigned to each for inventory. As in, BX = 1, EA = 0.125, as example.

Now, the problem is, if i sell one box and 4 units, and my inventory counts boxes, i have sold ((4x0.125)+(1x1)), so 1.5 units. The problem is, the inventory system in that case adds up boxes and each units, so i end up with a quantity of 5 in the transaction logs. However, the total of the bill adds up, due to the prices being set according to "EA" or "BX", not calculated.

So i thought this would be simple, i just have to take the total cost of the items, the price of the smallest unit it could be sold as, and divide by it. If an "EA" is 0.46$, and is 0.125 of a "BX", if the total is 7.03$, i can just divide 7.03 by 0.46, and i have 15.something, meaning i can safely-ish assume i sold 15 "EA", or 1.875 "BX".

This also brings up the fun part of dealing with many rounded up values.

But the real fun i discovered there is that a LOT of our merchandise is sold with some form or other of discount. So i can't even properly use the total price of the items, since the discount is applied, but not stored anywhere. No way of knowing if they paid full retail or had 15% off the retail price.

However, i do have the profit margin of the transaction as information there. Not much help on it's own... I do have access to the cost of the items, so i thought this could be simple. Remove the profit from the retail price, you get the cost of the sale, use the cost of the sale divided by the cost of a stock unit, and that should get me the amount of items sold.

... and not that simple either. Turns out the profit of the transaction is calculated not according to the purchase cost of said item, but the average cost value of items in stock.

Also turns out that this value is also not stored anywhere in the database, only re-calculated when a purchase order is completed to update the cost according to the new items, going by ((average cost * items in stock) + ( purchase cost * items purchased)) / ( items in stock + items purchased), giving the new value.

So whee...

However, i do have a proper history of the transaction value of previous purchase orders. But the value on those is based on the purchase cost, not the average cost. But logically, if the stock was 0, the purchase cost becomes the average cost as soon as new stock comes in.

But having no history, i have no way of knowing when that last happened.

Except for one. Before the store opened. Everything was at zero then, obviously.

Which led to one big stupid simply complicated solution. To start at zero.

If things were perfect, it'd be easy. Step day by day, on each day re-add the matching purchases, calculate the average cost, get the amount of items sold from the cost of sales, subtract that to the inventory, move forward one day.

Except that things are not perfect. That only gives part of the big picture, because it doesn't take in account theft, in store breakage, items returned to suppliers, items left behind after being paid for, items changing into others, etc...

And those, sadly, while i've go some form of information about, are all as useable as the sales. The only reliable values i have about those are the cost values, once again, since it's what government verificators rely on when checking our inventory and accounting.

But, well, all based on average cost still.

So the only way, once again, is to re-calculate step by step from zero, taking in account all those...

And fun, it once again doesn't add up. Whee...

Turns out, all those numbers are also rounded up in various way, with no decimal, or 2, 5, 8 digits after it, rounded up, rounded down, rounded to the nearest half, etc...

So i'm still working on that fun part right now, i have to guess how things were rounded up, so i can do the same with my own numbers. Would be fun if this wasn't all done in a mix of old compiled languages that i have never really heard of before. B4BB? Anyone knows what that is? And IBM 4680 BASIC... More documented, but i have nothing but the binaries.

Some of those things are at least compiled iSQL reports, so some of it is still plaintext-y in the file, at least.

But yeah, pretty interesting. Considering that the store has over 5k transactions, over 100k$ worth of them, each day, plus all the other data, things are getting pretty interesting.

Still, so far, i seem to have figured things out mostly right, since i've got only about 7% of the inventory where the result for today, as in, what the value in stock should be according to my program is, doesn't match the physical reality. About 13% doesn't match with the "current" values of the DB, but it seems that quite often it's said "current" value that is wrong. And only very few of those have a difference of more than one unit.

... the problem with all this, is that it's getting to the point where i more than once get lost into that so much that i fail to notice everything that goes on around me. It's eating my braaain!

... hey, it's 10pm, i need to eat today.



July 2011

101112131415 16

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 26th, 2017 04:13 pm
Powered by Dreamwidth Studios