Kit And Extract Beer Spreadsheet

[phas21]

G'day ianh - thaks for a great piece of work - I have a couple of questions. I don't understand the HCF versus the Tinseth method for the IBU calculations - for instance with DR Smurto Golden Ale Extract recipe Tinseth gives an IBU of 32 and with HCF gives 16.3 this seems like a big difference - just wondered if you could shed some light on why such a difference. Also where do the kit IBU figures come from as I would like to know the IBU's of other kits not listed.

Thanks for some great work

 

[Sydneybrewer]

any help trying to get this this to run on microsoft office for mac 2008, just brought a new macbook pro and can not get this spreadsheet to work. please help, i dont want to fork out for beer alchemy just yet.

edit: the main problem is none of the macros work, and my troubleshooting is just hitting brick walls.

 

[ianh]

G'day ianh - thaks for a great piece of work - I have a couple of questions. I don't understand the HCF versus the Tinseth method for the IBU calculations - for instance with DR Smurto Golden Ale Extract recipe Tinseth gives an IBU of 32 and with HCF gives 16.3 this seems like a big difference - just wondered if you could shed some light on why such a difference. Also where do the kit IBU figures come from as I would like to know the IBU's of other kits not listed.

Thanks for some great work

Thanks, the original data for IBU calculations was obtained from here Link

Tinseth is generally used by AG brewers who boil the total volume that is going to be fermented (plus some) but Kit & Extract brewers tend to boil smaller volumes.

To try to decide how to handle the small volumes, the option was given to use either Tinseth or Garetz which has a HCF to correct for volume. It's a matter of seeing what works for you and the volume you boil.

I set off having boil volumes of 2-3 litres and using the HCF method, but I now boil about 10 litres (which seems common amongst extract brewers) and use Tinseth.

There is a third main method for calculating IBU's which is Rager which if I remember right gives a higher value than the other two methods.

The Kit IBU's were obtained from makers websites, Coopers specify the IBU's of the can rather than fermented volume and thus are calculated.

 

[Pete2501]

1 ) nervous about opening an excel spreadsheet from a foreign site , but

before I do

does this spread sheet give me a list of recipes to try or does it do some calculations
from the ingredients I put into the sheet

Open it using google apps.

 

[ianh]

1 ) nervous about opening an excel spreadsheet from a foreign site , but

before I do

does this spread sheet give me a list of recipes to try or does it do some calculations
from the ingredients I put into the sheet

AHB a Foreign site? 258 downloads of latest version 2.1 and still nervous.

Mainly calculations but does have some recipes.

 

[muckanic]

The Kit IBU's were obtained from makers websites, Coopers specify the IBU's of the can rather than fermented volume and thus are calculated.

Ian, nice work, and a couple of questions on your calcs if you're still listening.

As you indicated, you're taking the quoted Coopers IBU spec and adjusting by 1.25/23 (that is, for a 23L brew). But when it comes to colour, you're using 5/80. For Black Rock, on the other hand, it's 5/80 for both colour and IBUs. Why the difference?

Then, having adjusted the kit colour for volume in the "kits" tab, it looks to me like you're doing it differently in the "main" tab in rows 100+, using a constant adjustment of about 87% of the first figure. For IBUs, however, the "kits" and main" figures are identical. One again, why the different treatments?

More generally, I am having difficulty following your colour formula. This is something I have been looking for a for a while for my own spreadsheet, for all-grain. As far as I can tell, you are saying colour = 1.4922 [lbs * SRM/US galls] ^ 0.6859. Where do those parameters come from? It is interesting that this is a non-linear formula, ie, three times the grain, or the same amount of a three times darker grain, does not mean three times the colour. Can you point us to a source for more information or more discussion of this point?

 

[ianh]

Ian, nice work, and a couple of questions on your calcs if you're still listening.

As you indicated, you're taking the quoted Coopers IBU spec and adjusting by 1.25/23 (that is, for a 23L brew). But when it comes to colour, you're using 5/80. For Black Rock, on the other hand, it's 5/80 for both colour and IBUs. Why the difference?

