Category Archives: Spreadsheets

My Brewing Spreadsheet – Water Calculation, Gravity Estimation and Correction & More

The spreadsheet is setup from my perspective, as a batch sparger.  Having said that, almost everything applies equally to a fly sparger with the possible exceptions of the computed mash ratio (more below), planning on a set volume of sparge water and the step by step batch sparge directions.

Brew Day Sheet (click to enlarge):
img_brewday

 This is the main sheet where you will fill in information about your beer and your mash parameters.

Continue reading

BIAB Brew Day Spreadsheet – Water Calculations + Narrative Directions

Brew in a Bag (BIAB) is a quick, easy and economical way to brew all grain batches of beer.  I released my brew day spreadsheet some time ago along with a version of that to be used for small batch beers.  See below for links.  Either of those could be modified to work with BIAB by changing variables,  This version is specifically designed for full size BIAB batches.  Related: Hands on Review: The Brew Bag – Purpose Made BIAB

How do I use brew day spreadsheets?  First, I do use recipe formulation software.  Some of those programs have brew day components.  I’ve just never gotten into using those features.  For better or worse, I use a spreadsheet.  I make a copy and name it using the batch number and beer name and then quickly plug in the basics.  This creates a one sheet printable page that I can use on brew day.  That gives me a single piece of paper with all of my numbers and a spot for brew day notes.  Notes can go back into the spreadsheet for archival.

Brew Day Sheet (click to enlarge):

homebrew brew in a bag biab spreadsheetThis is the main sheet where you will fill in information about your beer and your mash parameters.

Color Coding: Green Cells are information that you can fill in and Blue/Light Gray Cells are calculated.

General Layout and Flow: Start with Beer Name, Brew Date, Batch Number and 1:Beer Info.  Fill in current grain temp under 2:Strike Water Temperature and step by step directions are populated under 3:BIAB Directions.  Constants on the right hand can be set once for your setup and adjusted as needed.  The bottom section of this sheet contains three calculators explained below.

1:Beer Info: Basic information about your beer and mash profile.

 “Reserve (gallons)” B12 Cell: Allows you to set aside a set number of gallons for sparge, dunking, topping off, etc.  This is subtracted from the total strike water value.  It is assumed that you will add or use this at some point.

“Mash Volume – Can I mash it?”: This field estimates total mash volume including grain and water.  This is an estimate.  The cell turns red if the projected volume of your mash exceeds the size of your mash tun.  This is an adaptation of the formula found on the Green Bay Rackers Calculators Page.

2:Strike Water Temp: Fill in the Beer Info section and your Grain’s current Temperature (cell B5) and the spreadsheet calculates your strike water temperature (cell B6).  Note that the temperature will be offset by the “Undershoot Mash Temp” (cell H7).  Read the constants section for more info on that.

Strike Water Volume: Calculates the amount of water you will need in both quarts and gallons.

Volume Needed – Start of Boil and 15 Minutes Remaining: These sections list required water volume at two important times.  The 15 minute calculation attempts to take into account expansion of wort at boiling.  Take a measurement at 15 minutes and use this figure to correct a low volume.

Constants: The constants section has some variables that you can adjust based on your setup.  For example, I’ve found that grain absorption for my crush is right around .11 gallons/lb.  I think that will be close for you but you can tweak it here if you observe something different.

A note on “Undershoot Mash Temp” – This cell allows you to come in under your desired mash temp.  Why would you want to do that?  It’s easier to ramp up a degree or two vs cool down a degree or two.  This number is subtracted from the calculated Strike Water Temperature recommendation (cell B6).

I suggest reviewing the Constants section to make adjustments for your setup.

3: BIAB Directions: These are narrative step-by-step directions that you can follow after you fill in Beer Info, Grain Temp and Constants.  Note that you can use the table from the “Summary Tab” for on the go adjustments to the strike temp referenced in step 1.

Calculators – The bottom three sections of this tab are calculators.  These calculators can pull from cells in the top portion of the tab, but they do not affect 3:BIAB Directions.

Gravity: This is an adaption of Sean Terrill’s Refractometer Calculator (used by permission).  Thanks Sean for your excellent work on this!

Efficiency: This calculation uses your recipe software’s efficiency setting for a particular recipe along with target gravity to figure efficiency.  That means no re-entering fermentables for every batch.  I figure efficiency when going from the mash tun to the boil kettle.  This is a calculator in the sense that it is standalone and has no bearing on other calculations or cells.

