ABS Alcohol Blending Software - Version 3.3 Blending alcohol is an integral part of distilling operations.To assist in this process ABS Alcohol Blending Software has been developed to perform the calculations necessary to accurately and reliably blend alcohol in a distilled spirits plant.

Based on 10 years of experience in operating a licensed distilled spirits plant. This suite of functions has been developed using the prescribed tables and algorithms currently in effect in the United States.In addition, the results obtained by looking up values in the Government tables have been expanded to convey more information about alcohol processing operations by making explicit the results which a simple manual lookup will not convey without further mathematical operations.By allowing the worksheets to do the lookups for the user one can obtain all the information available about the alcohol being processed each and every time the function is performed.System Requirements. All programSpreadsheets are compatible with Excel 1997-2003. To learn more about the software download the following PDF and scroll through the functions. Turn on page scrolling in the Adobe Reader and just stop at the pages with data on them.

New: Version 3.3

What is most noticeable about version 3.3 is that many of the underlying calculations are visible so you can see how the results are derived. Also, more of the worksheets can be copied and pasted into new workbooks so users can modify them to suit their purposes. These are mostly the, batch planning, accounting, component cost and inventory worksheets.

The the text of the user’s manual is vastly improved and the entire manual now runs to over 125 pages of examples and text. A list of the more important improvements included in version 3.3 follows: 1.Ability to blend to a target proof accuracy of 1/10^{th} Proof or (5/10,000) in all worksheets. 2.Ability to set individual blending tolerance values in each worksheet. 3.A method for demonstrating Water Volume change and Alcohol Volume Change with temperature . 4. Finding Present Volume from Apparent Proof and Weight. 5.Hydrometer Thermal Expansion or Contraction correction. 6.Adding calculated wine and proof gallons per case and bottle values to most blending sheets. 7.Demonstrating calculations with intermediate steps in each worksheet. 8.Volume correction to destination volume other than present volume or 60° F. Volume. 9.Ability to use hydrometers calibrated for temperatures other than 60° F. 10.Blending by specific gravity with hydrometers calibrated for temperatures other than 60° F.

I hope these changes are useful and helps make blending and accounting for alcohol easier.

This is a read only version of the program. This will allow you to see how the functions are set up and what the results of various inputs will be. For a brief overview the following list of functions is provided.

The single best reason to use ABS Alcohol Blending Software is to make the § 30.23 calculations which are outlined in the Gauging Manual in §186.23 for you each and every time you look up a value for alcohol.

Distillers must keep in mind that Table 1 can only be used directly to correct the temperature and Apparent Proof of Alcohol blends to 60°F ONLY in the special case where both temperature and Apparent Proof are whole number values. Since this almost never happens when actual measurements are taken of spirits this table is truly a Special Case table only. To find true corrected proof to 60° for intermediate temperatures and intermediate proofs one must us the § 30.23 procedures which are outlined in the Gauging Manual in §186.23. One must look up the next higher and lower temps and higher and lower proofs in Table 1; then measure the differences then add and subtract them in the righ order in order to arrive at an properly corrected proof. This extended and math/lookup intensive procedure is run by ABS Alcohol Blending Software each time a calculation is made as required by the regulations.

The software consists of 60 plus functions which, in addition to blending operations, perform functions in the following areas:

Alcohol Properties - Find True Proof @ 60°F and other properties of One Alcohol using Expanded Table 5 and 6

Alcohol Properties from TTB Tables Only

Batch Plan Plain -Simple way to plan the size of a bottling run

Blend One Alcohol to Target Proof by Volume using Table 4 then adding components by weight or volume

Blend One Alcohol to Target Proof by Weight of Alcohol and weight of Water using Table 4

Batch Plan for 1 Alcohol Blend by Volume Using Table 4 linked to blending sheet and bottling log

Blend Two Alcohols by Volume using Table 4 then add components by weight or volume