Then, having adjusted the kit colour for volume in the "kits" tab, it looks to me like you're doing it differently in the "main" tab in rows 100+, using a constant adjustment of about 87% of the first figure. For IBUs, however, the "kits" and main" figures are identical. One again, why the different treatments?

More generally, I am having difficulty following your colour formula. This is something I have been looking for a for a while for my own spreadsheet, for all-grain. As far as I can tell, you are saying colour = 1.4922 [lbs * SRM/US galls] ^ 0.6859. Where do those parameters come from? It is interesting that this is a non-linear formula, ie, three times the grain, or the same amount of a three times darker grain, does not mean three times the colour. Can you point us to a source for more information or more discussion of this point?

Originally both the Black Rock and Coopers were calculated the same way, but back in version 1.2 I changed the way Coopers were calculated because of comments received. The values listed on the Kits page are sourced from the internet but Black Rock, Muntons, TCB etc I have never used so have not looked further into the values and included them on the Main worksheet.

The beer colour is calculated using Morey's Equation, google should find some results.

Still working on the spreadsheet looking at including an inventory.

 

[ianh]

Hi All

Just finished updating the spreadsheet and have attached version 2.2

View attachment Kit___Extract_Beer_Designer_V2.2.xls

Have added an Inventory button so you can create and add stuff to your Inventory.

Also added a Make Recipe button. This checks your Inventory to see if you have all the necessary ingredients for the recipe. If you have not tells you what you are short. If you have all the ingredients then subtracts them from the Inventory and puts the recipe onto the Brews worksheet including the Brewday worksheet data as a comment.

Changed the layout of the Brews worksheet, if you have an existing Brews worksheet you will need to change it to match the new version before copying it across (columns A to K need to be the same).

Also modified the Bulk Prime so you now select dextrose or sucrose. If bulk priming and dextrose are selected, then the dextrose is subtracted from the Inventory and the amount of dextrose for bulk priming is added to the Brews worksheet.

If your Brews worksheet layout is different (columns A to K need to be the same) but the Bulk Prime is in a column other than V then you either need to inset some columns so it is in V (can always hide the extra columns) or edit the makerecipe macro, Near the end of the macro (about half way down the page) you will see some comments about dextrose and bulk prime. You need to change the value of 19 in the bit suggested to match your column.

I have also made some slight changes to some of the ingredients. I think some ingredients had a space at the end (which I have now deleted) and I have added a letter "s" to some ingredients thus golding hops becomes goldings and crystal hops become crystals. This was done so the Inventory macro finds the correct ingredients.

One result of this, if you had stored recipes with any of these names that have been changed, when you Get Recipe to bring it back to the Main Worksheet then it won't calculate correctly, You will have no ebc against a grain or %aa value next to a hop. Just select the item from the dropdown list and it will fix the problem. Then save the recipe.

Have fun.

cheers

Ian

 

[ianh]

OK found a minor problem.

If you add Inventory for Dextrose and Maltodextrose you must have a weight for both even if its 0 (zero) otherwise you get a macro run error.

cheers

Ian

 

[Oatlands Brewer]

Ian......You Rock

:beerbang:

 

[antains]

Hi Ian,
I've been following this since you first posted about it.

Thanks for your efforts. It's a great resource. (Thanks to everyone else for adding to the refinement of it.)


Ant.

 

[amiddler]

Ian

I thought I was good with Excel but that is cool. Now your next project is to make one for All Grain brewing. :lol:

Drew

 

[ianh]

I thought I was good with Excel but that is cool. Now your next project is to make one for All Grain brewing. :lol:

Thanks.It was done partly so I could learn about brewing and partly because I could not find any software I liked that catered for Kit & Extract brewers.

I think All Grain brewers have a much better choice of software and at present I have no plans for AG, well maybe have a look at Biab.

 

[Brewlord]

