CP's Brew Chart 3.0

[ame]http://rapidshare.com/files/448162486/CP_s_Brew_Chart_3.0.xls[/ame] - updated link, again

Hello All,

I've finished the most recent upgrade. I've added quite a few more features and have rearranged for a cleaner/more functional layout. The last version was great, but this one is much better imo. A lot of that comes from all the suggestions/thoughts/etc... from users here in the previous thread (General Beer Discussion thread) and via pm and email. There was a lot of great feedback and I've done my best to incorporate as much of it as I could while still staying true to my vision of this project. I'm hoping there will be another round of great feedback here as well so this thing can keep moving forward and improving. All told it's become a fun project for me and I don't plan on stopping it anytime soon. You'll find that it's about as automated as it can be while still giving the user full control over almost everything.

Some of the new features:

1. Added a kegging calculator/how to which coincides with the bottle priming calculator/how to.
2. All ingredients are in drop downs now. The malts and other fermentables all prefill with max points/yield and color for each one. I also included the functionality for you to change those figures if you'd like and the ability for the user to add ingredients to those lists as well.
3. The Recipe Sheet tab and Brewday tab have been separated to clean it up. You can still design a recipe with all the ingredients on the same page for easy review. Once you're done with designing the recipe you'll have a dedicated tab to your brewday where all the math is taken care of for you so you can concentrate on brewing and measuring.

Please don't hesitate to ask questions and offer feedback. Your feedback last time around is what pushed this version to where it is now. Thanks to all that helped with feedback, testing, questions, etc... MX1 and NorCalAngler were nice enough to test this thing while I was developing it so thank you both for taking the time to do so as it really helped.

For those that have not used this in the past, here is a brief description of what you'd find if you download:

This workbook should allow you to design your beers and take them from the recipe design stage all the way to first taste while giving you the ability to do things optimally to produce the best beer you can. Each tab contains how to information based upon best practices found within the homebrewing community. You should find everything easy to use, automated where possible, and designed to help you concentrate on brewing and brewing well rather than math. If you use the Brewday tab properly it will do all the brewday math for you so you can just concentrate on your process and measurements. You will be able to track everything you need to track in order to replicate or troubleshoot the beer in the future.

Hope you enjoy it.

cp

Awesome! Checking it out this weekend.

Sounds good. Let me know if you have any questions etc...

Also, I just realized I forgot to unprotect the water profile tab starting water cells. I'll fix that when I get home and correct the link. Will post once it's been updated.

cp

[ame]http://rapidshare.com/files/448162486/CP_s_Brew_Chart_3.0.xls[/ame] - updated again.

Updated link above with the cells unlocked. If you notice anything that you feel may not be working as it should, whether it's a locked cell or a calculation, please let me know.

The link in the original post has been updated as well.

Looks like about 40 people had already downloaded. If you are one of those 40 please make sure you re-download.

Please offer your feedback, questions, or ideas.

cp

Apologies for the additional post. However, a user just let me know that I still had a couple of cells locked that should not have been. If you downloaded a previous link please make sure to re-download. The locked cells were on TH's EZ Water Spreadsheet.

The link in the original post and my signature have been updated.

Here is the link as well - [ame]http://rapidshare.com/files/448162486/CP_s_Brew_Chart_3.0.xls[/ame]

Thanks.

cp

CP,

I'm getting this error when trying to download your chart:

ERROR: Login failed. Account not validated. (2b976ba6)

Coog_Brew said:

CP,

I'm getting this error when trying to download your chart:

ERROR: Login failed. Account not validated. (2b976ba6)

Click to expand...

Wow, I've never seen that before. You shouldn't need an account to download it. It's free. Keep in mind, when you hit the link there will be a small pop up on your screen. Hit "no" to that and then use the slow download option on the page for the free download.

Try the link in my signature below and let me know if you get the same thing. I thought I updated all the links, but maybe I missed one. If you are still having issues post it here and then also send me your email address via pm and I'll send it to you directly.

Thanks.

cp

I'm getting the same error. I tried to right click and download the spreadsheet but when I open the .xls document it is blank. I'll send you a PM.

CP,

I got it to work. It had something to do with trying to download it in Firefox. Thanks. The spreadsheet looks great!

Thanks, glad to hear it worked for you.

If you have any questions etc... please don't hesitate to ask.

cp

I'll have a go with it...thanks for sharing with us!

No problem. If you have any questions etc... please don't hesitate to ask.

cp

I'm a MAC user, in the original EZ_water_calculator_2.0 there was some difficulty settingthe Bicarbonate radio button, it would not indicate that you have selected it. Someone suggested you enter the number 1 in the field just to the right of Bicarbonate (HCO3 ppm). Unfortunately, that cell is locked in your spreadsheet. Also, If I try to select the Bicarbonate radio button, it says it is locked.

tom.

My apologies. I will need to get those cells unprotected and re-post.

cp

