Kit And Extract Beer Spreadsheet

[Tubs bar]

Thanks for putting onto your spread sheet Ian.

 

[gvhorwitz]

This is a great tool. I'm putting in information for a Cooper's Lager. Is it really a lager? I heard an ale yeast is included in the kit.
The reason I'm asking is what style of beer should I put in cell H3?

Any help is appreciated.

Greg

 

[carniebrew]

You're right, it's not really a lager if you use the kit yeast, 'coz it's an "all purpose" ale yeast. If you replaced the kit yeast (like most do) with something like 2 packs of Saflager 34/70, and fermented at 10C, then you could make a lager out of it.

No reason you can't choose "Australian Lager" or "Australian Premium Lager" in H3 though, it's just about colour, ABV and IBU, it won't care what yeast you use.

 

[Nth Qlder xxxx]

Hi everyone,

I've just put my second lot of Mangroves Jack Larger on bottled my first batch hope I do ok any tips or tricks will be great. I clean with bleach average temp of fermenter is 18-22 deg I'm using 25 litre drum but am about to use a 50 litre as we'll left the first batch in fermenter for 11 days will do the same with second didn't get very much bubbling in airlock well fingers crossed...

 

[Alex.Tas]

1. Don't worry about airlock activity, it's not an accurate indicator of active fermentation

2. I hope you rinse after bleach. Maybe look into some brewing sterilisers. Cost a little more but generally safer. If your brew shop sells it I recommend starsan. It's a few bucks but diluted as recommended it will last ages.

3. Depending on what you are making, try and drop your temps a bit and keep it a constant temp. It will ensure a more predicable fermentation. Do a search for temperature control, heaps of good pages on here.

4. Make sure you download the spread sheet. Really helped my kit brews.

5. Read as much as you can, and ask lots of questions. No one starts out knowing everything

6. Plan to be kicked out of brewing in the kitchen by your missus soon. Plan around this, and you will be a winner.

 

[Nth Qlder xxxx]

Thanks Alex,

Will look into the cleaners. Do you have to check specific gravity or just leave in fermenter for plenty of time...

 

[gsouth82]

You should always check specific gravity. This will tell you when fermentation is finished. If you get your head around the spreadsheet you'll notice the OG and FG fields (original gravity and final gravity). This will give you a guideline on what your expected FG should be. You'll know when fermentation is finished and your ready to bottle when the specific gravity is the same over 2 or 3 days and is close to what the spreadsheet has predicted. Measuring your gravity is the only reliable way to check the progress of fermentation.
Welcome and good luck!

 

[Nth Qlder xxxx]

Ok will do might be able to bottle quicker meaning more beer sounds good to me... Cheers mate...

 

[btg]

I'm interested in a simple toucan stout but instead of putting 2 cans of Coopers Irish Stout in a 23 litre fermenter, I thought I could get a similar beer if I put 1 can in half the volume - 11.5 litres (I got a Coopers Craft Brew kit with the smaller fermenter for father's day).

However when I put the figures in Ian's spreadsheet I get wildly different IBU levels

2 cans (3.4kg) Coopers Irish Stout in 23 Litres (nothing else) - 30.4 IBU
1 can (1.7 kg) in 11.5 litres - 60.8 IBU

Am I doing something wrong here?

 

[BrosysBrews]

I'm interested in a simple toucan stout but instead of putting 2 cans of Coopers Irish Stout in a 23 litre fermenter, I thought I could get a similar beer if I put 1 can in half the volume - 11.5 litres (I got a Coopers Craft Brew kit with the smaller fermenter for father's day).

However when I put the figures in Ian's spreadsheet I get wildly different IBU levels

2 cans (3.4kg) Coopers Irish Stout in 23 Litres (nothing else) - 30.4 IBU
1 can (1.7 kg) in 11.5 litres - 60.8 IBU

Am I doing something wrong here?

It is 60.8 with 23L if you put it in as 2 cans rather then 3.4kgs for 1 can. It would be as the IBU does not change if you change the qty in Kgs.

 

[carniebrew]

I'm interested in a simple toucan stout but instead of putting 2 cans of Coopers Irish Stout in a 23 litre fermenter, I thought I could get a similar beer if I put 1 can in half the volume - 11.5 litres (I got a Coopers Craft Brew kit with the smaller fermenter for father's day).

However when I put the figures in Ian's spreadsheet I get wildly different IBU levels

2 cans (3.4kg) Coopers Irish Stout in 23 Litres (nothing else) - 30.4 IBU
1 can (1.7 kg) in 11.5 litres - 60.8 IBU

Am I doing something wrong here?

I'm guessing you've overwritten the formula that's in cell c6. It's the number that comes up automatically when you choose the kit can in cell B6. Did you type 3.4 in there yourself?

If so, you need to put the formula back, which is:
=IF(ISBLANK($B6),0,VLOOKUP($B6,KITS!$A$3:$D$152,4,FALSE))

And instead of trying to put 3.4kg in that cell, instead just add the can of Coopers Irish Stout twice in the two rows available for kits, that will give you the 60.8 IBU you're after. It should look like this:

 

[Ruddager]

Quick question - how accurate is the spreadsheet for a mini-mash with >1kg of grains? (wrt OG)

 

[ianh]

The spreadsheet uses an efficiency factor for steeping grains, you would need to change that for mashing..

 

[Ruddager]

