Category Archives: Spreadsheets

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: Niko Hops Sale · Pin Lock Deal · Kegging System · Grain Sale · $24:Drip Tray

How To: Easily Convert Your Keg and Carboy Washer into a Draft Line Pump

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.

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

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

Mash Ratio Cells: Based on your beer parameters, the spreadsheet suggests a mash ratio (cell E12).  That ratio is designed to allow for no mash out water and should produce equal first and second runnings.  Should you want to override that, just fill in a mash ratio in the “Mash Ratio Manual” cell.

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).

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

Water Volume Summary: This double checks to make sure you’re mash parameters do not exceed the volume of water you practically have available.

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 “System Temp Adjustment”, Cell H7: this is the number of the degrees that my mash tun (a 10 gallon Rubbermaid Cooler) generally drops when I put hot water in it.  This gets added to the “Strike Temp” figure cell B6 so that I don’t have to preheat my mash tun to account for this loss.  If you want to figure this for your system, you can put in a typical amount of mash water at a typical (and known) mash temperature.  Wait a minute or two and measure the temperature.  Subtract that temperature from the start temperature and you should have a good value.  You can refine this over time based on your observations.  You can set this to 0 if you preheat your mash tun or if you have a direct fired mash tun.

I suggest reviewing the Constants section to make adjustments for your setup, paying special attention to the mash tun loss, mash tun size and system temp adjustment values.

Age: The age of your beer based on brew date.  Day, Month and Year are totals.  If you see 365 days, 12 months and 1 year, you’re beer is 1 year old.

Efficiency: This calculator is based on recipe or software efficiency.  I did that so I did not have to add up the potential fermentables for every batch.  I use Beersmith and have my efficiency set at 70%.  I figure efficiency when going from the mash tun to the boil kettle.

Conditional Formatting:  “Mash Out Water” turns red if there is any mash out water available.  I don’t usually do a mash out, so I want to know if any water is sitting here.  “Mash Ratio Effective” turns red if the computed value is overridden.  “Total Used Water” turns red if this value exceeds available water.  “Mash Volume – Can I mash it?” 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.

Batch Sparge Directions: These are narrative step-by-step directions that you can follow after you enter data for your brew day.

Summary Sheet (click to enlarge):Homebrewing Spreadsheet

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 do not, generally, open up a spreadsheet on brew day.  I use a print out of this sheet.

You can enter information about your starter and other hop, adjunct and miscellaneous additions.

Prepare:  This is a simple to do checklist.  The water amount figured here is rounded up based on your beer parameters.

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.

Downloads
I’m making both Excel and Open Office versions available to download.  I’ve only tested the Excel version, but I’ve had no complaints about the Open Office Version.  I would suggest running this through your previous calculation methods to double check that all this makes sense for you.  I don’t want you coming back to me and complaining the your double IPA is a Pale Ale because of me. :)

Microsoft Excel Version
Open Office Version
Google Docs Version (Thanks to HBF Reader Dan for this conversion!)

Select File > Download from the resulting page to download your chosen version.

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

Pinned: Niko Hops Sale · Pin Lock Deal · Kegging System · Grain Sale · $24:Drip Tray

How To: Easily Convert Your Keg and Carboy Washer into a Draft Line Pump

Recent Great Deals:

toppost:brewingspreadsheet

Small Batch, All Grain Stove Top Brewing + Spreadsheet

For many of us, today is a good day to… stay inside.  This small batch stove top technique works great.  It’s my go to technique when it’s too cold outside to brew…
 


For fun and sport, I’ve been brewing small (1-3 gallon) batches of all grain on my stove top.  It’s a simple, quick and economical way to brew all grain.  It’s been a blast!


What kind of gear is needed to accomplish this amazing feat?  A comprehensive list follows.


1.  A Bag.
 
That’s about it.  You kind of just need a bag.


My total investment was about 4 for two bags.  This assumes you already have a reasonably sized kettle that you’ve used for extract and a thermometer.  My extract kettle is 6 gallons.


