is an Excel Spreadsheet created Martin Brungard, a homebrewer and Environmental Engineer. You can read his bio at the bottom of the Bru'n Water website. This spreadsheet is a great tool to help brewers adjust their water and grain bill in order to reach an optimum conversion pH for the style of beer they are brewing. The download is FREE! If you do download it and find it useful, send the man a donation and he'll send you a personalized (your name on the sheet) version that has a few improvements.
Read the Instructions (sheet 0) and Water Knowledge (last sheet) Pages.
The pages are not long and you'll learn a lot. Much of the information presented in these pages is also available in the program, but, read it here first. It will help you as you start working with the program.
Enter your Water Report Input (sheet 1) from your municipal water report or other water report. Even if you plan on using Distilled or Reverse Osmosis water, go ahead and input your municipal water report. The program will still allow you to use Distilled or Reverse Osmosis water, it's just nice to have your municipal water inputted in case you need to use it in the future.
If you have questions as you input the numbers, hover your cursor over the cell of the value you are putting in. For example, when you hover over the cell "Calcium" a pop-up box with information about how calcium is reported comes up. If you read all the pop-ups it becomes pretty easy to put in all the values.
Keep in mind that water reports are a moving target. The values often change in different seasons depending on snow melting, rainfall, etc. Most municipal reports have average values. The average value is what I use for my calculations.
The Mash Acidification (sheet 4) Page is where you enter your grain bill. I usually input my grain bill and then do the water adjustments. You could just as easily do the water adjustments first.
Here's where it starts to get fun. Enter the grain bill for one of your beers. Enter each grain, select the type (hovering the cursor over the "Grain Type" cell will help you determine the type if you are in doubt), enter the amount, and finally enter the "Color".
There is a place at the bottom to change color from Lovibond to EBC if you prefer. Also, you may check the boxes at the bottom if you wish to exclude the crystal or roasted malts from the mash.
The Water Adjustment (sheet 3) Page is basically beer Sudoku puzzles. Start by placing the name or ID of your beer in the box at the top of the page. Next, go to the "Desired Water Profile" box and scroll through all the options in the drop down menu. All of those profiles are listed at the bottom of the page and include a place for you to enter a custom profile if you desire. Select the desired water profile for the grain bill you put in to the mash acidification sheet.
Below the "Desired Water Profile" box is the "Dilution Water Profile". It allows you to use 0-100% of distilled or reverse osmosis water for your calculations. This is particularly useful for those with hard water that might need to dilute their own water in order to match a given water profile.
Next, fill in the amounts of water you want to use for your mash and sparge. Also, put in the batch size desired.
Finally, we get to the meat of why you're here - "Water Additions". In this section you put in values for Mineral Additions next to the mineral you want to add. You are trying to match the "Finished Water Profile" to the "Desired Water Profile". The important players in this game are Calcium, Sulfate, Chloride, and Bicarbonate. If you match those numbers closely to the "Desired Water Profile" you will find that the Hardness, Alkalinity, RA, and SO4/Cl2 Ratio closely matches the "Desired Water Profile".
While you are working on matching the profiles, hover over the different cells and read the program notes he has put in. There is a wealth of information there. Take some time while solving the puzzle and read. Particularly, take the time to note what he has to say about Sodium, Magnesium, and each of the different mineral additions. For example, don't spend a lot of time working with Chalk unless you are willing to go to the effort required to get it properly dissolved. Pickling Lime is supposed to be added to the mash only. Read the additional information about all the mineral additions you might select before solving your puzzle.
Once you are content that you have matched your "Desired Water Profile", check the "Estimated Mash pH". There are three possible outcomes here. The first is that you've nailed your desired mash pH. Great! Game over. The other two will be listed below with ways to adjust your pH as needed:
Lower Than Desired Estimated Mash pH - ways to raise the mash pH
Increase mash water amount
Remove Roast or Crystal Malts from the mash
Higher Than Desired Estimated Mash pH - ways to lower the mash pH
Decrease mash water amount
Add acid malt to grain bill on the Mash Acidification Page
With the help of those adjustments you should be able to get your estimated mash pH close to what you are seeking for the style of beer you are brewing.
The Sparge Acidification (sheet 2) Page is pretty simple compared to the steps 2-4. Just hover your cursor over each value you are to input. It is pretty self-explanatory. The important decisions here are your "Target Water pH" and what acid you want to use. I use Lactic Acid 88%. A "Final Water Alkalinity" under 50 is what you are looking for here. Some folks may not even need to acidify their sparge water.
OK, you're basically finished. Well done! Now you need to go to the Adjustment Summary (sheet 5) Page to print out your water additions for brew day. First, highlight the section that you want to print. Then click on the "Print Area" tab at the top and select "Set Print Area". Finally, go to the upper left and select "File" and then select "Print" from the drop-down menu. Now you're ready for brew day!
This may seem like a lot, but, it's really not. Once you start playing with the additions page you'll start to get the hang of it. For those of you that are more visual learners, I created some quick video tutorials
to help as well.
I hope this is helpful. If you have problems as you work through a recipe, don't hesitate to contact me.
Edit: MChrispen has made some good points:
1. Magnesium is helpful to enhance bitterness at times (IPA or Bitters).
2. Sodium does have some flavor impact he likes in darker beers.
Also, MChrispen/jwalkermed mentioned that adjusting the bicarbonate to match the profile is probably not necessary. That leaves Calcium, Chloride, and Sulfate as the major players, with Magnesium and Sodium having importance in certain styles.
Bottom line? Use the program and continue to read/re-read the pop-up boxes in the cells. I've still got a lot left to learn here.
its ironic I was asking about this very thing in the forum earlier today.. I'll be sure to look more in the blog posts
posted by blur_yo_face on 1/27/2015 at 08:13:35 PM
This is awesome! Thanks for the great info, dude.
posted by brulosopher on 1/27/2015 at 08:43:18 PM
Tags for this post: Water chemistry, Water Adjustments, Mash pH, Bru'n Water