As stated in some of my previous posts, I have been having issues with my efficiency. I had found through an experiment that I am getting 82% efficiency in my Mash Tun (MLT), and that my falling efficiency is in my losses to hops and kettle. I took the information from my system and essentially built my own brewing software in Excel.
First I calculated my system volumes. I can input the desired volume I want in my fermenter, and it will account for hop losses (from where I input the hop additions), keggle losses, and boil off (based on length of boil), to calculate the pre-boil volume. It will also calculate the volume of sparge water to add based on 1.5qts/lb (water-to-grist ratio), and then for absorption losses giving me the needed amount of sparge water as well. I also configured the spread sheet to calculate the strike water temperature based on the input mash temp, and the total volume used in the MLT to ensure that I don't overflow it.
Next I set up my recipe calculations. I input the grain type, weight, lovibond (color), and the PPG (points-per-gallon: the maximum gravity points that can be obtained from 1# of that grain in 1 gallon of water @ 100% efficiency). With this information, the spread sheet will calculate SRM color (thanks to Beer Smith Blog for the calculations), OG, and percentage of each grain based on contribution to gravity as opposed to percentage of weight. The OG is based, not on final fermenter volume, but on post boil volume so that no matter how many hops there are, or how much is left behind in the kettle, my OG will remain the same, based on my extract efficiency instead of brew house.
The next step in my recipe calculations is IBUs. I configured the spread sheet to figure my IBUs based on the AA% (alpha acids) of the hop added, the weight in ounces, and the time in the boil, and factoring for gravity. Big thanks to cmac1075 on BeerAdvocate for allowing me to use his calculations in my spread sheet. I also set it up to factor for the real OG and volumes based on the actual brewday as opposed to the recipe so I can know what my real IBUs are for the batch (although I have no real way to know my IBUs w/o having my beer tested.) The hop formulation also effects the OG since as the amount of hops goes up, so does the post-boil volume (to account for hop absorption), thus lowering the OG. This is easily adjustable since another 1/2# of grain should easily fix the lost gravity points.
I also infused the Bru'nWater spread sheet into my spread sheet which allows me to change my water profile based on the recipe I am creating. A few clicks and "=" and it now autofills the grains, weights, colors, etc, and then I can adjust my water. Getting all of this to work was a little tricky since Bru'nWater is owned by someone else, so I couldn't just hit "=" on there formulated spaces to make them appear in another spot on my spread sheet, so I had to use some of their formulas to move the outcomes, but it worked. I was able to take the outcomes which are in grams to the tenth (which I can't accurately measure) and use the info from John Palmer to convert it to teaspoons. With some more help from a few more BA's I was able to calculate the sodium content in my home brew (Dr said I have to cut back on salt), and another BA told me that I can sub Pickling Lime for Baking Soda to buffer for mash pH and that will cut out the level of sodium I add to my brews.
Next I set up a "cover sheet", essentially it takes all the information compiled in all the other spread sheets and puts it in one, easy to read area that can be printed out and used to buy my ingredients or put into my brewing log book. This page lists beer name, style, batch #, brew and bottle dates, grains/fermentables, hops, strike water volume and temp w/ mash temp, mash adjustment additives in tsp., sparge volume, temp, and additives, kettle additions like Whirfloc, spices, etc, amounts, and times, yeast used, fermentation temp and length, sodium levels, calories (another Beer Smith steal), estimated FG (based on attenuation of yeast), estimated ABV (based on estimated (OG and FG), and even estimated cost. It also has a place for the OG and volumes of the actual batch, from which it calculates efficiency and brew house, and recalculates the IBUs based off of the actual numbers; then post fermentation, will calculate the real ABV based on the actual FG.
It was a major undertaking with lots of hours moving, and removing, formulating, and reformulating, scouring forums and websites, and then reconstructing again, but now, I can input my grains, hops, and yeast (all w/ accompanying information), length of boil, mash temp, and desired fermenter volume, and it will tell me strike water temp and volume, sparge water temp and volume, IBUs, SRM, OG, est FG, and est ABV. A few more clicks to adjust my water, and it tells me how many teaspoons of each addition to make.
