Kit And Extract Beer Spreadsheet

[ianh]

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

 

[unterberg]

I reckon that is a great spreadsheet!

I think its pretty good already, very detailed and I like the fact that you can change/adapt many details to your liking e.g. hops AA that you have available etc.
The dropdownlists are very nice too. I can only see the coopers and morgans kits in the kit dropdownlist however.
I assume the others that are already listed in the kits worksheet should appear there as well.

Love your work! :beerbang:

This should get into an article so that people can find it when they are looking for it.

 

[whitegoose]

Thank you sooooo much, this is awesome.

I've only just moved to kits and bits and this spreadsheet looks like it it will be extremely helpful in putting recipes together, rather than just guessing or copying others, or investing in software.

Legend. Let us know if you make any tweaks!

 

[ianh]

The dropdownlists are very nice too. I can only see the coopers and morgans kits in the kit dropdownlist however.
I assume the others that are already listed in the kits worksheet should appear there as well.

Thanks for the great encouragement guys. I only use Coopers and Morgans kits so they are the only ones listed, however others are listed on the kits worksheet. To add then you need columns A,D and E from the KITS worksheet plus the weight of the Kit putting on the MAIN worksheet in Columns B to E starting row 139. If you add more than 2 you also need to change the formulas in cells C7,C8,G7,G8,H7 and H8 where it has $E$140 in the formula change this to $E$160 or whatever to allow for the number you added.

Also note the worksheets are not protected, so be careful not to delete any formulas. After you make any changes you can protect the Main worksheet.

With the Main worksheet displayed go to the Tools Menu select Protection then Protect Sheet then OK. As I say it is still a work in progress.

cheers

Ian

 

[Hefty]

Top stuff Ian!!
I have toyed around a little with making my own spreadsheet calculator but nothing like the level of sophisticated simplicity (and I mean that as a compliment) you've made here.
Capable of very complex styles but very user friendly!
Fantastic! Exactly what I haven't had the time to create! :icon_cheers:

Cheers!
Jono.

 

[mfdu]

i was just waiting to hear feedback from others b4 i opened it - dont take it the wrong way, i just wanted to know the macros were safe. . .

i think you've done a great job, and we basic kit&bit brewers should all shout you a beer!

greatest regards for your work,

chris.

 

[unterberg]

Thanks for the great encouragement guys. I only use Coopers and Morgans kits so they are the only ones listed, however others are listed on the kits worksheet. To add then you need columns A,D and E from the KITS worksheet plus the weight of the Kit putting on the MAIN worksheet in Columns B to E starting row 139. If you add more than 2 you also need to change the formulas in cells C7,C8,G7,G8,H7 and H8 where it has $E$140 in the formula change this to $E$160 or whatever to allow for the number you added.

Also note the worksheets are not protected, so be careful not to delete any formulas. After you make any changes you can protect the Main worksheet.

With the Main worksheet displayed go to the Tools Menu select Protection then Protect Sheet then OK. As I say it is still a work in progress.

cheers

Ian

Thanks mate.
I have included the Black Rock kits as well since I use them a fair bit.
I really love this spreadsheet.

Some suggestions:

It is already good to print on 2 pages but ideally you would arrange things in a way that you can make a 1-page printout for the brewday. Am just thinking of printing it out and then stick it on the wall in the kitchen for brew day with all the important info on 1 sheet. It could for example also be a new worksheet for that purpose to summarize the ingredients/brew process.

I imagine it would also be handy being able to print out a recipe on the 2 pages as you already can with all the details to collect/keep recipes. A field to put the date, name of beer and brewer in would be nice to include on those 2 pages. But hey that is only cosmetics and anyone can fit that in themselves.

The alcohol display for the keg/bottle is nice too. But is it really half a percent that gets added through adding that bit of sugar to it? Just wondering thats all.


But I really think this spreadsheet wont need any more big changes.
Should really put that in the articles section as soon as you are happy with it to make it available to anyone looking for such a great helpful tool.

 

[buttersd70]

Ian
I think it's a good sheet overall, and when tweaked will be a great resource...however, I'm interested to know what formulation you used for the gravity estimates, because it doesn't add up. I've had a look at the OG's for 1 tin and 1 kilo of dex (because it matches the example in the 'beersmith assistance - low abv' thread here, as well as just entering dextrose, lme, and dme on their own. Changing the weights of the adjunct, and working backwards to find the HWE shows that, as the weight of the adjunct is increased, so does the HWE. So whatever error is in the formulation is increasing as the weight increases. (this is clear to see by entering 5kg of dex and nothing else...you end up with 404hwe, or 105% potential)

 

[WarmBeer]

Great bit of work Ian.

I have a question regarding the boil volume. Adjusting the boil volume appears to have an impact on the IBU value and the corresponding BU:GU ratio.

