CP's Brew Chart 2.0

[ame]http://rapidshare.com/files/445323364/CP_s_Brew_Chart.xls[/ame]

This is a pretty extensive upgrade to my previous version. If you are considering buying brew software I would recommend downloading this first to see if you really need to spend that money. You should find that this will allow you to:

1. Design recipes with just about all ingredients at your finger tips. Each of those ingredients has detailed info as to their profile and how to use. Just scroll over any of them to see the information.
2. Your brewday is made much easier as well. The brewday section takes care of all the math for you. All you need to do is concentrate on your brewing and make sure to input the necessary information to the chart. It will calculate your expected OG post boil and make recommendations on what you can do to hit your target gravity if your expected gravity is off. You'll also be able to make hop schedule adjustments prior to boiling as well based upon the wort you've got on brewday.
3. Use TH's ez water calc along with ajdelange's water profile recommendations.
4. The yeast pitch tab gives you detailed information on the amount of cells you need for the beer you're making. Let's you know how to get there with liquid yeast or how many packets of dry yeast you'll need. Very similar to Mr. Malty.
5. Very detailed bottle priming calculator with recommended carb levels for each style of beer.
6. BJCP guidelines tab has all of the style guidelines at your finger tips.
7. Designing beers tab is an idea I have as well. I have one beer style there at this time. Want to see what people think. Info was primarily from Ray Daniel's Designing Great Beers book along with some other sources.

All told I wanted to have something that allowed me to design a recipe with all necessary information to do so right in front of me. Got tired of sitting at a table with books, guidelines, etc... all over the place. This allows me to do that. Also wanted to have something that I could take from recipe design all the way to first taste while tracking all the details. This allows you to do just that as well.

I use this for every beer I make. I created a brew folder and have a folder within that folder for each style of beer that I brew (APA, Porter, Kolsch, etc...). You would do a "save as" for each beer and save it in that styles folder. That allows me to have a record of every beer I brew while still having the original program for a new beer.

I have a couple of other ideas for down the road as well. Tab for keeping track of your ingredient inventory, kegging how to/pressure calculator, etc... If there is something that you feel would be a good addition please don't hesitate to say so. I'll also be working on getting this converted to a google doc as well. For some reason it will not map correctly, but I'll get it figured out.

Hope you enjoy it.

cp

I guess I'll have to check this out at home, the servers at work are currently blocking the link.

Just downloaded it. Looks like you went all out on this thing! Thanks!

Edit: Didn't see Magnum on the Hops list.

Thanks PB, really appreciate it.

Magnum is under the Hallertauer cell. If you scroll over that cell you should be able to see it. Otherwise, right click on the cell and hit "show comment" and that will have the notes for that cell remain open so you can read it. When done just right click on the cell again and hit "hide comment".

If you have any other issues/questions just let me know.

cp

Let me know what you think or if you have any questions once you've been able to view it Ato.

Cool, I just missed it. It took me a couple of minutes to figure out what numbers to enter where, but after that I think this will be very useful. I was concerned about my next recipe, the grain bill seemed light, but after plugging things in and getting the numbers I feel ready to go!

Great to hear PB.

If you have any further questions don't hesitate to ask. I tried to set it up as user friendly as possible while still allowing for control of aspects that you may not find in other programs (expected attenuation, adjusting potential yield/max gravity and color of each grain, etc...). It takes a bit to get used to it because of all the information/tabs, but once you get it you should find that it's very easy to use.

cp

Looks great. I get more than what i need from Beersmith, but I'm very curious as to how you're "on the fly" brewday calcs will work. I'm going to flex your spreadsheet's might compared to my Beersmith experience for my next batch.

Sounds good cactusgarrett. They better work, I've tested them too many times now and have worked great on all my recent brews.

Good luck and please let me know how it goes. If something is amiss I'll get it researched/corrected.

cp

RapidShare says I need to be a RapidPro account. In other words it requires me to BUY your file.

You are getting a kick back, right?

Ha, I wish! No, this is a free spreadsheet for all to use.

When that window pops up just close it out or choose "no". You'll then see two options on the screen. The one on the right is the free download (believe it's called slow download) which takes about 30 seconds.

I wish I was getting paid. I could then justify all of the time I've spent on this with my wife

Did that before and no options appeared in IE or Opera. Tried it again.

Maybe the site is supper busy on a Friday evening. Will try later or tommorrow.