Blend Two Alcohols in a Ratio to each otherby Volume using Table 4 then add components by weight or volume

Blend Two Alcohols by Weight using Table 4 then add components by weight or volume

Plan Batch & Blend Two Alcohols by Volume Using Table 4 Linked to a Bottling Log for that batch

Blend one alcohol using specific gravity and volume with Table 4

Blend one alcohol using specific gravity and weight with Table 4

Using Specific Gravity readings - Find True Proof @ 60°F and other properties of 1 Alcohol in Air

Gauging Drums of Alcohol

Withdrawing Proof Gallons Using Number of Proof Gallons

Blend & Sum using Volume Find Unknown Destination Proof to 1/10 Proof and Reduction in Volume due to Blending.Also, Using Volume to Determine Weight and Proof Gallons of Two containers of Alcohol.Also calculate % alcohol by volume and weight

Blend & Sum using Weight Find Unknown Destination Proof to 1/10 Proof and Reduction in Volume due to Blending. Also, Using Weight to Determine Weight and Proof Gallons of Two containers of Alcohol. Also calculate % alcohol by volume and weight.

Weight & Apparent Proof to true Proof & 60° F. volume & P.G.Then find Present Volume.Apparent Proof to Present Volume. Proof Gallons Obscured, % of expansion of alcohol and water.

Using Weight and Proof To Find Proof Gallons and Wine Gallons using Table 4.

Using Wine Gallons to find Weight via Table 5 to whole & 1/10th Proof values.

Using Proof Gallons To Find Weight & Volume Using Table 5 Whole Proof Rounding & Table 4 1/10th Proof accuracy too

Fill a Container using Weight, Temperature and Apparent Proof

Volume Correction from Present Volume and True Proof to 60° F. Volume

Sight Glass or known volume.Present Temp and Present Volume to 60° Volume Using Known True Proof.Find 60° F. Proof gallons and Wine gallons.

Sight Glass or Known Volume.Present Temp and Present Volume to 60° Volume Using Apparent Proof.Find 60° F. True Proof as well as Proof Gallons and Wine gallons.

Translate Present Volume to Volume at any Temperature

Volume Correction to find Capacity of Container - Present Volume Correction

Volume Correction from present volume & apparent proof find 60° F. Volume & True Proof & Proof Gallons

Volume Correction from True Proof & 60° Volume to Present Volume

Volume Correction from Blended target proof and 60° F. Volume to present volume Geared towards Bottling Volume Correction

Correct the Volume of Blending Water Used in Batch when adding water by Volume

Bottling LogAlcohol with Volume Correction + Processing Report and Storage Report Calcs.

Production and Withdrawl of Alcohol Monthly & Quarterly Excise Tax and Revenue Reconciliation

Costs Per Bottle

Proof Gallon order for blending and bottling

Batch Flavor Additions to Volume That Reduce the Volume of Water Required in a Batch

Flavor Additions 2Change Brix of Alcohol and track volume change by addition of Sugar and Other Solids

TTB public domain sheet for calculating Flavor Additions

Tax on Adding Flavors & Wine as a component of blending operations

Increase Proof of any Alcoholic Mixture -Make Port with Wine

Obscured Alcohol - Using Weight to find correct number of proof gallons in obscured alcohol.

Predict Obscuration of Proof by addition of solids to Weight of Alcohol

Predict Obscuration of Proof by addition of solids to Volume of Alcohol

Use Weight to Blend One Obscured Alcohol to a Target Proof using Table 4

Use Volume to Blend One Obscured Alcohol to a Target Proof using Table 4

Adjust for Hydrometers Calibrated at Temperatures other than 60°/60° (15.56°/15.56°) then Blend using specific gravity and weight

Constants Used in Operations

Temperature and Mass Converters

Calculate how Long Reverse Osmosis system must operate to process batch blending water.

Hydrometer Calibration Temperature Offset Calculations and Blend by Weight and proof

Hydrometer Calibration Temperature Offset Calculations and Blend by SG & Weight