My understanding is that hop utilisation, at least once you have a couple of litres of water volume, was related to hop weight, %AA and time, not volume.

I'm not 100% sure, and don't have anything in front of me to back it up, just posting a query.

Cheers,

Brett

 

[ianh]

Ian
I think it's a good sheet overall, and when tweaked will be a great resource...however, I'm interested to know what formulation you used for the gravity estimates, because it doesn't add up.

Thanks for taking the time to check the spreadsheet. Yes for some reason I was using 405 instead of 386 for the gravities. So in the next version it will be 386 Dex, 384 DME, 308 LME and at this stage I have taken an average for grain of 290 with an efficiency of 70% giving 203. May have to look at that again later.

I am hoping you and others will help me tweak the spreadsheet as part of the reason for doing this was to learn more about the brewing process and the factors involved. I am certainly no expert I have being brewing for 4 months or 40 years given there was 40 years in between.

Also changed the % alc difference between kegging and bottling to 0.4% (6g of sugar in 750ml).

Given there are a number of ways to calculate hop utilisation which give differing results I chosen a Boil Gravity of 1.040 and because Kit and Extract brewers tend to use much smaller volumes compared with AG brewers and I have included a Hop Concentration factor from Tinseth for those smaller volumes.

The next version will also have a chart which compares the Styles Malt Hop Balance to the Recipe Malt Hop Balance. Don't know how well the chart will go with different display formats. If a beer style has a slightly hoppy or maltly characteristic then the recipe should follow suit.

Will also do a worksheet of some descirption for brew day. Plan was to do a macro but may be able to get away with a sheet that just contains all the required information. The problem is that there are some many different possible combinations.

cheers

Ian

 

[buttersd70]

ah, ok. I see how you did it...sometimes those long multi cell formulae get to the point where you lose track of what the basic formula is if you didn't write it yourself.
I think 384 is going to be too high for averages of dme, though...going through the beersmith inventories and looking at all the extracts in there, I think that 375 would be a better average to use. The grain is the hard one...so much varience, so you can't do much more than a best guess.

One suggestion I might make is a yeast attenuation field, with a default value if left blank. That way, if you want to add in balance indicator, it could be in both BU:GU and BV.

 

[Mantis]

I just knew when I read the OP that butters will love this, and well, hehehe
Great spreadsheet Ian

 

[buttersd70]

I just knew when I read the OP that butters will love this, and well, hehehe
Great spreadsheet Ian

Once it's all ironed out, I can save myself a lot of typing in future posts, and just link to it.. :lol:

 

[shellnaf]

Shouldn't the IBU for the kit change when the "ferment volume" is changed?? e.g. Coopers Real Ale goo stays at 30.4 no matter what volume is entered. Or am I missing something?? Could be the latter, it's my last nightshift then 4 brewing days off

Nat

 

[ianh]

Shouldn't the IBU for the kit change when the "ferment volume" is changed?? e.g. Coopers Real Ale goo stays at 30.4 no matter what volume is entered. Or am I missing something?? Could be the latter, it's my last nightshift then 4 brewing days off

Nat

Yes it should, just did kits for 23 litres. Fix in next version

Thanks

Ian

 

[chappo1970]

Ian,
Damn awesome mate! Well done! Simple and straight forward KIS. A bit of a tidy up as others have suggested and it will be da bomb! :beerbang:
Thanks for taking the time and effort on it... Your bloods worth bottlin'. Ever get near Bethania or Brisbane City drop me a PM and I will shout ya beer.

Cheers

Chappo

 

[ianh]

Hi Guys

After taking in the comments and suggestions. I have modified the spreadsheet.

Corrected some calculation errors, made the Kit values vary with ferment volume.

Added a Brewday worksheet, added a graph in the MAIN worksheet, just go right to cells R4:W24

Hope the graph still displays correctly for those who have different screen sizes and resolutions. Hope you find the graph interesting as I did.

Again it is still a working in progress and I deleted all the comments from the styles worksheet so it's much smaller file in size.

cheers

Ian

View attachment K___E_Beer_Designer_2.xls

 

[ianh]

One suggestion I might make is a yeast attenuation field, with a default value if left blank. That way, if you want to add in balance indicator, it could be in both BU:GU and BV.

Yeast attenuation as I understand it is a factor in FG which is part of the BV calc. Not sure what you are trying to do, can you point me in the right direction.

cheers

Ian

 

[brettprevans]

keep at it Ian. great work

 

[MarkBastard]

Hello ian, first of all great spreadsheet.

With LME etc additions can you designate when it is added? E.g. sometimes you'll add 1.5L pre-boil and boil your hops etc, but add another 1.5L at flame out. This affects the IBU.

Cheers.