Kit And Extract Beer Spreadsheet

[whitegoose]

Sorry about that I was trying to sort others out and forgot about the kits. Then had to put my maths hat on to fix the kits.

Hopefully all now working correctly.


View attachment 25001

cheers

Ian

Thanks for the quick response!

It's still giving me a different EBC to the spreadsheet 2 versions ago, but much closer... My beer that used to come out about 10 EBC is now 7.9 EBC - I take it you have tweaked the calculations?

 

[ford-ute]

thunmb up mate i have notice that the beer still does not change colour but the rest is good

 

[ianh]

Thanks for the quick response!

It's still giving me a different EBC to the spreadsheet 2 versions ago, but much closer... My beer that used to come out about 10 EBC is now 7.9 EBC - I take it you have tweaked the calculations?

The original calculations did not calculate the total EBC correctly, hopefully they do now and will be lower than the previous values. There could still be an issue with the values from Coopers Kits. Coopers specify can contents rather than what the can will produce in say 23 litres. I have taken a value between what Coopers say and what others say.

Still have not got a copy of Excel 2007, so the beers colours won't change till I can have a look at it.

I think in general though we are getting close.

cheers

Ian

 

[unterberg]

Thanks mate. I think its great!

Put down a Doppelbock 2 weeks ago and according to the spreadsheet my EBC was way to high (I didnt care really) but after putting things in this one its actually all within the style. I think the EBC nearly went halves from 45 to 23.

 

[ianh]

Thanks mate. I think its great!

Put down a Doppelbock 2 weeks ago and according to the spreadsheet my EBC was way to high (I didnt care really) but after putting things in this one its actually all within the style. I think the EBC nearly went halves from 45 to 23.

Thanks I thought it more important to sort out the hops/bitterness first before the colour.

cheers

Ian

 

[smollocks]

Thanks for this Ian, I've found it very educational. My only suggestion is to consider version numbers and maybe even a brief changelog on the notes page.

 

[syd_03]

G'day Ian,

The speadsheet is a great Idea and you have done a great job.

On the issues of Coopers kits, they explain how to get the values on their FAQ page. Basically it is the number times the can weight divided by the volume.

Eg. IPA is 710 IBU which is times 1.7 then divided by 23L is 52.478 IBU (your value was 30.9 which is 710/23) same goes for colour values.

They then go on to say that fermantation will lower these valuse by 10 to 30 percent so the final IBU in the brew due to the kit may be between 36.735 to 47.230 IBU for this example.

Not really sure how you calculate how much is lost due to fermentation I would say that the entire brew would have the loss i.e. the Kit IBU of 52.478 added to the IBU from hop addition then reduced by somewhere between 10 to 30 percent?
Similar to EBC from kit plus all additions then reduced?

Sorry if this is confusing, I have confused myself, just starting to look at all these factors for the first time.

 

[ianh]

G'day Ian,

The speadsheet is a great Idea and you have done a great job.

On the issues of Coopers kits, they explain how to get the values on their FAQ page. Basically it is the number times the can weight divided by the volume.

Eg. IPA is 710 IBU which is times 1.7 then divided by 23L is 52.478 IBU (your value was 30.9 which is 710/23) same goes for colour values.

G'day syd_03

Yes I was aware of that, but a number of more experienced brewers (not hard just bottled brew 20) say that, especially for the bitterness, that a value of Can IBU / Volume is more realistic and that the Coopers calcs still overstate the bitterness. So I went with the experience.

Like you learning and trying to get my head round all the numbers.

cheers

Ian

 

[Leigh]

Crikey, how have I missed this thread!

Awesome tool Ian...will have to start playing with this.

Thanks heaps!

 

[Leigh]

OK, so I used this spreadsheet last night when I put on my latest Golden Ale extract with partial mash...adjusted the grain and hop schedules to get a "better balance"...will report back on how it goes.

 

[chriscapetrib]

Wow Ian

I have only just found this thread..... Great work! Heaps of info. Easy to understand and use. Gotta love Excel!!


:icon_cheers:
Chris

 

[ianh]

Hi

Just updated the spreadsheet called the new version 1.1

View attachment Kit___Extract_Beer_Designer_V1.1.xls


Changes since previous version

Changed the efficiency factor for steeping grains from 50% to 60%.

The colour for liquid malts was incorrectly calculated. This has been changes so it matches the manufacturers data of xxx EBC in 23 litres for the can, except for Coopers which specify the can contents.

The colour of the beer in the glass now changes. (I got a copy of Excel 2007, made the changes but it also seems to work ok in older versions of Excel)

cheers

Ian

 

[mrwest]

Thanks ianh, this is quite useful even for a beginner like me who's only doing K&B brews. Even though I'm not making use of all the features, basic stuff like estimating alcohol content and what gravity readings to expect is very handy

 

[Gwyn]

Cheers ianh, I'm a big fan of Excel and thats a great spreadsheet! Just joined up and am hoping that there is heaps more stuff as useful as this.

 

[newbud]

Ditto, thankyou for this one. Really good and I was thinking of doing two cans now I can try and give it a more calculated shot

cheers,

 

[DKS]

Hi

Just updated the spreadsheet called the new version 1.1

Top stuff ianh just used your spreadsheet for a pale this week end. I like it because you dont have to be that technically advanced down the HB path to get it working for you.
Could you or others give a link or info on BU:GU and BV (grouped with attenuation on the speadsheet.) What they are and what they mean in relation to each other. What target numbers to go for if thats applicable etc. Probably in wiki but havent found yet .
Thanks for your efforts. :icon_cheers:
Daz

 

