Untitled Document

Workflow Guide

My workflow with this spreadsheet is to use the Worksheet tab while initially designing a recipe. Once it is close to being solidified I export it to a new tab in the Recipes file. I then continue to work with it in this file as the beer progresses. When the beer is finally done, I lock the tab to prevent accidental modification of the recipe's details. As long as the two files are kept together in the same folder, the recipes will be able to reference the data in the Brewing file. Note that when the ingredient attribute data is updated in the Brewing file, the changes are reflected in all worksheet tabs, this includes locked tabs in the Recipes file so the derived values in that sheet will change. I am okay with this as I will keep updating the recipe tabs with the latest version of the recipe. I will keep making notes as I drink the beer, but to create historical snapshots for each specific brew, when I lock the tab (and am done with that beer) I archive the recipe to a JPEG. I'll always have the latest version of the recipe in the Recipes file, but also will be able to reference historical changes if needed. I name the JPEGs like "Example Ale YYYY.MM.DD.jpg" and everything is easily sorted. You can do simple filename searches to see all brews from one year or month, etc. I don’t default to this file name because I will often make a jpeg to send to friends when discussing an upcoming recipe. When I am going to repeat a recipe I will work it up in the Worksheet in the Brewing again using the Recipes file as a reference, and then export it back to the Recipes overwriting the tab. I do this because the Worksheet will be the master record of my brewery, and variables can change so this keeps the data up-to-date.

When I have finished designing a recipe I will add the recipe to the purchasing sheet. There is a button on the Worksheet which will add the grains, hops and yeast to the purchasing sheet if there is not enough stock on hand. This will work from a tab in the Recipes file as well. I will edit the quantities if I'm going to make a bulk buy or merge duplicate entries (which will happen the same ingredient is listed multiple times in a recipe). Once I have bought the ingredients I will use the button on the Purchasing tab to move them over to the Financial tab. They will be italicized there symbolizing that they have not been received yet. I do a lot of shipping orders. Once the ingredients are in stock, I use the button on the Financial tab to mark them received and if they are hops, grains or yeast the inventories will be updated. After brewday, I revisit the recipe and update it for what was actually done, and then subtract it from the inventory with the button on the Worksheet, this of course works for tabs in the Recipes file as well.

While working up a recipe for future planning, I like to use the Calendar tab to efficiently use my fermenter and cold storage space. This feature took some thinking about in order to get a design that I liked and worked. There is a macro for updating the stored data on the sheet from the Recipes file, but the Worksheet tab will always be represented live. This was one feature that I had to have in my spreadsheet, once I tried it in BeerSmith I couldn't be without the ability to look at a calendar for planning.

Frequently Asked Questions

I cannot open the file. Or when it opens I see lots of errors. How come?

Why are so many values in the spreadsheet blank?

How do I do an extract brew in this spreadsheet?

How do I enter Dry Hop additions into the spreadsheet?

How are the IBUs for First Wort Hopping (FWH) additions calculated? How do I modify it?

Why is the tab locked? What is the password?

How do I modify the AA% or other attributes of ingredients? I can't do this in the Worksheet.

I cannot open the file. How come?

This spreadsheet uses the new Open Office XML format that Excel 2007 uses as its default. While you can install a compatibility pack to open these files in older versions of Excel, the formulas in the spreadsheet have too many conditions and I use too many names for the older versions to work. I am investigating the limits of the older versions of Excel and will keep you posted on a downlevel file's progress if we can get around this issue by breaking up the formulas and deleting some names and referring to cells by ID.

I assume that Mac Office 2008 should work almost flawlessly, it's Microsoft's latest version of Excel extending 2007 to the Mac. My guess is that any problems will center around the file system access, especially if you try to use a subfolder when exporting a recipe or jpeg. Let me know if you use this on Mac so I can confirm this or look for resolutions to any problems.

OpenOffice support is ~~not currently planned~~ has been planned due to demand. All of the macros will need to be rewritten, as well as all of the formulas due to the fact that OpenOffice interprets both differently. This work was hopefully further down the line for me than full support for opening an Excel file is for OpenOffice. If you happen to use OpenOffice, check in on this thread in the forums, and let me know you're interested in seeing this built..

