How to calculate weighted average based on recency

Question

I am trying to estimate the unit cost of a product based on the unit cost on past purchase orders. For example, let's say I have the following purchase order history:

PO 1: January 1, 2019   -    Quantity 10   -   Unit cost $10
PO 2: March 1, 2019     -    Quantity 20   -   Unit cost $12
PO 3: July 1, 2019      -    Quantity 30   -   Unit cost $16

On August 1, 2019 I receive a product back from a customer. I do not know which PO the product originally came from. I would like to estimate the unit cost of that one product by calculating a weighted average based on quantity for all of the unit prices from past POs. That part seems straight forward.

However, I would also like to ensure that the most recent PO has more weight than POs in the past.

Does anyone have a recommendation for how to do this?

Answer 1

I use a backwards halving algorithm to do this. It requires running sums, but can be done in a spreadsheet, so not so bad...

Pick a time period for the first halving (say 365 days). For POn, the weight will be:

Weight = 2 ^ (POdate - Now) / 365

Now, take the value of POn and multiply it by the weight to get the weighted value:

WeightedValue = POvalue * Weight

Finally, take the sum of the weighted values and divide it by the sum of weights (hard to show in text). This value will provide a recency-biased average.