What is All Grain Brewing?
In a (very small) nutshell…
  • Soaking malted grains at a set temp using a set amount of water.  This triggers enzymes that convert complex carbohydrates to simple, fermentable sugars.
  • Separating those grains from the sugary wort.
  • Rinsing to retrieve some of the additional sugar that remains on the grains.

Small Batch Brew in a Bag (BIAB) Step By Step:
  1. Decide on a recipe and a batch size and gather your ingredients.  Buy your grain pre-crushed if you don’t have a mill.  More info on recipe sizing below.
  2. Heat the right amount of water to the correct temperature.  How do you figure that out?  Use the spreadsheet below.
  3. Put an appropriate fine mesh bag in your kettle.  Make sure the bag is large enough to hold your grain.  I’ve found with my 6 gallon kettle, doing one to three gallon batches, that a 5 gallon nylon paint strainer bag is perfect.  It’s plenty large enough to hold the grain and it’s not so large that it sits on the bottom of the kettle.  This helps to avoid scorching the bag.
  4. Put your crushed grain in the bag inside of the kettle.  Mix everything well and take a temperature.  The mash should settle out around your target temperature.  If it’s a little low, add some direct heat and stir.  If it’s a little high, add some ice and stir.  Your temperature does not need to be exact.
  5. Put the lid on your kettle and wait, usually 60 minutes, depending on the recipe.  Your grains are mashing.  Because there is no insulation, I usually take a temperature measurement half way through and add a bit of heat to bring it back up to the right temp.  Again, stir when you are adding heat.
  6. When the mash time is up, grab the bag and lift it out of your kettle.  I give it a spin or two to close up the bag.  Let it drip for several seconds.  I also give it a few light squeezes.  Have a large bowl nearby ready to receive your bag of spent grain.  When you have the time, discard the grains and rinse out the bag.  It’s ready for another use!
  7. Start boiling!

You will notice, there was no rinsing of grains.  The technique that I’ve outlined, is a no sparge method.  You’re just losing a little efficiency.  No big deal in my book.

The Spreadsheet

This is a simplified version of my regular brew day spreadsheet.

(Click the graphic to enlarge)


Green cells are to fill in as necessary.  Blue cells are calculated values.  The directions section is calculated as well and puts all the numbers into sentence form.  


Plug in your numbers under “Beer Info” and go.  You can adjust grain absorption rate and boil off rate, in the constants section, if you know your numbers or just leave the default values in place. 


Microsoft Excel Version

Open Office Version
Google Docs Version (Thanks to Google+ friend Daniel for this!)

How do you get an appropriately sized recipe?

  1. Use a free unlimited duration Brewer’s Friend Trial Account – Here.  Brewer’s Friend allows you to easily scale recipes up and down.
  2. Divide a 5 gallon ingredient kit or recipe down to whatever size you need.  Want to brew a 1.25 gallon batch?  Divide your 5 gallon recipe in quarters.  If you’re physically doing this with a recipe kit, make sure the grain is mixed up thoroughly to evenly disperse specialty grains.  You are losing a bit of accuracy in hop utilization by simply dividing a recipe.  Again… not a big deal in my book.
  3. I’m coming at this from a small batch perspective, but there’s nothing saying you couldn’t do this with a 5 or 10 gallon batch.  You just need get the right sized kettle and bag.


Important Note
However you end up with ingredients and a recipe, pay special note to the “Total Mash Volume” cell in the spreadsheet.  After you plug in your numbers in the “Beer Info” section, this estimates the total volume of your mash.  This should be less than the size of your kettle!  Special thanks to the Green Bay Rackers.  The Total Mash Volume calculation is an adaptation of the excellent “Can I mash it” online calculator.

Gear


The Bag:

Thanks to tipster Todd for this!  By the way, Todd is the guy behind Grog Tag.

These are elastic top nylon paint straining bags.

The elastic helps keep the bag from falling into the kettle when filled with grain.


5 Gallon Paint Straining Bags

-OR-


Kettle:

If you don’t have a large enough kettle to do the batches you’re interested in, check out the kettles tag.

If you’re looking to replicate this 1-3 gallon procedure.  Consider the 6 Gallon Winware Kettle.

Winware Professional Aluminum Kettle 6 Gallon 

These do not sell with lids, so add one on if you’d like.


Larger Batch Sizes…
If you want room to do larger BIAB batches, full boil extract batches and all grain batches.  Consider this 10 gallon kettle and lid.  This doesn’t cost much more vs the 6 gallon size.  As far as a bag for larger batch sizes… More Beer makes a larger bag that works well for full 5 gallon batches.  Some pictures of that bag in action are in our Brewing Pliny the Elder post.

Thermometer:

If need a thermometer, I’ll point you some Top Finds:

Waterproof, Instant Read Digital Thermometer from CDN.  Check out my hands on review here

CDN DTQ450X ProAccurate Quick-Read Thermometer



Manufactured by ThermoWorks.  Super fast response time (5-6 seconds), Dishwasher safe & Min/Max Function.

 

For the price, this is a great thermometer.  Waterproof, instant read, commercial quality. 

Taylor 9842 Commercial Waterproof Digital Thermometer

Start All Grain Brewing – For Four Dollars + Water Calculation Spreadsheet


For fun and sport, I’ve been brewing small (1-3 gallon) batches of all grain on my stove top.  It’s a simple, quick and economical way to brew all grain.  It’s been a blast!


What kind of gear is needed to accomplish this amazing feat?  A comprehensive list follows.


1.  A Bag.
 
That’s about it.  You kind of just need a bag.


My total investment was about four dollars for two bags.  This assumes you already have a reasonably sized kettle that you’ve used for extract and a thermometer.  My extract kettle is 6 gallons.


What is All Grain Brewing?
In a (very small) nutshell…
  • Soaking malted grains at a set temp using a set amount of water.  This triggers enzymes that convert complex carbohydrates to simple, fermentable sugars.
  • Separating those grains from the sugary wort.
  • Rinsing to retrieve some of the additional sugar that remains on the grains.

Small Batch Brew in a Bag (BIAB) Step By Step:
  1. Decide on a recipe and a batch size and gather your ingredients.  Buy your grain pre-crushed if you don’t have a mill.  More info on recipe sizing below.
  2. Heat the right amount of water to the correct temperature.  How do you figure that out?  Use the spreadsheet below.
  3. Put an appropriate fine mesh bag in your kettle.  Make sure the bag is large enough to hold your grain.  I’ve found with my 6 gallon kettle, doing one to three gallon batches, that a 5 gallon nylon paint strainer bag is perfect.  It’s plenty large enough to hold the grain and it’s not so large that it sits on the bottom of the kettle.  This helps to avoid scorching the bag.
  4. Put your crushed grain in the bag inside of the kettle.  Mix everything well and take a temperature.  The mash should settle out around your target temperature.  If it’s a little low, add some direct heat and stir.  If it’s a little high, add some ice and stir.  Your temperature does not need to be exact.
  5. Put the lid on your kettle and wait, usually 60 minutes, depending on the recipe.  Your grains are mashing.  Because there is no insulation, I usually take a temperature measurement half way through and add a bit of heat to bring it back up to the right temp.  Again, stir when you are adding heat.
  6. When the mash time is up, grab the bag and lift it out of your kettle.  I give it a spin or two to close up the bag.  Let it drip for several seconds.  I also give it a few light squeezes.  Have a large bowl nearby ready to receive your bag of spent grain.  When you have the time, discard the grains and rinse out the bag.  It’s ready for another use!
  7. Start boiling!

You will notice, there was no rinsing of grains.  The technique that I’ve outlined, is a no sparge method.  You’re just losing a little efficiency.  No big deal in my book.

The Spreadsheet

This is a simplified version of my regular brew day spreadsheet.

(Click the graphic to enlarge)


Green cells are to fill in as necessary.  Blue cells are calculated values.  The directions section is calculated as well and puts all the numbers into sentence form.  


