Recipe alteration for accounting for hop loss.

[KyleWolf]

hey everyone.

So, we all know we have to adjust our recipes and water volumes to account for loss due to absorption from hops. I found an excel spreadsheet called "Beer.Math Water calculator" on here a long time ago (I wish I could give credit to the person who designed it, but I can't remember who I got this from). I realized quickly though, that this spreadsheet did not account for loss of wort due to hop loss (meaning if you did a big IPA, you would end up with 4 gal instead of 5). So I began altering and adding to the excel sheet until I think I found a reasonable way to:

a) account for water loss from hops added to both the boil and dry hop
b)account for changes required in the grain bill to retain gravity from the addition of extra water

the one thing this sheet does not do is account for changes required for hops in order to maintain the same IBU that would be required because of changes in hop utilization from changes in both water volumes and malt concentrations.

I am attaching this sheet and I just want to hear people's feedback on what they think of the layout and overall their thoughts on equations and feasibility of utilizing the formulas.

Here is the link.
https://docs.google.com/leaf?id=0By...FjYWQtZWZkYTg1MzIxZDE1&hl=en&authkey=CNvpsPYM

Thanks in advance for any info.
Hope you like it.
Kyle

 

[KyleWolf]

After looking at this again, I realized I didn't really explain how to use this. Once you click on the link, download the excel document, if you save it, you will be able to make changes to the document and look at the equations.

In the upper left, go ahead and enter your variables (mash thickness, grain bill, boil time, hop volumes, etc). This will give you your mash and sparge water volumes as well as what the post-boil volume should be at the upper right.

On the bottom, you can enter in your the amount of your grain bill and even the specifics on each specialty grain. Below that will give you the amount of grain you would need into order to maintain your gravity and still keep your desired volume. The part at the bottom right will let you know what the new mash and sparge schedule is with the increased grains.

I would love you hear if people think this could potentially work. I have used it and it has seemed to work well.
I know it is a bit busy looking, but it really isn't difficult to use.