That's strange. I just tried it after you posted the first time and it worked just fine.

I assume you're getting a pop up that talks about signing up for rapidpro or something along those lines? Make sure to choose no to that and then the regular site should be there. It will give you two options to download. The one on the right is the slow download which is free.

Let me know if you run into any other issues. If you do just pm me your email address and I'll email it to you instead.

cp

Just downloaded and will try this thing out. Thanks for sharing your efforts CP!

As far as RapidShare goes, during peak downloading times they will shut down the free download service. So if you can't download it, try again during an off peak time.

No problem Nakeddog. Let me know what you think once you give it a try.

I didn't know that about RapidShare. If there is a better service out there I can put it there instead. If anyone else is having those issues please let me know.

I'm going to spend some time getting this mapped over to a googledoc this weekend hopefully as well. Just need to modify some cells that are not allowing the doc to map properly. Once that is complete then you'd be able to access this from any computer at anytime as well.

cp

There were two cells in the brewday section that were locked mistakenly. If you have downloaded the Brew Chart please re-download here:

[ame]http://rapidshare.com/files/445323364/CP_s_Brew_Chart.xls[/ame]

I gave it a test run with an American IPA recipe last night and it worked just as intended. Will make your brewing and brewday a little easier if you let it.

Hope you're enjoying it.

cp

Ha! I was kicking around in it, trying to see how theoretical data was going to play out, and i thought i was being thick - must have been the cells i was trying to use. I'll try it out again; thanks for adjusting.

No problem. If you have any questions/concerns just let me know.

cp

Thx for the resource, I am seeing that a few items do not translate over to Numbers (Mac) but will test out the functionality and report back. Seems like a great document to have on my iPad if it will work without me having to buy a 3rd party app.

Also, ever think about putting this in App format for iOS and Droid? You do deserve some payback for your time.

Tim

Thanks Tim, please do report back. Would like the feedback as it helps with improvements etc...

I have thought about that, but not something I'm looking to do quite yet. There are a couple of additional items I want to get added before considering it as well. Plus, I'd have to figure out where to start on that since I'm not real literate in those technologies.

Thanks again.

cp

quick Question
on the mash Ratio, is that qt/pound or gallon/pound.
or how would I put that i want 1,25qt per pound in that field.

Thx

Tim

That cell is gallons per pound. You'd want to multiply it by 4 to get quarts.

The spreadsheet is set up to maximize batch sparge efficiency based upon Ken Schwartz's and Kai's research/work. It figures out what amount of water you'll need in the mash to get 50% of your pre boil volume from the initial mash. You'll then get your other 50% from the sparge.

Ken Schwartz's website/work - http://home.roadrunner.com/~brewbeer/files/nbsparge.html

Link to Kai's Batch Sparge research - http://braukaiser.com/wiki/index.php/Batch_Sparging_Analysis

Let me know if you have any further questions. If you truly want to adjust that cell on your own just let pm me your email address and I can send you a copy where you'd need to input your desired mash ratio each time. I'd recommend giving the spreadsheet a try first though. I regularly hit 85% + efficiency. Some of that is the 50/50 rule, but quite a bit else is using a fine crush on the grain, draining the mash tun dry (relatively), etc...

cp

sure thing, I will work of a beer in the spreed sheet, the transfer it over to the Numbers version, and compare the functionality. I will also compare it against the software that I use, last question for now...what formula are you using for IBU?

Tim

I use the Tinseth calculation. I also use the average gravity during the boil for the calculation which according to Tinseth is the most accurate. Some software/calculators out there only use preboil gravity. If you want to see the formula just let me know.

cp

Nah, just wanted to know so I could set my software up to use the same formula. It will get me close enough for government work...

give me a day or 2 I will have some feed back for you.

Tim

Sounds great.

ok here it goes.
The Recipe - Simple Pale Ale

8# Belgium or Pale Malt
2# Munich Malt

Simcoe hops @ 13%AA
60, 30, 15, 5 min additions
Saf-05 yeast.

The Numbers / Apps - going to keep it simple OG, FG, ABV, Color, IBU.

Cp Brew Sheet Windows and Mac
OG: 1.047
FG: 1.012
AVB: 4.6
SRM: 6.4
IBU: 26.7

BrewTarget
OG: 1.050
FG: 1.013
AVB: 4.9
SRM: 6
IBU: 25.8

