In my "Go All Grain for $4" post, I posted a simplified version of my brew day spreadsheet. I've received several requests for a copy of the full version, so... Here it is!
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 exception of the computed mash ratio (more below), planning on a set volume of sparge water and the step by step batch sparge directions.
Green Cells are information that you can fill in.
Blue/Light Gray Cells are calculated.
Brew Day Sheet (click to enlarge):
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. 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):
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):
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 Open Office versions available to download. I've only tested the Excel version. I assume the Open Office version will work fine, but I haven't tested it. I'd also 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 about this, leave a comment. Your question may help others.
This is a work in progress! If you have a correction or suggestion for this page or spreadsheet, shoot me an email.
Related: All Grain, Tips Page
Recent: