BrewingTechniques

Spreadsheet for Recipe Design

by Karl King

Republished from BrewingTechniques' May/June 1993.

Using a spreadsheet to perform repetitive calculations makes light work of the technical side of recipe design, freeing you to follow your creative inclinations.

Recipe design has two aspects, the technical and the artistic. Although the artistic is by nature not reducible to numbers and equations, the technical aspect can be. Using a computer spreadsheet program to simplify repetitive calculations has several advantages, providing you the ability to try a variety of things quickly and express your artistic side without getting bogged down in time-consuming calculations. This article shows how to set up a program for recipe design using the commonly available Microsoft Excel (Microsoft Corporation, Redmond, Washington, USA) spreadsheet program. Other programs such as Quatro Pro (Borland International, Scotts Valley, California, USA) and Lotus 1-2-3 (Lotus Development Corporation, Cambridge, Massachusetts, USA) would work just as well.

Recipe formulation involves some basic choices:

  • What style is desired?
  • What ingredients (fermentables, adjunts, hops, yeast) are needed for that style?
  • What water characteristics are needed for that style?
  • What original gravity range is appropriate?
  • What color range is appropriate?
  • What IBU range is appropriate?
  • Long ago, the brewmaster's choices were limited by what was locally available. Traditional styles were defined by those geographical limitations. Now it is possible to get ingredients from nearly any significant part of the brewing world. Having so many choices promotes design freedom but also complicates design if you want to create an original style. Designing with a spreadsheet makes it easier to explore the possibilities.

    A spreadsheet can make calculation of original gravity, color, and bittering levels quite easy. It can easily store and use data on a wide variety of fermentables, providing the brewer optimum flexibility and choice in recipe design. The spreadsheet also provides a convenient place to record ancillary data such as the type of yeast used, details of water treatment, the brand of ingredients used, and other details that can be forgotten when they are not recorded. Serious brewing efforts require that all of the details of the recipe design, brewing, yeast culturing, fermentation, bottling, storage, tasting, and evaluation be recorded in a log book.

    THE RATIONALE

    Each fermentable has a characteristic extract profile, and the degree of extract for a given grain may differ from brewery to brewery. A can of syrup is pretty well defined in terms of degrees of extract per pound per gallon of beer, but a given grain will produce various yields depending on the methods used. The spreadsheet allows you to enter the value that corresponds to each fermentable's yield. Likewise, each type of malt has a characteristic color rating (in degrees Lovibond). That, too, goes into the spreadsheet. (For further information about extract and color values, see references 1-3.) The designer can enter the pounds of each fermentable and let the program calculate the original gravity and color for the beer based on the known volume of finished beer.

    Calculations for International Bittering Units (IBU) are a little more complex. Different hop types have different levels of alpha acid content, and the percent of utilization depends on the time in the boil and the gravity of the wort. Thus, it is necessary to know the volume of the boil, especially if it is not the same as the volume of finished beer. A 5-gal batch of beer, for example, might be brewed with a 2-gal boil. Obviously, the gravity of the wort that is boiled will be higher than the gravity of the 5 gal of wort into which the yeast is pitched. The higher the gravity of the boil, the less alpha acid will be extracted and the lower the hop utilization. I set up the spreadsheet to use a curve fit of the utilization data (4) to make the necessary corrections.

    Note that the hops calculations provide data for bitterness only. No simple mathematical approach yet lends itself to accurately predicting and designing hop flavor and aroma.

    SETTING UP THE SPREADSHEET

    Figure 1 shows the spreadsheet I used to brew a batch of European Dark Lager, which provides a good example of how to use the spreadsheet's capabilities for recipe design. If you are not familiar with spreadsheet programs, you might want to find a friend who is and see if they'll help you out for a couple brews.

    The text box at the top and bottom of the sheet allows you to enter text to label the sheet and to record comments and details about the beer or the method (instructions for creating text boxes are available through your software documentation). The cells in the main portion of the sheet hold numbers that are used for calculations or that are the results of calculations. Cells are referred to by row number and column letter. (Normally, you would not print the column letters or row numbers, but they are shown here for the sake of explanation.) Cell D8 is an inputted value, for example, showing that 3 lb of dry extract are used in this recipe. Cell E25 shows the total calculated value of extract contributed by the fermentables (50.72, which equals an original gravity of 1.05072).

    Enter the final volume or batch size in cell B4. You may want to give such cells names to make them easier to refer to in later calculations (see your software documentation for details). In this case, I named this cell "Gal." For simplicity sake, set the sheet up using the same units of measure throughout (for example, don't mix liters and gallons). The main part of the table is for the fermentables and specialty grains. For each you need its extract value in degrees of gravity per pound of fermentable per gallon of wort; enter these data into the "Deg of Extract" column. You also need the Lovibond color rating per pound of fermentable per gallon of wort; enter these data into the "Color" column. These data are available from various sources (see references 1-3; some catalogs provide this information). In some cases, product suppliers can provide the information.

    At this point, you can save the spreadsheet as a template that can be used when you start a new recipe. Later, when you have a number of recipes on file, you can call up one that is for a beer similar in style to the beer you want to make and modify the sheet according to your goals for the new recipe.

    A fermentable's extract contribution is calculated by multiplying the fermentable's extract figure (column B) by the number of pounds used (column D) and dividing by the final volume (cell B4). To instruct the spreadsheet to make this calculation, enter the following equation into cell E8:

    =ROUND(B8*D8/GAL,2)

    The "Round" function rounds the result to the number of decimal places that appears just before the closing parenthesis (in this case, 2). "Gal" is the name I gave to the final gallons cell (B4). The spreadsheet allows you to copy the equation in E8 down the column to E23. The contributions of each fermentable are totaled at the bottom of the column to obtain the total degrees contributed; cell E25 contains the following equation:

    =SUM(E8:E23)

    The same method is used to determine color contribution (column F), using the color values in column C.

    When you enter the number of pounds of each fermentable, you see the result calculated immediately. The amounts can be varied to adjust the results for both color and original gravity. You can select a cell, type in a new value, and hit "Enter" to see the result; type a new value, hit "Enter," see the result; and so forth. Once you get the hang of it, you can arrive at the desired result very rapidly. If you're experimenting with all sorts of combinations of specialty grains, remember to save or print any results you really like before going on. Later you may not remember what you had. Murphy's Law holds that the recipe you forgot will be the best one.

    The next several cells (lines 26-28 in Figure 1) can be used to record target ranges for original gravity, color, and bitterness. Entering these numbers in the spreadsheet gives you a handy reference to use in evaluating the results of spreadsheet calculations. I use the remaining space on line 27 to record the yeast type(s) that might be appropriate for the style.

    The next table is for calculating bitterness. I set up my program for as many as five unique additions, but that is an arbitrary limit. If, like Byron Burch, you favor complexity of flavor from many different sources, five additions may not suit you. The table can be expanded to suit your style. I am presenting a general method, not an inflexible tool.

    I set up this table to work with hops measured by the ounce. The equations can be modified for gram measurements if desired. Alpha acid content is measured in percent, and time additions are in minutes from the end of the boil. From these data and the volume of the boil, the program calculates the specific gravity of the boil, adjusts it for percent utilization calculations, and calculates the percent utilization and IBU for each addition. Cell F40 sums the IBU contributions and shows the total. The pertinent equations are shown in Table I. Note that if you change the value in cell B4 (the final volume), the values in the total degrees, color, and IBU cells will also change.

    Equations entered to calculate bitterness.

    Cell      Equation
    B34       =B4
    B35       =ROUND(1+0.001*E25*B4/B5,3)
    B36       =IF(B35>1.05,1+5*(B35-1.05),B35)
    B37       =IF(B33>23,0.64*B33-2.62,0.4*B33+3)
    B38       =ROUND(B31*B32*B37*0.7462/(B34*B36),1)
    F40       =SUM(B38:F38)

    It is also possible to do a spreadsheet for adjusting the pH and electrolyte content of the brewing water. The calculations for adding salts to fairly pure water are quite simple and are covered in numerous references (5-8). Adjusting water that has a high level of temporary hardness is more complex and requires knowledge of water chemistry. The water can be adjusted by boiling, precipitation, acidification, or a combination of these methods. Different beer styles may call for different approaches. Water with excessive electrolyte content may need reverse osmosis to lower the total dissolved solids. Once an approach is selected, a spreadsheet can be created to simplify the calculations.

    REFERENCES

    (1) Gregory Noonan, Brewing Lager Beer (Brewers Publications, Boulder, Colorado, 1986), pp. 179-180.

    (2) Randy Mosher, "The Flavorful World of Malt, in Beer and Brewing, vol. 10 (Brewers Publications, Boulder, Colorado, 1990), p. 70.

    (3) G. Bauer, "The Influence of Raw Materials on the Production of All-Grain Beers," Zymurgy 8 (4), 11 (1985).

    (4) J. Rager, "Calculating Hop Bitterness in Beer," Zymurgy 13 (4), 54 (1990).

    (5) Dave Miller, The Complete Handbook of Home Brewing (Story Communications, Pownal, Vermont, 1988), pp. 70-71.

    (6) Darryl Richman, "Water Treatment: How to Calculate Salt Adjustment," Zymurgy 12 (5), 29-32 (1989).

    (7) Charlie Papazian, The New Complete Joy of Home Brewing (Avon Books, New York, 1991), p. 274.

    [Home]  [BrewingTechniques Library]  [Contact Us]  [Order]