OK, I've updated the links in this thread and in my signature below. Couldn't figure out what was going on. I had all of those cells unprotected, however, it wouldn't let you choose the bicarbonate button for some reason. I just unprotected all of the EZ Water Spreadsheet tab so there is no longer an issue. Just be certain to be careful with that particular tab. Don't want to accidentally delete one of the calcs etc...

If you have any further questions please don't hesitate to ask. Always looking for feedback as well.

cp

Anyone willing to email me a copy of the spreadsheet? My work has decided to block all file sharing sites like rapidshare. Send a PM, if you are.

Thanks

pm me your email address and I'll send it off to you.

cp

PM sent, thanks

Ok, once again just want to thank you for keeping on top of this Cory....

millstone said:

I'm a MAC user, in the original EZ_water_calculator_2.0 there was some difficulty settingthe Bicarbonate radio button, it would not indicate that you have selected it. Someone suggested you enter the number 1 in the field just to the right of Bicarbonate (HCO3 ppm). Unfortunately, that cell is locked in your spreadsheet. Also, If I try to select the Bicarbonate radio button, it says it is locked.

tom.

Click to expand...

Tom,

Have you tried converting this to Numbers, and if so, have you found any issues yet?

I am still bashing at it in both excel and numbers to see if I can break it....lol

T

Not a problem Tim. I appreciate it.

cp

Thanks for telling me about this in my other post. Trying it out now and I have a few questions... What is the water to grain ratio it's using? Does it assume a 10% absorption? Is it possible to unprotect the Strike temp because I was planning on 170F?

hogie1980 said:

Thanks for telling me about this in my other post. Trying it out now and I have a few questions... What is the water to grain ratio it's using? Does it assume a 10% absorption? Is it possible to unprotect the Strike temp because I was planning on 170F?

Click to expand...

No problem.

The water to grain ratio is a calculation designed so that you will obtain half of your boil volume from your mash and the other half from your sparge. The number you see in the cell is in gallons. To convert it to quarts you would multiply by 4. My work in this area of the brew chart was based on the work/research of Ken Schwartz and Kai at braukaiser.com Their work/research on batch sparge brewing shows that our best efficiency is obtained this way. You can read more about this at:

Ken Schwartz - http://home.roadrunner.com/~brewbeer/files/nbsparge.html
Kai - http://braukaiser.com/wiki/index.php/Batch_Sparging_Analysis

It assumes an absorption of .125 gallons per pound of grain.

No reason to unprotect the strike temp cell really. If you don't want to use the calculation within the cell just use the amount you prefer instead. That cell doesn't effect any of the other calcs. I would tell you that my experience and most everyone I've interacted with have found it to be very accurate. It is science/math so assuming you are inputting your figures correctly and pre-heating your mash tun it should work well. My recommendation is always to go into your mash tun about 5 degrees higher than the calc tells you and allow it to come down to about 2-3 degrees higher than the recommendedtemp. That will alleviate any concern about further heat loss in your tun. At that time begin adding your grains while stirring well. As you're stirring monitor the temp and close it up when you hit your desired mash temp. This process eliminates the concern of being too low with your mash temp which is a much bigger pain in the ass than being a little high. If you're a little high you just keep stirring while it's open until you reach the temp you want.

If you go to the brewday tab you'll see the brewer schedule as well. This details my process which may be of some help. You can of course re-write that if you'd like as well.

If you have any further questions please don't hesitate to ask. Appreciate any feedback as it allows me to keep improving this thing. The last time I posted a version there was some great feedback which propelled it to where it is now. It's essentially turned into a homebrewtalk thing and not just my thing which is pretty cool.

cp

Hey CP,
So I used your 2.0 version for a tripel I brewed and everything went great! Volumes spot on, temps spot on, it was probably my most successful brew day to date.

My only question is regarding the mash ratio. While the 50% mash 50% sparge worked well for me when I tried it, I can't help but wonder why almost everything I've read has the mash at 1.25 q/lbs or something along those lines.

Are there any foreseeable drawbacks to mashing in with so much water?

That's great to hear. The whole purpose of this project was to help not only myself on brewdays, but whomever else I could as well. Make sure to download version 3.0 and transfer your brews to it. It's quite an upgrade over 2.0.

I'm not aware of any drawbacks. If there are any I'd like to see the research behind it. Kai has done great research over at www.braukaiser.com. It's really great stuff if you get a chance to review it. Covers most everything you'd like covered on batch sparging.

1.25 q/lbs is perfectly fine as well. However, it's not going to get you 50/50 on your runnings. Plenty of ways to brew with even more opinions out there on how to do it. I've chosen to base my work on the best info/research I could find out there. It works great for me and many others. My opinion is if I'm going to spend all this time brewing I want to do things optimally as it should lead to the best product I can produce. I hope that others find this tool pushing/leading them towards producing a better product as well.

If you have other questions etc... please don't hesitate to let me know.

cp

MX1 said:

Tom,

Have you tried converting this to Numbers, and if so, have you found any issues yet?

