Click here to download a spreadsheet designed by James Azotea which scales recipes to 5-gallon (19 L) batches to micro brewery levels and was written to be used Ray Daniels book Designing Great Beers. BYO Editor Chris Colby also has a calculator you can download: (BYOcalc). You can use it to perform your brewing calculations or to give you ideas for designing your own spreadsheet.
Most aspiring homebrewers begin brewing by following a recipe, whether from the back of their first brew kit, their local homebrew shop, books, magazines or the Internet. After brewing some successful batches from existing recipes, however, many homebrewers start formulating their own recipes. There are many possible calculations to do when formulating a beer recipe and doing them all by hand can be tedious. There are commercial software packages and Internet sites that can help you with these, but another solution is to write a custom spreadsheet specialized for your own needs.
I have written a brewing spreadsheet you can download above that helps brewers design their own beer. With this spreadsheet, you can calculate the original gravity of your homebrewed creation (and estimate its alcohol content) based on the amounts of grains and malt extract in your recipe. You can also estimate the bitterness — in IBUs, International Bittering Units — of your beer from the hopping schedule. Brewers who plan on entering homebrew contests can use the spreadsheet to see if their beer matches the specifications of the appropriate beer style in the Beer Judge Certification Program (BJCP) Style Guidelines. (These are given at www.bjcp.org.)
In this article, I’ll describe my brewing spreadsheet (BSS) — which is available for download at www.byo.com — and give pointers as to how you can write your own customized spreadsheet. (It will be assumed that you know how to use a spreadsheet program, such as Microsoft Excel or Lotus 1-2-3.)
Filename and Recipe Documentation
The first item on my spreadsheet is a space for the name or type of beer. The second entry is for the BJCP category, if applicable. The spreadsheet can help you document your recipes. Files can be saved under different filenames, with each file corresponding to a different recipe.
Extract Efficiency
The first value you input on my spreadsheet is extract efficiency — a measure of the amount of fermentables you extract from your grains compared to the maximum amount of fermentables that can be extracted from the grain. Most homebrew texts explain how to calculate this number for your system. Typical numbers for efficiency ranges from 60% to 80%. (Recipes in BYO assume a 65% extract efficiency.)
Kettle Additions
Extract efficiency does not apply to ingredients like sugar, honey, dried spray malt extract and malt extract syrup. The weight of these, multiplied by their potential extract, gives the weight of material they contribute to the brew. Dried malt extract has a higher potential extract than liquid malt extract, because part of the weight of liquid malt extract comes from water.
In my spreadsheet, this section can easily be used for making recipes for all-extract brews, meads or yeast starters. You can also calculate how much malt extract to add if you fail to hit your target gravity in an all-grain brew session.
Grain Bill and Malt Specifications
In the grain bill section, the name of various grains, their potential extract and their color (in degrees Lovibond) is given. These values can be edited as well as the names and types of grain. The spreadsheet calculates gravity units, color (in SRM) and percentage of grain bill for each. The SRM ratings chart included in the spreadsheet (taken from the book “Clone Brews”) allows you to look up a color description corresponding to the SRM number returned by the spreadsheet.
Original Gravity
The spreadsheet calculates the original gravity (OG) as the weight of the grains times their potential extract times the extract efficiency, all divided by the volume of the beer. Potential extract is the maximum amount a grain could contribute to the specific gravity of a beer. For North American homebrewers, this is usually expressed in terms of the gravity points that one pound of the ingredient would yield in one gallon of water. For example, if one pound of a grain would give a maximum original gravity of 1.038 when mashed in one gallon of water, its potential extract would be 38 point gallons per pound (often phrased as “points per pound per gallon.”)
The potential extract for various ingredients can be found on the websites of malting companies. Lists of typical potential extracts can also be found in the books “Clone Brews” (1998, Storey Books) and “Beer Captured” (2001, Maltose Press), both by Tess and Mark Szamatulski. The equation for calculating original gravity is:
SG = (W * PE * EE)/ V
where W equals weight in pounds, PE equals potential extract in point gallons per pound (or ppg — points per pound per gallon), EE equals extract efficiency as a decimal (i.e. 65% is 0.65) and V is volume in gallons. This equation gives you the specific gravity (SG) in gravity points (GP) (i.e. an SG of 48 corresponds to a specific gravity of 1.048). The SG can be calculated for each ingredient and summed to obtain the original gravity (OG) of the beer.
Final Gravity and Alcohol Content
The final gravity of a beer (in gravity points) can be calculated as:
FG = OG (1 - AA)
where OG is original gravity in gravity points and AA is the apparent attenuation as a decimal. Typical ranges of apparent attenuation of different yeast strains are given in the websites of yeast suppliers such as Wyeast and White Labs. (A value of 0.75 for AA gives a decent estimate for most yeast strains.)
Alcohol (ABV, alcohol by volume) can be calculated as:
ABV = (OG - FG) * 0.129
where OG and FG are the original and final gravity of the beer (in GP).
The BU to GU ratio — a measure of bitterness to original gravity, defined by Ray Daniels in his book "Designing Great Beers" (1996 Brewers Publications) — is also calculated in my spreadsheet.
Various equations for estimating color exist in the homebrew literature. One common calculation, a curve-fit by Dan Morey, is:
SRM = 1.49 * MCU0.69
where MCU (malt color units) is the weight of the ingredient in pounds times its color rating (in °L) divided by volume (in gallons).
IBU Calculations
Five entry areas for calculating International Bittering Units (IBU or BU) are designed into my brewing spreadsheet. This adds flexibility to calculating IBU when making American pale ales and IPAs. The ounces of hops and the total IBUs are totaled at the bottom. The Alpha Acid Units (AAU) — calculated as the weight of the hops (in ounces) multiplied by their alpha acid rating (as a percentage) — are given on the right of the IBU calculations. The formula for calculating IBUs is:
IBU = (W * AA% * U * 74.89)/ V
where W equals weight (oz.) of the hops, AA% is the alpha acid rating of the hops in percentage, U is the utilization factor as a decimal (charts that give hop utilization as a function of boil time can be found in most homebrew books, including “How to Brew,” by John Palmer (2001, Defenestrative Publishing) and V is volume in gallons. (Note that W * AA% = AAU.) An IBU value is calculated for each hop addition, then summed to yield total IBUs.
Other Features
My spreadsheet also calculates the volume and temperature of water needed for mashing in and other steps and a variety of other things. You can customize your own spreadsheet to do as much (or as little) as you want.
This is James Azotea’s first article for Brew Your Own.
