152x Filetype XLSX File size 0.52 MB Source: beernbbqbylarry.com
Sheet 1: Instructions
How To Use This Recipe Calculator Template | |
1. Watch Recipe Calculator Tutorials and related What's New videos on my YouTube Channel | |
Brewing Recipe Template Playlist | |
2. Download this Excel file from https://beernbbqbylarry.com and save it to your computer. | |
Min Requirement for designing recipes: Excel 2010 (2016 preferred) or LibreOffice | |
Min Requirement for viewing recipes: Excel 2010 (2016 preferred) or LibreOffice but Google Sheets and iOS Numbers have worked well enough. | |
3. Rename the Excel file to the name of your recipe. | |
4. Add your equipment profiles. | |
5. Configure the Brewhouse Setup & Cacls tab with your equipment, process, & environment variables. | |
6. Go to Grain & Sugar Calcs tab to specifiy your desired types and amounts of grains and sugars. | |
7. Go to Hops Calcs tab to specify types and amounts of hops. | |
8. Go to Water Calcs tab to specific water chemistry additions if necessary/desired. | |
9. Go to Recipe Sheet tab to select yeast strain, add other ingredients as necessary, and enter other miscellaneous information. | |
10. Save, print (or use tablet, phone, or laptop to view), and use on brew day. Works best with Excel 2010 or higher | |
11. If you find this spreadsheet and related videos helpful, please consider supporting my work by donating via PayPal: | |
PayPal Donation for BEER-N-BBQ by Larry | |
Version History: | |
<2.0 Various updates not tracked. | |
4/13/2017 | 2.0: Revamped to add Metric units ability, pulldown and cascading menus, data validation in important cells, lists for grain/sugar/hops/yeast ingredients, combined the Mash Calcs tab with the Brewhouse tab, predict FG, calculate actual IBUs compared to predicted, AAU columns, expected and actual attenuation, expected ABV, improved descriptions of some parameters and other settings, improved readability, and added a refractometer section. Updated for Office 2010. (some functions do not work on Office 2007 or earlier.) Added BJCP category and style. |
4/15/2017 | 2.0.1 - Fixed issue with first row of Grain List (acidulated malt) causing #NA errors. Added empty first row as workaround. |
4/20/2017 | 2.0.2 - Changed website information |
4/25/2017 | 2.0.3 - Added many missing Yeast Attenuation values and some new yeast strains. Added Best Malz Red X grain. |
4/29/2017 | 2.0.4 - Added more Fermentis yeast information. |
7/15/2017 | 2.1 - Adjusted calcs for splitting sparge steps. Update yeast fermentation range to display current temperature units, fixed lallemand yeast temp ranges. Added manual input for grain absorption ration. Rearranged Brewhouse Setup & Calcs tab. Adjusted Brix FG formula per www.seanterrill.com and added an adjustable Wort Correction Factor to the brewhouse set up tab. Corrected error in Strike Water Temp calc for Metric units. |
9/5/2017 | 2.2 - Added SRM calculation to estimate color. - Updated Grain List and recalculated PPGs with corrected values from malt analysis sheets. (Apparently the grain list that I copied from somewhere online had incorrect or very generic PPG values for many grains. Using the formula at the previous link and using real malt analysis sheets from the malter's websites, I have updated the PPG values for the grains. They should now be more accurate.) NOTE: This may change your calculated extract efficiency from previous batches, so beware and be careful. Mine changed from 72% to 80% which makes me feel a whole lot better about my process. - Added keg carbonation section to Recipe Sheet.Renamed 'Priming' tab to 'Carbonation' tab to account for forced carbonation. - Added new grains/malts. - Added new Malt Sheet PPG Calcs Tab to help determine PG from malt analysis sheets. |
11/9/2017 | 2.2.1 - Fixed value in Hops tab for U calc when determining U from a row tagged as 'FWH'. No effect if on the first row where it is used. - Added 2-row Brewer's malt (Rahr) on Grain & Sugar List - Added sucrose formula to Common Variables tab - Added secondary US Gallon fields. - Reformated Brewhouse Setup & Calcs to be more in order of actual brew day steps. Added Total Water Req'd calculation. - Added Imperial yeast strains. - Added Rahr 6-row malt. - Added Hop Stand and 2 min as options on Hop Calcs tab. NOTE: No IBUs for Hop Stands are calculated; only the boil additions. Hope to add hop stand IBU calcs in a future update. - Added rice hulls to grain list. |
3/30/2018 | 2.2.2 - Fixed missing selection of first 5 rows in Grain List. - Estimated Weyermann Acidulated Malt extract value from other acidulated malt value due to lack of information from Weyermann. |
6/16/2018 | 2.2.3 - Added Avangard Malz Pilsner Malt - Updated Carbonation tab. |
10/13/2018 | 2.2.4 - Defaults for my latest brewhouse setup with 70 quart mash tun |
12/1/2018 | 2.2.5 - Added 'FALSE' option to VLOOKUP on 'Grain & Sugar Calcs' tab for Max PPG column to ensure an exact value return. Credit for discovery and fix goes to Richard Fex. - Added 'FALSE' option to VLOOKUP on 'Grain & Sugar Calcs' tab for Color column to ensure an exact value return. Credit for discovery and fix goes to Richard Fex. |
1/13/2019 | 3.0 - Rearranged Recipe Sheet tab. - Added Hydrometer Testing & Correction tab. - Added Hydrometer Offset and Temperature correction to Brewhouse Setup and Recipe Sheet tabs. - Added choice of SG tool to use (hydrometer or refractometer) to calculate outputs. - Added wort loss due to hop absorption calculation. - Added Water Source field to Recipe Sheet Tab. - Added Estimated pH and Measured pH fields to Recipe Sheet tab. - Added EZ Water Calculator tabs (one for English and one for Metric). - Changed Boil-Off Rate from percentage of batch size to real number per hour. - Added field for Fermenter Volume. - Embedded my previously separate 'Draft Line System Balancing' spreadsheet as 'Beer Line Length' tab. - Add automatic update of calculated strike water temperature on Recipe Sheet if measured ambient grain temperature is different than originally estimated. This avoids having to update the brewhouse setup tab to generate a new strike water temp value. - Mash tun and Boil kettle size fields in brewhouse tab also turn red if capacity is exceeded. - Now have a choice of Batch, Fly, BIAB, or Extract methods that also change the related sparging section on the Recipe Sheet. - 1st attempt to account for IBUs from Hop Stands via a fudge factor related to time. - Added more popup explanations for cells. - Added CTZ hops - Updated/Fixed actual Extract Efficiency to account for, and remove, any SG related sugar additions so that only the mashed grains are included. - Added Brewhouse Efficiency calculation for both as-designed and as-measured scenarios. This value is for informational purposes only and not used elsewhere in the spreadsheet. (Use Extract Efficiency instead.) - Corrected SRM color estimate. Thanks to Ryan Philpot for discovering and pointing out the math error. - Added Gladfield grains to grains list. |
1/15/2019 | 3.0.1 - Unlocked protected cells for Distilled/RO water on EZ water tabs. |
1/16/2019 | 3.0.2 - Unlocked more protected cells on EX Water Metric tab. |
1/17/2019 | 3.0.3 - More locked cells requiring unlock. |
1/29/2019 | 3.0.4 - Updated PPG values for some sugars |
2/6/2019 | 3.0.5 - Added Omega Yeasts to Yeast List |
2/26/2019 | 3.0.6 - Fixed VLOOKUP error in Yeast field on Recipe tab. |
3/19/2019 | 3.0.7 - Added/Updated Lallemand yeasts. Reset filter on grain list. Expanded notes section. |
4/9/2019 | 3.1 - Rearranged formulas to calculate some losses. - Add Mash Time and Boil-Off Rate entries. - Added Predicted Brix fields to recipe sheet. - Separated Pre-Boil SG contributions from grains and sugars on recipe sheet for recipes that use a cominbation of both. - Added actual Grain Absorption Rate to recipe sheet to compare with assumed rate. - Added actual Hop Absorption Rate to recipe sheet to compare with assumed rate. - Greyed out unnecessary actual Total Water and Sparge Water fields on recipe sheet. Has caused some confusion for some users. |
7/7/2019 | 3.1.1 - Fixed Actual Grain Absorption Rate Calculation on Recipe Sheet to account for Fly Sparging. - Fixed inaccurate FG calcucation from refractometer reading. |
11/30/2019 | 3.1.2 - Fixed incorrect reporting of actual extract efficiency for recipes with both grains and sugars added. |
1/2/2020 | 3.2 - Updated to work with LibreOffice for Linux. |
1/6/2020 | 3.2.1 - Added more grain and hop varieties. |
1/14/2020 | 3.3 - Added Diastatic Power information and calculations. |
1/19/2020 | 3.3.1 - Added several newer White Labs yeast strains. |
3/9/2020 | 3.3.2 - Added Rahr Pilsner Malt to grain list. |
3/17/2020 | 3.4 - Added better support for BIAB. - Clarified valid fields to use for various brew methods via conditional formatting on Recipe Sheet. |
4/12/2020 | 3.5 - Added BJCP 2015 Style Guidelines Reconfigured Total & Strike Water Volume calculation in support of no sparge brewing. |
8/25/2020 | 3.6 - More accurately account for volumetric changes in boil kettle due to temperature changes at various points in the process. - Added fields for entering in temperatures of measured volumes which are used in adjusting volumes based on the temperature change to align with predicted values. - Added two more rows for the hops bill. -Fixed bug in Diastatic Power calcs. |
1/2/2021 | 3.7 - Fixed boundary condition error for batch sparging steps of larger grain bills under certain conditions. - Fixed calcs for sparge and total water volumes by adding lauter tun dead space loss. |
1/11/2021 | 3.7.1 - Added some addition informational text in regards to volumes to clarify the temperature for each volume calculated. - NOTE to self: Still want to add the expected and real measured temperature for total water requred field on recipe tab. |
1/31/2021 | 4.0 beta 1 - Major Updates: - Recalculations around volumes and volume thermal expansions/contractions at every step of the process. - Added Equipment Profiles. - Added calorie counter. - Added Step Mash and infusion calculations. - Fixed issues with water tab entries for grain ph. - Added mash evaporation rate. - Corrected boil kettle deadspace loss for hops in and out of kettle. - Added another hops row. - Display lactic acid addition on recipe sheet. - Accommodates 2 vessel systems like the BrewEasy. |
2/13/2021 | 4.0 beta 2: Fixed esitmated fly sparge time on recipe sheet - Fixed Actual Extract Efficiency calcs on recipe sheet. - Update default hop absorption rate value. |
2/23/2021 | 4.0 beta 3: added grain absorption rate calc. |
Style: | 21A. American IPA | Brewer: | Beer 'N BBQ by Larry | Brew System: | 20 Gal Spike Kettle, Coleman 72 qt Cooler, Elect, 66% Power, Pump | |||||||||||||||||||||
Brew Date: | March 20, 2021 | Fermenter: | FermZilla All-Rounder 30L, Spike Flex+ | |||||||||||||||||||||||
Grain Bill, Adjuncts, & Sugars | Recipe Outputs | BJCP Guide | Water Information | Brewing Process | ||||||||||||||||||||||
Type | Qty | % of Bill | Design | Actual | Min | Max | Chicago (Lake Michigan) | Mash Schedule & Water Infusions | ||||||||||||||||||
lb | Extract Eff.: | 75.0% | 75.0% | ppm (mg/L) |
Orig. | Adj. | Step # | T1 | T2 | Time | Infusion | Actual | ||||||||||||||
Pale Malt: Brewers Malt, 2-row (Briess) | 20.00 | 74.1% | Brewhouse Eff: | 64.3% | 67.4% | °F | °F | min | °F | qt | qt | deg | ||||||||||||||
Munich Malt: Dark Munich Malt (Avangard Malz) | 5.00 | 18.5% | O.G.: | 1.059 | 1.059 | 1.056 | 1.070 | Calcium | 37 | 118 | 1 | 68 | 148 | 40 | 164 | 27.0 | 27.0 | 147 | ||||||||
Vienna Malt: Goldpils® Vienna Malt (Briess) | 2.00 | 7.4% | F.G.: | 1.011 | 1.008 | 1.008 | 1.014 | Magnesium | 12 | 12 | 2 | 148 | 158 | 20 | 179 | 15.4 | 18.0 | 153 | ||||||||
Ferm Vol: | 44.0 | 46.0 | Sodium | 9 | 9 | 3 | ||||||||||||||||||||
Batch Vol: | 40.0 | 40.0 | Choride | 16 | 100 | 4 | ||||||||||||||||||||
App Atten: | 81.0% | 87.0% | Sulfate | 25 | 108 | 5 | ||||||||||||||||||||
IBUs: | 41 | 42 | 40 | 70 | Alkalinity | 102 | Name | Design | Actual | Units | ||||||||||||||||
ABV: | 6.3% | 6.7% | 5.5% | 7.5% | Cl/SO4 Ratio | 0.64 | 0.92 | Mash | pH | 5.5 | 5.5 | |||||||||||||||
SRM: | 6.9 | 7.1 | 6.0 | 14.0 | Batch Sparge | Water | 29.8 | 27.3 | qt | |||||||||||||||||
Calories per US Pint: | 271 | Water Additions (grams) | Temp | 158.0 | 164.0 | °F | ||||||||||||||||||||
Mash | Sparge | Add | 1.2 | 0.0 | qt | |||||||||||||||||||||
Hop Bill & Schedule | Gypsum | 6.0 | 0.0 | Drain | 28.6 | 30.0 | qt | |||||||||||||||||||
Species | Type | Alpha (%) | Qty | AAU | Time (min) | Cal Chloride | 7.0 | 0.0 | Add & Drain | 28.6 | qt | |||||||||||||||
oz | Epson Salt | 0.0 | 0.0 | Pre-Boil | Volume | 57.3 | 62.0 | qt | ||||||||||||||||||
Centennial | Pellet | 10.0 | 1.00 | 10.0 | FWH | Slaked Lime | 0.0 | 0.0 | Temp | 158.0 | 140.0 | °F | ||||||||||||||
Centennial | Pellet | 10.0 | 2.00 | 20.0 | 30 | Baking Soda | 0.0 | 0.0 | Grain Abs Rate | 0.50 | 0.33 | qt/lb | ||||||||||||||
Centennial | Pellet | 10.0 | 1.00 | 10.0 | 5 | Support my work. Send me a tip: | Chalk | 0.0 | 0.0 | Grains Only | Hydro. | 1.053 | 1.047 | Temp | ||||||||||||
Centennial | Pellet | 10.0 | 2.00 | 20.0 | Dry Hop | Lactic Acid | 4 | mL | Corr SG | 1.049 | 67.7 | |||||||||||||||
Refracto. | 13.1 | ° Brix | ||||||||||||||||||||||||
Yeast Information | Corr SG | |||||||||||||||||||||||||
https://beernbbqbylarry.com/donate/ | Fermentis | Grain + Sugar | Hydro. | 1.053 | 1.047 | Temp | ||||||||||||||||||||
SafAle US-05 | Corr SG | 1.049 | 67.7 | |||||||||||||||||||||||
Ferm Temp: | 64-82 | °F | Refracto. | 13.1 | ° Brix | |||||||||||||||||||||
Corr SG | ||||||||||||||||||||||||||
Forced Carbonation | Extract Efficiency (Grains Only) |
75% | 75.0% | Hydro. | ||||||||||||||||||||||
CO2 Volume | 2.5 | Refrac | ||||||||||||||||||||||||
Temperature | 38.0 | °F | Boil | Starting Vol | 58.4 | 62.0 | qt | |||||||||||||||||||
Other Ingredients/Additions | Amount | When | Pressure | 11.2 | PSI | Time | 90 | 100 | min | |||||||||||||||||
Whirlfloc | 2 | 15 min | End Vol | w/ IC | 53.1 | 51.0 | qt | |||||||||||||||||||
Cannabis, decarboxylated | 1.5 oz | Dry Hop | No IC | 50.9 | 49.5 | qt | ||||||||||||||||||||
dry yeast | 2 pkg | pitch | Fermenter | Volume | 44.0 | 46.0 | qt | |||||||||||||||||||
Temp | 75.0 | 75.0 | °F | |||||||||||||||||||||||
Original Gravity | Hydrometer | 1.059 | 1.059 | Temp | ||||||||||||||||||||||
Corr SG | 1.059 | 46.7 | ||||||||||||||||||||||||
NOTES: Cannabis decarbed via sous vide (or is it sous weed?) method. Add dry hops and weed on tail end of fermentation (3-5 days). Keg after another week or so. | Refractometer | 14.6 | ° Brix | |||||||||||||||||||||||
Corr SG | ||||||||||||||||||||||||||
Final Gravity |
Hydrometer | 1.011 | 1.006 | Temp | ||||||||||||||||||||||
Corr SG | 1.008 | 67.0 | ||||||||||||||||||||||||
Refractometer | 2.9 | ° Brix | ||||||||||||||||||||||||
Corr SG | ||||||||||||||||||||||||||
Packaged Beer | 40.0 | 40.0 | qt |
Select | Color Legend | ||||||||||||
Units of Measure: | US Customary | Manual Entry | |||||||||||
Name | Value | Drop Down Selection | |||||||||||
Temperature | °F | For tutorials, go to: | Autofilled | ||||||||||
Volume | qt | beernbbqbylarry.com | Calculation | ||||||||||
Mass (grain) | lb | Something Good | |||||||||||
Mass (hops) | oz | Something Concerning | |||||||||||
Pressure | PSI | Something Bad | |||||||||||
Brew Day Equipment Profiles | |||||||||||||
Brew System: | 20 Gal Spike Kettle, Coleman 72 qt Cooler, Elect, 66% Power, Pump | Capacity Checks | |||||||||||
Chiller: | JaDeD Concentric Hydra w ElectriChair | Mash Tun | PASS | ||||||||||
Fermenter: | FermZilla All-Rounder 30L, Spike Flex+ | Boil Kettle | PASS | ||||||||||
Hydrometer: | Brewing American Precision | Fermenter | PASS | ||||||||||
Process Parameters | |||||||||||||
Inputs | Value | Units | Value | Units | |||||||||
Target Batch Size (Packaged) | 40.0 | qt | 10.00 | US Gal | |||||||||
Environment | Room Temperature | 68.0 | °F | ||||||||||
Water Temperature | 56.0 | °F | |||||||||||
Boil Temperature | 210.6 | °F | |||||||||||
Grain Temperature | 68.0 | °F | |||||||||||
Mash | Liquor-Grist Ratio | 1.00 | qt/lb | 0.25 | USGal/lb | Mash Schedule & Water Infusions | Check | Real Grist Ratio | |||||
Final Temperature | 158 | °F | T1 | T2 | Time | Infusion | 50.06 | ||||||
Time | 60 | min | °F | °F | min | °F | qt | qt | |||||
Lid | ON | 68 | 148 | 40 | 164 | 27.0 | 23.1 | 1.00 | |||||
Evaporation Rate | 0.00 | qt/hr | 148 | 158 | 20 | 179 | 15.4 | 7.6 | 1.57 | ||||
Boil | Time | 90 | min | 0.0 | 7.6 | 1.57 | |||||||
Hop Absorption Rate | 0.30 | qt/oz | 1.20 | USGal/lb | 0.0 | 7.6 | 1.57 | ||||||
Post-Boil | Hops Handling | Loose | 0.0 | 7.6 | 1.57 | ||||||||
Cooled Temperature | 75.0 | °F | 158 | 60 | 42.4 | ||||||||
Strike Water | |||||||||||||
Measuring Tool Calibration | |||||||||||||
Preferred SG Tool | Hydrometer | 29.8 | |||||||||||
Hydrometer Correction | Calibration Temp | 60 | °F | Sparge Water | |||||||||
Correction | 0.001 | ||||||||||||
Refractrometer Correction Factor | 1.04 | ||||||||||||
Equipment Parameters | |||||||||||||
Inputs | Value | Units | Value | Units | |||||||||
Mash / Lauter Tun | Capacity | 65.0 | qt | 16.25 | US Gal | ||||||||
Max Fill | 90% | % | |||||||||||
Dead Space | 1.5 | qt | 0.38 | US Gal | |||||||||
Grain Absorption Rate | 0.50 | qt/lb | 0.13 | USGal/lb | |||||||||
Sparge Method | Batch Sparge | ||||||||||||
- | 0.0 | qt/min | |||||||||||
Extract Efficiency | 75.0% | % | |||||||||||
Boil Kettle | Capacity | 80.0 | qt | 20 | US Gal | ||||||||
Max Fill | 85% | % | |||||||||||
Dead Space | 5.0 | qt | 1.25 | US Gal | |||||||||
Boil-Off Rate | 5.0 | qt/hr | |||||||||||
Chiller | Type | Immersion | |||||||||||
Displacement | 2.2 | qt | 0.55 | US Gal | |||||||||
Fermenter | Capacity | 59.7 | qt | 14.93 | US Gal | ||||||||
Max Fill | 85% | % | |||||||||||
Dead Space | 4.0 | qt | 1.00 | US Gal | |||||||||
Unit Dependent Constants | |||||||||||||
Vol Coeff of Thermal Exp | Units | Slope | Offset | ||||||||||
US Customary | 0.00000205 | -0.00001596 | |||||||||||
Supporting Calculations | |||||||||||||
Name | Value | Units | Value | Units | |||||||||
Packaging | |||||||||||||
@ 68 °F | Packaged Beer | 40.0 | qt | 10.00 | US Gal | ||||||||
75 to 68 °F | Cooling Loss | 0.0 | qt | 0.01 | US Gal | ||||||||
@ 75 °F | Packageable Beer | 40.0 | qt | 10.01 | US Gal | ||||||||
Fermenter | |||||||||||||
@ 75 °F | Dead Space Loss | 4.0 | qt | 1.00 | US Gal | ||||||||
Volume | 44.0 | qt | 11.0 | US Gal | |||||||||
Boil Kettle | |||||||||||||
@ 75 °F | Hop Absorption Loss | 1.8 | qt | 0.45 | US Gal | ||||||||
Dead Space Loss | 3.2 | qt | 0.80 | US Gal | |||||||||
Post-Boil Volume | 49.0 | qt | 12.26 | US Gal | |||||||||
210.6 to 75 °F | Cooling Loss | 1.8 | qt | 0.46 | US Gal | ||||||||
@ 210.6 °F | Post-Boil Volume | 50.9 | qt | 12.72 | US Gal | ||||||||
Boil Off Loss | 7.5 | qt | 1.88 | US Gal | |||||||||
Boil Volume | 58.4 | qt | 14.59 | US Gal | |||||||||
158 to 210.6 °F | Heating Gain | 1.1 | qt | 0.28 | US Gal | ||||||||
Pre-Boil Volume @ 158 °F | 57.3 | qt | 14.32 | US Gal | |||||||||
Mashing | |||||||||||||
Dry Grain | Amount | 27.0 | lb | ||||||||||
Volume | 8.4 | qt | 2.11 | US Gal | |||||||||
@ 158 °F | Grain Absorption Loss | 13.5 | qt | 3.38 | US Gal | ||||||||
Tun Deadspace Loss | 1.5 | qt | 0.38 | US Gal | |||||||||
Evaporation Loss | 0 | qt | 0.00 | US Gal | |||||||||
Total Water Req'd | 72.3 | qt | 18.07 | US Gal | |||||||||
Total Water Req'd @ 56 °F | 70.8 | qt | 17.69 | US Gal | |||||||||
Total Strike Water | Volume | 42.4 | qt | 10.61 | US Gal | ||||||||
Temperature | 158.0 | °F | |||||||||||
Tun Vol w/ | Starting | 50.9 | qt | 12.72 | US Gal | ||||||||
Grain + Water | Ending | 50.9 | qt | 12.72 | US Gal | ||||||||
Lautering | |||||||||||||
Collectible Wort in Mash @ 158 °F | 27.4 | qt | 6.86 | US Gal | |||||||||
158 °F | Sparge Water Req | 29.8 | qt | 7.46 | US Gal | ||||||||
MAX Available Sparge Additions | 4 Top Up Water | 7.6 | qt | 1.91 | US Gal | ||||||||
4 Subseq Water Add' | 35.1 | qt | 8.77 | US Gal | |||||||||
Number of Drain Steps Required | 1.6 | ||||||||||||
Optimize # steps | 2.0 | ||||||||||||
Optimize water vol per step | 28.6 | qt | 7.16 | US Gal | |||||||||
Add/Drain | Add Top Up Water | 1.2 | qt | 0.30 | US Gal | ||||||||
Drain | 28.6 | qt | 7.16 | US Gal | |||||||||
Add & Drain | 28.6 | qt | 7.16 | US Gal | |||||||||
Pre-Boil Volume @ 158 °F | 57.3 | qt | 14.32 | US Gal |
no reviews yet
Please Login to review.