jovialmonk.com.au

[The Monk]

Following on from my reply to Lefty (Q&A) I decided to prattle on about setting up a recipe calculator/brew record sheet.

You can use any spreadsheet program you like, Excel or Works or one of the free Open Office things. Open the program and start with a nice empty spreadsheet. Page set up, Landscape A4.

At top left, in big bold letters, type "Name" tab a few spaces & type "Code" so that the name of the beer and the code you write on your labels are recorded first. In normal text on the line below type "Description:" and leave a couple of lines. When deciding on the type of beer you want to brew you should be able to write a sentence describing the beer, plus a sentence or two describing any unusual ingredients you want to use, different process etc.

So below the space for describing the beer we need a line to put some parameters down, so type "OG", hit tab twice, "IBU", hit tab twice, "BU:GU" two tabs then "FG" two tabs then "Alc" -- we want the above bits of text in a cell followed on the right by an empty cell then the next bit of text, etc.

OG & BU should be fixed, no decimal places, the other 3 quantities fixed, 2 decimal places. Assuming we are on the fifth line and started with column A, then in the cell to the right of "BU:GU" type the formula "=D5/B5" In the cell to the right of the text "FG" type "=B5/4" then in the cell to the right of 'Alc' we type in the formula '(B5-H5)/7.45'

Your spreadsheet program will display a 'DIV by 0' error msg in the BU:GU calculation cell. So What. So let us start filling in some values. We will assume we are brewing a beer to drink during the first few AFL matches so type "It's Footy Time" in the spot where the name of the brew goes and "A session bitter for celebrating the start of the footy season, full flavored, thirst quenching and non-bloating"

Now we move down to the parameters line, time to decide what this beer will be like, using numbers. 'Session' means a small low-ish alcohol beer that you can drink a lot of in a session, say 1038, so type '38' in the empty cell next to "OG" The beer is bitter so we are looking for a beer of say 35IBU--we will know it is beer we are drinking!

The computer fills out the calculated fields, .92 for BU:GU, 9.5 for FG (i.e.1009.5) and alcohol (by volume) of 3.83. Neat. eh? The alcohol looks a little bit high for a session beer and the FG could be a tad lower but OK for now.