Anytime Gravity Estimate and Correction:  Plug in volume, boil time and gravity figures at any time during your brew day and this calculator will suggest volume and gravity corrections.  Accepts either gravity or Brix (if both are present the Brix value is used).  If you are under volume, I would suggest correcting volume and then re-checking gravity.  This calculator makes no attempt to correct volumes or gravities that are too high.

Summary Sheet (click to enlarge):BIAB Water Calculation Spreadsheet Homebrew

This is setup to print on a regular 8 1/2″ x 11″ sheet of paper and gives a rundown of essential brew day tasks and data.  I use this print out on brew day.

Prepare:  This is a simple to do checklist.  You can modify this section as you see fit based on your procedure.

Water Volumes/Gravity:  This projects the volume and gravity you should have at three stages (start of boil, 15 minutes remaining and end of boil).  The last two columns (lb DME/pt and grams) are meant to allow you to easily correct your gravity at those stages.  Each of those amounts should add 1 gravity point to your beer.  Let’s say you’re three points down at the start of the boil.  With the example in this graphic, you would add .42 lbs of DME to correct the gravity of the 6 gallon batch.  Bam… that’s easy!

Hop/Adjunct Schedule: You can choose either grams or ounces.  If you choose ounces it will also be converted to grams.

Strike Temp:  This is a table version of the calculated strike temp found on the brew day sheet.  The initial temperature can be changed.  This changes subsequent values.  If you’re using a summary printout, you can measure the temp of your grain and refer to this chart for the appropriate strike temp.

Log and Notes Sheet (click to enlarge):homebrewing excel spreadsheet

Log: This section is meant to log actions taken on the beer (fermentation temp changes, dry hopping, oak additions, etc).  It calculates the time that has elapsed since brew day, between actions and since the action took place.

Notes: Simple notes section.

Download
This is a Microsoft Excel Spreadsheet.  I would suggest running this through your previous calculation methods to double check that all this makes sense for you and to verify constants.  I don’t want you coming back to me and complaining the your double IPA is a Pale Ale because of me. :)

Homebrew Finds BIAB Spreadsheet

If you have a question or suggestion for the spreadsheet, send me an email.

Related:

Pinned: 25% Off Lavatools | 14% Off GrogTag | Giveaway | PoppetsNew Kegs!

Recent Great Deals:

toppost:biabspreadsheet

Update: My Brew Day Spreadsheet in Excel, Open Office and Google Docs

The spreadsheet is setup from my perspective, as a batch sparger.  Having said that, almost everything applies equally to a fly sparger with the possible exceptions of the computed mash ratio (more below), planning on a set volume of sparge water and the step by step batch sparge directions.

Brew Day Sheet (click to enlarge):
img_brewday

 This is the main sheet where you will fill in information about your beer and your mash parameters.

Continue reading

Small Batch, All Grain Stove Top Brewing + Water Calculation Spreadsheet

I periodically brew small (1-3) gallon all grain batches of beer on my stove top.  Some reasons I do that… 1.  The weather – it’s too cold, too hot or too something else, 2.  Time or 3.  I want to test something.  It’s a fun, simple, quick and economical way to brew all grain with minimal investment.

For the extract brewer, what kind of gear is needed to accomplish this amazing feat?  A comprehensive list follows…

Continue reading

Small Batch, All Grain Stove Top Brewing + Water Calculation Spreadsheet

I periodically brew small (1-3) gallon all grain batches of beer on my stove top.  Some reasons I do that… 1.  The weather – it’s too cold, too hot or too something else, 2.  Time or 3.  I want to test something.  It’s a fun, simple, quick and economical way to brew all grain with minimal investment.

For the extract brewer, what kind of gear is needed to accomplish this amazing feat?  A comprehensive list follows…

Continue reading

Top Post: My Brewing Spreadsheet

The spreadsheet is setup from my perspective, as a batch sparger.  Having said that, almost everything applies equally to a fly sparger with the possible exceptions of the computed mash ratio (more below), planning on a set volume of sparge water and the step by step batch sparge directions.

Brew Day Sheet (click to enlarge):
img_brewday

 This is the main sheet where you will fill in information about your beer and your mash parameters.

Continue reading

Update: My Brewing Spreadsheet

The spreadsheet is setup from my perspective, as a batch sparger.  Having said that, almost everything applies equally to a fly sparger with the possible exceptions of the computed mash ratio (more below), planning on a set volume of sparge water and the step by step batch sparge directions.

Brew Day Sheet (click to enlarge):
img_brewday

 This is the main sheet where you will fill in information about your beer and your mash parameters.

Continue reading