Plug in your numbers under “Beer Info” and go.  You can adjust grain absorption rate and boil off rate, in the constants section, if you know your numbers or just leave the default values in place. 


Microsoft Excel Version

Open Office Version
Google Docs Version (Thanks to Google+ friend Daniel for this!)

How do you get an appropriately sized recipe?

  1. Use a free unlimited duration Brewer’s Friend Trial Account – Here.  Brewer’s Friend allows you to easily scale recipes up and down.
  2. Divide a 5 gallon ingredient kit or recipe down to whatever size you need.  Want to brew a 1.25 gallon batch?  Divide your 5 gallon recipe in quarters.  If you’re physically doing this with a recipe kit, make sure the grain is mixed up thoroughly to evenly disperse specialty grains.  You are losing a bit of accuracy in hop utilization by simply dividing a recipe.  Again… not a big deal in my book.
  3. I’m coming at this from a small batch perspective, but there’s nothing saying you couldn’t do this with a 5 or 10 gallon batch.  You just need get the right sized kettle and bag.


Important Note
However you end up with ingredients and a recipe, pay special note to the “Total Mash Volume” cell in the spreadsheet.  After you plug in your numbers in the “Beer Info” section, this estimates the total volume of your mash.  This should be less than the size of your kettle!  Special thanks to the Green Bay Rackers.  The Total Mash Volume calculation is an adaptation of the excellent “Can I mash it” online calculator.

Gear


The Bag:

Thanks to tipster Todd for this!  By the way, Todd is the guy behind Grog Tag.

These are elastic top nylon paint straining bags.

The elastic helps keep the bag from falling into the kettle when filled with grain.


5 Gallon Paint Straining Bags

-OR-

Kettle:
If you don’t have a large enough kettle to do the batches you’re interested in, check out the kettles tag.

If you’re looking to replicate this 1-3 gallon procedure.  Consider the 6 Gallon Winware Kettle.

Winware Professional Aluminum Kettle 6 Gallon

These do not sell with lids, so add one on if you’d like.


Larger Batch Sizes…
If you want room to do larger BIAB batches, full boil extract batches and all grain batches.  Consider this 10 gallon kettle and lid.  This doesn’t cost much more vs the 6 gallon size.  As far as a bag for larger batch sizes… More Beer makes a larger bag that works well for full 5 gallon batches.  Some pictures of that bag in action are in our Brewing Pliny the Elder post.

Thermometer:

If need a thermometer, I’ll point you some Top Finds:

Waterproof, Instant Read Digital Thermometer from CDN.  Check out my hands on review here

CDN DTQ450X ProAccurate Quick-Read Thermometer



Manufactured by ThermoWorks.  Super fast response time (5-6 seconds), Dishwasher safe & Min/Max Function.

 

For the price, this is a great thermometer.  Waterproof, instant read, commercial quality. 

Taylor 9842 Commercial Waterproof Digital Thermometer

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.

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

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

Mash Ratio Cells: Based on your beer parameters, the spreadsheet suggests a mash ratio (cell E12).  That ratio is designed to allow for no mash out water and should produce equal first and second runnings.  Should you want to override that, just fill in a mash ratio in the “Mash Ratio Manual” cell.

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).

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

Water Volume Summary: This double checks to make sure you’re mash parameters do not exceed the volume of water you practically have available.

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 “System Temp Adjustment”, Cell H7: this is the number of the degrees that my mash tun (a 10 gallon Rubbermaid Cooler) generally drops when I put hot water in it.  This gets added to the “Strike Temp” figure cell B6 so that I don’t have to preheat my mash tun to account for this loss.  If you want to figure this for your system, you can put in a typical amount of mash water at a typical (and known) mash temperature.  Wait a minute or two and measure the temperature.  Subtract that temperature from the start temperature and you should have a good value.  You can refine this over time based on your observations.  You can set this to 0 if you preheat your mash tun or if you have a direct fired mash tun.

I suggest reviewing the Constants section to make adjustments for your setup, paying special attention to the mash tun loss, mash tun size and system temp adjustment values.

