Diesel’s Brewing Spreadsheet is now available for public use.
A full guide and download are available at http://dieseldrafts.com/spreadsheet.
The feature set includes:
- Worksheet – The soul of the spreadsheet, were the main action goes down.
o Batch Size is set by desired packaged volume of beer. Includes adjustments for post fermentation trub & transfer losses, kettle transfer losses, wort contraction, hops absorption, boil off rate, grain absorption, lauter tun dead space, and kettle dead space
o Mashing modes include Infusion, Decoction and Direct Heat. Predicts strike water volume & gravity, decoction or additional infusion volumes, mash out and sparge volumes. An indicator of total tun space required is given.
o Grains are selected in a dropdown that is based upon the Grains tab, all attributes of the grains are pulled from there.
o MCU, SRM, GU, Adjusted GU (based on mash efficiency), Weighted Adjusted GU (if extract is added late) are all calculated based on the weight of the grain and it’s properties on the grains tab. Extract GU Factor for weighting late additions to boil gravity is user configurable.
o Hops are selected in a dropdown that is based upon the Hops tab, all attributes of the hops are pulled from there. AA% percentage can be calculated based on hops purchase date, storage method and storage temperature.
o Utilization can be based on either Rager or Tinseth. FWH, leaf and pellet factors are user configurable. Boil gravity is the average of pre-boil and post-boil gravities.
o Yeast are selected in a dropdown that is based upon the Yeast tab, all attributes except for inventory are kept on the Yeast tab.
o Required Cell Count depends on the user configurable ale & lager factors and whether the recipe is set as an ale or lager as well as volume & gravity. Pitch volume depends on the type of yeast preparation and its cell concentration on the Tables tab.
o Pressure & Priming Sugar are shown for desired level of carbonation. User must set serving temp and choose sugar type (pulled from a select set of entries on the Grains tab). Beer temperature is assumed to be the warmest the beer was during fermentation.
o OG & FG in specific gravity and plato, ABV%, IBU, SRM, CO2, BUGU & BV are calculated for predicted and measured values and are comparable to two styles in a table. Measurements can be entered in either Specific Gravity or Plato. Styles can be from either BJCP or Brewers Association Guidelines.
o Mash Water Adjustments are based on the concept of residual alkalinity and John Palmer’s work. Predictions for adjusting pH, adding enough calcium and magnesium and achieving desired chloride to sulfate ratios are included. City or target profiles may be referenced in the table.
o Water attributes used in water calculation are pulled from the Water tab.
o Parti-gyle brewing mode will split the GU between the beers from the mash. The second beer’s pre-boil volume is determined by the first beer’s pre-boil volume & the parti-gyle ratio. Extracts and other ingredients added are specific to each beer, only mashed grains are split.
o Macro buttons are available to: Export the Worksheet to the Recipes file as a new tab or to overwrite an existing tab, Export the Recipe to a file for Sharing, Archive the Worksheet to a JPEG file, Add the grains, hops and yeast in the recipe which are not currently in stock to the Purchasing tab, Subtract the grains, hops and yeast in the recipe from the inventories on the Grains, Hops and Lab tabs, Toggle the Gravity Mode between Plato & SG, Reset the Worksheet to a clean starting position.
o Ample room for note taking.
- Calculators – A collection of calculators useful in a brewing operation
o Temperature, Volume, Mass & Pressure conversions
o Specific Gravity to Plato & the other way around
o Refractometer Correction based on starting & ending readings, calibration available with reference to a hydrometer
o Hydrometer temperature correction
o White Labs to Wyeast converter & vice versa
o Yeast Pitching Rate Calculator
o Yeast Propagation Calculator
o Weight of the Volume of a Liquid based on Gravity
o Beer Properties based on OG & FG: AA, RA, RE, ABV, ABW, Calories
o Beer Properties based on FG from Hydrometer & Refractomer: OG in Plato & SG, RE, ABV, ABW, Calories
o Hops Aging calculator for current AA%
o AAUs to Ounces
o IBU or Ounces based on the other
o % Lost based on Hops Stability Index
o Boiloff Rate
o Area & Perimeter: Rectangle, Circle, Triangle
o Surface Area & Volume: Rectangular Prism, Sphere, Cylinder, Cone, Dome, Pyramids 3 & 4 sided
o Unit prefix converter
o Grain color converter: EBC (old & new), ASBC/SRM, IOB
o Grain to Extract and Extract Equivalents
o Water Chemistry a la John Palmer
o Efficiency based on grains and measured gravities & volumes
o Efficiency based on measured gravities & volumes and inputted potential GU
o Recipe Scaling based on pounds or percentages, ounces or IBU
o Carbonation & Priming
o Electrical Heating
o Predicting BU required based on gravities and desired BU:GU or BV
o Mash Tun Space Required in metric or English
o Parti-gyle Adjustments to correct the first beer if it is off
- Calendar – great for planning a brewing schedule
o Calendar Display is set by choosing Month & Year
o Up to 7 brews can be displayed at one time, the Worksheet is always displayed
o Macro button will update calendar data from Recipes file
o User can select up to 6 recipes from the Recipes file to display in the calendar
o Calendar shows brew date, primary time, diacetyl rest, cold crash, conditioning and aging times and tap date
- Grains – inventory and grain attributes are stored here
o Malt Name
o Country of Origin
o Color (ASBC/SRM)
o Dry Yield
o Potential (calculated based on dry yield)
o Stock in pounds
o Price per pound
o Priming Factor used for priming sugars
o Link to store for purchase
o All the above can be filtered
- Hops – inventory and hops attributes are stored here
o Hop Type
o Alpha Acid %
o Commonly used in styles
o Possible Substitutions
o Usage as aroma, bittering or dual purpose
o Stock in ounces
o Form as pellet or leaf
o Price per ounce
o % Lost
o Date of Purchase
o Link to Purchase
o All the above can be filtered
- Yeast – Yeast attributes are stored here
o Yeast Strain
o Yeast Name
o Low Fermentation Temp
o High Fermentation Temp
o Low Attenuation
o High Attenuation
o Alcohol Tolerance
o Manufacturer’s Description & Notes
o Original Brewery
o Wyeast, White Labs Counterpart
o Price per package
o All of the above can be filtered
- Lab – Inventory of yeast cultures & lab equipment
o Yeast Strain
o Unique ID
o All the above can be sorted
o Equipment, Notes & Quantity
- Water – Brewery’s water profile here is used for Worksheet
o Brewing Cities’ water profiles for reference
- Styles – Based on the BJCP 2008 Style Guidelines, data is used for reference in Worksheet
o Style Name
o Link to BJCP & Style #
o OG range in SG
o FG range in SG
o OG range in Plato
o FG range in Plato
o ABV% range
o IBU range
o SRM range
o CO2 Volumes range
o BUGU average
o BV average
- CO2 – a wide range chart
o Increments in variables are 0.1 CO2 volumes & 1 degree Fahrenheit
o Ranges are 32 to 80 degrees and 0.5 to 5 volumes
o Prints well onto 1 sheet if you have a color printer
- Tables – various reference tables used in Worksheet calculations
o Yeast Starter Size – based on Jamil’s chart in Brewing Classic Styles but this method of starter prediction is no longer used.
o Yeast Source & Concentration
o Chloride/Sulfate Balance for water calculations
o Hops Storage Factors for hops aging calculations
o Hops Aging Temperature Factors
o Hops rate k constant based on percent lost
o Approximate % Lost based on hops variety
o Sizing Wire & Breakers for reference
o SRM to RGB for reference
o SRM to hexadecimal for reference
o Hydrometer CF for reference
o CO2 Volumes is residual CO2 in fermented beer
- Formulas – a reference of formulas used
- Brewhouse – inventory for the brewhouse
- Brewpub – inventory for the brewpub
- Packaging – inventory for packaging beer & keg tracking
- Library – inventory of the library
- Recipes – a tab for jotting down in plain text recipes as you receive them
o Includes a link to launch the external Recipes file
- Purchasing – a financial sheet for planning purchases
o Item, Price, Quantity, Total, Link, Notes and Category can all be filtered
o Total Cost of sheet is calculated
o Categorized costs shown are: equipment, hops, yeast, grain, books, glassware, artwork, memberships, shipping and uncategorized in case something is missing a category
o Priority Purchasing total depends on the total price being bolded
o Next Batch costs requires the total price to be italicized
o Includes a mcmaster partnum link for planning mcmaster purchases
o Macro button will move selected items to Financial tab when they are purchased
- Financial – a financial sheet for tracking and analyzing purchasing history
o Item, Price, Quantity, Total, Link, Notes, Category and Date can all be filtered
o Total Cost of sheet is calculated
o Categorized costs shown are: equipment, hops, grains, yeast, other ingredients, ingredients combined, books, glassware, artwork, equipment & assests combined, memberships, chemicals, shipping annual average, batch average, unreceived orders and uncategorized
o Items not yet received are italicized when moved from the Purchasing tab.
o Macro button will mark items received and add them to the inventory if they are hops, grains or yeast
- Notepad – a plain text sheet for keeping random notes