Hydrometer Calibration Temperature Offset Calculations and Blend by SG & Volume

Find how much time is required to Heat or Cool a mixture based on Btu's available.

Finding effect of .1 Proof change and .1 temperature change on proof gallons in a batch -Observe Table Accuracy

Using Weight and True Proof S.G. Equivalent to Find Proof Gallons and Wine Gallons Using Table 6

Table 61/10th Proof Volume Blending Linked to Table 4 Blending to show Difference in methods and rounding errors

Table 6Whole Proof Volume Blending Linked to Table 4 Blending to show Difference in methods and rounding errors

Showing Proof Gallons of Rounding Error if §186.61 Method of Table 1 use is applied incorrectly by comparing to §186.23 Procedures

Finding True Proof From Table 1 in the special case were Alcohol is at whole temperature and whole proof

Pearson's Square Used to Increase Proof of Mixture.Does Not Account for Shrinkage in Blending

Algebraic Blending Formulas.These Formulas do not account for shrinkage in Blending but can be useful

Thermal Correction Table for Hydrometer and overall correction to 60° F.Broad Spectrum Table

Blending Water - Time to Make using Reverse Osmosis System

ABS - Alcohol Blending Software Description of Work This work consists of a suite of computer algorithms programmed into Excel worksheets which relate to a data base of table values in order to calculate the relationships between density, temperature and alcohol concentration necessary for the measurement, blending and inventory management of alcohol by distilled spirits plants. The data base consists of values, based on excellent scientific work undertaken by the by the National Bureau of Standards on behalf of the United States Department of the Treasury.This research was published by the National Bureau of Standards in 1909.It was further developed into the gauging manual and tables by the Treasury in 1913. These values are published in the US Treasury Gauging Manual and represent the physical characteristics of alcohol at various concentrations.They allow for predictive calculations to be carried out in order to blend beverage alcohol for bottling. Use of the Treasury Gauging Manual tables to perform alcohol calculations is ubiquitous throughout the distilling industry. Any other algorithm an industry member would want to apply must conform very tightly to the blending tolerance limitations required and for the payment of excise tax on production. Users of this work would be:Distilled Spirits Plants or Distiller, Rectifier, Bottler installations or Bonded Warehouses under the jurisdiction of the United States Tax and Trade Bureau; formerly the Bureau of Alcohol Tobacco and Firearms. The first purpose of the work is to replicate the blending functions which distillers are accustomed to performing manually by automating the lookup of values in the tables and performing the required calculations. This feature of the work allows the user to see the alcohol properties and blending results of every basic function in an easy to use format so that no steps are skipped and reliability of the output is improved.To this end, this work employs several different methods of calculations to prove the same blending value in by different methods in order to verify the accuracy of the output values.

In many cases the answers to common questions are compiled in this work from tables different from those prescribed but which give more accurate answers that are consistent with the prescribed methods but allow for additional accuracy in the calculations.Usually this has involved using the data set to solve to 1/10^{th} proof accuracy or 1 part in 2,000 rather than to whole proof accuracy which is 1 part in 200.

The second purpose of this work is to allow the user to expand upon the basic blending functions in order to better control the transport, storage, blending and accounting for alcohol used in commercial alcohol production and blending operations.One way this is accomplished is to link blending and bottling spreadsheet together in order to cover these sequential tasks more easily.

In many of the new algorithms developed for this work the logic of the operations performed has been changed to allow for more opportunities to blend by volume rather than weight while still maintaining compliance with the prescribed blending tolerance.

In several cases the method applied in developing functions new functions was to find a way to use the table data to determine prospectively the effect of a change in blending procedures rather than to retrospectively find the value after it has occurred.Going from what did happen to what will happen.For example:The Gauging Manual defines a method of determining proof obscuration by evaporating and weighing out a quantity of solids contained therein. The new approach has been to determine from the batch size and the quantity of solids to be added what the effect will be prior to adding such solids. Thus proof obscuration can be predicted rather than just measured.