Ian,top job mate. Been using this from Vers 1.0 and it rocks.

But...I am an excel dummy and I am having a few problems using the inventory in Vers 2.2 Whenever I click the link button I get a visual basic compile error with variable not defined. I have also tried to manually enter my inventory onto the worksheet using the lists you provided on the right hand side of the inventory and doing a data validation to make them into pull down boxes under the various headings of malts, kits etc. (BTW, The make recipe button is recognising the ingredients I am putting into the recipe but not removing them from the inventory.) Its like the inventory is not linked to the main page???

Help!!!!!!!

BL

 

[RobboMC]

I have no problem with you doing whatever you want with the spreadsheet, the BCJP styles are copyright however.

Would be great if the spreadsheet did some 'thermal' arithmatic as well.
Since you know how big the boil volume is, and how much extract is being added,
the BrewDay page could go something like"

Add X litres of boiling water, add Y litres of cold tap water at 15 deg C,
resulting temp WILL BE XX Deg C.

I feel this would really help new brewers.

The spreadsheet is great work, well done.

 

[barneyb]

I just downloaded this the other day and it is fantastic. I'm still only making beers from other people's recipes but even for that it is great, especially being able to get all the info I'm still learning about - IBU's etc.

Good job. :icon_cheers:

 

[ianh]

Would be great if the spreadsheet did some 'thermal' arithmatic as well.
Since you know how big the boil volume is, and how much extract is being added,
the BrewDay page could go something like"

Add X litres of boiling water, add Y litres of cold tap water at 15 deg C,
resulting temp WILL BE XX Deg C.

I feel this would really help new brewers.

The spreadsheet is great work, well done.

Hi RobboMC

Thanks. Do lots of brewers do it this way, since boiling larger volumes (10litres) I have taken to cooling in the laundry sink using iced water if necessary. Suppose if I was only boiling a small volume it might help.

Brewlord I have sent a pm.

cheers

Ian

 

[RobboMC]

Hi RobboMC

Thanks. Do lots of brewers do it this way, since boiling larger volumes (10litres) I have taken to cooling in the laundry sink using iced water if necessary. Suppose if I was only boiling a small volume it might help.

Brewlord I have sent a pm.

cheers

Ian

I've had to break through 2 boundaries in my brewing learning curve. The first was when I realised I couldn't get a decent pitching temperature with just tap water, and I needed to chill some water beforehand. The 2nd boundary was when I discovered I was doing so much extra boiling that I couldn't do it with 100% chilled tap water and needed to start adding ice.

It simply occurred to me that all the needed data was already there to make these calc if some easy assumptions are made.
For new brewers it would be good to add in the extra functionality. I had to discover it all for myself.
If you want to program it and want some heat calc data PM me.

 

[dago001]

Hi RobboMC

Thanks. Do lots of brewers do it this way, since boiling larger volumes (10litres) I have taken to cooling in the laundry sink using iced water if necessary. Suppose if I was only boiling a small volume it might help.

Brewlord I have sent a pm.

cheers

Ian

Hi Ian.
Exactly the same as me. I just use tap water and change it twice over the course of an hour.
I suppose that Friday isn't washing day then.
Cheers
David

 

[bluejed]

Great spread sheet, already picked up where I whent wrong with my last brew A+ A+ A+..

Just a quick thing, I was looking at aprevious thread for a 2can brew listed below..

2 x 1.5kg Coopers LME tins (pale)
500g Dextrose
20g Centennial @ 30 min
40g Amarillo @ 20 min
40g Amarillo @ 10 min

Total 31 IBU

OG 1058

Pitch Nottingham (would probably work well with US-05 too) and keep the temps under 20 degrees.

FG 1013

As it states the total IBU is 31, I put the method into the calculator and it gave me 91 IBU, am I missing something??
As per the Graph below bitterness increases expedentially after 15min boil (Approx) where as the IBU is affected immediatly.
IS This correct or should the IBU be less affected at 1 - 15min boil.

Again great excel sheet very impressed