AG Extract Potential Calculator

[Ryat66]

I have relied on BeerSmith since I started brewing but recently decided to sit down and understand the math behind recipes. With some help from Google and How to Brew, I think I have a decent grasp on things. So, I decided to create an Excel spreadsheet and build the formulas myself to better grasp how variables affect the entire brewing process. This isn't meant to replace my BeerSmith but rather to understand what's going on behind the scenes.

I have provided a link to the file on OneDrive and was wondering if someone would kindly take a look at it to verify my formulas. The green cells require user input, everything else is formula driven output. Thank you in advance.

https://1drv.ms/x/s!AmNqNFPb0OdxqnXhdLhUlwfTaacl

 

[McKnuckle]

First, you've got the ppg concept down - that's good!

A couple of things stand out:

The conversion efficiency must be calculated against the TOTAL water you use in the very beginning; that is, the mash and sparge water combined. It's not the "batch size" - which really is the amount that hits your fermenter in the end. I realize there is some information online that seems to contradict this.

Maximum Potential:
Total Gravity Points/Total Vol
expressed as the points after the SG decimal; i.e. 61 for 1.061

Conversion Efficiency:
Pre-Boil SG/Maximum Potential SG
Or, Pre-Boil SG = Maximum Potential SG * Conversion Efficiency
Again, use only the points after the decimal as a whole number

The mash efficiency has a simple formula once you know conversion efficiency. Just factor in the volume losses:

Mash Efficiency:
Pre-Boil Vol/Total Vol * Conversion Efficiency

This takes the volume loss from the mash/lauter and the sugar into account. The brewhouse efficiency, then, is just one more step, where you take post-boil volume losses into account (except for boil-off, which is NOT counted against efficiency):

Brewhouse Efficiency:
(Fermenter Vol + Boil-off) / Total Vol * Conversion Efficiency

So you can see that conversion efficiency locks in the extent to which extracted sugar affects efficiency. It ain't getting any better after that initial point, and you can only lose. Any further efficiency losses are due to volume alone.

All volumes in these calculations should be room temperature values. Which leads me to one more thing: The post-boil 4% cooling factor does not require a gravity adjustment; or at least, doing so isn't useful. The 4% expansion (it's not really shrinkage) is related only to volume. So if you end up with 5 gallons at 212ºF after the boil, divide that by 1.04 and you'll get the room temperature equivalent which will end up in your fermenter (4.81 gal). Use the room temperature, final cooled volume to measure your OG.