web analytics

Bru’n Water Spreadsheet Tutorial 1.17: Free Edition

Bru'n Water Free Spreadsheet v 1.17a Adjustment Summary for a Koelsch

Martin Brungard’s Bru’n Water spreadsheet (BWS) is a modelling tool for managing your brewing water. Below, we walk through the FREE version of the spreadsheet with specific examples, reviewing options. For a modest donation, you will receive the advanced spreadsheet and details are later in this walkthrough. The 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.

There are a number of water calculation tools on the internet for brewers, including EZWater Calculator, Greg Noonan’s Water Witch, Palmers Mash RA spreadsheet, internet sites like Brewers Friend  (the mash chemistry written by Kai Troister). I have found that Bru’n Water works well for my workflow. I find that Bru’n Water and Brewer’s Friend are the easiest to use and provide good results. My advice is to choose one that you are comfortable with and stick to it – this removes a variable in your brewing.

(UPDATE: July 2016 – Mr. Brungard snuck a quiet update out that significantly improves the interface) If you still use the Free edition – I suggest grabbing the latest version – 1.17b at the time of this writing.

What you will need:

  1. Your water report with the variables important for brewing. You can build a list of required ions 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. You can also use a municipal report, however, be aware you may generate an unbalanced report as the nature of their testing is averaging amounts – not a specific snapshot.
  2. A computer with supported spreadsheet software. M. Brungard lists the requirements on his website.
  3. 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: Water Report Input

At the bottom of the spreadsheet there is a variety of numbered 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 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!

Bru'n Water Free Spreadsheet v 1.17a Instructions Page
Figure 1 – BWS Free Edition V 1.17a : Tab 0.Instructions

Click on the worksheet tab at the bottom of the screen named “1. Water Report Input” and get your water report handy. Enter the data carefully.

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.

Figure 2- a Ward Labs Water Quality report for Brewers.
Figure 2- a Ward Labs Water Quality report for Brewers.

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

The spreadsheet has information and tips embedded. Hovering the cursor over cells with the red marks in the corner pops up comments to guide your input or define the cell value.

Bru'n Water Free Spreadsheet v 1.17a Water Report Input Tab
Figure 3 – Bru’n Water Free Spreadsheet v 1.17a: 1. Water Report Input

The 1.17a version unifies the metric and US standard versions. At the top of the Water Report Input worksheet, you can choose Gallons or Liters.

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 spread sheet.

Figure 4- Report figure conversions maybe necessary for Sulfate and Nitrate
Figure 4- Report figure conversions maybe necessary for Sulfate and Nitrate

I have ridiculously high levels of sodium in my water. Roughly equivalent to 2 grams per gallon. This renders my filtered tap water worthless for brewing, even as a diluent. Using the section below the Ion Concentrations tables, I can get a sense of how accurate the laboratory report is. A red message in row 13 will read “Water Report in unbalanced. Check your inputs” if the cation/anion difference is greater than 0.5. A balanced water report will provide an accurate mash pH estimate, and water profile match. When using statistical reports from municipal water companies where water ion concentrations (including chlorine/chloramine), remember that you are getting annual averages and water content may fluctuation seasonally. I strongly recommend a Ward Labs report for brewing!

If you exclusively use RO or DI water, you can enter representative values in the Water Report Input and not use the dilution functions. This is particularly useful as there is no dilution option for Sparge water which can make Sparge acidification calculations difficult. That said – if you sparge with pure RO or DI water – you should have no need to acidify!

Bru'n Water Free Spreadsheet v 1.17a Water Report Input Error
Figure 5 – If you look at row 13, you will see “Water Report in unbalanced. Check your inputs” This is an indication that your water’s anion:cation ratio is greater than 0.50. Reports that are unbalanced should be adjusted or rerun. A Wards brewer report will have the ratio, and should be lower than 0.50. Values were changed to create an unbalanced example.

An important result from the water report is that the spreadsheet calculates the Alkalinity as CaCOat 500, 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. Below, I have adjusted my report to more closely match the comments from AJ Delange.

Bru'n Water Free Spreadsheet v 1.17a Water Report Input with Tap
Figure 6 – You input your basic water report. A Ward’s Brewer Report is more accurate as a snap shot of your water’s ionic content. Don’t weep for the crappy quality of my tap water. I use 100% RO. By putting my primary water here, I can blend with RO to achieve some more aggressive (and salty) water profiles.

For these two primary issues (high sodium and alkalinity) – I utilize Reverse Osmosis (R/O) 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.

 

Step 2: Recipe Input

Remember I said you need a mashing recipe handy? With that recipe, we  jump out of sequence on numbered tabs.  You will enter the grain data for your mash on worksheet 4. Grain Bill Input since that data is needed to help predict a mash pH. I jump to that Grain Bill Input sheet out of order because the worksheets 2, 3 & 4 are linked. The grist composition 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 BeerSmith to calculate those volumes for my brewing – you can use your preferred solution.

Let’s pick a simple Koelsch recipe and adjust my mash pH.

For 11.0 gallons,

18# Best Malz Heidelberg Pils (2.9 EBC) – base malt

1# Weyermann CaraHell (6.5 EBC) – caramel/crystal malt

1# Weyermann CaraFoam (30.0 EBC) – caramel/crystal malt

The recipe’s water needs are 9 gallons of strike water and 8.5 gallons to sparge. I have a 15 gallon system, so your infusion and sparge volumes may differ.

Flip over to the yellow “3. Grain Bill Input” worksheet and enter your grains. You will need to know their color (Lovibond or EBC) rating, which you can take from BeerSmith or from malt lot analysis sheets. For now, ignore the mash pH results, but double check your grain weights 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.

Bru'n Water Free Spreadsheet v 1.17a Water Report Input - simple Koelsch Recipe after water adjustment
Figure 7 – Once your water adjustments have been finalized, you can come back and see that the other datapoints are now accurate, such as Water to Grist Ratio. Note, you can change the Malt Color Setting to Lovibond or EBC. Because I am using Continental malts – I used the EBC values from the malt analysis sheets.

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 and mineral or acid additions.

Step 3: Building our Water Profile

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.

Bru'n Water's Water Input tab has an extensive table of regional and color-based water targets
Figure 8 – At the bottom of the Water Adjustment tab you will find an extensive list of regional and style-color based Water Profiles.

Since I am brewing a Koelsch, I picked a Yellow profile that aligns to the very pale color of the beer. The drop-down box under the Desired Water Profile heading is where you select target water profiles. In my case, I selected the “Yellow Balanced” profile to provide a bit of sulfite and chloride. Alternatively, you could choose a city profile that maybe representative of beer (Cologne for Koelsch). It is purely a personal choice, but I encourage you to use the boiled version of the profile, which more likely represents the treated brewing water from that region. 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 that guides you, and provides some information on what each ion does.

Bru'n Water Free Spreadsheet v 1.17a Water Adjustment Tab
Figure 9 – Bru’n Water Free Spreadsheet v 1.17a: 4. Water Adjustment

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 mash with the full boil volume of liquor – simply put that number in the Mash volume and set the Sparge to 0.

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 our Koelsch. If you use any portion of your municiple water, please make sure to treat that portion for chlorine or chloramine removal before brewing. You can use Campden tablets, filtering through activated charcoal, or both.

Bru'n Water Free Spreadsheet v 1.17a Water Adjustment for Koelsch
Figure 10 – I chose Yellow Balanced as the water profile and adjusted the concentrations of Gypsum, Calcium Chloride, and Epsom Salt to most closely match the target water profile. Note the addition of lactic acid to achieve the target mash pH of 5.4. NOTE: Since my water report is tuned to my RO profile, I am not using diluting with RO or DI water.

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. 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 1.2:1 ratio. The ratio should be treated solely as a guideline, but helpful to understand the relationships as you choose minerals. Gypsum adds a lot of sulfate, so you will need to supplement while watching your calcium additions. Calcium chloride favors the chloride ion, but also adds calcium.

By playing with the amounts of various minerals, Gypsum at 0.31 g/gal (grams per gallon), Epsom at 0.25 g/gal and Calcium Chloride at 0.33 g/gal I can achieve most of the desired flavor profile. I like to have about 50 ppm calcium which the profile suggests. Calcium is critical to a healthy fermentation and has desirable effects in the boil. I am also at this point largely ignoring the mash pH, but also aware that a pale beer will require some acidification to achieve the right mash pH. This thinking will come more naturally as you brew with the spreadsheet.

Procedurally, add some value into the addition column next to the mineral salt. Then look up at the row named Mashing Water Profile and the representative columns of ions. You want to get reasonably close to the proposed Water Profile values. Focus first on sulfite and chloride, then if you wish, focus on magnesium and sodium. Calcium will track with calcium salt additions, and will lower estimated mash pH. Bicarbonate represents alkalinity – and generally should be ignored, unless you are using a salt like sodium bicarbonate to raise your estimated mash pH. If you are using a very mineral rich water profile (like the Pale Ale profile) you many need a bit of alkalinity to prevent the mash pH from falling too low. In MOST cases, you will simply need a small measure of acidity.

