jagomart
digital resources
picture1_Spreadsheet Calculator 46101 | Hi Pa Beta4


 152x       Filetype XLSX       File size 0.52 MB       Source: beernbbqbylarry.com


File: Spreadsheet Calculator 46101 | Hi Pa Beta4
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 ...

icon picture XLSX Filetype Excel XLSX | Posted on 17 Aug 2022 | 3 years ago
Partial file snippet.
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.

Sheet 2: Recipe Sheet
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


Sheet 3: Brewhouse Setup & Calcs

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








The words contained in this file might help you see if this file matches what you are looking for:

...Sheet instructions how to use this recipe calculator template watch tutorials and related what s new videos on my youtube channel brewing playlist download excel file from httpsbeernbbqbylarrycom save it your computer min requirement for designing recipes preferred or libreoffice viewing but google sheets ios numbers have worked well enough rename the name of add equipment profiles configure brewhouse setup amp cacls tab with process environment variables go grain sugar calcs specifiy desired types amounts grains sugars hops specify water specific chemistry additions if necessarydesired select yeast strain other ingredients as necessary enter miscellaneous information print tablet phone laptop view brew day works best higher you find spreadsheet helpful please consider supporting work by donating via paypal donation beernbbq larry version history lt various updates not tracked revamped metric units ability pulldown cascading menus data validation in important cells lists grainsugarhops...

no reviews yet
Please Login to review.