Kit And Extract Beer Spreadsheet

[gruntre69]

Thanks mate, I had a look at this a while ago but I think my brewing knowledge was insufficient so I didn't use it. It has now inspired me to have a go with a recipe created in the spreadsheet with the style guidelines as a guide. Thanks for the work and the sharing. PM me for help on a later version of Excell....

 

[ianh]

Thanks mate, I had a look at this a while ago but I think my brewing knowledge was insufficient so I didn't use it. It has now inspired me to have a go with a recipe created in the spreadsheet with the style guidelines as a guide. Thanks for the work and the sharing. PM me for help on a later version of Excell....

Hope it continues to inspire you to try different things. It in an old version of Excel for maximum usage. I have Office 2013 which I actually BOUGHT, well it was only $15 plus another $15 for the disc.

 

[stretch69]

Awesome spread sheet mate!

I've been looking at trying drsmurtos golden ale,

Just a couple of questions,

The brew day sheet says to add 1153g LME for the boil for SG 1040, then disolve the remaining LME plus the kit in to the fermenter.

1) what difference does it make if I add all the LME to the boil?
2) what's the best way to check the SG at the start of the boil?,

If theres a thread that explains this sorta stuff I'd be happy to read it, sorry for the stupid questions, its the first time I've used this sheet

 

[Droopy Brew]

Hop Concentration Factor. It helps if you're doing boils under say 8 litres, in guiding you as to how much less your IBU will be because of the small volume boil. It seems most of us, including Ian, have agreed that when doing anything over 8 litre boils you can leave it off, as the amount of hops don't really need changing.

So how do you use it? THE HCF is set at 1- what do I need to set it at for a 5L boil?

I just ignore that and enter the boil volume below it. I imagine this would adjust the IBUs automatically?

 

[ianh]

Awesome spread sheet mate!

I've been looking at trying drsmurtos golden ale,

Just a couple of questions,

The brew day sheet says to add 1153g LME for the boil for SG 1040, then disolve the remaining LME plus the kit in to the fermenter.

1) what difference does it make if I add all the LME to the boil?
2) what's the best way to check the SG at the start of the boil?,

If theres a thread that explains this sorta stuff I'd be happy to read it, sorry for the stupid questions, its the first time I've used this sheet

The spreadsheet calculates what you need to add to the boil based on what grains if any are steeped and the volume of the boil. There is no need to check the SG at the start of the boil.

An SG of 1.040 is roughly 100g of DME per litre and is commonly used as it gives good hop utilisation ie more ibu's for a set amount of hops. If you add all the LME at the start of the boil it would give high SG and poor hop utilisation.

http://realbeer.com/hops/research.html

 

[ianh]

So how do you use it? THE HCF is set at 1- what do I need to set it at for a 5L boil?

I just ignore that and enter the boil volume below it. I imagine this would adjust the IBUs automatically?

To use HCF select Yes in cell K24. If you use it, it will require more hops to give the same IBU. It is then a question of personal preference.

Background the HCF was put there as there is very little data for small volume hop boils, all grain brewers boil the total volume. Tinseth is the normal method for calculating IBU's and does not include a volume factor. An alternative less used method is the Rager method with does include a volume factor. So that HCF was put in the spreadsheet to simulate the Rager method for low boil volumes.

A bit more light reading is here

http://realbeer.com/hops/FAQ.html

 

[carniebrew]

<snip>
If you add all the LME at the start of the boil it would give high SG and poor hop utilisation.

And you'll also likely get a darker beer than expected, as the boil will darken the high concentration of extract.

 

[stretch69]

cool thanks guys for the imput!

I've nearly got enough gear to give AG a go. In some ways it seams more a little more straight forward then some of these kit/extract recipes, ALTHOUGH i havent done it yet so we'll see haha

 

[damoninja]

Great sheet! I've just started brewing and have found it useful, and pretty accurate so far!

The yeasts I've been using aren't available in the sheet, is it possible to get them added? I couldn't find any data which translated into similar numbers to yours, but I did find this fact sheet that has a bunch of info:
http://www.lovebrewing.co.uk/downloads/dl/file/id/48/m07_british_ale_yeast_data_sheet.pdf

One enhancement I would love to see is the ability to track observations over the course of fermentation... Things like temperature, gravity, etc. Either at specific intervals or a complete list.

