Kit And Extract Beer Spreadsheet

[Sam Smith]

Hi Guys

I am a newbie here but as part of my home brew learning process I thought I would develop a spreadsheet to enable me to design beers using Kits & Extracts. I find most of the software available is complicated and not really suitable. So it has certainly been an interesting experience and I am sure other guys have done this before me.

As I look around the forum I feel there is a need for such software as most software seems to cater for all grain brewers.

So I present it here, whilst I don't recommend people use it as their only piece of software. Hopefully in the future that may not be the case, I would appreciate comments on what is required and what is wrong especially on conversion factors.

So please tell me what's wrong with it, whether the factors need to be changed, what you would like to see and we will see what we can do.

If you download it, please read the Notes before running the program to tell you how to set it up and comment on any problems you find.

Thanks

Ian

The latest revision is located here

And here is the latest version as well.
View attachment 52561

 

[Sam Smith]

You could use the free “YoBrew Beer Wine & Jam etc. Calc’s” from www.yobrew.co.uk/calculators.php.

 

[monkey brewing]

Anyone that cant open the spreadsheet like me, home brew kit master does the trick for us kk brewers

 

[user 77260]

I took a look at it. It looks really nice. The one thing that doesn't work for me is the way it handles different batch sizes. Even when I enter a ferment volume less than 23 liters, it doesn't make the adjustments. I wind up getting odd figures like a 9-liter batch of lager, no added hops, with a 65-point IBU.

Otherwise it's really nice, especially since I only use Morgans kits at this point.

 

[ianh]

Given the IBU's of the can you should expect something like that. If you enter a ferment volume of 23 litres and the enter a recipe, you should be able to see the values change as you change the ferment volume.

 

[Yuz]

Given the IBU's of the can you should expect something like that. If you enter a ferment volume of 23 litres and the enter a recipe, you should be able to see the values change as you change the ferment volume.

Also - make sure Excel has Automatic Workbook Calculations enabled in "Formulas" option.
If it's set to Manual, it won't update itself.

 

[user 77260]

Thanks. Calculations are set to automatic I'm still having problems, though.

I've got the following:
Ferment volume: 9 litres
Kit: Morgans BML, 0.6kg
LME: Morgans Unhopped Extra Pale, 0.6kg
no hops entered

And I get an IBU of 63.9. That just can't be right, can it? The IBU field doesn't change as I change the amounts of kit and LME. Is it solely dependent on the ferment volume field?

 

[Thomas Wood]

Thanks. Calculations are set to automatic I'm still having problems, though.

Have you seen this post?

Kit And Extract Beer Spreadsheet

 

[user 77260]

Thanks. I checked it, and that formula was already there.

 

[Stewart Duffy]

Hi all. Love the spreadsheet, some awesome work has been put into this. I have a couple of questions about adding to he Kits worksheet.

I understand that the IBU's for the coopers kits follow the following formula:

Product bitterness x 1.7 / Brew volume = Brew Bitterness before fermentation. So following this example, and the example on the coopers FAQ page, we can do this:

Mexican Cerveza up to a volume of 23 litres: 270 x 1.7 / 23 = 20 (rounded from 19.95) IBU (International Bitterness Units). Ok cool. But the spreadsheet lists the IBU as for that kit at 14.7. I am guessing this is to account for loss after fermentation which the coopers site says can be 10-30%. All good.

So what is the the formula used to calculate the IBU for 23 litres? Is it Product bitterness x kit weight / Brew volume = Brew Bitterness - [some & in here for loss]? What % is used for loss? Am I missing a bit here?

Likewise I would like to know how the Black Rock kits were calculated. I am guessing the were calculated in the reverse way than the coopers because both the new Black Rock (& old site) list the IBU for the 23 litres before loss. What % was subtracted from these before calculating the IBU for the can?

I would very like to update this spreadsheet to add all the current Black Rock range, some of the new Coopers kits and the Mangrove jacks, but I am just missing some key numbers here, that I haven't been able to place.

Any advice would be awesome!

Chur

 

[user 77260]

Yay, finally got it to work. There was an extra permission that appeared at the top (in a yellow bar) that I hadn't noticed before. Works well now, thanks!

 

[Indian Giver]

I would love it if it went partials as well, but that's being picky. It was great.

Noticed on little thing. I wanted to calculate a 2min hop addition and it shows as 0 IBU from that addition if I do (on 150g of hops). I know that adequate isomerisation will occur before I cool below 85 degrees, so just wondering about this.

 

[Meddo]

https://aussiehomebrewer.com/threads/improving-precision-in-ibu-calculations.91684/

 

[Jamesrs59]

Hey all, reviving an old thread, been using this sheet and it’s great, so thank you OP.

being the lazy fella I am, does anyone have a sheet filled with a bunch of premade recipes they’re willing to share?

 

[Journeyman]

@ianh
I run Linux Mint and use LibreOffice, a version (or replacement) for OpenOffice. The spreadsheet works partially - e.g. the 'New Recipe button clears the front page but the other buttons give a VBA error. See attached.
Apparently OpenOffice users don't get even the level of functionality I get in LO.
Any way to resolve this without me learning VB programming?

 

[blacktop™]

Use a different operating system?

 

[Journeyman]

Use a different operating system?

Now THAT raises an interesting point - am I addicted enough at this early stage to go back to Windoze? Even as a dual boot Billy's garbage is a pain in the arse - and I supported users using it for 20+ years.
Maybe I could VM it and just open it for the SS?

 

[koshari]

even just having a VM you could boot. i dunno that the VB programming is going to help because thats the functionality generally missing in OO or LO. someone would have to try and port the advanced functions in perl or some other native linux script.
for the record we generally use linux mint mate and i run a dual boot laptop and have a few windows VMs with XP , win7 and win 10 that i boot through virtualbox for some legacy aps i require for my work.

 

[ukulele01]

Question for Ian H: been using your spreadsheet for years, it is great. The only thing lacking is that it does not calculate IBU from FO additions, for partial mashes. Any chance that could be added? Thanks for all of your hard work. Cheers.

 

[clayts]

Hi all,

First time poster but I've got 5 or 6 brews under my belt. I seem to following along the traditional path (straight up kit and kilo, then adding a short hop boil, then steeping grains, then dry extract/grains/hops without the kit....)

So I've found myself using this masterpiece of a spreadsheet and I've run into a slight spot of confusion.

I've started brewing smaller batches and experimenting a bit. My question relates to the amount of DME for the hop boil (to raise it to 1.040 BG) vs the DME in the actual recipe; specifically if the hop boil DME weight ends up being less than that the recipe calls for.

The reason this has come up is because I'm using a Coopers Draught can to brew a 12L English Strong Bitter batch with a 6L boil. My recipe is pasted below. So you can see that for a 1.040 BG i need approx 600g of DME. But the recipe itself only needs 400g (plus the kit itself) to get to a OG of 1.059. I've not run into this issue before. Usually it is the other way around and I end up adding the balance of the recipe's DME post boil before diluting to the final volume/stirring.

Obviously I can just not add any more DME post boil but would that affect the OG? I notice that the OG in the spreadsheet is independent of the hop boil DME addition, but for small batches like this one, should it be? In this specific case, I doubt it is going to make a huge difference either way considering we're talking 150g DME in 12L but I guess I'm more interested in the theory!

Any help would be greatly appreciated.