Click to expand...

If you mean did I try putting a number in the cell just to the right of the radio buttons, yes. I put in number 1 for the Bicarbonate radio button, number 2 for the Alkalinity button and a 0 for no radio button. Also, selecting either of the radio buttons using a MAC worked also.

tom

millstone said:

If you mean did I try putting a number in the cell just to the right of the radio buttons, yes. I put in number 1 for the Bicarbonate radio button, number 2 for the Alkalinity button and a 0 for no radio button. Also, selecting either of the radio buttons using a MAC worked also.

tom

Click to expand...

No, what I was asking is have you converted it from Excel to Numbers ( Mac's spreadsheet program)

MX1 said:

No, what I was asking is have you converted it from Excel to Numbers ( Mac's spreadsheet program)

Click to expand...

No, I don't have iWork on my MAC

tom

millstone said:

No, I don't have iWork on my MAC

tom

Click to expand...

ahh ok then.

Tim

Does this support partial boils and partial extracts? Couldn't change boil size.

chaserchap said:

Does this support partial boils and partial extracts? Couldn't change boil size.

Click to expand...

Not really. It's set up for full boil batch sparge brewing. You could probably finagle it to work somehow, but it wouldn't be optimal. I've personally never brewed with partial boils or partial extracts.

I'd be willing to put in the time to convert this to that type of setup if there are enough people interested. Could have two spreadsheets - the original and a partial boil/extract version. I don't think it would take all that much to do so.

cp

I'd definitely like it, but don't base your decision on me alone. I don't have the setup or the space for anything but extract/partial...one day I'll get into big boy brewing.

Quick question. On the Recipe Sheet, there is a box for yeast. Is this cell dynamic in anyway? Will it affect calculations elsewhere in the spreadsheet? Also, the mash ratio seems to be off (low). Once I input the ingredients, it reads of a ratio of .241. Mash Volume and and Sparge volume seem to be accurate but I would like to have a better idea of my ratio. Maybe its just me being lazy not wanting to do the math on the side =]

BrotherFreeman said:

Quick question. On the Recipe Sheet, there is a box for yeast. Is this cell dynamic in anyway? Will it affect calculations elsewhere in the spreadsheet? Also, the mash ratio seems to be off (low). Once I input the ingredients, it reads of a ratio of .241. Mash Volume and and Sparge volume seem to be accurate but I would like to have a better idea of my ratio. Maybe its just me being lazy not wanting to do the math on the side =]

Click to expand...

No, the yeast cell is just your yeast choice.

Can you post your recipe? I'll have a better chance to answer your question that way. The mash ratio cell is in gallons per lb of grain so just multiply it by 4 to get quarts per lb. Though your ratio seems low regardless so I'd like to better understand your recipe if possible. Just list the amount of grains and the batch/boil size and I should be able to take it from there.

So I've come to my conclusion:

I love this spreadsheet. For me, it beats the brewing software by leaps and bounds. It's simple, easy, comprehensive and free; what else is there to ask for?

CP thanks for making this available, I am definitely a convert. Two brews down, all of the calc's were spot on.

If you are thinking about getting some brewing software, do yourself a favor, try this first!

Thanks Jrome, I really appreciate it. It's been a lot of work, but also very worthwhile and enjoyable work.

If you end up with any questions or ideas for improvement please don't hesitate to post them. I'd like/intend to keep developing this for the board.

cp

Said it before, and I will say it again....

This Brew Sheet is Rock Star Silly.....

Now, if I can only learn the fundamentals of Mash water, I can put one more part of it to use.

Tim

Hey CP - thanks for the spreadsheet. Couple suggestions (some easy, some probably less so). 1st - it would be really nice if the cells in which user inputs are needed are highlighted a different color than the non-modifiable cells. 2nd - would be nice to have an option of doing a mash out prior to 1st runnings to increase efficiency. Finally, being able to print out a brew-day action plan would be awesome. As it stands, it is necessary to flip back and forth between the recipe sheet and brewday sheet to determine mash volume and temperatures. Anyway, really great stuff, so thanks!

CP,

I just downloaded this and began playing with it. I usually do 6 gallon batches with 7 gallons to start. The boil size cell is locked. Does it matter?

EDIT: Nevermind. I just figured out that when I added my boil off, it adjusted the pre-boil volume

havadog said:

Hey CP - thanks for the spreadsheet. Couple suggestions (some easy, some probably less so). 1st - it would be really nice if the cells in which user inputs are needed are highlighted a different color than the non-modifiable cells. 2nd - would be nice to have an option of doing a mash out prior to 1st runnings to increase efficiency. Finally, being able to print out a brew-day action plan would be awesome. As it stands, it is necessary to flip back and forth between the recipe sheet and brewday sheet to determine mash volume and temperatures. Anyway, really great stuff, so thanks!

Click to expand...

Thanks for the suggestions. I'll keep all of that in mind for the next release. If you think of anything else please don't hesitate to post.

cp