This would be useful for me and I suspect many others who ferment in an environment that cannot be as easily regulated as others (I don't have a basement) and tracking gravity readings.

 

[carniebrew]

The sheet is set up so you can do that yourself. Go to the "Yeast" tab, and insert a row wherever your new yeast belongs, enter all the details, and they'll then appear in the drop down box on the main sheet....

In regards to observations, I use the "Comments" column on the "Brews" sheet to record that sort of stuff, along with tasting notes I update as the beer ages.

 

[damoninja]

I've been doing the same, but it would be handy to have in a standardised form.

As far as adding the yeasts, I don't really have the knowledge to be adding the data just yet, like I said I can't translate the stuff from the fact sheet into something that looks like what's already there. I'd really just be guessing, and what's the point in that?

 

[damoninja]

Another possible addition: Additional parameters for carbonation

Additional input parameters:

• Size / type of bottle
• Type of sugar using (DDME, LDME, DLME, BE1, BE2 etc etc)
• Maybe a second type of sugar? I hear some people use a mixture

Output

• Impact on flavour
• Amount of sugar per bottle calculation: (bottle size / fermentation volume)-1
  ... Minus 1 litre is to assume sediment, testing ;samples, etc.
• Warning if the carbonation level / amount of sugar is too high for the bottle type and may cause a breach

Thoughts are welcomed

 

[carniebrew]

The sheet already has a priming choice between dex and sugar. I personally don't think anyone bother using DME/LME or any kind of brew enhancer for priming, it's a waste of money and too variably fermentable to give the accuracy we desire for bottle priming. I've used both dextrose and raw sugar for bulk priming, neither add any kind of flavour to any of the beers I've brewed...we're talking tiny amounts compared to the volume of beer.

And while I used Ian's spreadsheet religiously when brewing kit/extract beers, I always used this calculator for my bulk priming calculations:
http://webspace.webring.com/people/ms/sirleslie/AlcoholChart/PrimingCalculator.html

Which btw does allow you to choose DME, but you have to know/guess if you're using 70% or 75% fermentable stuff. Again, just use sugar or dextrose!

 

[Alex.Tas]

as for how much sugar to use per bottle it isn't a particularly hard calculation to make yourself. All you need to do is find an empty few cells and add:

Column xyz (Litres) column zyx
0.375 =$m$19/($C$3/xyz1)
0.750 =$m$19/($C$3/xyz2)

Then have another row below 0.750 with the value 0.330 for a 330ml bottle, or 0.568 for a cider bottle or whatever you want. then just copy zyx column down.

My suggestion would be to insert a few columns to the right of column O and have your calculations there. As far as I'm aware, inserting columns shouldn't mess up any other formulas that are at work.


As for your yeast damoninja, just add the name to column a, add whatever you like to column B (this is just your reference so use something like MO7 for example).
Add a D in column C as its a dry yeast not a wet one. Column D is the important one as it tells the spreadsheet the attenuation of the yeast (how much sugar it will eat before it settles out). the qualitative value of 4 isn't too helpful here, so i did a quick google to find out the attenuation is stated as high. so you can probably assume a value of 75 here. IanH would probably be able to answer this bit better than me.

Column E just add the name of the yeast, in this case Mangrove jack British ale yeast. column F add in High. This means that once the yeast has done its work, it will clump together (flocculate) and get heavier and fall to the bottom of your fermenter. higher flocculation generally means clearer beer. A German wheat beer for example would halve low flocculation. G&H just put in 75 again. I and J add 16-22 respectively. not too sure about the alcohol tolerance. you can leave this blank as there are no formulas dependent on this column. You could probably assume a 8-12 percent range maybe based on the info on that link you provided.

 

[damoninja]

Thanks Alex - I'll give adding the yeast a go...

I'm good enough excel and math I do a lot of operations in excel/powerview.
The main thing I wasn't comfortable adding was the volumes etc, as I'm still learning this stuff.

As far as not using DME goes for priming, I've heard of heaps of people who do it, especially with stouts and darker beers. It's supposed to give you finer bubbles / thicker head which is often desired in these types of beers.

I wouldn't suggest using something like DDME in something like a pilsner, but I don't think it should be totally condemned.

 

[Alex.Tas]

in the end mate, its your brew so you can do what you like with it. I use DME sometimes. All you need to do is find out what the fermentability of the DME is and then alter your calculations accordingly. you can add a few cells to do this based on the value calculated. Assume dex is 100% fermentable. I guess this wont get translated into the saved recipe section though.

 

[Forever Wort]

I love the spreadsheet. However, I am having trouble using it to get an accurate OG of my latest brew.

I used a Coopers Lager kit and 1.7kg of Blackfern Ultralyte LME - does anyone know the correct statistics to get this LME into the spreadsheet? Or is it basically the same as the Morgans Extra Pale or Coopers Light LMEs, which both give the same results?

My OG was epic wack - 1112 when adjusted for temp - I thought "fair enough" then pitched the yeast. But checking the spreadsheet using the Morgans or Coopers LMEs gives me OG 1048.

 

[damoninja]

I love the spreadsheet. However, I am having trouble using it to get an accurate OG of my latest brew.

I used a Coopers Lager kit and 1.7kg of Blackfern Ultralyte LME - does anyone know the correct statistics to get this LME into the spreadsheet? Or is it basically the same as the Morgans Extra Pale or Coopers Light LMEs, which both give the same results?

My OG was epic wack - 1112 when adjusted for temp - I thought "fair enough" then pitched the yeast. But checking the spreadsheet using the Morgans or Coopers LMEs gives me OG 1048.

Woah - no way those ingredients would give OG that high unless your final volume was like 9 litres. Have you tested your hydrometre on water?
What was your final volume by the way?

 

[Liam_snorkel]

it probably wasn't mixed properly, heavy stuff sinks to the where the tap happens to be. Don't stress, the yeast will find it.

 

[Forever Wort]

Thanks. I think something trippy like that must have happened. I did stir the **** out of it though.

Total recipe:

Coopers Lager kit
1.7kg Blackfern Ultralyte LME
500g Cane Sugar
10g PoR boiled for 10 min in 2 litres of water

Final volume 26l.

Ian's Spreadsheet says 1048.