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 concentrations necessary for the measurement, blending and inventory management of alcohol by distilled spirits plants.
The data base consists of values, based on 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. In the United States 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. These features 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 improving and modifying the data set solve to 1/10th proof accuracy or 5 parts in 10,000 ( which is actually 2.5 parts per 10,000 since one is never more than that quantity away from a 1/10th proof value.
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.
1.Blending two alcohols in a ratio to achieve a target proof while keeping track of % alcohol by volume for standard of identity purposes.
2.Determining the proof and volume of a blend of two alcohols when no water is used in the blend.
3.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.
4.Making explicit the volume reduction which occurs when alcohol and water are blended together.
5.Linked sheets which blend a batch and then flow through to the bottling stage to integrate the blending and bottling functions.
6.Demonstrating blending tolerance in Proof Gallons as well as the weight and volume of those proof gallons for any blending batch.
7.Demonstrating blending tolerance in Wine Gallons as well as the weight and volume of those wine gallons for any blending batch.
8.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.
9.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.
10.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.
11.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.
12.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.