Go down to line 8 and starting from A8 type "Fermentable", "Weight" "DE" and "22". Into D7 type "Vol (L)." You may want to make the 'A' column wider and 'B' to 'J' a bit narrower. Now the point of the '22' is that we can vary the size of a batch by replacing that with another number (in our Footy Bitter we could increase the volume say to 24L which will reduce the alcohol and FG a tad and slightly reduce bitterness (which we can then correct for as seen later.)

Now under fermentables we will put some generic descriptions you can later replace with detailed info. You could put a blank line in between liquid extract and dry extract, dry extract and grain and grain and sugar. So, this time using the 'Enter' key not tab type in "Kit" Enter LME Enter Enter "LDME" Enter "WDME" enter "DDME" Enter Enter "Crystal 1" Enter "Crystal 2" Enter Enter "Roast Grain 1" Enter "Roast Grain 2" Enter "Roast Grain 3" Enter Enter "Sucrose" Enter "Dextrose" Enter Enter "Total"

So, of course kit is for kit name, LME is for liquid malt (LLME, light liquid malt extract or Amber or Wheat or Dark) LDME etc are our dried extracts then we can use two crystal malts then 3 malts then two sugars. In a real workseet we would put the actual name (e.g. 'Morgans Aust Lager' and say carapils and cararom for the crystal etc. Sucrose is white can sugar, if using the Billingtons sugar say 'Billingtons Muscovado' etc etc.

So that is just labels, we now go down the 'C' column putting in values. Against Kit and LME type '303' the dried extracts are all 364, the crystal malts 201 and the roasted malts (black patent & choc malt and Roast barley) 195.

Sucrose is 375 (Muscovado sugar is 10% water so reduce that to 338. Dextrose and invert sugar are 319.

Now, we need a formula for the 'D' column, gravity. I like to specify weights as grams not kilograms, neater without all the dots and leading zeros. So, go to cell D9 and type in the formula (B9*C9)/(D8 * 1000) Copy this down to D24 or 25, make sure that 'D8' occurs in every cell. Now we need to total that up so click in cell D26 and type in the formula 'sum(D9..D25)' Format the D column (or at least D9..D26) for Fixed number, 1dp


OK, we need to use this to work out a fermentables bill for our session bitter. For maximum flavor we won't use a kit just a can of light malt extract, so put '1500' in B10. Now we want some wheat malt for head retention & mouthfeel so let us put '300' in cell B13. Marvellous, the computer calculates our beer as having 25.6 OG units! 11 to go! So let us put in some crystal malt and a pinch of roast grain, so change cell A16 from 'Crystal 1' to 'carared' and type '250' in B16, then change A19 to 'Black patent' and type '75' into B19. OK, we now have a total of 28.6 gravity units, let us see HM light dry malt extract we need to hit our target of 38 gravity units. Hmmm 1000g gives 45GU, 500g gives 36.8 so 550 should do the trick. Yup, 37.7GU, exactly!

Now we thought 3.83% was a bit high (we make up by drinking LOTS of it the alcohol that is not in the beer!) so let us change our volume (cell D9) from 22 to 24 and see that the OG drops to 34.5, perfect! We now type the new OG into cell B5 and see that our BU:GU increases to 1.00, our estimated FG drops to 8.8 and our alcohol to 3.52, perfect for a session bitter!

While we won't add hops to our worksheet just yet you do realise that to keep the bitterness constant we will have to add more hops to our slightly bigger than normal volume.

OK that is all for today, I will upload the worksheet to the website for you to download. You can spiff it up a bit with color, lines etc, as long as that makes clearer what each bit is doing.

[The Monk]

I have uploaded it to www.jovialmonk.com.au/worksheets/kits as "Basic to OG" but unfortunately I have no idea what the suffix is for Excel spreadsheets--can some kind soul let me know what it is? I use neither Windows not Office.

Also, can you let me know how it looks in Excel, a pdf of the document as it looks on your screen would be great.

Try playing with the numbers a bit, see how it goes.

We need to add volume of boil, from which we can calculate gravity of the boil from which in turn we can calculate hop utilisation and so bitterness.

On the second page you can add details of kit name & date, yeast type/name/date/preparation, time boil started, hops added etc then allow 10-20 lines for tasting notes etc.

If anyone ends up with a spiffy worksheet I will be happy to upload it for others to use.

Please advise me of any problems locating/downloading/opening in Excel etc etc.

[KingDuck]

G'Day Tom

Excel extension is *.xls. I'll email you the screenshot - not sure how to add images in here easily?

The calculation seem to work from first glance. There is no 2nd page though?[/img]

[Timmsy]

Not sure what happens but i cant seem to load it up. I comes up in notepad i think?

[The Monk]

I think you need to add '.xls' after the file name Timmsy--thx KingDuck!

Images could be uploaded as PICT to the same worksheets-kits folder. Second page doesn't really have calculations, just a place you can flesh out the details and record of actual brewing e.g. "Boil started & 'A' hops added at 10.35" sort of stuff

I have done more work on the spreadsheet, adding in boil volume, hop utilisation & IBU calculator. About as far as I can go with a kit worksheet I think. I will put a lot of description about the changes here then upload the completed version late today.

[The Monk]

Had a look at the screenshot, looks good. Sure it would but I had no way of checking!

I will upload the more advanced version now, see what you think. I think in Excel you could add a lookup function so that the correct hop utilisation is added depending on the boil SG. I have no way of doing that--should download one of the Open Office suites and see if that spreadsheet program will do it.

[The Monk]

OK. Uploaded the 'complete' worksheet and renamed the Basic to OG to add the Excel suffix--should work now, Timmsy.

What I have put on page 2 is just a sketch of what would be put there. Once you have it as you like, print out a few copies of just P2, have that handy then on brewday put the brew name/code/date on the sheet and write on it as you do the brew--timings, observations, details of the kits etc.

Once the brew is in the fermenter open the worksheet for that brew and type in all the details. You now have a computer record of all your brews!

You can tizzy the worksheet up; lines, color text whatever but if you move lines ensure that the formulas still work etc. Bit of white space left, you could use 10pt italic text to add annotations etc.

OK in a minute will upload a minor update of the complete worksheet. It contains a sample worksheet--values I have entered in blue. Note that I have included the 300g dextrose in the boil SG calculation but not the liquid wheat malt: the reason being that the liquid malt is added right at the end of the boil. If we had used dry malt extract in place of the liquid then I would have put the dextrose in at the end of the boil to keep the boil SG low and so the hop utilisation is high and the quantity of expensive hops to be added low.

You might now look at some my Packs that you have brewed and see how I have juggled all this to keep the price of my Packs low! If using a lot of dry malt extract remember to cut this 20% or so with dextrose and a small amount of sucrose if desired (e.g. dark brown sugar in a stout)

[Timmsy]

Nice work

[The Monk]

If there are any real Excel wizzes out there they could tizzy the sheet up and upload it with a filename that includes their name or handle. Could add say, a calculation for water used/recovered with dark grains that are cold steeped (5 times as much water by weight as the grain, assume 65-75% of that is recovered plus 100ml sparge water all of which is recovered, a lookup function for working out what set of utilisation figures to use.

I haven't put a copyright notice on it for the obvious reason that the formulas and values I used are public information. I use this sort of worksheet to calculate recipes for series 2 Packs, Part mash Packs and full mash recipes.

If you want to use the sheet to calculate out my Packs well you might be in luck or not I will try to present what information I got out of common brewbooks but any unusual recipe I worked out myself I might not disclose, sorry.

The recipe should be more to design a beer you want to brew: 'Crisp summer ale,' 'smooth malty well matured winter warmer,' 'dinner ale' or 'Session bitter' etc. Once you know the type of beer you want to brew summarize it in a sentence or two."Summer ale: a crisp and refreshing low alcohol thirst quencher."

Then work out a target alcohol level--enter this up on your sheet, then start entering some figures in the OG column, see how this affects FG and estimated alcohol content, next work out bitterness level, not too high in summer ale or session bitter, a beer to be matured should start with a fair bit of bitterness (BU:GU of about .75-1)

To get the FG down use some dextrose or other sugar, for a maltier beer use more liquid rather than dry malt which does not need to be cut with sugar, etc. Roasted grains are not very fermentable and so will increase FG.

Also, keep the crystal/caramalt way down for beers to be aged or the caramel will become overpowering, 200g or less where for the summer ale you could put in 500g of crystal to improve malt flavor--this beer will be finished by the time the caramel glug starts forming.

Even in a light beer you might use some dark grains! 25g of chocolate or black malt won't add much color but will add nuttiness. For a medium colored beer you might add say some very light crystal malt, some medium and then 50g of a roasted malt, this way adding more complexity to the beer than just adding a small amount of medium crystal malt!

Another way of adding color is to make say 8L light colored wort, then put this on very low heat, mostly covered by the lid and let it simmer at 'a bare shiver' overnight. The next day this wort will have darkened with lots of lovely melanoidins (browning reaction) formed and some mild caramelisation, way superior to adding, ugh, crystal malt!

Similarly with hops. For a less bitter beer try boiling for an hour but adding the bittering hops at 30 minutes. Because this gives lower utilisation you need to use more hops and that and the shorter boil means a lot of the flavor will carry though into the final beer.

Blend hops! I have worked out a lot of the recipes in the Wheeler recipe books (based on a survey of commercial european breweries and when 2 bittering hops were used they were added so each hops gave the same number of IBUs to the beer (so they would use twice the weight of hop1 with 5% alpha acids as they added of the 10% hop 2.

To get much flavor from 60 and 30 minute hops they should be full-flavored hops like Amarillo or Fuggles.

Dryhop! When fresh plugs are available add one or two to the cube for huge hop aroma! Put a note on p2 of the worksheet to describe the type & plugs added, date this happened and period and temperature the beer was kept at in cold conditioning, etc.

In the mash worksheets I will add percentages and FG calculations--you could shoehorn either or both of these onto the kit worksheet but what are the figures for the kit??

In the kit worksheet note I have a cell to insert the kit IBUs and then the balance of bitterness that needs to be made up from the hops. So what is the IBUs of the kit in your hot little hand? For a lot of the 'ordinary' kits you can assume 21IBU (Coopers Original Series Lager & Draught, Canadian Blonde & Pale Ale and likely the Euro Lager, Morgans Australian Lager & Pilsener.)

The better quality Coopers & Morgans tins I have the IBUs listed in the Forum somewhere and an eager beaver could put these into an Excel spreadsheet for uploading into the worksheets/kits folder on the website--any volunteers?

[The Monk]

Hmmm well, quite a few people have looked at this Forum thread and from the webstats I know some have at least looked at the worksheets, so, what do you think?

Like the looks of the sheet? Tried calculating out a beer? Think it is all a waste of time? Please let me know! Comment here or send an email or come into the shop if you need further explanations!

[KingDuck]

Tom, I think it's a good idea to have these calculations available for all. Unfortunately for me, it's too late. I've already made my own spreadsheet, after working out all the brewing equations. However for those that haven't been through this process, it will be handy!

It would be good to include a print-out of the sheet with your packs (unless it's a secret recipe of course).

I also decided to buy a licence for Beersmith - which really is a great bit of software. I can recommend it.

When I get some time, I might fix your spreadsheet up a little, or even share my with you.

Keep up the good work!

KingDuck

[The Monk]

I must admit, having put the worksheet up, that I am ambivalent about the real value of brew calculators.

The real value of the commercial calculators like Promash & Beersmith etc probably is calculating water additions when doing a multi-step, multi-infusion mash and for calculating color. The danger of these calculators is that you type in, say, 'Bitter' as the beer style and it just pops up OG & BU ranges and you think you 'know' all about the beer style! Besides which the values they pop up are just the BJCP mandated values which I think are a joke and bear no relationship to the history and evolution of the style!

For the brewers out there use the sheets to create a computer record but for a year or so calculate the SGs and IBU's with paper pen & calculator! Really!

Start by writing down the beer name & style, then write down the formula for calculating gravity

SG = wt * de/V

Then write it re-arranged to give the weight of a fermentable needed to yield a particular gravity

wt = SG * V/de

Then calculate out the values for whatever fermentables and volume you are using.

Then determine your bitterness level for the beer, usually by consideration of the desired BU:GU level for the style

Hefeweizen .3-.4
Mild, Scottish, Bock BU:GU =.5
Bitters, Old Ales, Barleywines BU:GU .8-1.25

Then write down the formula for calculating IBU

BU = wt * AA * U/(10 * V)

And write the formula to calculate the weight of hops to give the right bitterness:

wt = BU * 10 * V/(AA * U)

Now when you have done it this way for a year or 10-15 brews you will find those equations are fixed in your brain! I can be on the phone to someone, with customers waiting, and advise on iBU or weight of hops just using the calculator! You also get a feel for quantities and whether those given in a published recipe are right or wrong. then you can start using brew calculators to calculate recipes.

And to get to know the styles? read some books, buy examples of the various styles and drink them! Don't let some faceless committee without any real feel for beer tell you the parameters for a beerstyle!

[The Monk]

I have put a link to the kit spread sheet on the contents page.

I will have a look at the partmash worksheet and perhaps improve it then add a link to that as well.