Age: The age of your beer based on brew date.  Day, Month and Year are totals.  If you see 365 days, 12 months and 1 year, you’re beer is 1 year old.

Efficiency: This calculator is based on recipe or software efficiency.  I did that so I did not have to add up the potential fermentables for every batch.  I use Beersmith and have my efficiency set at 70%.  I figure efficiency when going from the mash tun to the boil kettle.

Conditional Formatting:  “Mash Out Water” turns red if there is any mash out water available.  I don’t usually do a mash out, so I want to know if any water is sitting here.  “Mash Ratio Effective” turns red if the computed value is overridden.  “Total Used Water” turns red if this value exceeds available water.  “Mash Volume – Can I mash it?” 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.

Batch Sparge Directions: These are narrative step-by-step directions that you can follow after you enter data for your brew day.

Summary Sheet (click to enlarge):Homebrewing Spreadsheet

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 do not, generally, open up a spreadsheet on brew day.  I use a print out of this sheet.

You can enter information about your starter and other hop, adjunct and miscellaneous additions.

Prepare:  This is a simple to do checklist.  The water amount figured here is rounded up based on your beer parameters.

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.

Downloads
I’m making both Excel and Open Office versions available to download.  I’ve only tested the Excel version, but I’ve had no complaints about the Open Office Version.  I would suggest running this through your previous calculation methods to double check that all this makes sense for you.  I don’t want you coming back to me and complaining the your double IPA is a Pale Ale because of me. :)

Microsoft Excel Version
Open Office Version
Google Docs Version (Thanks to HBF Reader Dan for this conversion!)

Select File > Download from the resulting page to download your chosen version.

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

Pinned: Niko Hops Sale · Pin Lock Deal · Kegging System · Grain Sale · $24:Drip Tray

How To: Easily Convert Your Keg and Carboy Washer into a Draft Line Pump

Recent Great Deals:

toppost:brewingspreadsheet

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.

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

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

Mash Ratio Cells: Based on your beer parameters, the spreadsheet suggests a mash ratio (cell E12).  That ratio is designed to allow for no mash out water and should produce equal first and second runnings.  Should you want to override that, just fill in a mash ratio in the “Mash Ratio Manual” cell.

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).

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

Water Volume Summary: This double checks to make sure you’re mash parameters do not exceed the volume of water you practically have available.

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 “System Temp Adjustment”, Cell H7: this is the number of the degrees that my mash tun (a 10 gallon Rubbermaid Cooler) generally drops when I put hot water in it.  This gets added to the “Strike Temp” figure cell B6 so that I don’t have to preheat my mash tun to account for this loss.  If you want to figure this for your system, you can put in a typical amount of mash water at a typical (and known) mash temperature.  Wait a minute or two and measure the temperature.  Subtract that temperature from the start temperature and you should have a good value.  You can refine this over time based on your observations.  You can set this to 0 if you preheat your mash tun or if you have a direct fired mash tun.

I suggest reviewing the Constants section to make adjustments for your setup, paying special attention to the mash tun loss, mash tun size and system temp adjustment values.

Age: The age of your beer based on brew date.  Day, Month and Year are totals.  If you see 365 days, 12 months and 1 year, you’re beer is 1 year old.

Efficiency: This calculator is based on recipe or software efficiency.  I did that so I did not have to add up the potential fermentables for every batch.  I use Beersmith and have my efficiency set at 70%.  I figure efficiency when going from the mash tun to the boil kettle.

Conditional Formatting:  “Mash Out Water” turns red if there is any mash out water available.  I don’t usually do a mash out, so I want to know if any water is sitting here.  “Mash Ratio Effective” turns red if the computed value is overridden.  “Total Used Water” turns red if this value exceeds available water.  “Mash Volume – Can I mash it?” 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.

Batch Sparge Directions: These are narrative step-by-step directions that you can follow after you enter data for your brew day.

Summary Sheet (click to enlarge):Homebrewing Spreadsheet

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 do not, generally, open up a spreadsheet on brew day.  I use a print out of this sheet.

