Mike's Recipe Spreadsheet

I use this Excel spreadsheet to formulate my recipes. It isn't as full-featured as the commercially available recipe formulation software packages, but it provides all of the basics -- OG, color, and IBU estimation -- to allow you to easily formulate your own recipes.

To download the spreadsheet, right-click here, then save the file to your hard drive. The spreadsheet should be usable with Microsoft Excel (97 or later), as well as the freeware OpenOffice.org org office suite.

Using the spreadsheet

Informational fields are highlighted in gray; these fields are optional. Input fields which are used in the recipe calculations are highlighted in blue. The estimated OG, IBUs and color calculated by the spreadsheet are highlighted in red (at the bottom of the spreadsheet). I suggest you refrain from changing any of the values or formulas in any of the other cells of the spreadsheet, unless you understand what they are for!

As you enter your recipe information, the estimated OG, IBU and Color numbers at the bottom of the spreadsheet will be automatically updated. You can then tweak the ingredient types and amounts, to arrive at the OG, IBU and Color values that you want. Ranges of OG, FG, ABV, IBUs and color for each beer style are available at the BJCP web site.

Generally what I do is work out the recipe using the spreadsheet, then print a hardcopy before I start to brew. I then write my mash schedule, and any other information I want to keep track of (e.g. yeast strain used, fermentation temperatures) on the back of the hardcopy. The hardcopy goes into my brewing notebook.

General Batch Info

Style (optional) -- The style of beer you are brewing.

Code (optional) -- If you keep track of your batches with a code written on the bottle caps (like I do), enter the code here.

Name (optional) -- The name of the beer.

Brew date, Rack date, Bottle/keg date, Prime with/amount, Gravity (optional) -- I write these in (on the hardcopy) as the batch progresses.

Target volume -- The final batch size you are shooting for in the fermenter, including top-up water (if any).

Est Mash Efficiency -- Enter your best guess of your mash efficiency. If you are consistently undershooting your target OG, decrease the number you enter here; if you are consistently overshooting your target OG, increase the number you enter here. Also note that your efficiency may vary slightly, depending on the OG; I get around 75% for low-gravity beers, but only 65% for high-gravity beers. If you are brewing from extract with steeped grains, I suggest using 50% here (it will apply only to the steeped grains).

Boil volume -- The amount of wort you intend to boil (before evaporation losses). For partial-boil extract batches, this will be less than the target volume; for all-grain, this should be the amount of wort you intend to collect during sparging (which will generally be larger than the target volume).

Boil time -- Total number of minutes you intend to boil.

Target OG, IBU, SRM (optional) -- If you are trying to hit specific OG, IBUs and SRM, you can enter them here for reference purposes.

Mashed/Steeped Grains

In this section of the spreadsheet, you enter all of the grains that you will be using in the recipe. The information for each grain is entered on its own row.

Grain Type (optional) -- Description of the type of grain used (e.g. "Pale Ale Malt").

Theoretical Max PPG -- The theoretical maximum yield for this type of grain. (A table of values for common malts is available here.)

Color -- The color (in degrees Lovibond) of the grain. This should be printed on the package the grain came in.

lbs, oz -- The number of pounds and/or ounces of the grain in the recipe.

Malt Extract and Other Sugars

In this section of the spreadsheet, list any malt extract, honey, sugar, etc. (other than priming sugar) which will be used in the recipe. The PPG, Color, and lbz/oz columns have exactly the same meaning as for the Mashed/Steeped Grains section of the spreadsheet.

You may need to guess on the color, since Lovibond ratings for malt extracts and sugars is frequently not specified. If in doubt about the color, you can just leave it blank (but the color calculations will not be accurate).

Also note the Boiled column -- enter an "N" in this column if the extract/sugar will be added at the end of the boil (or directly to the fermenter); enter a "Y" if it will be added at the start of the boil. This information is needed because the gravity of the boil affects hop utilization.

Hops

In this section, you enter the hop additions. Enter the information for each hop addition on a separate row.

Type (optional) -- The type of hops.

AA% -- The alpha acid percentage of the hops. Should be printed on the package.

Age -- If you are using older hops, you can enter the approximate age of the hops (in years) here. The spreadsheet will attempt to compensate for the estimated alpha acid degradation over time, assuming that the hops have been properly stored (i.e. frozen). If this entry is left blank, the spreadsheet does not compensate for the age of the hops.

oz -- Number of ounces of hops used.

Boil Time -- Number of minutes prior to the end of the boil that the hops are added to the kettle.

Recipe Summary

This section lists the estimated OG, IBUs and Color of the beer, as calculated by the spreadsheet. These numbers are updated whenever you change any of the ingredients.

Acknowledgements

The hop bitterness calculations are based on Glenn Tinseth's research. The article on which I based the hop bitterness calculations is here. The "bitterness fudge factors" near the top of the spreadsheet correspond to two of the constants in Glenn's equations; if the IBU values calculated by the spreadsheet seem too high or too low, you could tweak these values to make the calculated IBU numbers track your own brewing system better.

The color and hop aging calculations are loosely based on information from Ray Daniels' excellent book Designing Great Beers.

Questions? Comments?

If you have any questions about the spreadsheet, I can be reached at uchima@pobox.com.

Cheers!

Back to Mike's Brew Page

(Posted to Web June 27, 2004)