1
$\begingroup$

How to calculate the current average cost of a product mixed in stock ?

The individual deliveries are not identifiable: it's gasoline delivered in bulk, each day has a different price and everything is mixed in the storage tanks.

I've worked out by trial and error and using real data from our network (500+ locations) the equation that gives the average price for one daily delivery with a 80 % stock renewal by day. i.e we start the day at 00:00 with 80 % of the stock from d-1, 16 % from d-2, 3% from d-3 etc.

As the day goes by, more and more stock is from today's price and at 23:59 we're at 80% priced at current figure, 16 % from d-1, 3% from d-2 etc.

The model works well and can give hour by hour (24 periods) the evolution of stock costs, but only for one daily delivery.

As a side note, after analysing a sample about 40 000 real delivery schedules, the average number by the hour is very constant. Basically, you can just assume that time of the day /24*100 = % of total deliveries done for the day.

Also there's no need to go back earlier than 3 days for product costs as even for a store getting only a delivery once a week the improvement on precision is not worth it.

What I'm looking for is to get and understand the general equation or procedure to get these ratios for different delivery schedules: some locations get filled-up only once a week and others nearly twice a day.

Basically I've got 7 variables say:

a=average frequency of deliveries/week (different for each store from 9/week to 1/week, decimal)

b=period number/24 (hour of the day, integer)

c=cost of product on d day

c'=cost of product on d-1

c''= cost of product on d-2

c'''=cost of product on d-3

And I guess I'm looking for the equation that'd give a problable average cost of product in inventory for a location at a particular period of the day.

I hope this make sense ?

At the moment I don't even know what terms I should be googling. Any hint on how to approach the problem would be appreciated.

$\endgroup$

1 Answer 1

2
$\begingroup$

You can just keep a running value of the gas in the tank. Say you buy 1000 gallons at 3, so your inventory is worth 3000. You sell half the stock, leaving stock worth 1500. Now you buy 500 gallons at 3.2 for 1600, so your stock is 1000 gallons and cost 3100. You sell 800 gallons, so you have 200 gallons worth $0.8 \cdot 3100=248$ You buy more, add the cost to your running average, etc. The basic idea is
cost before fill=cost after last fill * fraction remaning
cost after fill=cost before fill + cost of fill
the cost per gallon will stay constant between delieveries. You can refigure it after each fill by dividing cost after fill by volume after fill

$\endgroup$
5
  • $\begingroup$ That's waht I'm doing right now, with an Excel sheet. I'm looking for a more streamlined way. But your comment helped me precise my thoughts. I'll edit my question. Thanks. $\endgroup$
    – P. O.
    May 6, 2015 at 23:26
  • $\begingroup$ I don't think you can do that if you want an exact answer. If the last delivery was very expensive, you still have all that fuel in the tank. If the expensive delivery was at the start of last week, you have sold most of it. You are really only keeping track of two numbers at any time-the volume and cost in the tank now. Of course, if prices don't change much you could just use the average price over the last week or month for your cost and not be far wrong. $\endgroup$ May 6, 2015 at 23:26
  • $\begingroup$ That's why i tagged my question "probability". I'm not looking for an exact figure every time but something that can be close to truth when repeated enough times. $\endgroup$
    – P. O.
    May 8, 2015 at 15:29
  • $\begingroup$ I would just assume that all the fuel left in the tank before a fill was priced at the previous fill then. If you drain the tank pretty well between fills that will be very close as you don't have much left from the fill before that. $\endgroup$ May 8, 2015 at 15:33
  • $\begingroup$ The margins are very low on gas: a 1% cost variation (typically it's more a 1.5% going up or down every day) can equal up to a 15% variation on margins. For cost efficiency, ideally, the tanks are filled when they are about 80 % empty, So for a daily delivery going back 2 days would be ok (80% stock from yesterday 16% d-2 ) but for locations getting deliveries 1/week you need at least 4 days back: on average, if you pick a day by chance, you're 3 days away from the last delivery. $\endgroup$
    – P. O.
    May 8, 2015 at 16:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .