My Homebrew Spreadsheet

This is about the spreadsheet that I use to calculate my beers. If you want to download a copy, here is a link to it Link to brewing spreadsheet. I've put it in MicroSoft Excel format (although I use OpenOffice at home) and it is zipped up. It's about 9kB. Many of the formulae were devised from information in Graham Wheeler's excellent 'CAMRA Guide to Home Brewing'. Other information came from various issues of Zymurgy. The hopping calculations are based on Glenn Tinseth's formula at realbeer.com. A formula to calculate IBU is very useful when you're using a spreadsheet, and it works well for me.

There are two worksheets, one for the malt/sugar and one for the hops, and there are effectively three areas to each worksheet - yellow areas, where you enter values, orange areas where calculations are made and blue areas where there are constants which are used to make the calculations.

Naturally if you use it, you may need to adjust some values depending on your system. I'll cover these later.

Let's work through an actual example and assume that you want to make a beer similar to Wychwood's Hobgoblin. We'll look at the malt side first. You can right-click on the image and open it in a new window to see a larger image. Use your browser's 'Back' button to return to this page.

According to Roger Protz's 'Real Ale Almanac' (5th Edition) we're looking for a beer with 2% of chocolate malt (to give it that nice roasty chocolate flavour). Roger says that it's 1053 OG but that's the bottled version - we'll make it 1050, which is the gravity of the cask version. The first thing to do is put a value of 1050 in the OG box at the top left. The almanac says that it's 28 IBU. We'll make 23 litres and do a 90 minute boil, so put 28 in the IBU box, 23 in the Batch Size box and 90 in the Boil Time box. (Don't worry about the other yellow boxes at the top left for the time being.)

Now, going down to the 'Mash' section, you only have to enter the percentages for the malts other than Pale Malt - so we enter 2 in the 'Chocolate Malt' box - and the spreadsheet automatically calculates the 98% of pale malt that is needed to make up the rest of the brew, to the required Original Gravity. Look in the orange 'calculated values' boxes alongside and you see that in this case we need 5154g of Pale Malt and 105g of Chocolate Malt - Simple!

Now let's take a look at the hopping.

The amount of hops in the hop worksheet is done by trial & error. I tried various ways of doing it automatically but this method works the easiest for me. I nomally start my calculations by adding about 15 to 20g of hops somewhere near the end of the boil for flavour and then calculate the amount of bittering hops to reach the required IBU. The Alpha Acid values for the hops in the spreadsheet may have to be changed to what you have and there are 5 different boil times which can be used if you want to do multiple hop additions (these times can be changed if necessary).

The Almanac say that Wychwood use Progress for bitterness and Styrian Goldings for aroma. As this is a fairly straightforward beer we'll just add a main batch of bittering hops for 90 minutes and a smaller amount near the end for flavour, so let's start by putting 16g of Styrian Goldings in the spreadsheet for the last 5 minutes. Looking down at the bottom left under 'Actual IBU', this shows that it would give us 1.2 IBU of bitterness so we need to add 26.8 IBU from the Progress. It's just a matter of putting in various weights in the '90 minute' column against Progress and you soon narrow it down to 42g of Progress, giving an actual IBU of 27.7, or 43g giving 28.3 IBU. My kitchen scales only work to the nearest gram, so I'd most likely use 42g - I doubt if I'd notice, or even care if it was fractionally less bitter that what I was aiming for anyway.

And that's it!! All the ingredients worked out to make a very nice beer.

Notes about the Spreadsheet

The blue areas of the 'Malt' worksheet have a number of constants in them. If you use this spreadsheet you may need to unprotect these areas and change them to suit your system. There are some notes over in column J about the spreadsheet, too.

Cell E2 has the amount of mash water in litres for each kilogram of grain. I use 2.3 times the weight.

Cell E3 has the extract efficiency. I worked this out over a period of time for my system, measuring the Original Gravity produced against what I'd calculated. Your system may be more or less efficient - in which case adjust this value. Once you've calculated it, it shouldn't need changing unless your setup changes.

Cell E4 has a value for the evaporation in litres/hour. The longer you boil your beer, the more you are going to lose in evaporation, so you'll need to start out with more in order to end up with the amount that you want. I've got a measuring stick marked in litres that I used to measure typical evaporation in my boiler over a period of time.

Cell E5 has typical trub losses - this is the liquid that remains in the hops and sludge in the bottom of the boiler, after the boil. You can easily estimate this by measuring how much you've got in the boiler and how much you manage to get out.

Cell E6 has a value for System Heat Capacity. This is part of my method for calculating the temperature of the mash water based on the temperature of the grist, so that when I add a given weight of grist to a given quantity of mash water I know what the resulting mash temperature will be. I used a Fluke Digital thermometer and measured the temperature in the middle of the mash with a temperature probe over a number of mashes in order to work it out.

The principle is explained in an article in 'Brew Your Own' Called Managing Mash Thickness. It's all to do with physics, 'specific heat capacity' and conservation of energy. The idea is that when you mix grain and water at two different temperatures, (the mass of water x the temperature of the water x the heat capacity of the water) + (the mass of grist x the temperature of the grist x the heat capacity of the grist) = the final temperature x ((the mass of water x the heat capacity of the water) + (the mass of grist x the heat capacity of the grist)). The heat capacity (or Specific Heat) of water is known (4.2) so the only unknown factor is the heat capacity of the grist - which is why it takes a few measurements of what actually happens to get a value for it. It also explains the yellow 'Mash Temp' and 'Grist Temp' alongside. If I ever change my mashing system I'll probably have to revise this value slightly, but as a guide I have found that if the grist is 20°C the water has to be about 73°C to give a mash temperature of 66°C - if the grist is 14°C the water has to be nearly 74°C to give the same mash temperature.

The Blue areas entitled 'Extract Potential' and '% Fermentable Sugar' are values taken from Graham Wheeler's books, Zymurgy magazine and hbd's recipator. The Extract Potential is the amount of sugars that might be expected to be released by mashing, and the % Fermentable Sugar is (obviously) the percentage of this which is fermentable sugar. You can see that Cane Sugar is 100% fermentable, whereas Glucose Powder (Dextrose Monohydrate) is only 80% - so if you used the same quantity of glucose powder instead of sugar in a brew your beer would end up with slightly less alcohol, and it would be a bit sweeter with more body.
These figures are used to give an estimate of the final Alcohol by Volume (ABV) which can be seen in cell B3.

There are values for most of the common malts, sugars and hops (at least, the ones that I can obtain) with a couple of 'other' cells if needed, but it's not difficult to extend the spreadsheet if you need to.

If you wanted to add another ingredient in the mash section of the malt worksheet for example, simply unprotect the sheet, copy one of the rows and then insert the copied row. In column A, rename the ingredient you've just added. Then, in column E put a new value for Extract Potential and in column F put a new value for the % fermentable sugar. Protect the sheet again and that's it. The Recipator gives extract potential in '[Specific Gravity]points per pound per (US) gallon', but this spreadsheet is in 'points per kilogram per litre'. To convert from US to metric, multiply the value by 8.3454