Beer Alchemy
OG: 1.047
FG: 1.012
AVB: 4.7
SRM: 5.9
IBU: 27.3

So there you have it. The converted Windows spreadsheet to Numbers for Mac says there are deleted macros, and lost formatting, but I have not found them yet.

Tim

Interesting. Nothing really concerning there unless I'm missing something.

I'm guessing Brew Target is using a higher potential yield for the grains used than what you are inputting into my spreadsheet and for what Beer Alchemy is using. That would account for the difference in OG, FG, and ABV. Color is essentially the same, the amount that you input to my spreadsheet vs what those programs are assuming for the color of the grain would explain the small differences. The IBU's have very small differences. I'm not familiar with those two programs so can't comment on what formula they are using for IBU's. However, with a difference of less than 1 across the board there isn't anything to be concerned about.

All told, nothing to be surprised or concerned about which is good I'm certainly not surprised as it's just math and I've cross referenced my numbers with a few different programs.

Thanks for the visual as it may help people be comfortable with using the spreadsheet. I'm in the middle of adding a kegging calculator/how to that should be a nice addition for the future as well.

cp

The Main reason I tried this was to see if I could find where the missing Macros in the Mac version were, but as of yet, no luck. They might be on a different sheet. I still need to look deeper at the mash / sparge water numbers and see how they compare to each other.


The main idea is to show that your work is outstanding, and to let people see that it gets the same numbers as other programs. I did not post it, but the numbers were very close with the free online version of Beer Tools as well.

Thanks for all the hard work that you do with this project.

Tim

Thanks again Tim, really appreciate it. If you have any other thoughts/ideas or questions please don't hesitate to let me know.

I think it's a great step in the right direction but you could make it a bit more automated with some simple formulas and macros. It will also condense down the main sheet and put the info in subsequent sheets to keep it orderly.

What kind of automation are you thinking about?

I had thought about having drop downs for malts, hops, and yeast but decided against it. I like having them all in front of me when building a recipe with info on all of them as well. Is that what you were thinking about or something else?

You can do drop downs or use Vlookups. You simply move the all the malt info into a new sheet and link it back over.

I thought about that as well. The problem is that it just adds a tab and moves all the information on the ingredients to build a recipe to that new tab. I don't want to go back and forth between tabs to do that. You already are moving between the bjcp guidelines tab and the first page. Incorporating vlookups just adds another tab to use while doing that. I also have quite a few tabs already (water, yeast pitch, color, bottle priming, bjcp guides, cost analysis, and designing beers) and have plans for more. Want to save the space for those additional tabs if possible.

I really thank you for taking the time to look and offer suggestions. If I can make it better/more functional I certainly want to do so. If you think I'm misunderstanding what you mean please let me know.

CP. it needs a hot girl to enter in all the numbers, can you add that plz, it will increase my "functionality"

Tim

I can't wait to try this out. If you couple this with Dropbox you can have access to your recipes and brewing "software" anywhere you can get Internet access. I have a brewing folder on Dropbox so everything brewing related is sync'd on all of my computers and my iPhone.

Here is my referral link. Mods: I'm not sure if it's cool if I post my referral link so edit it out if you wish.

http://db.tt/hYZ2uFu

I've never had to determine my boil size before. Am I missing it or do I have to run outside calculations to determine the appropriate boil volume?

Boil volume is really dependent upon your set up. I've seen people use anywhere from 10 - 20% boil off rate. The more surface area your kettle has the higher your boil off rate will be typically.

For example, I used to have a slim/tall kettle and I used to plan on about a gallon per hour. Just bought a new kettle which is fatter and my first batch with it I boiled off about 1.5 gallons.

MX1 said:

CP. it needs a hot girl to enter in all the numbers, can you add that plz, it will increase my "functionality"

Tim

Click to expand...

that's a great idea.

CPooley4 said:

Boil volume is really dependent upon your set up. I've seen people use anywhere from 10 - 20% boil off rate. The more surface area your kettle has the higher your boil off rate will be typically.

For example, I used to have a slim/tall kettle and I used to plan on about a gallon per hour. Just bought a new kettle which is fatter and my first batch with it I boiled off about 1.5 gallons.

Click to expand...

I would suggest you allow the user to enter the boil-off rate and calculate the boil volume in the spreadsheet. That is how most brewing software packages operate. Pre-fill at the standard boil-off around 10% and let the user adjust for their equipment.