Kit And Extract Beer Spreadsheet

[MHB]

Great to hear that someone is interested in the theory, rather than just blindly shoving numbers into a calculator.

Start with some real basics just so we are on the same page.
Extract is the term given to everything in solution in the wort.
The amount of extract is measured in two ways. SG is times the mass of an equivalent Volume of water so if we had 1 Litre of wort and it weighed 1.050kg we would say it has an SG of 1.050.
The other way to measure wort is Plato (oP) or Brix (oB). Plato is the equivalence to a given amount of Sucrose (sugar) in water, but it is %WW (weight in weight) if we made a 12.5% Sugar in water solution, it turns out that 1L would weigh 1.050kg.
It isnt exact but the conversion between the two scales (SG an oP) across the brewing range can by described by
SG = (4*oP)/1000+1 There is some error but it’s smaller than the ability of most brewers to measure, so close enough.
One more equation
Mass of extract = Volume * SG * oP (NB its %ww so your equation will need oP/100)

Let’s say we took that can of Coopers and diluted it to 12L.
You will find that most LME, kits, honey, golden syrup... are going to be very close to 80% Solids (extract) and 20% water. Given that the kit weighs 1.7kg, we can say there is 1.7*0.8=1.36kg of extract
If we plugged that information into the equation above
1.36=12*SG*oP
I would shove that into a spreadsheet and use Goal Seek (in Excel, Data Tab, What if Analysis, Goal Seek) to solve, doing that gives an SG of 1.0434669428456 and a oP of 10.8667357114. Let’s call it an SG of 1.043 and 10.9oP.

Unlike Liquid Malt Extract (LME) Dry Malt Extract (DME) is pretty much water free, as is Sugar but its handy to know that Dextrose is 91% solids and 9% water.

From there you should be able to calculate exactly what SG you will get from any addition.
Just remember that it’s always mass in volume or mass in mass, the amount of liquor you end up with is just as important as the amount of mass in it.
Also mass has volume. Sugar is used as a model because it is very similar to wort constituents and it’s cheap, pure and highly available
If we took 100g of sugar and added 900g of water we would have a 10oP wort, it would have an SG of 1.040 but there would be only be 0.963L.
Mark

If you play with Excel this might help

 

[clayts]

Thanks so much for that.

Just on the theory, I honestly think it's better to know where the numbers come from because then when you need to ad-lib you can. And so when you run into confusion like this you can sort it out from first principles. This is all taking me back to my engineering days at uni. I think that's part of the appeal of this hobby; plenty of numbers if you want them.

It is tricky though. Especially the last couple of sentences in remembering that mass has volume too and that the total volume of liquor is important! I think I was missing that in my thinking. I had picked up that the spreadsheet adds the same mass of extract for any (of the same total mass) can but I guess I didn't know why and now feel silly for not figuring it out myself.

To be honest I feel like I fully understand the theory behind the numbers but it will take a while for it to become second nature for me. I think I'll still need to either have a calculator handy to do the multiplication/division until I get a better feel.

Thanks again for clearing it up. I had been lying in bed the last few nights trying to figure it out before I bit the bullet and asked. Glad I did.

 

[MHB]

First up, you are entirely welcome and I agree on the need to understand the maths that underpins brewing.
Having a bit of an engineering background would make that clear.

The same equations apply to grain brewing. Being equations you can work left to right and right to left. Once you decide how much extract you want in a given volume its pretty straight forward to work out how much malt you will need.

If the equations interest you this is probably the best reference
Mark

 

[yankinoz]

Calculations of amounts of extract needed to reach a target o.g. are easier and faster than accessing and using spreadsheets, once you get used to doing it. With mashing grains, you need to factor in mash efficiency, but that is true for spreadsheets too.

For most other brewing calculations, spreadsheets do save time and effort, but Mark's comments on gaining an understanding of processes still apply, especially if one is inclined to experiment. Sooner or later, many brewers do.