Mathematical approach to derive function

I have a data sheet which lots of data, all spread out over several columns. The data extracted below will show the problem I am facing:

data sheet example

This table represents changes in an inventory system. Positive amount changes mean new items were added to the inventory and the item price for these are known. The item price equals the import purchasing price.

When an order was made, the corresponding amount change is negative, and the price is unknown. The algorithm should fill in these unknown prices.

The items that have been the longest in stock should be sold first, at the same price they were bought. Once such a batch is depleted, the next batch should be used to determine the price of a sale.

For example, in row 3, 3 items were sold, and we are left with 9 items in the inventory. We know by looking at the data, that the single item price for these 3 products is 49.55. But of course, it starts to get more complicated as items have been added and subtracted:

Take the last row. At that moment, the first batch (at 49.55) has already been sold completely. The next batch (at 48.25) only has 2 items -- not enough for the 7 that are ordered. The batch after that (at 47.23) has enough to provide for the remaining 5 items. The price per item should thus be a weighted average of these two prices.

So now, I am looking for a function or algorithm to determine the item price that should be used when an order was placed.

1 answer

  • answered 2018-01-13 17:22 trincot

    Here is some python code that implements a FIFO algorithm on selling items from stock:

    stock = [
        { "before":  0, "change": 10, "price": 49.55 },  
        { "before": 10, "change":  2, "price": 48.25 },  
        { "before": 12, "change": -3, "price": None  },  
        { "before":  9, "change": -3, "price": None  },  
        { "before":  6, "change": -4, "price": None  },  
        { "before":  2, "change":  8, "price": 47.23 },  
        { "before": 10, "change": -7, "price": None  },  
    ]
    
    queue = []
    for line in stock:
        if line["change"] > 0:
            queue.append({ "stock": line["change"], "price": line["price"] })
        else:
            # calculate price from queue
            count = -line["change"]
            totalPrice = 0
            while count > 0:
                take = min(count, queue[0]["stock"])
                totalPrice += take * queue[0]["price"]
                count -= take
                queue[0]["stock"] -= take
                if not queue[0]["stock"]:
                    queue.pop(0) # no more items in this "batch": remove it
            # Calculate an average price. Because of rounding, cents may not add up
            line["price"] = round(totalPrice / -line["change"], 2)
    
    # output result
    for line in stock:
        print(line)
    

    Output is:

    {'before':  0, 'change': 10, 'price': 49.55}
    {'before': 10, 'change':  2, 'price': 48.25}
    {'before': 12, 'change': -3, 'price': 49.55}
    {'before':  9, 'change': -3, 'price': 49.55}
    {'before':  6, 'change': -4, 'price': 49.55}
    {'before':  2, 'change':  8, 'price': 47.23}
    {'before': 10, 'change': -7, 'price': 47.52}
    

    Note that some prices (the last one in the example) are weighted averages of prices from two or more batches. In that case the real average may have more decimal digits, which need to be rounded away. This will then lead to an amount balance that is not exactly 0 after all has been sold.