Oh ok, yes, there it is on the grains page

Does that get saved as part of a recipe or will I need to put it back again next time I steep? Also, what's a good number to use for mini-mashing? I suppose the best thing would be to work out how to measure and calculate my result too so that I can use that number again next time.

 

[Alex.Tas]

if your batch size is around 23L it probably wont make too much of an impact as you are using less than 1 kilo. maybe use 70% or so.

depending how mathematically minded you are, you can check your sg of the mash liquor (remember to adjust for temp). if its lower than what you predicted you can lower your IBUs to compensate, or boil the liquor for a little longer (will give some small colour change). if its higher, you can either add more water and up your hops to compensate.

As im still trying to get my numbers down pat for my AG setup i find im making adjustments to the hopping schedule/boil volume after i check my post mash gravity. helps to keep my beers in balance.

 

[Ruddager]

Well, what I'm trying to do is an Oktoberfest style recipe, starting with Briess's 1.5kg munich LME and then mashing a whole pile of vienna and some victory (up to 2kg). I've played around with several volumes, from 15-20L to try and get the balance but that was before I was aware of efficiency. Perhaps I ought to start a discussion for this in the proper place ...

 

[Jim79]

Hi guys,
Can anyone tell me where the saved recipes end up? I can't seem to find them once I have saved.

Cheers,

 

[Liam_snorkel]

the "get recipe" button

 

[ianh]

There should be a Recipes worksheet. Fifth Tab along the bottom.

 

[Jim79]

Thanks for the replies guys, I may be doing something wrong. If I click on the get recipe button it only has a list of 4 recipes, not one that i have inputted. Do I just need to put in all my figures and then click save recipe, or do i need to do more.

Under the get recipe tab it doesn't show any of the brews that I have saved.

As I have said, I am very new to this so I apolagise for my questions.

 

[Liam_snorkel]

you probably haven't saved them then.

 

[ianh]

You need to press the Save Recipe button on the Main worksheet to save a recipe. However all the recipes you have done should be on the Brews worksheet, if you look at the comments attached to the cells in Column C it gives you the recipe. You could enter this back into the Main worksheet and press the Save Recipe button to save the recipes.

 

[GibboQLD]

G'day Ian -- top work on the spreadsheet, big fan!

I made a small addition/modification I thought I'd share - added a "default batch size" column to the "KITS" tab and added a conditional reference to F6/7 & G6/7 on the "MAIN" tab (use batch size if it isn't blank, otherwise default to 23L) to enable adding the Mr Beer Craft kits sold by Coopers.

Found these numbers on a retail site and I'm not sure how correct they are, but I ran them through the calc and the colour/IBU/ABV seemed pretty right. Here they are in case anyone else is looking to run the numbers and test them:

                              EBC   IBU   Weight   Batch (L)
Mr Beer Bewitched Amber Ale    30    30     1.3       8.5 
Mr Beer Winter Dark Ale        60    60     1.3       8.5
Mr Beer North West Pale Ale     8    43     1.3       8.5 
Mr Beer Diablo IPA             22    70     1.3       8.5

 

[Dae Tripper]

First of all thanks ianh for making this, it is awesome!

Is it possible to get another line for the LME?

 

[ianh]

First of all thanks ianh for making this, it is awesome!

Is it possible to get another line for the LME?

Thanks. But the only way around the LME problem is to create another LME on the Malt worksheet that combines 2 of your LME's

 

[bingggo]

Hi, fantastic spreadsheet.

I have been trying to get my head around the hop utilisation issue, as Ian explained it above. Yet, I don't really understand when you're meant to set HCF to yes or no? When it's below 8L?

For example, with a 6L boil volume for a 22L batch, the difference between yes and no for 160g EKG on a 60m boil is 43 vs 86 IBU. Which one would be correct? (I would be adding pale malt after the boil)

To confuse me more, the same recipe and boil volume entered into beersmith on my ipad gives me 114.7 IBU

Also, is it a danger sign if the malt weight to bring BG to 1.040 is a negative number (eg. I have about 2kg speciality grains steeped in that volume).

Look forward to any tips.

B

 

[ianh]

Hi bingggo, thanks, and good to see another Tassie brewer.

What I recommend is that you do minimum of 10 litre boils and don't use the HCF, get a 19 litre pot from BigW.

Without the HCF the bitterness calculation is done using Tinseth's method and is the one normally used by the vast majority of brewers. Using the HCF calculation is by Garetz's method which contains a factor for volume. The two methods give quite different results even on large volumes.

Have a read here http://realbeer.com/hops/FAQ.html

2kg of speciality grain in a 22L batch is excessive, I use to do 0.8kg and some people thought that was excessive. Whilst 3L per kg of Speciality grains is a minimum I would have thought you would need more than 6 litres to steep 2 kg grain and get full extraction.

That's probably why it gives a negative value for malt to be added, SG above 1.040 lowers the IBU's.

cheers

Ian

 

[bingggo]

Thanks so much for the reply. The 2kg is for a Russian imperial stout so perhaps ok

But I take your advice on the volume and pot!

 

[woodie]

Hi all, is there info on how to use the spreadsheet as I don't know how use them but would like to.
It looks good though.
woodie

 

[ianh]

Hi all, is there info on how to use the spreadsheet as I don't know how use them but would like to.
It looks good though.
woodie

Hi woodie

Read the Notes worksheet, that should give you a start.