My Recipe Builder Project

I decided that rather than spend money on canned brewing software (not that there’s anything wrong with that), it would be fun to develop a recipe builder on my own. I know my way around Excel, and all of the critical brewing formulas are readily available to anyone who has an internet connection, so I thought, “why not?” About ten months later, here is my creation in its current form. It is far from perfect. The dry hopping and fermentation areas in particular are inefficient in their usage of space, poorly designed, and overall lack the “bang for your buck” that the other areas of the sheet achieve. That said, I don’t tend to dry hop much, so that hasn’t become much of a problem for me. I really like the builder and use it for all of my brews. I generally build the recipe, print it, and take notes on it throughout the brew day, then punch some holes in it and throw it in a binder.

Though it is far from perfect, it works very well for me and I thought it might work for others as well. Also, I am confident that there are other spreadsheet nerds out there who also love home brewing who can take what I’ve done here and improve on it. I like the idea of it being sort of like an open source document in spreadsheet form, rather than a software program. I am quite limited in the programming area. Of course, if any of you programmers out there want to give it a whirl by all means go crazy. If enough people like the builder and get involved it could be a living, breathing recipe builder that evolves over time into something way better than I would do on my own. I have no financial interest in this. So, feel free to download it and check it out. Any feedback is welcome. Suggestions for improvements, improvements you make, criticisms (be nice &#9786, whatever – all are welcome and in fact, encouraged! Though I like and use the builder in its current form, I see this as a beginning. Also, I’m sure there are things I have left out – please ask any questions that come to mind and I’ll be happy to answer them as quickly as I can. I plan on editing the original post as questions come in that highlight missing information, typos, etc.

I would like to acknowledge and thank the following people for making available the brewing formulas I used in the making of this builder, and for their generous gift of homebrewing knowledge and experience through their websites and various forum contributions. I owe you and many others a huge tip ‘o the cap and a thank you. Without your efforts I could not create this builder.

Denny Conn
http://hbd.org/cascade/dennybrew/

John Palmer
http://www.howtobrew.com/intro.html

Glenn Tinseth
http://www.realbeer.com/hops/

Credit also to the Beer Judge Certification Program for the 2008 BJCP Style Guidelines, and BYO.com for their grain, hop, and yeast charts.

Here is a link to the download: https://dl.dropbox.com/u/75330431/Bretzbier_Recipe_Builder.xlsx

Specifics on the builder to follow...

Cheers,

BeerBDrew

A note on this recipe builder: As it is not a program, but rather a file, it must be manually updated for things like changes in the alpha acid content of the hops you purchase, adding new grains or hops you purchase that may not be on the list in the file, etc. These can be updated on their respective tabs. You should have a good working knowledge of Excel in order to use this file successfully over the long term. That said, I do not expect this builder to be for most people, as it does require some maintenance. Not to mention all formulas are exposed, which means you can modify them as you see fit, but also means you can royally screw up the whole thing if you’re not careful!

The first thing you will need to do is change the system dependent variables so that the estimates are as accurate as possible. Your mash tun/lauter tun design will have more or less dead space than does mine, with a likely different capacity too. Your brew kettle may or may not have a different evaporation rate than does mine. Click the link above to visit Denny's site for a good description of my set-up, which is more or less modeled after his (same cooler and MLT set-up, etc.). These variables have an effect on your brew data and therefore, your brew.

To that end, here are the system dependent formulas you will want to address:

1) Though not a formula, but a user input value, cell D19 pre-boil volume should be set to whatever pre-boil volume you always target for your brews on your system. Be aware that, as the builder is set up for batch sparging with equal runoffs, the calculated value in cell E43 will always be equal to half the value in D19.
2) The formula for strike water temp in cell E37 includes an adjustment I made to fit the calculation to my system. The “+5” in the formula compensates for the 5-degree difference I saw in the mash temps during my trial runs with the builder. It is now accurate to within a degree (+/-) for every brew. I recommend setting this adjustment to +1 or less to start, and adjusting from there based on your results.
3) The formula for grain water absorption in cell E39 includes an element that you may need to adjust to fit your system, based on the volume you net on your first runnings. This element is the “0.122” in the formula.

This is how the file looks upon opening. Some navigation:

The sheet is divided into two halves, the left being the working file, and the right beer style descriptions, per the BJCP style guidelines. This area is formula based, so the fields populate based on your style selection, discussed below (2).
1. At the top left, you will see fields for entering the beer’s name, version #, and brew date in cells D2-4.