With the mineral additions I have chosen, the spreadsheet computes 2.8 grams of Gypsum, 2.3 grams of Epsom, 3.0 grams of Calcium Chloride. The new version (1.17a) now shows the estimated Mash pH on both the Water Adjustment and Grain Bill Input tabs making adjustments far easier.

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 any alkaline additions directly to the mash, and not into the strike water.

Step 4: Adjusting for mash pH

At this point, you need to determine a target mash pH. Mr. Brungard suggests 5.3-5.4 for pale beers (see the Grain Input Tab for a table of suggested pH). Most pale beers that use a modest water profile will require a small amount of liquid acid or acidulated malt to bring the pH into that range. Acidulated Malt (or acid malt) is added to the Grain Input worksheet – selecting “Acid Malt” from the grain type. Usually a few ounces are all that are needed, and if you have preset your minerals to your Water Profile, you can now add the acid malt necessary to lower the estimated mash pH. 5.4 is a great target, as if you miss a little high or low, you will still be within good ranges for enzymatic activity.

Most traditional German brewers prefer acidulated malt to liquid acid. However, liquid acids (lactic or phosphoric) are very convenient and can be more accurately measured to provide consistent results. The Water Adjustment worksheet allows you to choose a liquid acid type, set the acid concentration, and make adjustments to the mash pH by slowing adding as mL/Gal.

In some cases, you may need to reduce or increase your mineral additions. Most basic (alkaline) salts will change your flavor ion profiles. Remember, you just need to get into the ballpark. Perfectly matching water profiles are not critical.

When dealing with mash pH, especially with highly alkaline water, you may need to use liquid acid additions. More often, while mashing you will use lactic or phosphoric acid to adjust your mash pH downward, and lime or baking soda to adjust upward. 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.

Step 5: Sparge Acidification

If you use R/O or DI water for your entire sparge volume, you can skip this step.

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 picked by the Sparge Acidification worksheet. Since I am using R/O water, I can use the estimate for R/O water alkalinity provided on the Water Adjustment profile table, which is 13.  I leave my Water Volume to 1.0 gallons which will be automatically scaled by the spreadsheet to the volume entered on the Water Adjustment worksheet.

Bru'n Water Free Spreadsheet v 1.17a Sparge Acidification with RO as source
Figure 11 – As you can see, without the ability to dilute tap water with sparge, if you use RO in your Water Report Input, then the sparge acidification works appropriately. Note that since the alkalinity of RO water is so low, you will not need to acidify for sparge. It will not significantly shift the mash pH during lauter.

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), or Molarity (M).  The packaging for the liquid acid should provide the strength data.  Solid acids do not use the strength settings and they can be ignored.

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

If I had used my very alkaline tap water, the target pH should be lower than 6 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.

Step 6: The Summary Worksheet

Finally, worksheet “5. Adjustment Summary” provides a printable summary of all of the calculations.

Bru'n Water Free Spreadsheet v 1.17a Adjustment Summary for Koelsch
Figure 12 – Print this page as a worksheet for your brew day. It will accurately split out additions for your mash and sparge water.

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 additions. It is a handy reference when I have my pH meter in hand looking at mash and sparge water pH.

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

I strongly suggest that you download the free version and use it in a few brew days. Follow the instructions and recommendations closely and determine if the result is positive. I believe that you will see a better result in the brewing day, in the kettle (hot and cold break, hop utilization) as well as in the finished products. If you do – then make a donation and get the advanced spreadsheet. This is not Martin Brungard’s day job, and he deserves to get paid for his work!

The upgraded version of the spreadsheet features advanced functionality and automation:

  1. You can work in many standard volume scales, from gallons to hectoliters.
  2. Enhanced worksheet automation, importing key values between worksheets
  3. Sparge water dilution options, including accounting for mineral additions (from the Water Adjustment Worksheet)
  4. Option of adding Hardness Minerals directly to the Boil Kettle
  5. Options to use more than a single acid source
  6. Side by Side comparison of EBC and SRM results
  7. Separate options for removing Crystal and/or Roast malts from Main Mash
  8. A “raw text” report worksheet that can be cut and pasted into the notes of your preferred recipe manager
  9. Personalized features include your name, and a place to enter your beer name

As previously mentioned, I manage my water spreadsheet by both cut and paste of the information into BeerSmith’s notes field, and save the spreadsheet in a folder with the recipe name and dates. M. Brungard has been very accessible and responsive to any questions, and I owe him many thanks for improving my brewing and for his input and help with this post!

Related Posts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.