Calculating the average age of an annually changing stockpile. Whiskey/Wine example

[Adrian Ghaui]

Hi All

I've been grappling with this for a long time and would really appreciate any help. Many, many hours have been spent trawling help, books this forum and others but to no avail yet.

I will use the fictitious example of Acme distilleries to illustrate the problem. To anyone who may want to help but is offended by the alcohol reference, sorry, and please be assured that the real world application for this has nothing to do with making alcohol.

Let's say Acme wants to know the average age of it's total stock of wine/whiskey/moonshine that is ageing in barrels. Each year some will be removed from barrels (always the oldest) and new stock will be introduced. When the distillery is expanding, new barrels are purchased. After a point the distillery is at peak capacity and any new stock is only put into barrels from which that year's 'harvest' is taken out.

The key figure is the average age of the overall volume of liquid in barrels.

The amount being taken out will not be limited to a certain age. EG in one year more than the oldest 1 year's worth of liquid can be removed.

I'm looking for a non VBA solution to this:

I need a function that will look at the amount being removed from barrels (the oldest stock) and subtract this from the total stock.

Then I need the change in volume of stock per year to reflect this in the calculation of the average age of all remaining stock including the addition of the new liquids for that year.

The attached document shows my progress. I'm close, but may be overlooking something obvious/more eloquent. Please take a look and let me know your thoughts.

Thanks very much!

 

[Luke M]

Hi Adrian,

Take a look at the attached. I had to rethink/rearrange your table to do needed calculations. Read the formula table by going down on left to find batch, go right to look at a specific year, and calculated values are the age of that batch in that year. There's some complexity in the formula, but basic premise is something like this logic

X = difference of total barrels used from total barrels made
If X <= Barrels from a batch, that means some of old stock was used. If X is more, old barrels from this batch are untouched.

Take number of barrels from that batch (can be no more than initial batch, no less than 0), and multiply by number of years since batch. Number of years passing is calculated through combination of ROWS and COLUMNS, depending on which way you go.

Once we have the age of each batch all lined up in a column, the average age is easy to calculate, as SUM for that column / # Aging ltrs.

PS. Excellent write-up of problem, showing what you had, what you wanted, and an uploaded workbook. Plus, it was an interesting challenge to solve.

 

[Adrian Ghaui]

Luke M

Thank you SO much for your solution, you truly are a ninja above ninjas!

I think I've fully understood it, but will take some time over the week end to try and reverse engineer the formulae. I now see that expressing a batch as it's contribution to the average is more eloquent than expressing it as a volume of liquid.

The cherry on top is that a solver solution now works on this, so I run that to see how much liquid I can take out to maintain the correct average age! Solver didn't like the last table at all.

I have the following as "bonus questions". If you have the time and inclination please have a look, but I can probably figure them out myself with time. You have already helped immeasurably so if you are bored with this, or want to "teach a man to fish" by all means, tell me to "get lost!"

1. Scalability. Can you explain to me which formulae need to be copied in which way if I want to extend this further in the future?

2. In order to make this more user friendly to a wider (read "excel wary") audience, I'm considering putting this on a separate sheet and linking another table to it so that the second table shows the volume of ageing litres rather than it's contribution to average age. Do you have any suggestions for this?

Thank you once again, if Acme distilleries was a real thing, you could drink for free any day.

 

[Luke M]

Adrian,

Glad you liked it.
Bonus questions are welcome, I love the challenges, and the opportunity to teach someone else. As someone who learned himself via forums and self-teaching, I feel like it's only right to "pass it on".

1. The formulas are built for scalabililty. Need to copy down and to the right, since we're essentially working with a square (rows and columns = number of years). I suppose you don't have to keep it a square, but the results would get...screwy, and harder to understand. You'll notice that on the left, 2021 and 2022 are listed, but no formulas are in those rows.
The only part of the formula that you need to change is in the 3 index. The formula currently references C:S as a range. If you add more years, going beyond column S, will need to adjust this. Nothing else needs to change.

2. Good question, and at this point, I must apologize. In solving this problem, I realized I made a mistake before. Formulas were only looking at amount going into new barrels, forgot to include amount going into old barrels. New spreadsheet has this correction.
Also, made a new sheet to answer your question. For each year, we can look at our table which has age, and do some division to get quantity. Used another INDEX function which helps us move down (to go to next batch) as we go to the right (since 2004 batch is now 2 yrs old, then 3, etc).

 

[Adrian Ghaui]

Luke

I've got the file and will spend some time looking over it, but at first glance it seems to be working perfectly. Thank you very much! If anything comes up I'll leave a message here. In the meantime, I've sent a small donation through paypal. Hopefully this will be enough to get you a beer in your hometown.

Many thanks again from Tanzania

 

[Luke M]

Updating the file here to account for days, as several people have referenced this thread.

Keywords for later searching: brew, brewery, product age, average