Why are so many values in the spreadsheet blank?

Rather than allowing calculation errors to be displayed, I have tried wherever possible to validate the inputs of every formula. So instead of seeing #VALUE or #DIV/0 in the boxes, you should hopefully be just presented with a clean sheet until you have entered in all the data required to calculate the value in that cell. For instance, Estimated FG will not be displayed until you have entered a yeast strain selection, which might not be immediately clear. If you are having trouble with a certain value, please let me know. I can help you out or resolve the issue in the code if there is one. Another not so obvious instance is when you enable Hops Aging you must enter in the % Lost and the Date Purchased in the Hops tab for IBU & Utilization to work. Similarly Utilization requires that some amount of fermentables be added to predict original gravity which is a factor.

How do I do an extract brew in this spreadsheet?

The Grains tab includes extracts in there. Be sure to add the specific ingredients you will be using if they are not already listed, and to modify the attributes to your needs. When composing a recipe in the Worksheet tab, you need to change the "Late?" column's dropdown for your extract addition to be either "Yes" or "No" depending on whether or not you are going to add the extract to the boil at the beginning of the boil (choose "No") or add it later around 15 minutes left (choose "Yes"). To configure how much a late extract addition impacts the Pre Boil Gravity (and thus Utilization) you can adjust the Extract GU Factor in the Equation Factors section.

How do I enter Dry Hop additions into the spreadsheet?

In order to enter in your dry hopping additions, simply enter "dry hop" for the time value on the Worksheet. No IBUs are contributed to the beer when using dry hop additions.

How are the IBUs for First Wort Hopping (FWH) additions calculated? How do I modify it?

In order to use hops as a FWH, simply add "FWH" for the time value of the addition. The default is to assume 10% more utilization than would happen if the hops were added for the entire boil. While utilization actually goes up, some people prefer to have their FWH impact the IBU calculation like a 20 minute addition. In order to do that you will need to modify the FWH Factor in the Equation Factors section of the Worksheet. Divide 20 by the total time of your boil, and use that result as your factor. The default is 1.1 corresponding to a 10% increase in utilization. If you want to set it similar to a 20 minute addition with a 60 minute boil make it .33, for a 75 minute boil .27, and a 90 minute boil set it to .22.

Why is the tab locked? What is the password?

The password for locked/protected content is "unlock" and locking is used for tabs that are mostly static and I want to prevent accidental modifications from happening (such as previously brewed recipes and chart tabs). I also locked the Worksheet, Calculators and Calendar tabs except for the cells that need to be modified for use in order to prevent accidental modification of the sheets. The equations were compiled from books, the internet, and other brewing spreadsheets and are documented in the Formulas section.

How do I modify the AA% or other attributes of ingredients? I can't do this in the Worksheet.

The attributes of the ingredients are stored in the Hops, Grains and Yeast tabs respectively. These tabs are not locked. To edit AA% go to the Hops tab and change it to match the values you have in stock. If you have multiple types of the same kind of hop (say you have pellets and leaf, or say you have purchased from two different sources) you can add more copies of the same variety too and then modified each one to match. My one word of caution when doing that is to make sure each one has a unique name otherwise the look-ups that check the attributes like AA% will only find the first one, so the names must be unique. You can do something like Cascade '07, or Cascade, P (for pellets) or Cascade, HD (for Hops Direct).

To create a copy of a variety what you do is you go to the Hops tab, click on the hops variety that you want to duplicate. Hit Ctrl-Space to select the whole row, Crtl-C to copy it, then hit Ctrl-Shift-+ (Control Shift Plus), and this will insert a copy, then change the name of one of them.

If you want to insert a new variety that is not listed, find the spot that you want to insert it and click the variety that will be right below it. Like if you were inserting something called Fast Hops (just making up a variety name) then you would click on First Gold if you want to make it alphabetical, then hit Ctrl-Space to select the whole row, then hit Ctrl-Shift-+ to insert an empty row (same thing as before except without copying first). Then you can go in and fill all the information.