You can enter information about your starter and other hop, adjunct and miscellaneous additions.

Prepare:  This is a simple to do checklist.  The water amount figured here is rounded up based on your beer parameters.

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.

Downloads
I’m making both Excel and Open Office versions available to download.  I’ve only tested the Excel version, but I’ve had no complaints about the Open Office Version.  I would suggest running this through your previous calculation methods to double check that all this makes sense for you.  I don’t want you coming back to me and complaining the your double IPA is a Pale Ale because of me. :)

Microsoft Excel Version
Open Office Version
Google Docs Version (Thanks to HBF Reader Dan for this conversion!)

Select File > Download from the resulting page to download your chosen version.

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

Pinned: Niko Hops Sale · Pin Lock Deal · Kegging System · Grain Sale · $24:Drip Tray

How To: Easily Convert Your Keg and Carboy Washer into a Draft Line Pump

Recent Great Deals:

toppost:brewingspreadsheet

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.

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

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

Mash Ratio Cells: Based on your beer parameters, the spreadsheet suggests a mash ratio (cell E12).  That ratio is designed to allow for no mash out water and should produce equal first and second runnings.  Should you want to override that, just fill in a mash ratio in the “Mash Ratio Manual” cell.

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).

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

Water Volume Summary: This double checks to make sure you’re mash parameters do not exceed the volume of water you practically have available.

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 “System Temp Adjustment”, Cell H7: this is the number of the degrees that my mash tun (a 10 gallon Rubbermaid Cooler) generally drops when I put hot water in it.  This gets added to the “Strike Temp” figure cell B6 so that I don’t have to preheat my mash tun to account for this loss.  If you want to figure this for your system, you can put in a typical amount of mash water at a typical (and known) mash temperature.  Wait a minute or two and measure the temperature.  Subtract that temperature from the start temperature and you should have a good value.  You can refine this over time based on your observations.  You can set this to 0 if you preheat your mash tun or if you have a direct fired mash tun.

I suggest reviewing the Constants section to make adjustments for your setup, paying special attention to the mash tun loss, mash tun size and system temp adjustment values.

Age: The age of your beer based on brew date.  Day, Month and Year are totals.  If you see 365 days, 12 months and 1 year, you’re beer is 1 year old.

Efficiency: This calculator is based on recipe or software efficiency.  I did that so I did not have to add up the potential fermentables for every batch.  I use Beersmith and have my efficiency set at 70%.  I figure efficiency when going from the mash tun to the boil kettle.

Conditional Formatting:  “Mash Out Water” turns red if there is any mash out water available.  I don’t usually do a mash out, so I want to know if any water is sitting here.  “Mash Ratio Effective” turns red if the computed value is overridden.  “Total Used Water” turns red if this value exceeds available water.  “Mash Volume – Can I mash it?” 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.

Batch Sparge Directions: These are narrative step-by-step directions that you can follow after you enter data for your brew day.

Summary Sheet (click to enlarge):Homebrewing Spreadsheet

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 do not, generally, open up a spreadsheet on brew day.  I use a print out of this sheet.

You can enter information about your starter and other hop, adjunct and miscellaneous additions.

Prepare:  This is a simple to do checklist.  The water amount figured here is rounded up based on your beer parameters.

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.

Downloads
I’m making both Excel and Open Office versions available to download.  I’ve only tested the Excel version, but I’ve had no complaints about the Open Office Version.  I would suggest running this through your previous calculation methods to double check that all this makes sense for you.  I don’t want you coming back to me and complaining the your double IPA is a Pale Ale because of me. :)

Microsoft Excel Version
Open Office Version
Google Docs Version (Thanks to HBF Reader Dan for this conversion!)

Select File > Download from the resulting page to download your chosen version.

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

Pinned: Niko Hops Sale · Pin Lock Deal · Kegging System · Grain Sale · $24:Drip Tray

How To: Easily Convert Your Keg and Carboy Washer into a Draft Line Pump

Recent Great Deals:

toppost:brewingspreadsheet