Kit And Extract Beer Spreadsheet

[Blitzer]

I'm deleting my post.. you are just full of useful links today Bosher

 

[wbosher]

My boss has gone out, lots of interweb surfing. When the cats away...

 

[BrewDaddy]

Hi BrewDaddy and welcome to the forum.

My understanding is Liquid malts contain approximately 20% water, varies with manufacturer. The figure I used is slightly different. If you put 3kg of DME in 23 litre gives OG 1.049, 3.75kg LME gives OG 1.050 however I would expect the calculated FG to be the same. So something is slightly amiss with the FG formula and I suspect the true answer lies somewhere between the two.

cheers

Ian

Thanks for the response - I only noticed this as in the specific recipe I was looking at, the FG was changing depending on whether or not I used LME or (the equivalent) DME, normally I wouldn't bother too much, but in this case it must've been straddling the threshold for in or out of style, I like to see the 4 green 'GOOD' cells (which I got with DME but not with the equalivent amount of LME).

 

[woodwormm]

As with all of us on here, I am super impressed with this tool you've given us Ian. Thankyou.

My questions relate to boil volume versus ferment volume versus malt additions.

I'm about to set up an extract brew rig to do a u-brew-it style system at home..

I'm hoping to get 3 cubes of wort out of my kettle (60L)

just playing around with figures, if I put in 60L ferment volume and 65L litre boil volume (just playing around I won't know my real evaporation or wastage until i try) and all of my fermentables etc to get everything in the 'green zone', the brewday sheet tells me to add more malt to get my hop boil sg of 1040 than i actually have in the recipe?

What am i doing wrong?

edit,,, figured it out , i was putting in a double batch amount of extract not a triple... and using dex to adjust OG ... put in correct levels of extract and got rid of dex and all good

thanks Ian! awesome tool.

you should set up a donation fund... I reckon many of us would throw you a beer's worth of cold coins... you could buy some new kit or take Mrs Ian out for a swanky meal.

 

[woodwormm]

Lazy question now

Is there a way of converting the data for Briess Malts to include them on the spreadsheet?

I'm quite happy just to plug them into my copy of it, but i'm unsure what they're figures mean

http://www.brewingwithbriess.com/Assets/PD...denLightLME.pdf

 

[ianh]

Thanks. To add the Bries Malt. Go to the MALT worksheet and down to the LME's. You can just insert a row after Black Rock and Put Briess in Col A and 8.0 in Col B.

The EBC value is about twice the Lovibond value thus 2x 4 lovibond = 8 EBC

The Boil volume is there so you can find out how much malt you need to add to give an SG of 1.040. Most Kit & Extract brewers don't do full volume brews so there is no boil off rate. If you want to do full volume boils just do your ferment volume then top up later.

I only produced the spreadsheet for my learning experience and to help other Kit & Extract brewers (which I no longer do) and hopefully gain rewards from others giving of their experiences.

cheers

Ian

 

[Scottye]

I only produced the spreadsheet for my learning experience and to help other Kit & Extract brewers (which I no longer do) and hopefully gain rewards from others giving of their experiences.
cheers

Ian

And thanks for doing that Ian. It is legendary and obviously has gone viral among kit & extract brewers and will continue to do so.
See here

 

[Juzdu]

I only produced the spreadsheet for my learning experience and to help other Kit & Extract brewers (which I no longer do) and hopefully gain rewards from others giving of their experiences.

cheers

Ian

Ian, just fantastic work on that spreadsheet, i've learned so much in only two weeks, the ability to instantly see what changes various ingredients make to IBU, gravity/ABV etc is brilliant.

Just curious on one thing, i'm looking to brew the "Coopers Irish Ale" from their website, which uses a Coopers Draught kit can, their "Brew Enhancer 1" and 300gm of golden syrup. Given the BE1 is 600gm dex, 400gm maltodex, that's 3 "ADJ"'s I need to enter on the spreadsheet, which I can't work out how to do. Is there a way to increase the number of rows for ADJ's?

Justin.

 

[ianh]

Ian, just fantastic work on that spreadsheet, i've learned so much in only two weeks, the ability to instantly see what changes various ingredients make to IBU, gravity/ABV etc is brilliant.

Just curious on one thing, i'm looking to brew the "Coopers Irish Ale" from their website, which uses a Coopers Draught kit can, their "Brew Enhancer 1" and 300gm of golden syrup. Given the BE1 is 600gm dex, 400gm maltodex, that's 3 "ADJ"'s I need to enter on the spreadsheet, which I can't work out how to do. Is there a way to increase the number of rows for ADJ's?

Justin.

Thanks. Short answer is no you cannot increase the number of adjuncts (guides you too use more malts) but you can add Adjuncts to the ADJ worksheet.

If BE1 is 60% Dextrose and 40% Maltodextrin then if you add BE1 to the ADJ worksheet BE1 EBC 0.1 Potential 1.0414 Fermentability 61 the you just add your kilo of BE1 in your recipe instead of the dex and maltodex.

cheers

Ian

 

[Thylacoleo]

Thanks Ian for providing this fantastic tool; it's made for a fun re-introduction to homebrewing.

I do have a couple of questions regarding hops. Despite several searches and permutations in search terms, I haven't been able to find exactly the answers I'm after.

Does the spreadsheet, for IBU calculations, assume hop pellets or flowers?

The nearest answer I've found, in [topic="45168"]another thread[/topic] is a "rule of thumb" that one needs ~10% more flowers than pellets for the same effect, but I would only need to apply that if the spreadsheet's formulas are tailored for pellets.

Although I do 10L boils, could I use the HCF to control for the use of flowers? I've noticed (at least with one of my APA recipes) switching it on decreased the expected IBU by around 25% (~38 IBU to ~28), which sounds like an under-estimate if trying to align with the 10% rule (although I know this wasn't the original intended purpose of the HCF). I'm okay with bitter beer, but that could be the difference between an APA and an AIPA...

