The original walk through of the Bru’n Water Spreadsheet remains one of the most popular features of Accidentalis.com. While the core information remains relevant, there is a significant difference in workflows between the Free and Subscriber version of the spreadsheet. This tutorial is focused on the Subscriber version, and will attempt to illustrate the differences between versions, while providing the basic understanding of the spreadsheet’s functionality. Hopefully, this will convince you that the Subscriber version is worth a donation!
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 of the spreadsheet with specific examples, and point out the areas that give you options. The free version spreadsheet can be downloaded here: https://sites.google.com/site/brunwater/home/files . Look for the red “download” arrows at the bottom of the page.
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 larger and more legible version in your browser.
Step 1:
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!
The subscriber version has slightly different instructions, as you would expect, due to enhanced functionality. Pictured below is from the Free version.
Click on the worksheet tab at the bottom of the screen named “1. 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. 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.
Note: The free version requires you to download either the US or SI version individually, and is limited to gallons or liters.
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).” – note that the ‘Free Version’ examples contain this error.
The bottom area of the worksheet contains a variety of useful “Ion Concentration Conversion Calculators” to help convert various 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. 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.
Accidentalis’ 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 sporatically, 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 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 Reverse Osmosis or RO treated water and build my brewing water from scratch using mineral additions. If your water report shows ions that are very high like my water, you may need to employ dilution or replacement of your water with R/O 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 an acid addition or process it in a way to remove much of the bicarbonate through lime softening. 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.
TIP: At this point, I recommend some simple changes to the spreadsheet.
- Save the original spreadsheet unaltered. This way you can quickly return to the original if required.
- Save the version of the spreadsheet with water information pre-entered. This will get a name, such as MASTER_Water_MMDDYY.xlsm. This will be a convenient starting point for every new recipe.
- Go to each tab, and set the viewing size to a comfortable and easy to read size on your monitor. This is accessed at the very bottom of the spreadsheet window with a slider followed by a percentage value.
- Subscriber Version:Tie together the sparge volume values on both the Sparge Acidification and Water Adjustment worksheets – select the cell B11 on the Sparge Acidification worksheet, then enter the following formula {=’3. Water Adjustment’!L17} leaving off the brackets.
- Setup a storage folder that is convenient and accessible. When creating a new recipe, open the MASTER spreadsheet and Save As with the new recipe name and date. Then you can make your adjustments, while keeping a clean record of your water adjustments per recipe.
- Save your changes often as you work.
Step 2:
Remember I said you need a beer recipe handy? With that recipe, we need to jump out of sequence of the numbered tabs. You will enter the grain data for your mash on worksheet 4 (Mash Acidification) since that data is needed to help predict a mash pH. I jump to the Mash Acidification sheet out of order because the worksheets 2, 3 & 4 are linked. The grist is highly influential on the mash pH.
You will also need to figure out how much strike and sparge water your recipe will require. Those volumes are necessary for the calculations. I use Beer Smith to calculate those volumes for my brewing – you can use your preferred solution.
Let’s pick an imperial stout grist, because I know the roasted grains will impact my mash pH, and require some adjustments in alkalinity with R/O water. This recipe made up for the sake of demonstration and should not be considered an actual recipe!
For 5.0 gallons or 19 liters,
14 lb or 6.35 kg Pale Ale 2-Row (2.0 SRM)
1 lb or 0.45 kg Caramel/Crystal 60 (60.0 SRM)
1 lb or 0.45 kg Roasted Barley (300.0 SRM)
12.0 oz or 0.34 kg Black Patent Malt (500 SRM)
8.0 oz or 0.23kg Chocolate Malt (350.0 SRM)
The rough water needs are 5.6 gallons or 21.2 liters of strike water and 3.87 gallons or 14.65 liters to sparge. I have a 15 gallon RIMS system and mash moderately thin, your infusion and sparge volumes may differ.
Select the red “4. Mash Acidification” worksheet and enter your grain bill. You will need to know their color (Lovibond) rating. For now, ignore the mash pH results, but double check your grain weights, Water to Grist ratios and estimated color. This should closely match your recipe’s expectation. If it does not, please double check your work. Make sure to choose “Adjusted Water” in the “Water used for Mash” box in the Mash Acidity Results table. We will return here for the results after the water adjustments are entered.
NOTE: Here the differences between the Free version and the subscriber version become much more apparent. With the Subscriber version, we will enter our Mash Water, Sparge Water and Batch Wort Volumes in a single place, in the Water Adjustment Worksheet. With the free version, you must be careful to ensure that in every place required, the volumes match absolutely! Also the grain cells change color with the Grain Type in the subscriber version – adding a level of quick visual check when you are entering your grist. As you can see, the basic calculations are the same, however the water volumes are different and thus will result in a different pH estimate in this example. The subscriber version also supports EBC and Lovibond as options, and has separate check boxes to allow you to evaluate removing either the Crystal, the Roast or both from the main mash to manage pH. This would require a separate steeping of the grains in 165° F or 74° C water and added to the main mash at time of lautering.
Step 3:
Now that the recipe is entered, We need to return to the yellow “3. Water Adjustment” worksheet. Here we will make decisions about treatment of our mashing and sparging water which may include dilution with R/O or mineral additions.
First, consider and select your target water profile. Please don’t take the profile as anything more than a suggestion, but use it as a starting point. For brewing a Pale Ale, you might try the “Pale Ale Profile”, brew with it, and determine you prefer more or less sulfate or chloride. The ion concentrations are just starting points. All water profiles can be edited in the table at the bottom of the Water Adjustment page. Looking through the list, you will see a lot of location based profiles (all researched and balanced), location profiles that indicate ‘boiled’ to simulate likely treatment in the brewery, and finally color based profiles that indicate Malty, Balanced or Bitter. There are also open customized profiles if you prefer. Do some serious research for the beer style you are brewing before randomly choosing a geographic profile, including how the brewery treats its liquor.
Since I am brewing an Imperial Stout, I picked a profile that balances dryness to offset some of the sweetness from a high gravity beer. The drop-down box under the Desired Water Profile heading is where you select target water profiles. In my case, I selected the “Black Bitter” profile to provide a relatively high level of sulfates. Alternatively, you could choose “Black Balanced” or a city profile such as “London (Boiled)”, however that profile may not work well. The water profile establishes target ion concentrations in parts per million, and gives you a quick comparison of your existing water to that target profile. Each ion heading has a pop-up note to guide you and provides some information on what each ion does.
My water, again – is too high in sodium and bicarbonates, so I am going to dilute with R/O at 100%. Since R/O doesn’t remove absolutely everything, Mr. Brungard’s preset has left in a few ions. You can modify this if you choose based on your rejection rate or a post R/O water report, but it really is unnecessary. R/O is generally less than 40 ppm in total dissolved solids, so essentially you have a blank slate. Deionized water is completely free of ions, but that is a more expensive water source for brewing. After the dilution is setup properly, we need to then start adding in minerals and watch the “Finished Water Profile” line for feedback. Don’t forget to input your Mash and Sparge water volumes on the Mash Acidification sheet.
If you have a reliable water report, and the water has excessive ion concentrations for your Desired Water Profile, you can increase the Dilution Percentage to reduce those concentrations. For many people, their municipal water is acceptable for brewing a few styles, and dilution with R/O or Distilled water may help target desired profiles they could not otherwise achieve. For example, high alkalinity in your water may be appropriate for a dark coffee porter, but not appropriate for a light colored lager, such as Pilsner. If you use any portion of your municiple water, please make sure to treat that portion for chlorine or chloramine removal before brewing. You should use Campden tablets, filter slowly through activated charcoal, or both.
NOTE: Once again, you will see significant visual differences between the free and Subscriber version – in particular the ability to visually see the Estimated Mash pH change with the additions you are making. The decimal resolution is also out to hundreds, as opposed to tens of a decimal for those of us who prefer precision. Also, as previously noted, Mash and Sparge volumes are input here, with the total Batch volume. These will drive the values for the remaining worksheets. Otherwise, the functionality and presets are the same.
Because we are using mineral ions for FLAVOR, we don’t worry about mash pH right now. We will adjust mineral additions, using grams per gallon to get close to the water profile for this demo. As such, we will focus on the mineral additions that do not impact carbonate or bicarbonate levels first, then adjust those after to help achieve the desired mash pH.
The primary drivers of flavor here are the ions Sulfate and Chloride with about a 2:1 ratio. While the ratio isn’t as important to brewers as in the past, it is helpful to understand the relationships as you choose minerals. The ratio should be treated solely as a guideline and always in context with the actual mineral or ion concentrations. Gypsum adds a lot of sulfate, so you will need to incrementally add Gypsum while watching your calcium additions. Calcium Chloride favors the chloride ion, but also adds calcium. In a dark beer, magnesium plays a flavor role, so Epsom is also added, but note it also affects Sulfate levels.
If you are just starting out with water management, focus on Gypsum and Calcium Chloride as salts and perhaps some Baking Soda. Using these three and RO water, you can achieve the bulk of the primary flavor constituents for a water profile. Secondarily you can focus then on calcium, magnesium and sodium ion levels to fine tune your profile.
NEVER add an alkali addition when you are using acid.
Using alkali additions to target 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 possibly 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 profile’s Bicarbonate, Alkalinity or RA.
Practically, exactly matching a profile 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 instability in the transition of the mash into the required zone of mash pH.
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 Bitter” profile. I am completely ignoring the Bicarbonate, Alkalinity and RA numbers in the “Black Bitter” profile!
I am also at this point largely ignoring the mash pH, but also 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.23 g/gal of Pickling Lime to achieve an estimate near to 5.4. The side benefit is that additional calcium brings me to the recommended 60 ppm. Baking Soda would be a safer choice, but also elevate the sodium ion beyond 20 ppm. You will need to decide which works better for you and your personal tastes.
With the mineral additions I have chosen, the spreadsheet computes 0.7 grams of Gypsum, 3.4 grams of Epsom, 1.5 grams of Calcium Chloride and finally 1.3 grams of Pickling Lime for the strike water. We will further confirm the mash pH in the next step and make any final necessary adjustments. A similar amount is indicated for the sparge liquor, and if the volumes of mash and sparge liquor were equal – the measures would also be equal.
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 dough-in to ensure they dissolve, but you can just add them to the mash. 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 strike water.
NOTE: When using the free version, it is necessary to switch back and forth between Water Adjustment and Mash Acidification worksheets to see where your mash pH may fall. With the subscriber version – the workflow is much smoother and clear as the mash pH is right in front. This will allow you to also consider the acid or alkaline adjustments at once, rather than through the process described in Step 4.
Step 4:
The final check on mash pH. Here we need to take a more critical look at the specific estimated Mash pH and determine if that is the right target. We have set on the basic mix of mineral additions, as well as determined the alkaline additions necessary. Double check your work on the Mash Acidification worksheet, and check both the total grist weight as well as the grain colors and types. If you have questions about how a specific malt should be categorized, do a Google search. Many maltsters have some variation from a “normal” level of acidify in their malts and edge cases will occasionally pop up. You can always post a question on Homebrewtalk or the AHA Forum and ask M. Brungard to comment, or post the question to the Facebook page for Bru’n Water.
FREE VERSION: If we switch to the “4. Mash Acidification” worksheet, we can see that we have an estimated Room-Temperature Mash pH of 5.4. This is excellent as it falls right in the middle of the recommended range, and doesn’t require us to use any additional acids to hit our target mash pH. Changing the drop down to “Existing Water” in the “Water used for Mash” section will show you the impact of your mineral ion additions, but make sure to return the value to “Adjusted Water” before proceeding.
SUBSCRIBER VERSION: You can change any mineral addition and directly see the impact on Estimated Mash pH on the Water Adjustment worksheet. However on the Mash Acidification page, you will find a table that recommends a higher mash pH for darker beers. Taking this advice, I would most likely add more Pickling Lime or Baking Soda in the example above to achieve a 5.5 estimated pH. It is informative to see the difference between our source water and adjusted water – use the drop down for “Water Used for Mash” to switch between the two. Make sure to return the variable “Adjusted Water” before proceeding.
In this Imperial Stout, if the estimated mash pH is higher than 5.6, you should reduce the amount of Pickling Lime or Baking Soda. If I had been brewing a lighter colored beer, I might not have needed the pickling lime and may have needed to add acid to the mash.
If your pH is below 5.2, then you should consider adding more Pickling Lime or Baking Soda and adjusting the other additions appropriately. Or you can consider steeping your roasted and crystal malts separate from the base malt mash. This creates some additional flavor challenges, but is something to consider. In the free version of the spreadsheet, you would just zero out the weights of the dark malts and observe the mash acidity change and rise. For example, steeping only the 12 ounces of Black Patent malt away from the base mash would cause the estimated mash pH to rise to 5.5.
When dealing with mash pH, especially with highly alkaline water, you may need to use liquid acid additions. If this is the case, DO NOT USE ANY ALKALINE MINERALS! More often, while mashing you will use lactic or phosphoric acid to adjust your mash pH downward. Proper use of a pH meter will help keep you on target. Brewing often with your water, observing and tasting the results will help you determine the right course of action.
TIP: Once you have established your baseline for a given estimated mash pH, you can use the tool to calculate the incremental liquid acid or alkaline mineral addition to move the estimated mash pH PLUS or MINUS 0.1 pH unit. I write this down on the printed out summary and keep it handy should my mash require a dramatic push in either direction. Make sure you restore the spreadsheet to the previous values before preceding. I would not make any mash pH adjustments unless the mash is outside of the 5.2-5.6 range, and would recommend only doing so if you are familiar with the proper use of a calibrated pH meter.
Step 5:
Since the calculations show we are in a good spot – we will move onto the “2. Sparge Acidification” worksheet. Here, you will need to know the alkalinity of your water in ppm as CaCO3. For those with a suitable tap water, the alkalinity of the tap water is calculated on the Water Report Input sheet and is shown about half-way down that sheet. That value should appear automatically . Since I am using R/O water, Dilution will automatically calculate the alkalinity in the mix using a preset RO alkalinity value of 13. If you followed my TIPS in Step 1, then you are ready. Otherwise, set the Water Volume to 3.9 gallons to represent the sparge water volume I intend to use in my brewing.
SUBSCRIBER VERSION: The subscriber version has the option to dilute the sparge water with RO or DI water. The manual inputs above are not necessary as the program already selects from the Water Input page. I dilute to 100% RO water which has very little alkalinity. If my mash pH is correct, I should not need to acidify my sparge water. However, if I am mashing at a high pH, I may choose to add the tiny bit of acid necessary to prevent my pH from rising over 6.0. If you prefer more specific accuracy, you can have your RO water tested and enter the data into the tables at the bottom of the Water Adjustment worksheet.
Select the type and strength of acid you will be using. The drop-down box lists the types of acids that Bru’n Water is capable of using. For liquid acids, enter the acid’s numerical strength value and use the drop-down box to select the strength setting as either: Percentage (%), Normality (N), Molarity (M) or Solid. The packaging for the liquid acid should provide the strength data. Solid acids do not use the strength settings and they can be ignored.
The Starting Water pH value is not very influential on the acidification results, so it is OK to guess at a value to input. For many tap waters, its pH is around 8. In some cases, water reports will include the pH. The final setting is the Target Water pH. Normally sparge liquor is neutralized to under 6.0 pH. The most important thing to monitor when setting the target pH is the Final Water Alkalinity. The final alkalinity must be less than 50 ppm to avoid tannin extraction. A safer result is to acidify the sparging water to around 25 ppm alkalinity. Alter the Target Water pH value to produce the desired Final Water Alkalinity. When the alkalinity is reduced sufficiently, the calculated acid amount is accurate.
The system predicts that I will need about 2.47 mL of 10% Phosphoric acid to reach a pH of 6.0 and a very low alkalinity of 2 ppm. As you may note, R/O water already has sufficiently low alkalinity and doesn’t really need to be acidified for lautering.
If I had used my very alkaline tap water, the target pH would have to be at least 6.0 and the resulting amount of acid would have been substantially greater. With the sparging water acid quantity calculated, the remaining mineral additions will be calculated on the Water Adjustment sheet. Do note the warning on the page to NEVER put alkaline minerals into the sparge water!
Step 6:
Finally, worksheet “5. Adjustment Summary” provides a printable summary of all of the calculations, additions and comparison chart of Historic Brewing Profiles from A.J. DeLange.
This summary sheet is extremely useful in the brew house. I print it out and tape it to my HLT as a reminder of the volumes I require, and the proper mineral additions. It is a handy reference when I have my pH meter in hand looking at mash and sparge water pH. There is a text report page that can you copy out of and paste into your brewing software notes as text.
A note about pH meters: There are two very important issues that you need to consider while using a pH meter, of any grade. Firstly, pH meters need to be calibrated before use, and during use if over an extended period of time. Your pH meter should have instructions, and using calibration solutions is fairly straightforward. Secondly, mash pH should be measured at room temperature. While it seems like an extra step, nesting two small bowls, the bottom filled with ice water, will cool a small sample down quickly. You only need a few ounces, filtered. I strain my sample through a paper towel to keep out the grits and fill a shot glass. Then test your sample. Give the probe some time to react, it is not an immediate reading. Using the same procedure every time should give you consistency. Cooling the wort will also extend the life of your probe and increase the accuracy of Automatic Temperature Correction (ATC) features. I check the mash within a few minutes of dough in, and about 15 minutes in. From there I decide if further samples are required, based on observation.
Conclusion:
You now have a guideline and process to manage your brewing water using Bru’n Water. You should spend the time and investment to brew the same beer, changing only the water profile. The differences can be staggering, and often the difference between a medal or a disappointing score in competition. M. Brungard has suggested the following experiment:
Create independent solutions of Gypsum and Calcium Chloride, at a ratio of a gram to 100 milliliters of cold water of each will work fine. Then pour a beer, something not too bitter (to avoid fatigue) but is very familiar, then dose with one or the other solution, measuring your additions, gently stir and taste. Take notes. Dose again, always tracking the concentration. Or split the beer into flights and increase the Gypsum in one and the Calcium Chloride in the other. This, alone, convinced me to treat my mineral additions as part of every recipe!
This experiment is even better when you are using a beer brewed with a specific profile. You can then tweak your mineral additions to achieve an optimal effect in flavor and mouth feel to your personal preferences. Just remember to take notes and scale the additions into your next recipe.
There is a good deal more that we will explore here in other articles. The key to successfully using a tool is planning, practice and experience. Managing my water, and playing with the effects of different water profiles has opened up many new vectors in my brewing, and the attention to detail has spread into all of the practices in my brewery. I am a better brewer for it.
