Category Archives: Spreadsheets

Homebrewing Spreadsheets – Brewing Spreadsheet, Brew in a Bag and Small Batch

homebrew brew in a bag biab spreadsheet

Homebrew recipe and brew day software can be handy.  Something like BeerSmith is invaluable for creating recipes, figuring SRM, figuring IBUs, fitting beers into established styles and lots more.  Some are BeerSmith power users and track every recipe and every brew day in this great software.

Sometimes you just want to brew.  It’s recipe that you’ve brewed before, something you’re putting together on the fly, or you just want something quick and easy to tell you temperatures and volumes.  Even though I have and use recipe software, I just find it easier to use brew day spreadsheets to track each individual beer.  I’ve developed three that I use in different situations.

The formulas in these spreadsheets are tried and true.  I’ve used them myself for years.  Hundreds, maybe thousands of homebrewers have used them too.  I’ve taken feedback and tweaked and corrected things over time.

  • My Brewing Spreadsheet – written from the batch sparge perspective, although variable can be modified to work with fly sparging.  Figures water volumes, strike water temperatures, projects volumes, helps you correct gravities and more.  If you’re batch sparging, your brew day is presented in narrative step by step directions.  A one page printed brew day summary is also created from your recipe letting you know what to do at each step and giving you something to write notes on.
  • BIAB Brew Day Spreadsheet – similar to my original beer making spreadsheet but tweaked for Brew in a Bag/BIAB style brewing.  This spreadsheet has an anytime gravity estimate and correction calculator to help you project and correction gravities.  Instead of a one page summary, the entire spreadsheet is setup to print out on a single  8 1/2 by 11 piece of paper.  You still get narrative step by step directions.
  • Small Batch BIAB Spreadsheet – Brew in a Bag, but this time a simplified version for small batch BIAB brewers.  This is designed to make homebrewing easy and the post contains a step by step how to for brewing small batch all grain homebrews on your stovetop.

This is a Top Post! See: All Top Posts

Pinned: Mash & Boil | Pin Locks | Temp Controller | BIAB | Dry Yeast | Giveaway!

Recent Great Deals [view more]:

toppost:homebrewspreadsheets

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:

This is a Top Post! See: All Top Posts

Pinned: Mash & Boil | Pin Locks | Temp Controller | BIAB | Dry Yeast | Giveaway!

Recent Great Deals [view more]:

toppost:biabspreadsheet

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

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