In March 2015, Martin Brungard released the Bru’n Water Spreadsheet 3.2 to subscribers with major improvements to UI and functionality. This walk through supercedes the 2.0 version which will remain up if you choose not to upgrade. If you have not yet received your 3.0 version, I suggest another small donation which will trigger the release to you within a few hours.
Martin Brungard’s Bru’n Water spreadsheet is an excellent modelling tool for managing your brewing water. Below, we will walk through the Subscriber version 3.2 of the spreadsheet with specific examples, and point out the areas that give you new and improved options. Fortunately, while the update is significant, familiarity with either the Free versions or the Version 2.0 Subscriber spreadsheet will give you a head start!
Please donate and get the subscriber version!
This will not only give you access to some key advanced features, but also ensure this tool continues to be supported and updated as required. The subscriber version will be emailed to you.
What you will need:
- Your water report with the variables important for brewing. You can build a list from the spreadsheet. Ward Labs is an independent testing facility, and provides a brewing specific report. My Ward Labs report will be used in this tutorial.
- A computer with supported spreadsheet software and basic familiarity with spreadsheets. M. Brungard lists the requirements on his website.
- A recipe and some understanding of what your target water profile might be.
Note: Clicking on the images below will open a full sized and more legible version in your browser.
If you are familiar with the previous versions of BWS, then you will notice the order of the tabs has changed. This allows for a more natural input approach and step order.
At the bottom of the spreadsheet you will see a variety of colored tabs. The first, and an important tab, is “0. Instructions” which provides details on water chemistry, as well as information on using the following worksheets. PLEASE READ! Bru’n Water looks complicated, but it’s not that difficult to use once you enter the basic data and learn how the program works. You probably won’t be able to use the program instantly without reading the instructions. At the end of the spreadsheet, you can also find a Water Knowledge tab, which contains much of the contents you will find online at the Bru’n Water website.
Tips:
- Save a master version of this spreadsheet with your water report input. This allows you to return to a known good state for a new recipe.
- Save often. Hiccups happen – don’t lose your work.
- Save a copy of the spreadsheet with your beer name, revision number and date to allow you to return to the spreadsheet and make any necessary comments or adjustments for a future brew. If you make changes in a later brew – re-save with a new version number in the title.
Step 1: Water Report Input
Click on the worksheet tab at the bottom of the screen named Water Report Input and get your water report handy. As mentioned, I have my Ward Labs Report and will put in the pertinent information from my report in this example. I highly recommend getting a brewer’s specific report. Typically the analysis is specific to the sample that you draw, and usually will adequately ‘balance’. If your Cations/Anions delta is larger than 0.50, you should contact the lab for a retest. Wards Labs has made a few changes in their procedures recently increasing the accuracy of their carbonate and bicarbonate measurements, however, you should also look at the Total Alkalinity calculator and see if the results help to better balance your report (more later). I also recommend testing at least once a year, but more often if your water is seasonably variable. This can help you make adjustments accordingly throughout the year.
Be careful to follow the instructions in the spreadsheet, especially when inputting Sulfate and Nitrate concentrations. These may need to be modified, depending on the metric presented in the water report.
Comment from AJ DeLange: “Whenever alkalinity is available in a water report use the alkalinity value rather than the bicarbonate/carbonate numbers which are calculated from the alkalinity. In the case of Ward Labs reports, such as the one given as an example here, the bicarbonate and carbonate are calculated incorrectly. For pH 8.3 and alkalinity of 503 the bicarbonate is approximately 607 and the carbonate 5. Specifying 582 and 15 to the spreadsheet have to confuse it as that ratio is not possible at pH 8.3 (the spreadsheet should ask for alkalinity or be modified to check for incorrect data like that in this example).” This means that I should allow the Total Alkalinity calculator (lines 12-14) determine the bicarbonate level – doing so, I increase the Ion Balance Result significantly.
The bottom area of the worksheet contains a variety of useful “Ion Concentration Conversion Calculators” to help convert common report measurements into useful data. As you can see – input a value into the cyan cell – and the result will read in the yellow as PPM or Parts Per Million. The spreadsheet also has information and tips embedded.
Note: Hovering the cursor over cells with the red marks in the corner should pop up comments that can help guide what you should input or understand in that cell.
My Water – Salty and Alkaline disaster!
In the case of my water report data from Ward Labs, you will notice that I needed to modify the sulfate and nitrate values from the report results to use in the spreadsheet using simple math.
First thing to notice in my water report is that I have ridiculously high levels of sodium in my water. Roughly equivalent to 2 grams per gallon. This alone renders my filtered tap water worthless for brewing, even as a diluent. But using the section below the Ion Concentrations tables, I can get a sense of how accurate the laboratory report is. That green box for Cation/Anion difference stays green under 0.5, and shows that my lab report balances. A balanced water report result suggests that it is more likely that the reported results are reasonably accurate. This is very important when using statistical reports from municipal water companies where water ion concentrations (including chlorine/chloramine) may fluctuation seasonally. Most municipalities will run tests sporadically, and not always synchronized to a single snapshot so I strongly recommend a Ward Labs report for brewing!
An important result from the water report is that the spreadsheet calculates the Total Alkalinity as CaCO3 at 502, the RA Effective Hardness at 33, and the Residual Alkalinity (RA) as 492. This is a tremendous amount of alkalinity for a mash to overcome. I struggled for several years brewing with this water. When I finally invested in a pH meter and attempted to hit a mash pH of 5.4, I realized that I had some serious problems. The amounts of acid I was using was flavoring the beers, and it took a very long time for my mashes to convert… as a result, the beers were flabby, poor malty characteristics, and often very tannic and astringent.
For these two primary reasons (high sodium and alkalinity) – I use 100% Reverse Osmosis (RO) treated water and build my brewing water from scratch using mineral additions. If your water report shows ions that are very high, you may need to employ dilution or replacement of your water with RO or distilled water to make it more suited for brewing. Please be aware that if my water had low sodium and a more modest bicarbonate content, I might have been able to neutralize the bicarbonate with a reasonable acid addition or process it in a way to remove much of the bicarbonate through slaked lime reactions. Moving forward with my particular water – I will be diluting 100% with RO water in the spreadsheet. This also brings an advantage in that my water is far more consistent as it is not subject to the whims of the seasons or the water provider, but also the flexibility to use some amount of my tap water to raise alkalinity if I choose.
If you solely use RO or DI water, you might skip this section. However, it is useful to use dilution of tap water as a tool to manage alkalinity or for a specific hardness mineral. If this is the case, I still recommend filling out the primary water profile and using the dilution tools later, even at 100% dilution.
Step 2: Sparge Water Acidification
Remember I said you need a beer recipe handy? We need to consider the recipe and determine if the sparge water needs to be acidified to account for potential tannin extraction. When the mash pH rises over 6.0 and the temperature approaches 170º F (76º C) the potential for polyphenol, silica and tannin extraction rises – resulting in husk/grain astringency in the end beer. Because a Robust Porter has a significant dark roast contribution, we want the sparge pH to be below 6.0. This requires a very small 88% Lactic Acid addition. I will often target my desired mash pH just to be safe.
Note: If you sparge with 100% RO or DI water, this is probably not necessary. The extreme low levels of alkalinity should not cause the mash pH to rise much during a sparge. Also – if you Batch Sparge, the contact time with the grist is very small – assuming you dump in the sparge water, stir vigorously, vorlauf, and run off at a fairly fast rate. You need to determine if this component is required for your process. If you begin to detect astringency in your end beer (not from wild yeast) consider utilizing Sparge Acidification to eliminate that variable. Another area to look at might be your mill settings.
You can see in the above image that I have set the dilution rate to 100% RO water and targeted a sparge pH of 6.0. I have left line 11 set to 1 gallon as this will automatically pick up the sparge volume in the Water Adjustment tab when we fill it out later.
Note: With the 3.0 version, we now have up to 2 acid addition choices per Sparge and per Water Adjustment. In the drop downs, are more acids are supported including acetic, hydrochloric, lactic, phosphoric, sulfuric, citric, tartaric, malic and CRS. Of course, most homebrewers will choose phosphoric, lactic or CRS. If using other acids – make sure they are food grade.
Step 3: Grain Bill Input
We are now ready to input our Grain Bill. I have gotten into the habit of putting the maltster name into the spreadsheet as many variations between maltsters occur within general malt types. Example, Best Maltz Munich is roughly 8.3 L and maybe slightly less acidic than other Munich products at 10 L. You will need to make some assumptions about the grain type, as well as the color (Lovibond), or use the values provided by malt lot sheets where you can. If you use EBC rather than Lovibond, a drop down is provided to change the value, but you need to convert values yourself. The more accurate you can be here, the better. Many of the errors I have seen have been caused by mis-categorized malts or incorrect color. There are some additional hints (colored boxes) that will show when a color is outside of the expected value for a given category the cell turns red. Also – double check your work against your recipe application or spreadsheet looking at Percentage of Grain Bill and Total Weight. For now – ignore the remaining values, unless you are using Acid Malt.
Note: Because of variability of certain aciduated malts & sauermaltz by source, you can adjust the apparent strength in line 26. This is best accomplished by brewing with the acid malt addition, and adjusting the value up or down until you reach the measured stable mash pH and matching it to the mash pH estimate in Bru’n Water. Whenever using acid or acid malt to adjust a mash – I highly recommend using a stable, freshly calibrated pH meter. Martin Brungard uses Weyerman Aciduated Malt as the baseline average of 1.
Let’s pick a Robust Porter grist, because the roasted grains will dramatically impact my mash pH. I want to add the minimal minerals necessary to achieve my flavor profile and to reach a desirable mash pH estimate. This may require a small amount of alkalinity since my RO base water has very low alkalinity (<30 ppm alkalinity as CaCO3).
For 11.75 gallons or 44.5 liters (please forgive the non-standard batch size),
20 lb or 9 kg Great Western Brewers Malt 2-Row (2.0 L)
2 lb or 0.9 kg Simpsons Medium Crystal (55.0 L)
1.6 lb or 0.73 kg Best Maltz Munich (8.3 L)
12.0 oz or 0.34 kg Dingeman Debittered Black Malt (500 L)
10.0 oz or 0.28 kg Fawcett Pale Chocolate (250.0 L)
4.0 oz or 0.11 kg Crisp Roast Barley (300.0 L)
This matches with my BeerSmith recipe values. I am leaving the crystal and roast malts in the mash, but there are options to add them late in the mash or during lautering (a good choice if you cold steep). However, be aware that any additional acidity from the crystal/roast malt will still follow into the kettle – if you manage your boil pH. Also, we are going to target a 5.5 mash pH estimate as suggested for darker color beers.
Note: A few specialty malts and base malts fall outside the ‘averages’ that are used to calculate their buffering and acid contributions. If you are using a new specialty malt, or have questions relative to specific malts, it is a good idea to visit the Bru’n Water site or Facebook page and see if something has been mentioned. In most cases, follow the specific category, but for some lightly kilned roasts, the base malt or crystal categories may be more accurate. If you brew and the resulting stabilized mash pH is significantly different, revisit this worksheet and change either categories or Lovibond color values to isolate the malt. I would also encourage the use of a test mash on any new recipe that maybe outside of your brewing experience. This data will allow you to tweak the worksheet before the big batch, resulting in more accurate information.
Step 4: Water Adjustment
This page is where we can quickly select a water profile (or create our own), manage dilution (if required), and get a good understanding of the correlation to calcium bearing minerals to mash pH. This page has changed since Version 2.0, but the overall interface looks and functions very much the same. Make sure to pay attention to the Row Names as you now have more profile indicators than before.
It is very important to get your volumes correct. I tend to round the BeerSmith volume recommendations, but have modified my mash profiles to provide a 1.5 quart/pound grist ratio to facilitate my RIMS system. So when inputting these volumes, I try to match to the neared 1/4 gallon value. This recipe calls for 10 gallons of Mash liquor and 7.25 gallons of Sparge liquor. I treat these volumes separately out of convenience for my 3-vessel brew house, but you can treat all of the water together if you prefer. You will notice that, unless specified otherwise, the actual weights of minerals are based on the concentration of the addition in grams/gallon.
For this beer, I am going to use the Black Balanced selection under Desired Water Profile, but any of the three options would work fine. Another key difference from Version 2.0 you may note is that the beer color profile names have shifted slightly to FULL, BALANCED and DRY to better indicate the effects of the sulfate:chloride ratios. FULL equates to the former MALTY indicator and DRY equates to BITTER. For more information on this – please revisit the Water Knowledge page. This distinction is important and a better way to express the effects of sulfate and chloride on the mouth feel of the finished beer and contribution to flavor expression.
You may also note that the upper profile tables no longer include RA, Sulfate:Chloride ratios, or even the Cation/Anion indicators. This simplifies the interface tremendously, and removes the temptation to target values that may cause you to add excess minerals, acids or alkali – that may impact the flavor of your beer negatively. Those values are now reported on the Adjustment Summary and appropriately represent the finished product.
While working to match the profile, I am watching the Mashing Water Profile closely andmonitoring the Overall Finished Water Profile, with an eye to the Estimated Mash pH. Because I split my additions between Mash and Sparge, the values are different. Mashing Water Profile drives the estimated Mash pH value, while the Overall Finished Water Profile drives what goes into the boil kettle. This distinction should give you much better information to determine the impact of your mineral additions, and give clues to what will happen in the boil.
I prioritize Gypsum, Calcium Chloride and Epsom salts as my primary flavor additions but you can work in any order you prefer. The Mineral list has been re-ordered to show the primary flavor minerals first and alkali choices last (see WARNING below). This reordering maybe confusing at first, but makes much more sense in my workflow. Here, we will add 0.25 gram/gallon Gypsum and 0.20 grams/gallon Calcium Chloride. This gets me in the ball park of my primary flavor ions and brings my estimated Mash pH down to about 5.36. I need to make a choice here as my mash pH is slightly lower than my desired target around 5.5. Also – you will note that the calcium load is lower than the 50 ppm indicated in the profile. As this is a dark beer, I am less concerned about clarity. A recent article in Zymurgy by M. Brungard (Ca and Mg in Brewing Water) indicates that 30 ppm Ca is adequate for oxalate precipitation, break formation in the kettle and good yeast flocculation. You may choose to increase calcium to 50 ppm.
WARNING: The re-ordering of the minerals in the spreadsheet will cause confusion if you are used to the V 2.0 spreadsheet. Slow down and make sure you are using the right rows, and reading the ingredients on the Adjustment Summary. Muscle memory may have you grab Epsom salts instead of calcium chloride by accident!
I can add either 0.25 grams/gallon of baking soda, which will add some sodium or use Pickling Lime at 0.15 grams/gallon to achieve a 5.5 estimated mash pH. I chose pickling lime simply for it’s faster reaction in the mash, which should allow for the mash pH to stabilize more quickly. I also suspect that I have moderate sodium in my RO output – and tend to avoid additional sodium. From a style perspective, it maybe more appropriate to have a bit more sodium as indicated in the water profile. My finished profile, as indicated in the screenshot above, adequately covers my desired expectations. We need to get close and manage any compromises, but it is not necessary and impractical to match ion concentrations to the PPM.
Note: You can make some choices at this point, including where to add the sparging minerals (in the mash?) or if you would like to put the hardness minerals into the boil kettle. There is also a calculator to use a hydrous solution of CaCl2 as opposed to dry weight if you are concerned about the hygroscopic nature of the dry form. CaCl2 takes on humidity reducing its concentration by weight.
If you scroll down in the spreadsheet, you will see a large set of water profiles shown above. All are editable, and you are free to tweak the profiles to your preferences. Do make sure to keep the profiles balanced in anion/cation. As previously mentioned, when using a profile that is regional, it is highly recommended to use the profile with “(boiled)” in the name. This shows you the original profile de-carbonated much as the brewery would do to treat the water. This will allow you to brew without adding unnecessary acids or alkali into your mineral schedule. Of particular interest are the Color and Style profiles, which may provide an excellent starting off point for creating your preferred water profile for a given beer. Look for Pale Ale Profile or the Yellow/Brown/Black + Dry/Balanced/Full profiles. An open User Custom line is provided.
Below this, you can modify (if you choose) the water profiles for dilution water. One can do some calculations based on your RO filter’s efficiency and figure out the general ion concentrations which may resolve a more accurate profile configuration. Again, this should be worked out such that the cation/anion ratio balances.
Some additional hints:
NEVER add an alkali addition when you are using acid.
Using alkali additions to build toward a water profile tends to be a bad idea. Those choices are Baking Soda, Pickling Lime and Chalk and should only be used to increase your estimated mash pH when the estimate is low. Chalk is no longer recommended, but seems to have a strong hold in many brewers minds. Chalk dissolves very slowly and the increased amount required to move mash pH up rapidly (during the period of the mash) means that the buffering effect will extend well into your boil and into the fermenter.
Re-Adjust your mineral additions after adding the alkaline minerals.
In all cases, ion concentrations will naturally be affected by the mineral addition. When we use the alkali additions, we are usually adding more calcium, sodium or bicarbonate. While this can be beneficial, we will likely need to modify our primary constituents to account for the shift. Always prioritize proper mash pH over your flavor ions!
Do NOT target a water profile’s Bicarbonate level.
Practically, exactly matching a profile bicarbonate level will result in using more mineral salts and acid than you need. Less is more in these instances. Adding bicarbonate that then needs to be neutralized by liquid acid additions is self defeating, and can create slow the stabiliziation of the mash to the required measured mash pH. In the example above, if I matched the bicarbonate level exactly, I would need additional acid to reduce my mash pH level to an expected level. See the first hint above! The only instance I would recommend this procedure is to replicate a historical and regional water profile, but also remember that breweries treat their water to remove bicarbonates – choose de-carbonated/boiled regional profiles.
By playing with the amounts of various minerals, Gypsum at 0.13 g/gal (grams per gallon), Epsom at 0.6 g/gal and Calcium Chloride at 0.26 g/gal I can achieve most of the desired flavor profile. While Calcium is critical to a healthy fermentation and has desirable effects in the boil, it is not an absolute requirement to have a minimum of 50 ppm, much less the indicated 60 ppm in the “Black Dry” profile. I am completely ignoring the Bicarbonate levels “Black Dry” profile and not worrying so much about the calcium!
I am at this point manipulating the mash pH and flavor ions, aware that a dark beer will require less additional acidification and likely will need an alkaline addition to achieve the right mash pH. This thinking will come more naturally as you brew with the spreadsheet as you observe how minerals and acid influence the mash. At this point, a quick look shows that my Estimated Mash pH is lower than desired, and I need to use 0.15 g/gal of Pickling Lime to achieve an estimate near to 5.5. The side benefit is that calcium is slightly supplemented. Baking Soda would be a safer choice, but also elevate the sodium ion. You will need to decide which works better for you and your personal tastes.
Mineral additions should be weighed out carefully on a reliable gram-weight scale. A small error has a large potential impact! I add my Mash additions to my strike water before grain-in to ensure they fully dissolve. The same would apply to the Sparge water additions, with a possible liquid acid addition. Also note that the spreadsheet recommends adding the Pickling Lime directly to the mash, and not into the prepared strike water. Stir vigorously to ensure complete dissolution of all of the minerals!
Note: Even though we didn’t use any liquid acid in this example, at the bottom of the sheet is the appropriate place to plan out any required acids. And you should note that a sparge addition is also provided here, based on the Sparge volume that you entered on this worksheet.
Step 4: Review
I left this step off previous tutorials, however I am prone to entering incorrect data or rushing.
- Review your Sparge Acidification tab. Now that all of the information is correct, you can see the estimated amounts of acid required (should you acidify). Check your Target Water pH as well as the acid and strength. Look for a Final Water Alkalinity < 25 ppm when possible.
- Review your Grain Bill Input Worksheet. Recheck the malts and categories, as well as the amounts and the expected color. Check these against your beer recipe program or worksheet and make sure they are as expected. If using acid malt and you know you need to adjust the apparent strength of that acid contribution – do it now (again, only after a test batch to confirm and follow the instructions on the tool tip). Verify your Estimated Room-Temperature Mash pH.
- Review your Water Adjustment worksheet. Look for glaring errors and review your work. Ensure the volumes of Mash and Sparge water are correct and that you have adequately covered the ion concentrations for the chosen water profile. Review tool tips on any cells that are showing RED or otherwise indicating a possible problem. Address those as necessary. Review your mineral additions. Look out past the tables on line 33 to determine the PPM concentration of acid -ions that are added into the water.
I like to print this last summary out, and scale to fit the printout. The larger text is easier to read than the old version with the RA chart and useful as I am weighing things out and preparing my liquor. This goes into a folder with my recipe printout and notes from the brew day.
Step 6: Cut & Paste into your Beer Recipe Application
To keep things up to date in BeerSmith (or other application), you can easily cut and paste the text version of the results found on the Raw Text Summary. This allows me to quickly reference the water profile used without opening up Excel or digging through my written notes. Simply highlight the desired information, copy and then paste into the NOTES section of your application.
Now, just go brew!
Special thanks to /u/vinpaysdoc from Homebrewdad.com and Reddit for providing editing and comments. You can check out his Primer to Bru’n Water at Homebrewdad.com.