[Rodolphe01]

This spreadsheet is great, I was doing kits and bits without ever really knowing what IBU and EBC I will get. I've just started using this, great work.

 

[AlexL]

Another big thank you from me. Very useful spreadsheet. It's a good way of keeping track of of old recipes too

 

[ianh]

Top stuff ianh just used your spreadsheet for a pale this week end. I like it because you dont have to be that technically advanced down the HB path to get it working for you.
Could you or others give a link or info on BU:GU and BV (grouped with attenuation on the speadsheet.) What they are and what they mean in relation to each other. What target numbers to go for if thats applicable etc. Probably in wiki but havent found yet .
Thanks for your efforts. :icon_cheers:
Daz

Just google BU:GU will give you heaps of sites and the BV Link

The values given for the BJCP types are calculated using the midpoint or average values.

 

[ianh]

Another big thank you from me. Very useful spreadsheet. It's a good way of keeping track of of old recipes too

If you read the notes it tells you how to copy the BREWDAY worksheet into a Cell Comment in Excel that way you can either use it as part of your Excel Brewlog or create a spreadsheet of recipes, all you need is the recipe name then the Brewday Worksheet inserted as a Comment.

The Excel Brewlog I use has one brew per line and then all the details inserted as Comments.

cheers

Ian

 

[Gwyn]

Gday ianh
Being a new brewer I've found your spreadsheet has been really useful to understand how all the ingredients effect the outcome of your brew. I am now on my 4th brew and looking at putting down a stout (now the weather is starting to cool off) and was wandering if it was possible to modify your spreadsheet to include a couple of things....
1. A section to add lactose (fully unfermentable)
2. A section to add dried corn syrup (maltodextrin? partially fermentable) with the means to adjust the percentage

I don't know if anyone else would find this useful? I've had a crack at modifying your spreadsheet myself but its way out of my league!

Any help would be greatly appreciated, and cheers again for your work!

:beer: Tom

 

[groucho]

Top stuff!

Just one question (not sure if it has been mentioned in this thread before). Are you sure the IBU calculations are correct in column C of the KIT sheet? I thought (at least for the Coopers hopped extracts) you needed to multiply the can IBU by the can's weight and then divide by how many liters you put it in? eg: for the Coopers IPA in row 27, IBU should be 710*1.7/23 = 54.5 IBU.

Here's the link (see towards the bottom of the page):
http://www.coopers.com.au/homebrew/hbrew.php?pid=4

Keep up the top work. :icon_cheers:

groucho

 

[ianh]

Top stuff!

Just one question (not sure if it has been mentioned in this thread before). Are you sure the IBU calculations are correct in column C of the KIT sheet? I thought (at least for the Coopers hopped extracts) you needed to multiply the can IBU by the can's weight and then divide by how many liters you put it in? eg: for the Coopers IPA in row 27, IBU should be 710*1.7/23 = 54.5 IBU.

Here's the link (see towards the bottom of the page):
http://www.coopers.com.au/homebrew/hbrew.php?pid=4

Keep up the top work. :icon_cheers:

groucho

Hi groucho

This is one of the areas of much debate. If you go through the Coopers website it calculates it as above but then says expect the fermented IBU value to be 10 to 30% less ie between 38 and 49 IBU.

Some experienced AHB brewers suggested a straight division by volume gave a more accurate value so in the current version of the spreadsheet that's what I used and it gives a value of 31 IBU.

My current thinking is that as a compromise I should use the volume rather weight for both the IBU and EBC values thus for the IPA 710*1.25/23 = 38.6 IBU and 12.5 EBC and this is what I plan to use in version 1.2 of the spreadsheet.

cheers

Ian

 

[Dazza_devil]

I'll be looking forward to trying out vers. 1.2 Ian, very useful tool. The option of adding more than three spec. grains for steeping could be handy. Also two decimal places for the weight of each ingredient would be nice and may give more accuracy.
Cheers

 

[ianh]

I'll be looking forward to trying out vers. 1.2 Ian, very useful tool. The option of adding more than three spec. grains for steeping could be handy. Also two decimal places for the weight of each ingredient would be nice and may give more accuracy.
Cheers

Hi Boagsy

Attached Version 1.2 added an extra grain plus changed the Coopers Cans. You can change the weights to display 2 decimal places, but it's only the display that's affected not the calcs.

View attachment Kit___Extract_Beer_Designer_V1.2.xls

cheers

Ian

 

[Munut]

G'day ianh,

I would also be interested in be able to include corn syrup on your spread sheet as alot of the recipes I have include this.

Besides that its a great tool and has been of great help.

Keep it up and thanks for sharing. :super:

 

[muckey]

G'day ianh,

I would also be interested in be able to include corn syrup on your spread sheet as alot of the recipes I have include this.

I beleive that's dextrose

 

[ianh]

I beleive that's dextrose

I don't think Corn Syrup is fermentable link just added for body.

 

[Gwyn]

I guess what I'm buying from the HBS must be a mixture of dried corn syrup and dextrose as the guy there said it was 30% fermentable?

ianh
Is there anywhere in your spreadsheet where you can add unfermentables so that it can be incorporated into the final SG?
Cheers

 

[muckey]

ahh they must be referring to maltodextrin..

how quickly I forget