The Brew Tool - Homebrewed spreadsheet

[ODI3]

Hi Everyone,

I would like to introduce my homemade brewing tool. If you have a moment, check it out!

https://drive.google.com/file/d/0Bx_nygQJGds3QTdCeVVqVDNCUGVIczNmUDd3RVltcXJnTmE0/edit?usp=sharing

Edit: The Downloaded file contains a example batch I created as well as information in the brew Time table. Feel free to clear the brew time table or update it with more accurate information as you brew new batches.

Overview:

The Brew Tool is an excel spreadsheet that works in reverse when compared to all other brewing software. Rather than inputting batch details and the software showing you the big picture, you input the big picture and the software will calculate for you the batch details. The software uses grain percentages, overall brewhouse effeciency and planned OG and FG to calculate, rather than the conventional grainbill, mash efficency and % losses of each process approach.

Basically you make a brew plan, you brew, you then have brew results. you review your plan and results to make a more refined plan for next time. (This works best with electric systems since the boil off rate is standardized and the amount of boil off liquid is easier to predict, but if boil rate is measured each batch, the idea should translate to propane or NG systems too).

The main brew page consists of the following sections:

Brew Info - Stores batch Information for each batch - optional, for reference only
Yeast - Stores yeast characteristics - optional, for reference only
BJCP Style - Displays BJCP Styles - optional, for reference only
Notes - Stores Notes information - optional, for reference only
SRM Color Map - Reference for final beer color based on width of glass and SRM - for reference only
Glassware - Displays widths for common glassware - for reference only
Boil Time Table - Quick reference location to save batch efficency information for future batches


Brew Plan - Used to design brew recipe and predicts outcome based on inputs. - Must be filled out
Fermentables - Used to select fermentables in recipe - Must be filled out
Hops - Used to select hops in recipe - Must be filled out



How to get started - Create a Brew Plan

1) Enable macros at the top of the page.
2) Select a blank recipe # in the brew log history Section. - A blank recipe sheet will load.
3) Design a brew plan:
The minimum requirements are to fill in the following fields:
Gallons in Fermenter
Fermenter OG
Fermenter FG
Brewhouse Efficency
Fermentables and % (all of the fermentables must total 100%)
Hops, AA and Boil Time

In future brews, you will use the boil Table information to populate the Brewhouse Efficency. but since this is your first batch and the table is blank you will have to guess at your brewhouse efficency. If your not sure, the following settings are a good starting point and will give you a 5% beer plan.

Brewhouse Efficency - 75%
Brewhouse OG - 1.050
Brewhouse FG - 1.012

The rest of the information in the brew plan as well as the yeast and style sections are just for current and future reference and are not required for the software to function or effect calculations.

Once your plan has been created, the yellow fields in the brew plan will forecast your %ABV, IBU, SRM. and %attenuation. At this point, modify the OG and hop schedule if the forecasted ABV and IBU are not to your liking.


Brewing Your batch - Brew Log

In future brews, you will use the strike water volume from the Boil time table to accurately guess the strike water volume. Unfortunately, since the table is blank for your first batch, you will have to guess. A reasonable starting point for strike water is 1.6x your final batch size. (8 gal of strike water for 5 gal batch, and 16 gal of strike water for a 10 gal batch)

As you brew your batch, fill in the green fields in the brew log section. When you have completed brewing, compare your brew log and brew plan information to see how close your result was to your original plan. Then, update/refine the Boil Time table so you will be able to make a better educated guess on your strike water volume and Brewhouse Efficency in future batches. After only a few batches you should be able to perfectly plan your batches and have them match your results.

Finally, click the Save Log button. All of your brewing information will be saved based on the RED batch number. To load previous information, just click on a batch number in the Brew Log table.



If you have any questions or comments, please let me know.

Happy Brewing!

-Ryan

 

[ODI3]

Additional Features:

SRM Chart:

This chart is to give you insight of what your beer will look like in a glass based on the beer SRM. For example, my demo beer has an SRM of 3, if i am viewing the beer in a 16 OZ Ale glass, I would check the chart for 8cm(average width of the beer glass) and 3 SRM. I should expect a light brownish color. I added this chart because I found myself often underestimating the color of SRM. I would expect a light brown beer and get black.

Yeast Table:

This section section is to display what yeast you used and display the characteristics of that yeast. Flocculation, min/max temp etc. When you load or save your sheet, the yeast selection will be load or stored. If you want to add extra yeast information such as volume, age, generation of yeast, I would add it to the notes section.

BJCP Styles Table:

If you are submitting your beer in a competition, this table will give you a quick reference on BJCP style guidelines. the selection you make will load/save in with the rest of the brew info. Unfortunately I have not yet updated it for the 2014 information.


Recipes Yeast Hops Grains Styles Worksheets:

These hold the information background information for the main brewsheet. Feel free to update or change the information as your see fit. If you insert rows into the middle of any of the backend sheets you should be ok. If you add rows to the bottom, u will need to update the rowsource variables for the software to work properly. If you have problems with cost variables not loading, you will need to add pricing per 55lb bag of grain or lb of hop in the hops and grain sheets.

 

[ODI3]

Also, I want to give credit and thank Nilo who made, and allowed me to modify this sheet's original version. It can be found here:

beertech.blogspot.ca/2012/12/nilos-brewing-tool.html

 

[BenS]

I just discovered your spreadsheet while researching for my own excel based brewing program. Yours is very well done. I am really enjoying it.