Cheers,
James

 

[ianh]

Hi Thylacoleo

The spreadsheet IBU values are for pellets (my BIAB spreadsheet gives you the option, had to check). An alternative to fiddling with the HCF is just add another hop to the hop list for the flowers. Flowers require about 10% more than pellets.

I use this Link for my hop info, if you go to Norm Pyle's Hops FAQ there section on different hop products.

cheers

Ian

 

[Thylacoleo]

Hi Thylacoleo

The spreadsheet IBU values are for pellets (my BIAB spreadsheet gives you the option, had to check). An alternative to fiddling with the HCF is just add another hop to the hop list for the flowers. Flowers require about 10% more than pellets.

I use this Link for my hop info, if you go to Norm Pyle's Hops FAQ there section on different hop products.

cheers

Ian

Thanks Ian.

I've adjusted the %AA levels in the hop list, to reflect the levels for the specific harvest I was able to buy (as the %AA changes from year to year). If adding another hop to list for the flowers, would subtracting 10% from the %AA stated on the packaging have the same effect?

'Cos I'm not hugely confident with Excel, hops or the maths involved there, maybe the best thing for me to do would be to enter the quantity of hops to hit my target IBU for the recipe, then add 10% extra to the mass when I make the recipe?

Thanks again for the help,
James.

 

[ianh]

Thanks Ian.

I've adjusted the %AA levels in the hop list, to reflect the levels for the specific harvest I was able to buy (as the %AA changes from year to year). If adding another hop to list for the flowers, would subtracting 10% from the %AA stated on the packaging have the same effect?

'Cos I'm not hugely confident with Excel, hops or the maths involved there, maybe the best thing for me to do would be to enter the quantity of hops to hit my target IBU for the recipe, then add 10% extra to the mass when I make the recipe?

Thanks again for the help,
James.

You could do it either way.

 

[verysupple]

Hi all (mostly Ian),

I'm a little confused when it comes to hop utilisation. It's very clear that the gravity of the wort affects the utilisation. What is not at all clear is if the volume of wort (given the same gravity) has an affect, i.e. does a 5 L boil with SG=1.040 give the same result as a 10 L boil with SG=1.040? I guess my real question is, does the HCF account for different boil volumes all with SG=1.040 or does it account for different gravities for if you do a boil with all the malt?

PS. Sorry if this has come up a million times but I can't seem to find anything using the search function and I'm not manually searching through 24 pages of comments

 

[ianh]

Hi all (mostly Ian),

I'm a little confused when it comes to hop utilisation. It's very clear that the gravity of the wort affects the utilisation. What is not at all clear is if the volume of wort (given the same gravity) has an affect, i.e. does a 5 L boil with SG=1.040 give the same result as a 10 L boil with SG=1.040? I guess my real question is, does the HCF account for different boil volumes all with SG=1.040 or does it account for different gravities for if you do a boil with all the malt?

PS. Sorry if this has come up a million times but I can't seem to find anything using the search function and I'm not manually searching through 24 pages of comments

Hi verysupple

There are 3 main methods of calculating bitterness see link in my previous post. Tinseth is method mainly used but it does not have any volume factors which is OK for all grain brewing when boiling the total volume. But how does it relate to kit or extract boils when in some cases only a small % of final volume may be boiled. In Answer to your first question - I don't know.

So what I did in the spreadsheet was give the option to use the Garetz method which includes a volume factor for use where brewers boil small volumes, hence the HCF.

In both cases the boil sg should be 1.040 no account has been made in either method for different sg's.

What I suggest is if possible boil 10 litres and do not use the HCF if you can only boil 5 litres or less then use the HCF. But whatever you do be consistent.

cheers

Ian

 

[Juzdu]

Where does all the info come from on the IBU and EBC of the kits? I can't see Coopers European Lager in the sheet, so figured i'd add it, but can't seem to find the info regarding this kit online. Do you need to ask them via email?

 

[fletcher]

http://www.coopers.com.au/the-brewers-guil...#European_Lager

the info is there, just on another part of their page

"Capturing the style and essence of the very fine lagers exported from Northern Europe.

Weight - 1.7kg, Colour - 90EBC, Bitterness - 340IBU, Aroma Hop - European Blend Top Note "

 

[JDW81]

Weight - 1.7kg, Colour - 90EBC, Bitterness - 340IBU, Aroma Hop - European Blend Top Note "

That would be one mouth puckering lager!!

 

[fletcher]

haha typo for sure. it's on their site too. what a classic

 

[ianh]

Where does all the info come from on the IBU and EBC of the kits? I can't see Coopers European Lager in the sheet, so figured i'd add it, but can't seem to find the info regarding this kit online. Do you need to ask them via email?

info

On the KIT worksheet, just inset a row after English Bitter put the Can EBC 90 in Col E and IBU 340 in Col F the values should then calculate. Put 1.7 in Col D.

note the spreadsheet IBU values are different from those Coopers calculates, Coopers calcalates a value then says in fermented beer value is 20-30% lower.

cheers

Ian

Edit Beaten, see what happens when you try and brew and on the internet at the same time. The values are Can values about 18.5 IBU in 23 litres.