A third objective of the work is to utilize the data contained in the tables in new ways to account for overlooked aspects of the alcohol blending process and to discover new functions possible using the table values as the source of the transformations.For instance the tables allow one to calculate the exact shrinkage in volume resulting from the combining of alcohol and water.This knowledge is very useful in determining the source of losses in the processing account and in planning for the actual volume output of a bottling run.To perform this calculation is a simple task but is not made explicit by the regulations which demonstrate how the tables are to be employed.It is simply never mentioned.The distiller is left to figure it out for themselves.This software performs this calculation reliably each time a blending operation is entered into the program.In this way the results that can be obtained by looking up values in the Government tables have been expanded to convey more information about alcohol processing operations by making explicit the results which a simple manual lookup will not convey without further mathematical operations.

Examples of this type of work are.

Blending two alcohols in a ratio to achieve a target proof while keeping track of % alcohol by volume for standard of identity purposes.

Determining the proof and volume of a blend of two alcohols when no water is used in the blend.

Correcting the volume of a batch back from the idealized 60° F world of the gauging manual to the actual bottling temperature and determining the effect on volume and thus case and bottle output.

Making explicit the volume reduction which occurs when alcohol and water are blended together.

Linked sheets which blend a batch and then flow through to the bottling stage to integrate the blending and bottling functions.

Demonstrating blending tolerance in Proof Gallons as well as the weight and volume of those proof gallons for any blending batch.

Demonstrating blending tolerance in Wine Gallons as well as the weight and volume of those wine gallons for any blending batch.

Dissolved solids in water used in blending can affect the weight constant of water used when the batch is blended by weight.A method of determining the weight of such solids and their effect on volume has been developed in this work.

In the Increase Proof and Wine to Port application the algorithm uses two different tables to determine the amount of spirits needed to correct proof or how to fortify wine with brandy to make port or fortified wine. The method contained in the work allows the user to prospectively determine how much alcohol to add to a volume of wine in order to obtain a target proof or percent alcohol by volume.This method greatly improves upon the Pearson’s Square method because it accounts for the shrinkage in volume in blending wine and alochol together.

Correcting the volume of water to 60° when it is added to a batch at a different temperature.The Tables allow you to do this if you ask the question in the right way.This innovation is to make explicit that the volume of water needed in a batch varies with temperature.In adding water by volume, which most distillers do; water colder than 60° F. will require less volume because it is denser and takes up less space and consequently less volume will need be added than indicated by the blending tables.This is because the tables assume the water is always 60°.Conversely water added at temperatures above 60° will require less volume because it is thermally expanded and takes up more volume.Consequently one will add more gallons of water than required for the batch so that the volume will match the ideal blend temp of 60° contemplated by the tables.A 500 Gallon batch of water at 75° F is one gallon larger than the same batch at @ 60°F s this variation in volume has a direct effect on proof, blending tolerance, and final output.

Blending functions in this work are constructed so that the user can keep track of other components, liquids and solids that are added to batch in order to accurately reduce the amount of water required to reach a blend to a target proof and also stay within regulations of adding such components.

Several completely new functions have been developed in which specific gravity readings from a hydrometer are used instead of hydrometer proof readings to make calculations on alcohol being blended or inventoried.In order to accomplish this several new tables have been developed which translate specific gravity to proof prior to using proof to perform the blending or inventory functions.Specifically developed for this purpose is Table TSG.

Only Table 1 and Table 4 have 2,000 data points as published in order to blend to an accuracy of 1/10^{th} proof.Tables 5 and tables 6 are limited to 200 data points.The software has developed tables which expand the existing tables from 200 to 2,000 data points to allow for accurate blending to 1/10^{th} proof for all blending functions. This allows functions that previously could only be carried out at whole proofs to be carried out at 1/10th proof accuracy with a consequent reduction in rounding errors and uncertaintly.