Oh man, my spreadsheets are a horrible spaghetti tangle of cross-references. The gauge and tank transfer records are probably the easiest to follow and have all my favorite formulas in them. IFERROR(), INDIRECT(), and CONCATENATE() are worth looking into. Also, conditional formatting is your friend. I then have sheets for all the transaction types in all the accounts - receipt, removal, packaging, bottling, batch, etc. Each account has a master sheet that summarizes everything so that I literally just copy everything down onto the tax record. Really need to get around to figuring out PDF export...
Gauge - Data is entered on monthly tabs and summarized on a master page. Harder to get lost in hundreds of gauges.
Tank - Data is entered on a master page and tracked on individual tank tabs. Makes transfers between tanks easy to enter, and to confirm that every movement has actually been entered correctly. I just caught a typo yesterday when rum left a tank that it never entered.
Removed - Our state tax rate is on a per-product basis based on state minimum price, so that and proof are on a lookup table. Removals are entered on a master page, with monthly summary tabs for taxes. We only serve the taproom so there's no customer breakdown yet. Products are easy to add just by giving them a unique name and extending the lookup table and adding a column to the monthly tabs.
I have used this spreadsheet for 3 years, I have one spreadsheet archived for every month I have been open.
I am slowly moving to a microsoft database because adding a new product becomes very onerous... you may see that I only have tabs for neutral, vodka and whisky, I never bothered to add entries for rum or brandy or such...
Tab 1 is for my State, Florida.. (and only for the current month) the left side is what info the state needs, but per product so I can fill it out, the right side is laid out like the state records I have to fill out..
The other three tabs are the federal forms, and they cover a whole year.. down below the forms are some tally areas that I find helpful...
This is my sheet that I use to print out my daily record forms... I make changes fairly often...
I print to pre-punched 3-hole paper and keep in the distillery, when filled out keep a three ring binder for every 6-12 months.. each 'action' has a date ant time and these become my unique identifier for my database entry
Coth, have you made any updates in the fed formula's recently they haven't talked about online at the DSP site? I have imaged the new report forms, and working on feed them from the data base. Your sheets made the task fairly easy once I got to the point I could work again. I did go ahead and pick up the other spirit types - if you don't mind, I am going top consult your daily logs for any new tips I am not considering..
am not aware of fed changes... I do see that state of FL warned me not to start any forms early for next month as they were making changes... play with them at will, nothing proprietary and willing to share
@CothermanDistilling said:
am not aware of fed changes... I do see that state of FL warned me not to start any forms early for next month as they were making changes... play with them at will, nothing proprietary and willing to share
Thanks, Coth - I was really ill and then some losses in the immediate family in 2016, so I was idle a very long time. But things are really running good now.
Heya Smaug and thanks... I check here a couple times a day and read thru, but I am full arse heads down workin on one thing or another around the ranch and development bench for the upcoming release at the turn of the quarter.
Comments
@crozdog requested a copy of the blending spreadsheet (XLSX) which is as below.
tnx
I'm learning Excel formulae as fast as I can, before the tax calculations kill me.
Zymurgy Bob, a simple potstiller
my book, Making Fine Spirits
Oh man, my spreadsheets are a horrible spaghetti tangle of cross-references. The gauge and tank transfer records are probably the easiest to follow and have all my favorite formulas in them. IFERROR(), INDIRECT(), and CONCATENATE() are worth looking into. Also, conditional formatting is your friend. I then have sheets for all the transaction types in all the accounts - receipt, removal, packaging, bottling, batch, etc. Each account has a master sheet that summarizes everything so that I literally just copy everything down onto the tax record. Really need to get around to figuring out PDF export...
Gauge - Data is entered on monthly tabs and summarized on a master page. Harder to get lost in hundreds of gauges.
Tank - Data is entered on a master page and tracked on individual tank tabs. Makes transfers between tanks easy to enter, and to confirm that every movement has actually been entered correctly. I just caught a typo yesterday when rum left a tank that it never entered.
Removed - Our state tax rate is on a per-product basis based on state minimum price, so that and proof are on a lookup table. Removals are entered on a master page, with monthly summary tabs for taxes. We only serve the taproom so there's no customer breakdown yet. Products are easy to add just by giving them a unique name and extending the lookup table and adding a column to the monthly tabs.
Got 'em, and thanks RobertS.
Zymurgy Bob, a simple potstiller
my book, Making Fine Spirits
Brilliant thanks
I have used this spreadsheet for 3 years, I have one spreadsheet archived for every month I have been open.
I am slowly moving to a microsoft database because adding a new product becomes very onerous... you may see that I only have tabs for neutral, vodka and whisky, I never bothered to add entries for rum or brandy or such...
Tab 1 is for my State, Florida.. (and only for the current month) the left side is what info the state needs, but per product so I can fill it out, the right side is laid out like the state records I have to fill out..
The other three tabs are the federal forms, and they cover a whole year.. down below the forms are some tally areas that I find helpful...
ABT calculations 2017 07 (XLS)
This is my sheet that I use to print out my daily record forms... I make changes fairly often...
I print to pre-punched 3-hole paper and keep in the distillery, when filled out keep a three ring binder for every 6-12 months.. each 'action' has a date ant time and these become my unique identifier for my database entry
CDC Production Template (XLSX)
Coth, have you made any updates in the fed formula's recently they haven't talked about online at the DSP site? I have imaged the new report forms, and working on feed them from the data base. Your sheets made the task fairly easy once I got to the point I could work again. I did go ahead and pick up the other spirit types - if you don't mind, I am going top consult your daily logs for any new tips I am not considering..
Hobby Distillers Association Member - Join us today!
The only sillae question is the one you don't ask folks...
am not aware of fed changes... I do see that state of FL warned me not to start any forms early for next month as they were making changes... play with them at will, nothing proprietary and willing to share
For pot stillers', attached is useful spread sheet.
Potstill-calculator1c (XLSX
Thanks, Coth - I was really ill and then some losses in the immediate family in 2016, so I was idle a very long time. But things are really running good now.
Hobby Distillers Association Member - Join us today!
The only sillae question is the one you don't ask folks...
Damn, Trak, I'm glad to hear you're back among us. Good job!
Zymurgy Bob, a simple potstiller
my book, Making Fine Spirits
Thanks Zb... I learned at the age of 5, get up, dust yaself off, tighten the cinch, and saddle up again...
Hobby Distillers Association Member - Join us today!
The only sillae question is the one you don't ask folks...
Yeah good to see you back.
StillDragon North America - Your StillDragon® Distributor for North America
Heya Smaug and thanks... I check here a couple times a day and read thru, but I am full arse heads down workin on one thing or another around the ranch and development bench for the upcoming release at the turn of the quarter.
Hobby Distillers Association Member - Join us today!
The only sillae question is the one you don't ask folks...