2. Working counter-clockwise from there, the next area of the sheet is the BJCP style selector. Click on cell C7 (where it shows “style&#8221, and you will see a drop-down menu appear with every BCJP style category, complete with numbering system. This is accurate as of the 2008 published style guidelines.



Notice how the parameters for the selected style now appear in the guidelines area, showing your targets for OG, FG, ABV, IBU, and SRM. Also, the style description area has now populated with text describing the aroma, appearance, flavor, mouthfeel, overall impression, history, comments, ingredients, and examples. You now have all the technical information you need to build your brew to style. I’ve included the description area so that all of the style guidelines are visible in one place, right in your recipe file, so you don’t have to open another window to view it. Also, I think the descriptions are equally as important as the parameters when building a recipe that you hope to be true to style. By no means is this all you need if you want to nail a particular style, but I believe it gives you a solid base with which to begin.

Back to the parameters, in our example above, we have selected style 10A. American Pale Ale. The sheet starts out with 10 pounds of US two row pale malt in the “grist” area of the sheet (directly to the right of the parameters area). We can see that the OG, FG, and ABV parameters are already satisfied for this style, because the font is black. Red font indicates that particular parameter has not yet been satisfied. We will add some crystal malt and hops to finish it off.



Malts and hops are selected from drop-down menus, and you input pounds and ounces. In the grist area, total weight and SRM are calculated automatically, while degrees Lovibond is given based on the malt selection. Immediately to the right of the grist area, each component of grist is calculated as a percentage of the total, which is helpful in duplicating someone else’s recipe or in cloning a beer.

The hop schedule area works in a similar fashion. Selecting a hop variety from a drop-down menu automatically changes the alpha acid % (AA%) to the corresponding value for that variety. You input the amount in ounces and the time in the boil (BT), including first wort hopping (FWH), and the utilization percentage and estimated IBU’s are calculated based on the numbers you input.

Any change made to either the grist or hop schedule area changes the output in the brew data area of the sheet (left side). The first three items are user defined, and are shown in orange font, while the blue font indicates a calculated value. Input your mash efficiency in cell D17, your desired post-boil volume in D18, and the target pre-boil volume in D19. These variables of course affect the estimated extract from your grains, and therefore all the estimates that follow in the area. As these values change, they are “tested” against the style parameters. Once all parameters are satisfied, the style name field in row 7 will fill in light yellow. There is also an SRM scale that runs along the left hand side of the sheet, giving you a visual estimate of the final beer’s color.

The Fermentation area gives you a drop-down menu again to select your yeast strain. Most offerings from White Labs and Wyeast and some dry varieties are listed.



Average attenuation and ideal fermentation temperature range change as you change yeast strains. The average attenuation figure affects the FG in the brew data area as well.

The Mash schedule area works in a similar fashion as the brew data area, with orange variables as inputs, and blue calculated values. This section is set up for batch sparge brewing. Input the target mash temperature and length of mash in minutes, the desired mash thickness (in quarts per pound), the temperature of your crushed grains, and target mash out temp. Based on some of these inputs, the strike water temp is calculated, along with the volumes of water needed for your strike water, pre-runoff infusion water, and sparge water, and finally, the sparge water temp. The grain water absorption calculation is going to change slightly depending on your system. What I like to do is start out at a mash thickness of 1.25, and raise or lower that value (within a reasonable range) until I get manageable strike water and pre-runoff amounts. By that I mean something relatively easy to eyeball when collecting water, as opposed to something like 3.64 gallons, for example.



Thanks for checking out my recipe builder and don't forget to tell me what you think!

Cheers,
BeerBDrew

Thanks for sharing your spreadsheet! After trying out the trial version of beersmith I started looking for some alternatives. Previously I was just filling in an XML file that I had planned on writing some parsing code later for. I think the spreadsheet has a number of advantages -- you can custom the look, equations, etc. I'd like to make this spreadsheet my brew log from now on.

Since I don't have/use Excel anymore (due to cost), I imported the spreadsheet into Google Docs. Google Docs has some nice bonuses: it's free, it's stored centrally, and you can still access the files offline (although at the moment you cannot edit spreadsheets offline). I did have to fix some things that broke in Google Docs (the SRM display, highlighting when certain properties go out of style) and I also updated the ABV equation.

Here's the link to the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AqxSGTWHAO8wdE5wMVBmQjg1clhyX2NsNkd5eTZlWWc&usp=sharing

One thing I'm trying to figure out is the best way to modify the mash section to switch between traditional 3 vessel or BIAB.

I'm with you fellers.

I just started brewing again after a 12 year hiatus, but I found my old excel spreadsheets I used to use.

I used to put them on a 'Velo', kind of a palm pilot type deal to update information, refer to during brewing, etc.

Now I use my android tablet.

Anywho, the point is, I uploaded my old excel spreadsheet into google docs as a spreadsheet. All the tabs and formulas work just fine, and I'm able to store the recipe 'in the cloud'. That means I can enter it on the PC, pull it up on the tablet on the back patio and all is well.